We are students running a small data warehouse (a copy Oracle's 9i example - SH - with some changes at the conceptual level) on Oracle 9i for university work. We are also using Oracle 9i Discoverer Suite (Administrator & Desktop) to execute ad-hoc queries.
We've created some bitmap indexes on all fk's of the sales table (facts
table) and some other bitmap indexes on the dimension tables.
After creating those indexes, we executed the same queries we we're using
before, to test the data warehouse
speed, and query execution got slower (by about 2x) on Discoverer, even
though the execution
plan is using the indexes. On some queries the indexes aren't used at all.
How can that be possible? What are we doing wrong, and how can we correct
We have not yet upgraded to 9i but I can share my experience from working with three generations of Oracle. I have a couple of suggestions:
- Cost-based Optimization. Strongly recommended by Oracle. Execution
plan is based on available statistics.
- You need to make sure that statistics are up-to-date. The cost-based optimizer calculates the execution path. We have experienced occasions where it does not choose an index even when it should, such as a primary key index. I would start with calculating statistics.
- Use a hint. You may specify the index by adding the appropriate hint (should be available in the documentation) to the query. This of course assumes that you have access to the SQL code.
- Rule-based Optimization. This is an older version of the optimizer that
is not generally recommended by Oracle. Oracle's plan is to de-support it,
but we still rely heavily on it. The rule-based optimizer figures out the
execution plan based on the same rules every time. It is more consistent so
I prefer it. I am pretty sure that it is still available except it cannot
be used with bit-mapped indexing. To phase customers off the rule-based
optimizer, Oracle will generally not provide support for rule-based
optimization with any of the new features. If you are using any 9i or even
8i specific features, it may not be supported. Partitioning is an 8i
feature. If we partition the table, we have to endure the cost-based
optimizer which means we are including hints in our code.
- Table order and where clauses are very important. Oracle accesses tables from right to left or bottom to top. The rule is to arrange the tables from the smallest result set to the larger result set in right to left order. The one exception is when the where clause contains a filter on an indexed column. For example, table A has the smallest result set; however, table B has a filter on an indexed column. It may access Table B first because it contains a filter and table A does not, assuming that the filter on Table B means a smaller result set. Again, this assumes that you have access to the SQL code. Please be aware that it the result set and not the number of rows in the table that is important.
- Oracle Press -- Oracle High-Performance SQL Tuning
- One of a series of Oracle authorized books which is a good resource for tuning SQL statements. I have one of the earlier editions but it is the resource I most often use.
This was first published in February 2003