I've got a process that's carrying out badly because of full table scans on the particular table. I've calculated statistics, reconstructed existing indices and attempted adding new indices with this table but this has not solved the problem.

Can an implicit type conversion stop a catalog getting used? How about some other reasons? The price of a complete table scan is about 1000 more than the index research ought to be.


SQL statement:

select unique_key 
from src_table 
where natural_key1 = :1 
and natural_key2 = :2 
and natural_key3 = :3;
  • Cardinality of natural_key1 is high, but there's a kind conversion.
  • Another areas of natural key are low cardinality, and bitmap indices aren't enabled.
  • Table dimensions are around 1,000,000 records.

Java code (not easily flexible):

ps.setLong(1, oid);

This conflicts using the column datatype: varchar2

an implicit conversion can prevent a catalog from getting used through the optimizer. Consider:


Table created

SQL> insert into a select rownum from dual connect by rownum <= 1e6;

1000000 rows inserted

This can be a simple table however the datatype isn't 'right', i-e should you query it such as this it'll full scan:

SQL> select * from a where id = 100;


This question is actually equal to:

select * from a where to_number(id) = 100;

It can't make use of the index since we indexed id and never to_number(id). To make use of the index we must be explicit:

select * from a where id = '100';

In answer pakr's comment: You will find plenty of rules concerning implicit conversions. One good starting point may be the documentation. Amongst other things, we learn that:

Throughout Choose FROM procedures, Oracle converts the information in the column to the kind of the prospective variable.

This means that after implicit conversion happens throughout a "WHERE column=variable" clause, Oracle will convert the datatype from the column and never from the variable, therefore stopping a catalog from getting used. For this reason it is best to make use of the right type of datatypes or clearly transforming the variable.

In the Oracle doc:

Oracle suggests that you simply specify explicit conversions, instead of depend on implicit or automatic conversions, therefore:

  • SQL claims are simpler to know if you use explicit datatype conversion functions.
  • Implicit datatype conversion may have a negative effect on performance, particularly if the datatype of the column value is transformed into what constant instead of the other way round.
  • Implicit conversion is dependent around the context that happens and could not work exactly the same means by every situation. For instance, implicit conversion from the datetime value to some VARCHAR2 value may return an unpredicted year with respect to the worth of the NLS_DATE_FORMAT parameter.
  • Calculations for implicit conversion are susceptible to change across software releases using one of Oracle items. Behavior of explicit conversions is much more foreseeable.