Re: Explain plan question

>I was running the following statement through explain plan:
 (Deleted)
>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
 (deleted)
>where field1 = decode('&&var1',NULL,'bozo','&&var1');
>
>Then explain plan says unique index will be used

This is what I would expect-- as soon as you apply a function (except for MAX or MIN) to an indexed field the rule-based optimizer will NOT use the index as an access path. Your second query no longer includes the indexed field ("field1") as an argument to the decode, so now the index will be used.
>d

-- 
   Vince Ventrone
   The MITRE Corporation       "...In my opinion, there's nothing
   Bedford, MA 01730           in this world beats a '52 Vincent
   vav_at_mitre.org               and a redheaded girl."  -- Richard Thompson