Re: Unnecessary table scans

How many of the columns need histograms, and are any of these missing ?

How realistic are your values for init.or parameters that affect I/O cost estimates, e.g. db_file_multiblock_read_count, sort_area_size, sort_area_retained_size, hash_area_size, hash_io_multiblock_count ?

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Steve Grant wrote in message ...


>Hi,

>Has anyone come across this before and is it a known problem or a

>configuration issue ? We are running a datamart on 8.1.6 on AIX. All our

>fact tables have a composite primary key and bitmap indexes on the indidual

>columns of the primary keys. Statistics are adequate and up-to-date.

>A lot of our queries are however defaulting to a full table scan which is

>leading to long query times.

>

>An example of of timings would be

>

>A - Run query with no hints

>     Time to run -  6 mins +

>     Cost      -    13736

>

>B - Run query forcing it to use composite index.

>     Time to run -  1 min +

>     Cost -    35558

>

>C - Run query forcing it to use bitmap indexes.

>     Time to run -  10 secs

>     Cost -    15478

>

>Any views or insights to this would be welcome.

>Thanks

>

>Steve

>

>

>

>

>

>