logo for asktheoracle.net
leftimage for asktheoracle.net

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.

Concatenated Indexes

A 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 
b-tree 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 table).  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.

Bitmap Indexes

Bitmap 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.

Oracle Tips & Tricks
to SKYROCKET Your Career!

Don't waste hours scouring the Internet for Oracle tips. Subscribe to our ezine and get them delivered straight to your in box.
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.

Let's 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 be represented.

Colour bitmap

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 Indexes

Bitmap 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 index.

CREATE BITMAP INDEX ON products(colour) TABLESPACE prod_ix_1;

CREATE BITMAP INDEX ON sales(product_colour)
FROM sales a, products p
WHERE s.product_id=p.product_id
TABLESPACE prod_ix_1;

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.
Looking for high quality Oracle training? 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.

Need 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 consultants have the knowledge and expertise to help you. Click here for a consultation application form.