Tanel,
<quote>Are your other indexes analyzed?</quote>
Yes.
Case 1:
If IDX_PROFILE_SHINKI is analyzed,CBO decides that index scan is costlier.
Case 2:
Now IDX_PROFILE_SHINKI is not analyzed (which works fine as of now),
CBO takes default values and decides that index scan is cheaper.
As per case 1,the I/O to read the index will be costlier.
So CBO decides not to use the index.
But as per case 2 (existing situation):
After the decision is made by CBO,
it still needs to read the index and then fetch table data.
So the same I/O to read the index is going to happen as in case 1.
***** correct me if i'm wrong *****
Aren't they contradictory ?!
So how does case 2 benefit in terms of CPU or memory consumption ?
and there is a big diff. in response time between both cases...how ?
...totally confused :(
Regards,
Jp.
25-07-2003 18:34:23, "Tanel Poder" <[EMAIL PROTECTED]> wrote:
>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.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Prem Khanna J
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
