INDEX:
A
database index
is a
data structure
that improves the speed of data retrieval operations on
a
database table
. Indexes are used to quickly locate data without having to search every row in a
database table every time a database table is accessed. Indexes can be created using one or
more
columns of a database table
, providing the basis for both rapid random
lookups
and efficient
access of ordered records.
Indexes are used to retrieve data from the database very fast. The users cannot
see the indexes, they are just used to speed up searches/queries.
CREATE INDEX Syntax
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX
index_name
ON
table_name
(
column1
,
column2
, ...);
CREATE
INDEX
idx_pname
ON
Persons (LastName, FirstName);
DROP:
DROP
INDEX
index_name
;
Creating a new index
– show you how to use the
CREATE INDEX
statement to create
an index for one or more columns in a table.
Removing an index
– learn how to remove an existing index by using the
DROP
INDEX
statement.
Unique index
– use unique indexes to enforce the uniqueness of values in the index
column or columns.
Function-based index
– speed up queries that involve expression which consists of
functions.
Bitmap index
– use bitmap index on a column or columns that have few distinct
values, or low cardinality.
Creating indexes on columns in WHERE clauses or
JOINs is a good idea.
If a column has a large percentage of unique
records, a b-tree index is usually more suitable.
If a column has a low percentage of unique records,
a bitmap index is usually better.
Consider creating an index on foreign key columns
(which are often used in joins anyway), as they are
not created automatically (in Oracle anyway).
Consider creating a function-based index if the
column in your WHERE clause uses a function or
expression in the criteria.
