Unacceptable behaviour of ORACLE optimizer

• Quoting Venkatesan N R to All dated 09-13-94 ***
  > explain plan set statement_id = 'S4'
  > for
  > select 'x' from testtable where col4 = :col4
  > /
  >
  > ORACLE optimizer chooses INDEX SCAN for the first three SQL statements,
  > but TABLE SCAN for the last one. I don't think this is because ORACLE's >
  cost based optimizer is much more intelligent and thinks that RELATION
  > SCAN will give a better response time. The index created on the column is
  > an UNIQUE index and 100,000 rows are there in the table.
  

Probably not. It is more likely that it is implicitly converting your statement to the following:
select 'x' from testtable where rawtohex(col4) = rawtohex(:col4); You can see the problem, of course, it cannot use the index because it must perform a function on the indexes column -- and this is because you're probably using the CHAR datatype or something similar which gets implicitly converted. This should work nicely, however:
select 'x' from testtable where col4 = hextoraw(rawtohex(:col4)); As always, allowing implicit conversion is not always the best route.

> I know that ORACLE optimizer has to take classes in COSTING, but this one is
> weird.

It also has to take much of the blame for programmer's and DBA's mistakes.

> But should not the OPTIMIZER be smart enough to know that users can
> bind host variables of RAW datatypes also. The optimizer uses INDEX
> SCAN if the SQL statement is changed as follows
>
> select 'x' from testtable where col4 = HEXTORAW(RAWTOHEX(:col4));

Why are you blaming the optimizer? The precompiler is the entity that decides what datatypes you can use, and you are the one who is using the wrong datatype.  Furthermore, I can't see what your problem is when you have a feasible solution. Received on Fri Sep 16 1994 - 15:38:07 CEST