12. INDEX

•          An index helps speed up retrieval.

•          Improves the performance of some queries

•          A column that is frequently referred to in the WHERE clause is a potential candidate for indexing.

Why use indexing?

•          It's easy and provides immediate value

•          It can be used to speed up existing applications without changing any of their logic or queries

Index Creation Guidelines

Create an index when:

-A column contains a wide range of values

-A column contains a large number of null values

-One or more columns are frequently used together in a WHERE clause or a join condition

-The table is large and most queries are expected to retrieve less than 2% to 4% of the rows in the table

-Do not create an index when:

-The columns are not often used as a condition in the query

-The table is small or most queries are expected to retrieve more than 2% to 4% of the rows in the table

-The table is updated frequently

Types of Indexes

IMPLICIT INDEXES

   B-Tree Intex

      Implicit indexes on a table are created whenever a primary key, unique constraint is created on the table. 

    create table products

    (

      product_id       number,
  product_name   varchar2(30)

    );
                      
Alter table products add constraint products_pk primary key(product_id);

Bitmap indexes

•          Bitmap indexes - Each bit in the bitmap corresponds to a possible rowid, and if the bit is set, it means that the row with the corresponding rowid contains the key value

•          In a bitmap index, the database stores a bitmap for each index key. In a conventional B-tree index, one index entry points to a single row. In a bitmap index, each index key stores pointers to multiple rows.

CREATE BITMAP INDEX Typeid_i

   ON Books (SYS_TYPEID (Author));

CREATE BITMAP INDEX sales_cust_gender_bjix ON sales(customers.cust_gender) FROM sales, customers WHERE sales.cust_id = customers.cust_id LOCAL;

SELECT sales.time_id, customers.cust_gender, sales.amount FROM sales, customers WHERE sales.cust_id = customers.cust_id;

Partitioned indexes

•          Partitioned indexes - Contain an entry for each value that appears in the indexed column(s) of the table

CREATE TABLE costs_demo (
   prod_id     NUMBER(6),    time_id     DATE,
   unit_cost   NUMBER(10,2), unit_price  NUMBER(10,2))
  
PARTITION BY RANGE (time_id)
   (PARTITION costs_old
       VALUES LESS THAN (TO_DATE('01-JAN-2003', 'DD-MON-YYYY')) COMPRESS,
    PARTITION costs_q1_2003
       VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
    PARTITION costs_q2_2003
       VALUES LESS THAN (TO_DATE('01-JUN-2003', 'DD-MON-YYYY')),
    PARTITION costs_recent VALUES LESS THAN (MAXVALUE));

CREATE INDEX i_cost1 ON costs_demo (prod_id) COMPRESS LOCAL (PARTITION costs_old, PARTITION costs_q1_2003, PARTITION costs_q2_2003, PARTITION costs_recent NOCOMPRESS);

Function-based indexes

•          Function-based indexes - Based on expressions. They enable user to construct queries that evaluate the value returned by an expression (can use built-in or custom functions)

CREATE INDEX Income_ix  ON Employees (Salary +

     (Salary*Commission_pct));

Domain indexes

•          Domain indexes -application-specific index (ie. XML)

CREATE TABLE Employees (name VARCHAR2(64), id INTEGER, resume VARCHAR2(2000));

CREATE INDEX ResumeIndex ON Employees(resume) INDEXTYPE IS TextIndexType;

SELECT * FROM Employees WHERE Contains(resume, 'Oracle') =1;

Removing an Index

•          Remove an index from the data dictionary by using the DROP INDEX command:

•          DROP INDEX index;

•          Remove the UPPER_LAST_NAME_IDX index from the data dictionary:

•          DROP INDEX emp_last_name_idx;

•          Index dropped.

•          To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege.