Create Oracle indexes
exercise will give you an opportunity to see
how the SQL optimizer accesses indexes to
generate an efficient access plan.
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.
– Save these SQL queries as test_idx.sql in
your c: directory
lower(store_name) = lower('Borders')
– Run this script and save the execution
plan for the queries.
– Create two indexes on the store table, as
create bitmap index
( store_state );
create a bitmap index on store_state.
Because there are only 50 distinct column
values, we can use a bitmap index.
create a function-based index on store_name
because out SQL is transforming the column
using the lower BIF.
– Re-run the query in test_idx.sql and note
all changes to the execution plan.