•
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)
product_name varchar2(30)
);
Alter table products add constraint products_pk primary key(product_id);
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));
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.
