||Oracle Database Tips by Donald Burleson
The same format is used no matter which type of
index you are creating.
unique|bitmap index <index name> on
<table name> (col1, col2, col3,…);
A unique index was covered in the discussion of
constraints as well as uses to both speed queries and enforce that
each value in the column is unique.
index auth_contract on author(author_contract_nbr);
index bk_auth on
If you do not define the type of index, the
database creates a non-unique b-tree index.
If you decide that you want to change an index,
from unique to non-unique for example, you have to drop the current
index and recreate it in the new type. You can rebuild indexes using
the ALTER INDEX command. When you rebuild an index, the database uses
the current index if it is valid to create the new index. Once the
new index is created, the old index is dropped.
If you specify online, the database will continue
to use the old index until the new one is completed and then switches
to the new index. You can also use the rebuild to move the index to
bk_auth rebuild online;
alter index bk_auth rebuild tablespace indx_ts;
If the current index is not valid, then the
database rebuilds the index from the original table. Invalid indexes
are not used. An index becomes invalid when the rowids in the
index no longer match the rowids in the table. If I rebuild a
table, all the indexes created on that table will be marked invalid
and must be rebuilt.
As a last point on indexes, you must be careful in
indexing your database. Each index adds overhead, having to be
updated with every change to that column in the table. If your
database uses static data and only select queries, then over indexing
will not hurt performance. But, most databases have a mix of INSERTs,
UPDATEs, DELETEs and SELECTs. Too many indexes can have a significant
negative impact on INSERTs, UPDATEs, and DELETEs.
There are many other objects in the Oracle
database, but we are focusing on those that impact SQL. Most of the
other objects belong in the realm of the DBA and are beyond the scope
of this book.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.