Oracle indexes provides faster access to table rows by storing sorted values in specific columns and using those sorted values to easily lookup the associated table rows. This means that you can lookup data without having to look at more than a small fraction of the total rows within the table, they are completely optional.
The trade off is that you get faster retrieval but inserting data is slower as the index needs to be updated, this slower inserting is bad news for OLTP type of databases but in a data warehouse type of database where inserting is at a minimal indexes can be used heavily.
|Unique/non-Unique||Based on unique column, something like national insurance number. It is better to use unique constraints on a tables columns which means oracle will create a unique index on those columns.|
|Primary/Secondary||Primary indexes are unique indexes that must always have a value, they cannot be NULL. Secondary indexes are other indexes in the same table that may not be unique.|
|Composite (concatenated)||Indexes that contain two or more columns from the same table, they are useful for enforcing uniqueness in a tables column where there's no single column that can uniquely identify a row.|
Here are some guidelines from creating efficient indexes:
Index size estimation
Just like oracle tables you can estimate the size of an index using the DBMS_SPACE package.
set serveroutput on
Oracle Index schemes
B-Tree Indexes vs Bitmap Indexes
|Good for high-cardinality data||Good for low-cardinality data|
|Good for OLTP databases (lots of updating)||Good for data warehousing applications|
|Use a large amount of space||Use relatively little space|
|Easy to update||Difficult to update|
In general B*Tree index would be placed on columns that were frequently used in the predicate of a query and expect some small fraction of the data from the table to be returned. It's purely a function on how large of a percentage of the table you will need to access via the index and how the data happens to be laid out. If you can use the index to answer the question accessing a large percentage of the rows makes sense, since you are avoiding the extra scattered I/O to read the table. If you use the index to access the table you will need to ensure you are processing a small percentage of the total table.
Reserve key indexes are the same as B-tree indexes except that the byes of the key column data are reserved during indexing, the column order is kept intact only the bytes are reserved. A big advantage is that data is distributed evenly which avoid hot spots within the index.
|Creating||create index employee_id on employee(employee_id) tablespace index01;|
|Creating reverse Key||create index reserve_idx on employee(emp_id) reserve;|
|Removing||drop index test_idx;|
|Moving||alter index test_idx rebuild tablespace index01;|
alter index test_idx rename to test99_idx;
alter index test_idx coalesce;
Note: this is a quick and dirty rebuild
alter index sales_idx rebuild;
Note: it is worth doing this regularly
|Rebuilding online||alter index sales_idx rebuild online;|
alter index p_key_sales monitoring usage;
select * from v$object_usage where index_name='p_key_sales';
Note: use this command to confirm if a index is being used
|Display indexed columns for a table||column column_name format a50;
select index_name, table_name, column_name from user_ind_columns;
|DBA_INDEXES||describes all indexes in the database|
|DBA_IND_COLUMNS||describes the columns of all the indexes on all tables and clusters in the database|
|DB_IND_EXPRESSONS||lists expressions of function-based indexes on all tables and clusters in the database|
|INDEX_STATS||stores information from the last
You can use this view to monitor index usage. The view displays statistics about index usage gathered from the database. All indexes that have been used at least once can be monitored and displayed in this view.
Bitmap indexes are structures that store pointers to many rows with a single index key entry. In a bitmap index there will be a very small number of index entries, each of which point to many rows. Bitmap indexes are best used on low cardinality data, this is were the number of distinct items in the set of rows divided by the number of rows is a small number for example a gender column may have MF, F and NULL. If you had a table with 20000 rows you would find that 3/20000 = 0.00015, this would be an ideal candidate for a bitmap index.
Remember also that a single bitmap entry key points to many rows. If a session modifies the index then all of the rows that the index points to are effectively locked. Oracle cannot lock an individual bit in a bitmap index entry; it locks the entire bitmap, this will seriously inhibit concurrency as each update will appear to lock potentially hundreds of rows preventing their bitmap columns from being concurrently updated.
An Oracle bitmap index would look like
Using the above table you can see that rows 1, 4, 6, 7, 11, 12 and 14 would represent a manager and clerk.
|Creating||create bitmap index job_desc_idx on employee(job_desc) tablespace index01;|
Function Based Indexes
Function-Based indexes give the ability to index computed columns and use theses indexes in a query, it allows you to have case insensitive searches or sorts, search complex equations and to extends the SQL language by implementing your own functions and operators and then searching on them. The main reason to use them are:
There is some work that needs to be done before you can use Function-based indexes
You can enable the above with the ALTER SESSION or at the system level via ALTER SYSTEM or by setting them in in the init.ora file. Function-based indexes will affect the performance of inserts and updates, if you insert or update frequently this may not be an option for you.
|Creating||create index lastname_idx on employee(LOWER(l_name));|
There are two types of index global and local
There is substantial maintenance involved when using globally partitioned indexes, when ever there is DDL (update, add, coalesce, exchange, merge, move, split or truncate) against the table the whole index has to be rebuilt as the DDL will mark the index unusable. To stop the DDL marking the table unusable you need to include the UPDATE GLOBAL INDEX statement otherwise the entire index will be invalidated. You can also hash the index which will reduce high contention of the leaf blocks. Local indexes have a one-to-one relationship, if any DDL activity is used against the partition only the index for that partition is invalidated, oracle will also automatically rebuild the partition, these are the big advantages to using local partition indexing.
|Create Global index||create index ticket_sales_idx on ticket_sales(month)
global partition by range(month)
(partition ticketsales1_idx values less than (3)
partition ticketsales2_idx values less than (6)
partition ticketsales3_idx values less than (9)
partition ticketsales4_idx values less than (maxvalue);
|Create Global hash Index||
create index hg_idx on tab (c1,c2,c3)
|Create Local Index||create index ticket_no_idx on ticket_sales(ticket_no) local tablespace index01;|