Re: Unnecessary table scans

From: Steve Grant <Steve.Grant_at_signet.co.uk>
Date: Wed, 4 Apr 2001 15:28:45 +0100
Message-ID: <RdGy6.4033$2F2.1425878@news2.cableinet.net>

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_at_exesolutions.com> wrote in message news:3ACA44C1.7BEDC459_at_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
>
Received on Wed Apr 04 2001 - 09:28:45 CDT