 |
|
Oracle Concepts by Burleson
Consulting |
Create Oracle Indexes
Description
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 sample database is so small, the
cost-based optimizer will choose full-table scans because it knows
that the tables reside on only a few Oracle 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:\Burleson 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.
Evaluation
Submit the execution plan listing (with and
without indexes), and submit a one paragraph summary of the index
usage to you instructor by due date.
ANSWERS
WITHOUT INDEXES
SQL> select
2 initcap(store_name),
3 store_state
4 from
5 store
6 where
7 lower(store_name) = lower('Borders')
8 ;
INITCAP(STORE_NAME)
ST
---------------------------------------- --
Borders
NC
Elapsed:
00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS
(Cost=1 Card=1 Bytes=18)
1 0 TABLE ACCESS (FULL) OF 'STORE'
(Cost=1 Card=1 Bytes=18)
SQL>
SQL> select
2 initcap(store_name)
3 from
4 store
5 where
6 store_state = 'NC';
INITCAP(STORE_NAME)
----------------------------------------
Barnes And Noble
Borders
Quagmire Books
Wee Bee Books
Books For Dummies
Specialty Bookstore
Blue Ride Booksellers
Ignoramus And Dufus
8 rows
selected.
Elapsed:
00:00:00.00
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1
Card=5 Bytes=90)
1 0 TABLE ACCESS (FULL) OF 'STORE'
(Cost=1 Card=5 Bytes=90)
WITH INDEXES
SQL> select
2 initcap(store_name),
3 store_state
4 from
5 store
6 where
7 lower(store_name) = lower('Borders')
8 ;
INITCAP(STORE_NAME)
ST
---------------------------------------- --
Borders
NC
Elapsed: 00:00:00.00
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS
(Cost=60 Card=1 Bytes=18)
1 0 VIEW OF 'index$_join$_001'
(Cost=60 Card=1 Bytes=18)
2 1 HASH JOIN
3 2 INDEX
(FAST FULL SCAN) OF 'STORE_NAME_IDX' (NON-UNIQUE) (Cost=33 Card=1
Bytes=18)
4 2 BITMAP
CONVERSION (TO ROWIDS)
5 4
BITMAP INDEX (FULL SCAN) OF 'STORE_STATE_IDX'
SQL>
SQL> select
2 initcap(store_name)
3 from
4 store
5 where
6 store_state =
'NC';
Book Report
INITCAP(STORE_NAME)
----------------------------------------
Barnes And Noble
Borders
Quagmire Books
Wee Bee Books
Books For Dummies
Specialty Bookstore
Blue Ride Booksellers
Ignoramus And Dufus
8 rows
selected.
Elapsed:
00:00:00.00
Execution
Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=17 Card=5 Bytes=90)
1 0 TABLE ACCESS (BY INDEX ROWID) OF
'STORE' (Cost=17 Card=5 Bytes=90)
2 1 BITMAP CONVERSION (TO
ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'STORE_STATE_IDX'
* Temporary tables were formally introduced as
global temporary tables in Oracle8i. Temporary tables can be
used to significantly improve SQL query speed.
* Oracle materialized views can be used to
pre-aggregate data and pre-join tables into summary tables. The
Oracle database will automatically detect when these pre-summarized
tables can be used, and re-write the SQL to reference the SQL.
* Indexes are created exclusively to improve
the performance of SQL statements. Most relational databases
provide b-tree, bitmap, and function-based indexes to ensure that SQL
never performs unnecessary large-table full-table scans.
We are now ready to move into more advanced
SQL topics and examine extensions to SQL and how they are used to
improve the capabilities of SQL.
For more
details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
