SQL Performance Tuning (ctd
Part 1 of this series on SQL
performance tuning looked at the use of full table scans and b-tree
(balanced/binary tree) indexes. In thsi part we will discuss the use of
concatenated indexes and bit map indexes.
when an index on those columns would have high cardinality (i.e. each
value in the index would refer to a small percentage of the rows in the
The best b-tree indexes refer to just one row in the table (as in the
case of primary key and unique indexes). If a b-tree index does not
have high cardinality it may well be ignored by the Oracle optimizer in
favour of a full table scan. If the indexed columns are frequently used
together in queries then Oracle will need to perform less i/o (because
only 1 index needs to be read) and less sorting because the results
from two or more indexes won't need to be merged.
Concatenated IndexesA concatenated index is simply an
index on more than one column and these should be considered for SQL
performance tuning when the same set of two or more columns is
often used in queries and in the case of
indexes are ideal for applications in which few (if any) updates occur
(as the whole bitmap has to be re-created whenever an update occurs)
and which have low levels of concurrency. They are also much more
suited to indexing values with low cardinality such as, for example,
colour for a car manufacturer. Knowledge of the application is still
important though as no benefit would be gained from your SQL
performance tuning if the columns being indexed are not used as
predicates in SQL queries.
waste hours scouring the Internet for Oracle
to our ezine
get them delivered straight to your in box.
SKYROCKET Your Career!
The structure of bitmap indexes
is different to b-tree indexes. Instead of indexing each row
separately, a bitmap is created for each distinct value with one bit
for every row in the table. Each bit in each bitmap signifies whether
or not the corresponding row has the corresponding value for the
column. If the bit is set, that row has the value for that column and
conversely if the bit is then the row does not have the corresponding
value for that column.
look at an example. Assume we have an index on the colour of each car
produced by a car manufacturer. To save space let's pretend there are
only 25 rows in the table. The number of rows determines the number of
bits in the bitmap so in this case each bitmap has 25 bits, but
normally there would be thousands or possibly hundreds of thousands.
The number of bitmaps is determined by the number of distinct values.
This shows how a bitmap index of 6 different values and 25 rows would
Red < 1 1 0 0 0 0 0 0 1 0 1 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 >
White < 0 0 0 0 0 0 1 0 0 0 0 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 >
Blue < 0 0 1 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 >
Green < 0 0 0 0 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 >
Yellow < 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 >
Black < 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 >
Bitmap Join IndexesBitmap join indexes are a subtype of bitmap indexes that are especially useful for SQL performance tuning in data warehouses.
This type of index is is created on a column of the fact table (the
table that has the real data for example SALES) that is joined with one
or more dimension tables (which allow facts to be rolled up in
different dimensions such as time (e.g. week to month to quarter to
year)). When the index key consists of dimension table columns, the
index stores the fact table rowids corresponding to that key.
As with normal bitmap indexes in Oracle, a bitmap join index can only index up to 30 columns.
Let's look at a couple of examples of creating bitmap indexes. The
first is just a normal bitmap index and the second is a bitmap join
CREATE BITMAP INDEX ON products(colour) TABLESPACE prod_ix_1;
CREATE BITMAP INDEX ON sales(product_colour)
FROM sales a, products p
Bitmap join indexes need to be locally partitioned if the associated fact table is partitioned.
More information on SQL performance tuning in general can be found in the Oracle Database Performance Tuning
Guide and information on indexes can be found in the Oracle SQL Language Reference manual.
You can also send us any questions you may have by submitting them on our Oracle questions page.
high quality Oracle
Advance your skills and sky rocket your career with expert
instructor-led Oracle training. Our partners offer Oracle training in
the UK for both developers and dbas. Click here for details of Oracle training in the UK and discover how you can slash costs
and explode productivity.
help with sql performance tuning? With
skills honed to a fine edge over many
years with myriad clients in a variety of industries, Smartsoft's
have the knowledge and expertise to help you. Click here for