Re: Oracle 8.0.4 index not working when using partitions

It may simply be that the index was not sufficiently cost-effective for
the CBO to use. Your 5-column index could be a significant fraction of the size of the partition it was addressing, and if you have a large
db_file_multiblock_read_count the CBO would have a strong bias towards ingoring the index.

Best bet is probably several single-column bitmap indexes - you do not need to index the time-period at all as this is the partitioning
column.

You may still find that some single-column queries are using tablescans - this can be the case if (a) the values in that column have _very_ few values (b) the scatter in the table is very random and (c) the db_blocksize is too large.

Queries which use several columns to return a few rows, however, should execute very quickly.

There are a couple of bugs in partition-tables with bitmap indexes though - you may find that queries against 4 or 5 partitions use the indexes, but queries against more partitions use tablescans.

If you have queries that use BITMAP MINUS, you may get some strange results (sample query:

	select
	where
		time_period = 'x'
	and	mod = 'y'
	and 	col3 = 'Z'
	and	col4 != 'A'	<---  can use bitmap minus.

Jonathan Lewis

Raj Nandkumar <nkumar_at_bellatlantic.net> wrote in article <6fs6fr$c2d_at_world1.bellatlantic.net>...

> 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.
>
>
> 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.
>
Received on Wed Apr 01 1998 - 00:00:00 CST