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 if you need to access no more than 10-15% of the data in the table. A full table scan (read all the table) is better if you intend to retrieve a high percentage of the table data, this is because a index read requires two reads
  • Do not use indexes on small tables, a full table scan would be fine.
  • Create primary keys for all tables as a index will be created by default.
  • Index the columns that are involved in multi-table join operations
  • Index columns that are used frequently in where clauses.
  • Index columns that are involved in order by, group by, union and distinct operations.
  • Columns that have long character strings are bad for indexing
  • Columns that are frequently update are bad for indexing
  • Choose tables where few rows have similar values
  • Keep the number of indexes small, to many will cause performance problems on inserting data.
Index size estimation
Just like oracle tables you can estimate the size of an index using the DBMS_SPACE package.
estimating set serveroutput on
declare
l_index_ddl varchar2(1000);
l_used_bytes number;
l_allocated_bytes number;
begin
dbms_space.create_index_cost (
ddl=>'create index persons_idx on test01.emp(emp_id)',
used_bytes=>l_used_bytes,
alloc_bytes=>l_allocated_bytes);
dbms_output.put_line ('used = ' || l_used_bytes || ' bytes'
|| ' allocated = ' || l_allocated_bytes || ' bytes');
end;
/

Note:
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 - These are the common indexes in Oracle. They are similar construct to a binary tree, they provide fast access by key, to an individual row or range of rows, normally requiring very few reads to find the correct row. The B*Tree index has several subtypes
    • Index Organized Tables - A table stored in a B*Tree structure (see Index organized Tables)
    • B*Tree Cluster Indexes - They are used to index the cluster keys (see Cluster Indexes)
    • Reverse Key Indexes - The bytes in the key are reversed.This is used to stop sequence keys being on the same block like 999001, 999002, 999003 would be reversed to 100999, 200999, 300999 thus these would be located on different blocks.
    • Descending Indexes - They allow data to be sorted from big to small (descending) instead of small to big (ascending).
  • Bitmap Indexes - With a bitmap index , a single index entry uses a bitmap to point to many rows simultaneously, they are used with low data that is mostly read-only. They should not be used with OLTP systems.
  • Function Based Indexes - These are B*Tree or bitmap indexes that store the computed result of a function on a row(s) - not the column data itself.
  • Partition Indexes - These indexes are used to index partitioned tables, there are two types local and global.
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;
Renaming alter index test_idx rename to test99_idx;
Coalescing alter index test_idx coalesce;
Note: this is a quick and dirty rebuild
Rebuilding 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
v$OBJECT_USAGE 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:
  • They are easy to implement and provide immediate value
  • They can be used to speed up existing applications without changing the application code or queries.
There is some work that needs to be done before you can use Function-based indexes
  • You must have the privilege QUERY REWRITE to create function-based indexes on tables in your own schema
  • You must have the privilege GLOBAL QUERY REWRITE to create function-based indexes on tables in your own schema
  • Function-based indexes only use the cost based optimizer (CBO) and never the rule base optimizer (RBO)
  • Use SUBSTR to constrain return values from user written functions that return VARCHAR2 or RAW types. Optionally hide the SUBSTR in a view (preferred).
  • For the optimizer to use function-based indexes, the following session or system variables must be set:
    • QUERY_REWRITE_ENABLED=TRUE (means allow optimizer to rewrite the query to use function-based index)
    • QUERY_REWRITE_INTEGRITY=TRUSTED (means that the optimizer should trust the code of the programmer
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
  • Local - Indexes are partitioned against the partitions, so for an example if a table is partitioned 12 ways using a range then there will be 12 indexes one for each partition, so they have a one-to-one relationship.
  • Global - Global indexes have no such relationship, but can be either partitioned or non-partitioned
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;