Oracle Indexes

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.

Index Types

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.

Index Guidelines

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
l_index_ddl varchar2(1000);
l_used_bytes number;
l_allocated_bytes number;
dbms_space.create_index_cost (
ddl=>'create index persons_idx on test01.emp(emp_id)',
dbms_output.put_line ('used = ' || l_used_bytes || ' bytes'
|| ' allocated = ' || l_allocated_bytes || ' bytes');

used - shows the number of bytes that the index data actually represents
allocated - show the number of bytes the index will take up in the tablespace when you actual create it   

Oracle Index schemes

B-Tree Indexes vs Bitmap Indexes

B-tree Index
Bitmap Index
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

B*Tree Indexes

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;
alter index sales_idx rebuild compute statistics;

Note: it is worth doing this regularly

Rebuilding online alter index sales_idx rebuild online;
Index Usage

alter index p_key_sales monitoring usage;

select * from v$object_usage where index_name='p_key_sales';

# to turn it off
alter index p_key_sales nomonitoring usage;

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;
Useful Views
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 ANALYZE INDEX ... VALIDATE STRUCTURE statement

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

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

Value/Row 1 2 3 4 5 6 7 8 9 10 11 12 13 14
Analyst 0 0 0 0 0 0 0 1 1 1 0 0 1 0
Clerk 1 0 0 0 0 0 0 0 0 0 1 1 0 1
Manager 0 0 0 1 0 1 1 0 0 0 0 0 0 0
President 0 0 0 0 0 0 0 0 1 0 0 0 0 0
Saleman 0 1 1 0 1 0 0 0 0 0 0 0 0 0

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));

Partition Indexes

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)
global partition by hash (c1,c2)
(partition p1 tablespace tbs_1,
partition p2 tablespace tbs_2,
partition p3 tablespace tbs_3,
partition p4 tablespace tbs_4);

Create Local Index create index ticket_no_idx on ticket_sales(ticket_no) local tablespace index01;