How Can I Find The Implicit Conversion (Doc ID 423181.1)

Last updated on MARCH 03, 2022

Applies to:

Symptoms

How to troubleshoot 'implicit conversion'...


Loss of functionality
----------------------
Implicit conversion is done by Oracle.
Implicit conversion often leads to inferior execution plans.
For example, indexes may become inaccessible.


Significance of loss
---------------------
Possibly inferior execution plans...

Testcase/Examples
==============

example 1:


SQL> variable deptno varchar2(4)
SQL> exec :deptno := '10'
SQL> select * from scott.emp where deptno = :deptno;
SQL> delete from plan_table;
SQL> explain plan for
       select * from scott.emp where deptno = :deptno;
SQL> set lines 130
SQL> set head off

SQL> @?/rdbms/admin/utlxpls

Predicate Information (identified by operation id):


2 - access("DEPTNO"=TO_NUMBER(:DEPTNO))

=> The predicate shows the function used in the implicit conversion.
In this case, the index on deptno can be used, as this column is not
converted.
.

example 2:


SQL> variable hiredate char(9)
SQL> exec :hiredate := '03-DEC-81'
SQL> select * from scott.emp where hiredate = :hiredate;
SQL> delete from plan_table;
SQL> explain plan for
          select * from scott.emp where hiredate = :hiredate;
SQL> set lines 130
SQL> set head off

SQL> @?/rdbms/admin/utlxpls


Predicate Information (identified by operation id):

1 - filter("HIREDATE"=:HIREDATE)

=> The predicate shows the function used in the implicit conversion.
In this case, the column HIREDATE of the table is modified
and possible index inaccessibility results.

NOTE
=======
These are 2 simple examples. In general, it can be more difficult
to find the implicit conversion from the execution plan predicates.

REFERENCE
---------

PL/SQL User's Guide and Reference
Release 8.1.6
Part Number A77069-01
Datatype Conversion
.
Table 2-1 Implicit Conversions
Implicit versus Explicit Conversion
shows when implicit conversion is done

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.