Oracle 7.3 bit map index problem

From: Tim Nickason <tnickasn_at_rogers.wave.ca>
Date: 1997/10/18
Message-ID: <62a11q$npd$1@news.bc>#1/1

Greetings,

We are running a Dss (star schema) database using Oracle 7.3.2.3 on a Dec unix box. recently I started applying bit map indexes to the central fact table and to attribute columns
of the surrounding dimension tables. For some reason Oracles optimizer
(Cost and rule)

will ignore the bit maps or remaining sorted btree primary key index on the dimension tables. Only a direct hit will force it to use them (this is no good for us, since the sql
is generated via a client OLAP tool). I suspect it's a bug in the optimizer and I've talked
to Oracle support and they haven given me no useful solutions. I know that bit maps are working at some level because explain plans on queies against the fact tables foreign keys
(Now supported with bit maps) show use of these indexes.

Here are some details

Table1 (customer dimesion, approx 50,000 records)

    cust_id number(10) has a primary unique btree index on it     attribute1
    attribute2
    attribute 3

    .
    .
    .

   attribute n

Table 2 (Product dimension, approx 10,00 records)   product_id number(10) same as above, has btree index   attrbute1

  .
  .
  .

  attribute n

Table 3 (Time dimension, approx 500 records) week_id (this is the primary key and also has a sorted index) week_desc (text description of week id)

FACT Table (approx 20,000,000 records)
 Product_id (foreign key to product dimension)  Cust_id (Same but to customer dimension)  Time_id (...but for time dimension)
 Fact1
 fact 2
 Fact N

In the above dimension tables, the attribute columns now have bit map indexes (all have cardinality less than 100). The fact table has it map index on each foreign key.
Considering there are 20+ million rows, the cardinality is relatively low
(In my estmation).

Here is a sample of a problem query:

Select Table2.product_id, max(time_desc), sum(fact1)  from table1, table2, table 3, FACT
  where table3.week_id = FACT.week_id and

             table2.product_id = FACT.product_id and
             table1.cust_id = FACT.cust_id and
             table3.week_id in (value1, value2, value 3) and
             table2.attribute1 in (value1, value2, value3) and
             table1.attribute4 = value1;

I expect that the optimizer will uses the bit map indexes on the dimesion tables
(Table1 and Table2) to limit the list of products and customers to search
for in the
 FACt table. In turn, the optimizer will use this list against the bit maps of the FACT table.

So far, The optimizer insists on full table scans of the Customer and product dimension
tables (TABLE1 and TABLE2 in the example here). It does however, use bit map indexes
on the central FACT table.

Previously, when I had no bit maps defined but had various btree indexes on the dimension
and fact table, this class of query ran in under 5 minutes. Now it takes over an hour.

If anyone could rview this and give some suggestions, it would be much appreaciated.
If you have come across this, please tell me how you got around it. Keep in mind,
Hints won't work for me because the queries are generated by an OLAP client tool.

Thanks in advance

Tim Nickason
tnickasn_at_rogers.wave.ca Received on Sat Oct 18 1997 - 00:00:00 CDT