Re: Help on execution plan

From: Thomas Kellerer <spam_eater_at_gmx.net>
Date: Thu, 06 Nov 2003 17:30:34 +0100
Message-ID: <bodsva$9d3$1@svr7.m-online.net>

 > might be worthwhile extending the index to include all columns that need  > to be returned (a valid tuning trick)

Yes I'm already evaluating that, to check if disk space is OK in the production environment

 > how many rows are you returning?
As I said: about 3500 (out of 4.5 million)

Thanks for your input

Thomas

mcstock schrieb:
> might be worthwhile extending the index to include all columns that need to
> be returned (a valid tuning trick)
>
> how many rows are you returning?
>
> "Thomas Kellerer" <spam_eater_at_gmx.net> wrote in message
> news:bodhot$1c6q3a$1_at_ID-13919.news.uni-berlin.de...
>

>>Mark,
>>
>>thanks for your feedback. I was thinking about the columns beeing part of

>
> an
>

>>index already. The funny thing about this, is that the index which *is*

>
> used
>

>>when I remove the two columns is not the index that is beeing reported by
>>EXPLAIN PLAN. The index beeing use is the one over (id, product_no)

>
> although
>

>>there is one over (id, name, product_no, ca_code). That's basically the

>
> reason
>

>>why I wasn't considering the index read.
>>
>>And yes, I have tried using hints, but any of the indexes I specify make

>
> it or
>

>>doesn't change too much.
>>
>>I am aware of the fact that an index scan *can* be more expensive then a

>
> FTS but
>

>>taking into account that the table has 4.5 million rows (in the test
>>environment, production has about 9 million rows) I would expect an index

>
> range
>

>>scan still beeing less expensive....
>>
>>Thomas
>>
>>
>>mcstock schrieb:
>>
>>
>>>the columns that remain in the select list likely are being read from

>
> the
>

>>>index -- is there any other type table access for 'big_table' in the
>>>execution plan replacing the FTS?
>>>does anything else in the plan change?
>>>have you looked into hints to force the index range scan (which may not

>
> be
>

>>>so cheap with the additional columns included -- the optimizer may be

>
> making
>

>>>a correct choice in this case)
>>>
>>

>
>
Received on Thu Nov 06 2003 - 10:30:34 CST