 |
|
Oracle Tips by Burleson |
Check Execution Plans for
Bitmap and Tree Indexes
In this exercise you will create several
Oracle indexes and then show how the indexes storage parameters are
different from b-tree and bitmap indexes. The student will also run
sample SQL queries against a test table and see differences in the
execution plan (and index usage) for the queries.
We will use our sample database for this
exercise and test the execution plans for SQL using a traditional
b-tree index versus a bitmap index.
For our test, we will create two indexes on
the book_type column of the book table. As we know, we need to start
by checking the cardinality of the column, and we do this by counting
the number of distinct values in the column:
select
book_type,
count(*),
(select count(*) from book) num_rows
from
book
group by
book_type
order by
count(*) desc;
Here we see that the book_type is a low-cardinality column with
only five distinct values.
BOOK_TYPE
COUNT(*) NUM_ROWS
------------------------------ ---------- ----------
miscellaneous 6
20 computer
5 20
fiction 4 20
management 4 20
music 1 20
Since we only have 20 rows in the book table,
the cost-based optimizer will detect that the entire table resides on
a single database block, and never use an index when the book table is
accessed. However, we can force the use of an index with an index
hint.
Let’s start by creating two indexes on the
book table, one using a b-tree on book_title and the other using a
bitmap index on book_type:
create
bitmap index
bitmap_book_type
on
book (book_type);
create index
btree_title_type
on
book (book_title);
You assignment is to force the use of each
index by modifying this SQL. You will need to use SQL hints to force
the desired index and verify the execution plan for your SQL.
select
book_title,
book_type,
book_retail_price
from
book
where
book_title like 'windows%'
or
book_type = 'computer'
;
Here is the default execution plan. Note that
the cost is 1, and the query reads the whole table with a full-table
scan:
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=8
Bytes=288)
1 0 TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=8
Bytes=288)
When you have correctly forced the use of your
indexes, your execution plans should look like this:
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=8
Bytes=288)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BOOK' (Cost=22 Card=8
Bytes=288)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP OR
4 3 BITMAP CONVERSION (FROM ROWIDS)
5 4 SORT (ORDER
BY)
6 5 INDEX (RANGE SCAN) OF 'BTREE_TITLE_TYPE'
(NON-UN IQUE) (Cost=14)
7 3 BITMAP INDEX (SINGLE VALUE) OF 'BITMAP_BOOK_TYPE'
Execution
Plan
----------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=5
Bytes=180)
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BOOK' (Cost=1 Card=1
Bytes=36)
3 2 BITMAP CONVERSION (TO ROWIDS)
4 3 BITMAP INDEX (SINGLE VALUE) OF 'BITMAP_BOOK_TYPE'
5 1 TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=1
Bytes=36)
Now, examine these execution plans. Which has
the highest cost? As we see, the b-tree index has a computed cost of
22, while the bitmap index has a cost of 8, and the full-table scan
only has a cost of 1. This is an example of a case where the
cost-based optimizer knows that indexes are not always the fastest
execution plan.
ANSWER
select /*+
index(book, btree_title_type) */
book_title,
book_type,
book_retail_price
from
book
where
book_title like 'windows%'
or
book_type = 'computer'
;
select /*+
index(book, bitmap_book_type) */
book_title,
book_type,
book_retail_price
from
book
where
book_title like 'windows%'
or
book_type = 'computer';
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. |
