Re: Index Usage ?!

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" <tanel.poder.003_at_mail.ee> 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 &
Received on Fri Jul 25 2003 - 06:00:06 CDT