Re: Index Usage ?!

Hi!

> but there are seperate indexes on column PREF, FLAG and SEX.
> when those indexes are used ,cost=999.
>
> whereas when IDX_PROFILE_SHINKI ( a composite index on ENTPC,
> FLAG,SEX,PREF,ENTDAY) is used , cost=7.
>
> 1.whys there is a huge difference in COST ?

As Wolfgang said, since your IDX_PROFILE_SHINKI index is unanalyzed, CBO is using default values, which look quite sexy costwise. Are your other indexes analyzed?

> 2.does it mean that a composite index is better than individual ones ?

Always depends. But I use them a lot. Optimizerwise - they might be bigger in sense of bytes & blocks, but again if they contain all columns required in qurey, that no table access is required, they can speed up lookups & short range scans significantly.

Tanel.

>
> Kindly throw some light on this.
>
> Regards,
> Jp.
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Prem Khanna J
> INET: jprem_at_kssnet.co.jp
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Fri Jul 25 2003 - 03:37:51 CDT