Re: Oracle 8.0.4 index not working when using partitions

From: s <spaquette_at_orisoft.com>
Date: 1998/04/01
Message-ID: <01bd5d32$669ee800$600aa8c0@spaquette>#1/1

Hi,

I have a similar environment : Oracle 8.03 with the partitionning option on HP10.20 on a K570.
Data is organized with fact tables and dimension tables.

Best performance is achieved by creating bitmap indexes on each foreign key of the fact tables.

---

Stephane Paquette
Database Administrator
spaquette_at_orisoft.com
www.orisoft.com

Raj Nandkumar <nkumar_at_bellatlantic.net> wrote in article <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