Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Oracle 8.0.4 index not working when using partitions

Oracle 8.0.4 index not working when using partitions

From: Raj Nandkumar <nkumar_at_bellatlantic.net>
Date: 1998/03/31
Message-ID: <6fs6fr$c2d@world1.bellatlantic.net>#1/1

We have encountered a problem with Oracle 8.0.4 partitions in a data warehouse environment. Our machine has HP-UX 10.2 and is a K460 with 4 processors. The problem is that SQL queries do not use the index at all even though they hit the right partition table. Our table columns are defined as follows:

        name
        product
        time-period
        company
        method of distribution (mod)
        sales measure 1
        sales measure 2

There are 24 time period values in terms of months (2 years of data) and the base table is partitioned into 24 segments--one for each time-period. Initially, we built a composite b-tree index in order of those 5 keys, namely name, product, time-period, company, mod.

What we observed was that when we selected the latest time period in the 'where' clause, Oracle would hit partition table # 24 correctly, however, it would NOT use the index and instead would do a full table scan. As a result, the query response time was considerably slower than if we were to not partition it in the first place. Our partition tables and indexes are analyzed and histograms have been computed.

Since this did not work, we changed the order of the indexes and created b-tree indexes in the order of time-period, name, product, company, mod and again analyzed the tables and indexes and computed the histograms. Oracle did not use the index here either.

At this point, we are going to create a composite bit-mapped indexes on the table followed by individual bit-mapped indexes on the 5 columns and see if we can get queries to run efficiently with this approach.

Anybody have any suggestions as to what we might be doing wrong that is causing Oracle not to use the b-tree indexes ??

I will find it easier if you could e-mail me at nandkr_at_imsint.com with any suggestions.

Thanks in advance for your help.

Raj Nandkumar
nandkr_at_imsint.com Received on Tue Mar 31 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US