PL/SQL – indexes

Indexes are used for:
* create primary key constraint
* create unique constraint
* sort results
* filter out results
* join datasets
* more…

Oracle as a lookup field uses it’s pseudocolumn rowid. This column is not visible by default, but every row has got it’s rowid. It is globally unique.
select rowid, hr.countries.* from hr.countries;

Types of indexes:
* B*Tree index – balanced tree
This index is very efficient when you try to select up to 4% of total numer of rows in the table. If your where clause selects more rows it’s faster to do full table scan.
Nulls are not handled in this index, so a query ‘… where name is null’ is executed without using index – full table scan.
Create B*Tree index when there are many rows in the table, cardinality (number of distinct values) in a column is high and column is used in join or where clause.

* Bitmap index
This index should be used when column’s cardinality is small (i.e. sex, days in year), column is used in boolean algebra (AND, OR, NOT, ==, !=) and the number of rows is high.

create index JOB_HISTORY_START_DATE_IX on hr.job_history (start_date); -- default nonunique B*Tree index
create unique index JOB_HISTORY_JOB_ID_IX on hr.job_history (job_id); 
create bitmap index JOB_HISTORY_DEP_ID_IX on hr.job_history (department_id); -- bitmap indexes available in enterprise edition
create index JOB_HISTORY_DATES_IX on hr.job_history (start_date, end_date); -- composite index on two columns
create index PRODUCT_TOTAL_IX on hr.product (price * qty); -- function based index
drop index JOB_HISTORY_START_DATE_IX; -- deleting the index

Leave a Reply

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

You are commenting using your 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