Indexes are used for:
* create primary key constraint
* create unique constraint
* sort results
* filter out results
* join datasets
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