Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Oracle 8.0.4 index not working when using partitions
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