Re: Unnecessary full table scans

"Steve Grant" <Steve.Grant_at_signet.co.uk> wrote in message news:kbmy6.3747$Tp2.1023315_at_news2.cableinet.net...
> 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
>

As mentioned by David, how good are your stats estimated/sample/compute ??

Additionally do you analyze the columns ?

Skewed data and estimated statistics can have this effect. (remember estimate only looks at the first 1000 or so rows)

If it still insists on using a full tablescan, have you considered parallel degree.

Another way may be to lower the value in the init.ora file OPTIMISER_PERCENT_PARALLEL
Higher figures encourage the optimiser to do full table scans. You have to remember that this affects the whole database though and could make other queries slow down. (Can't remember if you can use alter session for this)

Hope this helps Received on Fri Apr 06 2001 - 15:49:38 CDT