Oracle – Indexes

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.

Index Types

  • Unique Index
    • Primary Key
    • Unique Key
  • B-Tree
  • Bitmap
  • Function-Based

Unique

Oracle create unique index for Primary key and unique key constraints.

B-Tree Indexes vs Bitmap Indexes

B-Tree IndexBitmap Index
Good for high-cardinality dataGood for low-cardinality data
Good for OLTP databases (lots of updating)Good for data warehousing applications
Use a large amount of spaceUse relatively little space
Easy to updateDifficult to update

B-Tree Indexes

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;

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.

-- 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.

References

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s