An index is an object in the database that allows you to find specific data in a table faster. You don’t need to ask Oracle to use an index if it’s already there. Most of the time, Oracle will work out that it needs to use it.
- Unique Index
- Primary Key
- Unique Key
Oracle create unique index for Primary key and unique key constraints.
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|
The B*Tree index has several sub types. Some of them are,
- Index Organized Tables – A table stored in a B*Tree structure
- 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.
There are only a few things you need to specify to create a b-tree index:
- index_name: The name of the new index you’re creating, which must be a unique name.
- table_name: The name of the table you want to create the index for.
- columns: A single column name, or a comma-separated list of the columns you want to create the index on.
-- Syntax -- ======= CREATE INDEX index_name ON table_name (columns); -- Example -- ======== CREATE INDEX emp_id_idx ON emps(employee_id); -- Syntax - Reverse Index -- ======================= CREATE INDEX index_name ON table_name (column_name) REVERSE; -- Example - Reverse Index -- ======================== CREATE INDEX emp_fn_ln_ridx ON emps (firstname, lastname) REVERSE; -- Syntax - Composite Index -- ========================= CREATE INDEX index_name ON table_name (col1, col2); -- Example - Composite Index -- ========================== CREATE INDEX emp_fn_ln_cidx ON emps (firstname, lastname); -- Syntax - Index Organized Tables -- ================================ CREATE TABLE table_name (id INTEGER PRIMARY KEY, value VARCHAR2(50)) SEGMENT CREATION IMMEDIATE -- This Line Must from version 11g. ORGANIZATION INDEX;
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.
-- Syntax -- ======= CREATE BITMAP INDEX index_name ON table_name (columns); -- Example -- ======== CREATE BITMAP INDEX emp_id_bm_idx ON emps(employee_id);
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.
-- Syntax -- ======= CREATE INDEX index_name ON table_name(function(column_name)); -- Example -- ======== CREATE INDEX emp_sal_fn_idx ON employee(salary * 12);
Alter – Rename, Rebuild Indexes
-- Rename -- ======= ALTER INDEX emp_sal_fn_idx RENAME TO employee_sal_fn_idx; -- Rebuild -- ======== ALTER INDEX employee_sal_fn_idx REBUILD COMPUTE STATISTICS;
Disable, Drop Indexes
-- Disable -- ======== ALTER INDEX index_name ON table_name DISABLE; -- Rebuild (Enable) -- ================= ALTER TABLE index_name ON table_name REBUILD; -- Drop -- ===== DROP INDEX index_name;
Cardinality vs Selectivity
Cardinality and selectivity are two terms that are often used in the SQL world when working with indexes.
- Cardinality is the number of distinct values in a particular column.
- Selectivity is the ratio of cardinality on an indexed column to the total number of rows in a table.
Selectivity = Cardinality/Total_Rows. A selectivity value of a query closer to 1 is said to have high selectivity, and closer to 0 is called low selectivity. If a query has a high selectivity, then it is normally fast to select using an index. If a query has low selectivity, it is often not worth the time to use the index in the query, and Oracle may not use it.