Database, SQL and PL/SQL

The Importance of Data Types

The following steps demonstrate how to use the V$SQL PROGRAM_ID and PROGRAM_LINE# columns to track SQL back to a PL/SQL stored program unit. To see how an EXPLAIN PLAN command (issued either directly or via a tool such as SET AUTOTRACE) may give misleading results for an execution plan, first I will create a table T with a primary key column PK. Note that the data type of the column is character.
SQL> create table t as
  2  select
  3    to_char(rownum) pk,
  4    a.*
  5  from all_objects a;

Table created.

SQL> desc t
 Name                          Null?    Type
 ----------------------------- -------- -------------
 PK                                     VARCHAR2(40)
 OWNER                         NOT NULL VARCHAR2(128)
 OBJECT_NAME                   NOT NULL VARCHAR2(128)
 SUBOBJECT_NAME                         VARCHAR2(128)
 OBJECT_ID                     NOT NULL NUMBER
 DATA_OBJECT_ID                         NUMBER
 OBJECT_TYPE                            VARCHAR2(23)
 CREATED                       NOT NULL DATE
 LAST_DDL_TIME                 NOT NULL DATE
 TIMESTAMP                              VARCHAR2(19)
 STATUS                                 VARCHAR2(7)
 TEMPORARY                              VARCHAR2(1)
 GENERATED                              VARCHAR2(1)
 SECONDARY                              VARCHAR2(1)
 NAMESPACE                     NOT NULL NUMBER
 EDITION_NAME                           VARCHAR2(128)
 SHARING                                VARCHAR2(18)
 EDITIONABLE                            VARCHAR2(1)
 ORACLE_MAINTAINED                      VARCHAR2(1)
 APPLICATION                            VARCHAR2(1)
 DEFAULT_COLLATION                      VARCHAR2(100)
 DUPLICATED                             VARCHAR2(1)
 SHARDED                                VARCHAR2(1)
 CREATED_APPID                          NUMBER
 CREATED_VSNID                          NUMBER
 MODIFIED_APPID                         NUMBER
 MODIFIED_VSNID                         NUMBER

Next I create an index to support the primary key to enable fast lookups on this value.

SQL> create index t_ix on t ( pk );

Index created.

Now I examine a simple SQL statement via EXPLAIN PLAN to see if it correctly uses the index.

SQL> variable obj number
SQL> exec :obj := 123456;

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select * from t
  2  where  pk = :obj;

Execution Plan
----------------------------------------------------------
Plan hash value: 3343387620

------------------------------------------------------------------------------------------
| Id | Operation                           | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |      |    1 |   139 |     2   (0)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |    1 |   139 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN                  | T_IX |    1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("PK"=:OBJ)
Line 2 in the execution plan indicates that an index will be used, and I happily promote my code into the application, confident that it will run as expected. But this plan is not the plan that will be used by the application. The EXPLAIN PLAN command does not interrogate the data types of the bind variable (obj) that was used in the SQL statement, whereas it will be relevant to the optimizer in a true execution environment. I can see the true plan by running the SQL statement and then using the DBMS_XPLAN database package to reveal what plan the optimizer actually used at runtime.
SQL> set autotrace off
SQL> select * from t
  2  where  pk = :obj;

no rows selected

SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID  agwvcq8tng6b9, child number 0
-------------------------------------
select * from t where  pk = :obj

Plan hash value: 1601196873

------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |      |       |   435 (100)|          |
|* 1 |  TABLE ACCESS FULL| T    |    1 |   139 |   435   (1)| 00:00:01 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("PK")=:OBJ)

Because the data type of the obj bind variable is numeric and the PK column it was predicated against is a character string, the optimizer performed an implicit TO_NUMBER function for the PK column to ensure data type equivalence so that it could make a valid comparison of the values. The presence of TO_NUMBER in effect changed the SQL statement the optimizer was using to determine an execution plan. As the optimizer becomes more powerful with each release of Oracle Database, this is just one of many possible scenarios where the execution plan at runtime may be totally different from a plan observed with an EXPLAIN PLAN command or similar.

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.