|
|
|
Oracle:
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.
create bitmap index
Store_state_idx
on
store
( store_state );
create index
store_name_idx
on
store
lower(store_name);
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.
|
|
|
|
|
|
