Unnecessary table scans

From: Steve Grant <Steve.Grant_at_signet.co.uk>
Date: Tue, 3 Apr 2001 16:42:57 +0100
Message-ID: <rcmy6.3757$Tp2.1024155@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 Received on Tue Apr 03 2001 - 10:42:57 CDT