Unacceptable behaviour of ORACLE optimizer

ORACLE optimizer thinks that programmers will never use hostvariables of external datatype RAW (and its variants) when binding to variables used in WHERE clause. It refuses to use INDEX scan when it SHOULD. I know that ORACLE optimizer has to take classes in COSTING, but this one is weird.

Consider the following example.

create table testtable (

  col1       number(10),
  col2       varchar2(20),
  col3       date,
  col4       raw(20))

begin
 for i in 1 .. 100000 loop
   insert into testtable values (i,to_char(i),sysdate-i,

             hextoraw(lpad(to_char(i),6,'0')));  end loop;
 commit work;
end;
/

create unique index ix1 on testtable(col1)
/

create unique index ix2 on testtable(col2)
/

create unique index ix3 on testtable(col3)
/

create unique index ix4 on testtable(col4)
/

explain plan set statement_id = 'S1'
for
  select 'x' from testtable where col1 = :col1
/

explain plan set statement_id = 'S2'
for
  select 'x' from testtable where col2 = :col2
/

explain plan set statement_id = 'S3'
for
  select 'x' from testtable where col3 = :col3
/

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.

I assume, by default, the bind variables are expected to be of CHARACTER datatype and since there is a natural conversion from CHARACTER datatype to NUMBER and DATE datatypes, the parser uses the index scan, while for RAW datatypes, HEXTORAW should be used to get the raw datatype from a CHARACTER datatype. (The datatypes specified above are generic and they encampass the variants under each datatype).

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));

I think ORACLE has a strong opinion that RAW columns will not be used in tables, much less in the WHERE clause of a SELECT statement.

Hey! Come on. Wake up to realities.

--
Venkatesan N R
Contract Employee
Hewlett-Packard
Cupertino.


Opinions expressed are my own.