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 -> Re: Oracle 8.0.4 index not working when using partitions

Re: Oracle 8.0.4 index not working when using partitions

From: Alan V <alanv_at_opustel.com>
Date: 1998/04/01
Message-ID: <6ftujf$7va@news9.noc.netcom.net>#1/1

Create a separate index for the time field and set your check constraint against that column. Then create bit-mapped indexes for the other values. Only create the entire index after loads to ensure uniqueness.

Alan V.

Raj Nandkumar wrote in message <6fs6fr$c2d_at_world1.bellatlantic.net>...
>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 Wed Apr 01 1998 - 00:00:00 CST

Original text of this message

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