Re: Unnecessary table scans

Steve,

I've looked at the init.ora parameters you sent me in an offline post, and your comments that the data distribution is pretty uniform.

The problem is your value for hash_area_size, which is set to 20 MB. This is not a particularly unreasonable size for a typical datamart system, but it is having an adverse effect where you have potential star_transformations such as these.

I would guess that your very slow query
is taking some sort of multiple hashing
path through the data.

Try

    alter session set hash_area_size = 4000000;

and see if the fast path comes back and is more consistent. You might also like to look at bitmap_merge_area_size which defaults to 1M, perhaps increasing this to 2 to 4 MB (if the hash area change doesn't sort the problem).

One silly question - with apologies -do you have

    star_transformation_enabled = true

This is the parameter that allows Oracle to consider using multiple bitmap access paths in this fashion anyway.

--
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 ...


>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.

>

>