Re: Explain plan question

It is a varchar2 datatype. I personally feel that ORACLE's optimizer is ignoring the fact that the decode() returns a fixed value in this case.

thanks
d

Ken Mutchell (lbebs_at_cix.compulink.co.uk) wrote:
: I think your problem is that in a decode statement, Oracle automatically
: converts the the return value to the same datatype as the first result.
: In your second decode (below), the first result is a literal varchar2 and
: hence no conversion is required. In the first decode, I suspect field1 is
: either not a varchar2 datatype or Oracle is getting confused about which
: datatype it is because it has a null value. It is therefore doing an
: implicit conversion on the column field1 which is possibly preventing the
: use of the index on field1. See if I'm right by trying the following:
 

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

: and see if explain plan says that the index on field1 is used. If I'm
: right, it should use the index.
 

: Barbara
 

: --------------
 

: 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
: decode() evaluation happen ? If I change the above to:
 

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

: Then explain plan says unique index will be used
 

: thanks for any comments
: d
Received on Thu Feb 16 1995 - 03:36:45 CET