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 it?

    Requires Free Membership to View

    Login

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:

  1. 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.
  2. 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.
  3. 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.
I believe the bit-mapped indexes is recommended for star schemas. You are probably already committed. In that case, you should pursue the option for cost-based optimization and, if possible, obtain a copy of the Oracle Press book. I hope this helps.


This was first published in February 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top