Re: Explain plan question

In <3hc2tk$f13_at_sundog.tiac.net> fallin_at_max.tiac.net (Deeptendu Majumder) writes:

>I was running the following statement through explain plan:
 

>update <table>
>set field2 = 'jumbo'
>where field1 = decode('&&var1',NULL,field1,'&&var1');
 

>here field1 is the primary key and var1 is variable defined in SQLPLUS.
 

>The explain plane said it would be full table scan. I was curious if
>that is really true, especially if var1 is defined ? When does the

yes it is really true - the optimiser has chosen that path

>decode() evaluation happen ? If I change the above to:

the decode() cannot be evaluated until a row is read, as the decode() refers to "field1". If a column (field) is referenced by a function, an index on that field cannot be used.

>update <table>
>set field2 = 'jumbo'
>where field1 = decode('&&var1',NULL,'bozo','&&var1');
 

>Then explain plan says unique index will be used
Yes, because this query does not modify the column.

>thanks for any comments
>d

--
Mark Bluemel	Unix/Oracle Trainer and Consultant
		My opinions are my own, but I'll share them
		All solutions to problems are offered "as is"
		and without warranty - you have been warned :-)