Create Oracle indexes

 

This exercise will give you an opportunity to see how the SQL optimizer accesses indexes to generate an efficient access plan.

 

Procedure

Your assignment involves creating these indexes in your pubs schema and noting how they change the execution plan for your SQL.  Because the pubs database is so small, the cost-based optimizer will choose full-table scans because it knows that the tables reside on only a few database blocks.  Hence, we will force the use of the indexes with an index hint inside the SQL statement.

 

Step 1 – Save these SQL queries as test_idx.sql in your c: directory

 

select

   initcap(store_name),

   store_state

from

  store

where

   lower(store_name) = lower('Borders')

;

 

Step 2 – Run this script and save the execution plan for the queries.

 

Step 3 – Create two indexes on the store table, as follows.

 

 

 

 

 

Above, we create a bitmap index on store_state.  Because there are only 50 distinct column values, we can use a bitmap index.

 

We also create a function-based index on store_name because out SQL is transforming the column using the lower BIF.

 

Step 4 – Re-run the query in test_idx.sql and note all changes to the execution plan.