From: "Steve Grant" <Steve.Grant@signet.co.uk>
Newsgroups: comp.databases.oracle.misc
References: <rcmy6.3757$Tp2.1024155@news2.cableinet.net> <3ACA44C1.7BEDC459@exesolutions.com>
Subject: Re: Unnecessary table scans
Lines: 64
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <RdGy6.4033$2F2.1425878@news2.cableinet.net>
Date: Wed, 4 Apr 2001 15:28:45 +0100
NNTP-Posting-Host: 195.188.84.19
X-Complaints-To: http://www.blueyonder.co.uk/abuse
X-Trace: news2.cableinet.net 986394609 195.188.84.19 (Wed, 04 Apr 2001 15:30:09 BST)
NNTP-Posting-Date: Wed, 04 Apr 2001 15:30:09 BST
Organization: blueyonder (post doesn't reflect views of blueyonder)


SELECT
  TBDN_STO_FIXED.STO_NO,
  sum(TBFD_SLS.UNITS - TBFD_SLS.RTN_UNITS),
  TBDN_SKU_FIXED.SHORT_SKU,
  TBDN_STO_FIXED.STO_DES
FROM
  TSDN_CAL_WK,
  TBDN_STO_FIXED,
  TBFD_SLS,
  TBDN_SKU_FIXED
WHERE
  ( TBDN_STO_FIXED.STO_ID=TBFD_SLS.STO_ID  )
  AND  ( TBDN_SKU_FIXED.SKU_ID=TBFD_SLS.SKU_ID  )
  AND  ( TBFD_SLS.WK_ID=TSDN_CAL_WK.WK_ID  )
  AND  (
  TSDN_CAL_WK.YR_NO  =  2000
  AND  TSDN_CAL_WK.WK_SHORT  IN  (10,11)
  AND  TBDN_SKU_FIXED.SHORT_SKU  = 1234567
  )
GROUP BY
  TBDN_STO_FIXED.STO_NO,
  TBDN_SKU_FIXED.SHORT_SKU,
  TBDN_STO_FIXED.STO_DES;

What we have found is that if we have just the one value of
TSDN_CAL_WK.WK_SHORT, the bitmap indexes are used, but as soon as we have
more than one value, a tablescan is performed.
There are currently about 100 distinct values of this column.





"Daniel A. Morgan" <dmorgan@exesolutions.com> wrote in message
news:3ACA44C1.7BEDC459@exesolutions.com...
> > 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
>
> What do you WHERE clauses look like? Give us an example from the above.
>
> Daniel A. Morgan
>