Defy Logic – the Cost-Based Optimizer does Not Select the Lowest Cost Plan – Implicit Data Type Conversion

May 28, 2010

How many times have you heard someone say:

If you need to store numbers in the database, store the numbers in a column with a numeric datatype – storing the numbers in a column with a VARCHAR2 datatype invites problems.

If you need to store dates in the database, store the dates in a column with a DATE datatype – storing the dates in a column with a VARCHAR2 datatype invites problems; storing the dates in a column with a numeric datatype invites problems.

Yes, it happens in production envionments

Why is my SQL statement executing so slowly.

Why am I receiving unexpected results from this query?

The same holds true when selecting data from tables. 

• If a number happens to exist in a column with a VARCHAR2 datatype, and it is necessary to retrieve that row by specifying that column in the WHERE clause, make certain that either the bind variable is defined as a VARCHAR/VARCHAR2 or that the constant (literal) value is wrapped in single quotes so that it is treated as a VARCHAR2, rather than as a number.  Yes, this happens in real life, as demontrated in this very recent OTN thread.
• If the column is a VARCHAR2 datatype, do not define the bind variable with a NVARCHAR2 datatype.  Yes, this happens in real life, as demonstrated in this recent OTN thread and this slightly older OTN thread.
• If the column is a DATE datatype, do not define the bind variable with a VARCHAR2 datatype and do not pass in a constant as a VARCHAR2 (string).  Something is bound to go wrong at some point.  I am still trying to determine why this technique was demonstrated multiple times in the “Oracle SQL Recipes” book (I have not had a chance to finish reading this book, so I have not posted a review yet that draws attention to this bad practice).

In the comments section of the most recent True or False Quiz article I showed a couple of demonstrations why numbers should not be stored in VARCHAR2 columns, if only numbers are to be stored in that column.  One of my comments showed why, when the column datatype does not match the datatype of the constant, and one of those is a numeric, why the other entity is converted to a numeric, rather than converting the numeric to a VARCHAR2.  From that comment entry, consider the following:

CREATE TABLE T3(
  CHAR_COL VARCHAR2(10),
  C2 VARCHAR2(100),
  PRIMARY KEY (CHAR_COL));

INSERT INTO T3 VALUES('1','A');
INSERT INTO T3 VALUES('1.0','A');
INSERT INTO T3 VALUES('1.00','A');
INSERT INTO T3 VALUES('1.000','A');
INSERT INTO T3 VALUES('1.0000','A');
INSERT INTO T3 VALUES('1.00000','A');
INSERT INTO T3 VALUES('1.000000','A');
INSERT INTO T3 VALUES('1.0000000','A');
INSERT INTO T3 VALUES('1.00000000','A');

COMMIT;

Now consider the following three queries – would the developer expect the three queries to return the same result rows?

SELECT
  *
FROM
  T3
WHERE
  CHAR_COL=1;

CHAR_COL   C2
---------- -----
1          A
1.0        A
1.00       A
1.000      A
1.0000     A
1.00000    A
1.000000   A
1.0000000  A
1.00000000 A

9 rows selected.

---

SELECT
  *
FROM
  T3
WHERE
  TO_NUMBER(CHAR_COL)=1;

CHAR_COL   C2
---------- -----
1          A
1.0        A
1.00       A
1.000      A
1.0000     A
1.00000    A
1.000000   A
1.0000000  A
1.00000000 A

9 rows selected.

---

SELECT
  *
FROM
  T3
WHERE
  CHAR_COL=TO_CHAR(1);

CHAR_COL   C2
---------- -----
1          A

Notice that the last query only returned one row, while the other two queries returned nine rows.  So, why are VARCHAR2 columns always converted to numeric values, rather than numeric values converted to VARCHAR2?  If the number was automatically converted by Oracle into a character value, Oracle might need to test a nearly infinite number of 0 characters appended to the end of the converted value after the decimal point (up to the number of characters of precision) for a matching result – this extra work is avoided by converting the character value to a number.

I might have appeared to have drifted off the topic of this blog article, so now let’s see a case where Oracle’s cost-based optimizer does the impossible – it does not pick the execution plan with the lowest calculated cost for a simple SQL statement involving a single table.  This test case can be reproduced on Oracle Database 10.2.0.4 through 11.2.0.1 (and probably a couple of other releases as well).  This test case is from another one of my comments in the recent True or False Quiz article – the bonus question.  The test case:

CREATE TABLE T1 (
  CHAR_COL VARCHAR2(10),
  C2 VARCHAR2(100),
  PRIMARY KEY (CHAR_COL));

INSERT INTO
  T1
SELECT
  TO_CHAR(ROWNUM),
  RPAD('A',100,'A')
FROM
  DUAL
CONNECT BY
  LEVEL <=1000000;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)

SET AUTOTRACE TRACEONLY EXPLAIN

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'LOWEST_COST';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SELECT /* FIND_ME */
  *
FROM
  T1
WHERE
  CHAR_COL = 10;

SELECT /*+ INDEX(T1) */  /* FIND_ME */
  *
FROM
  T1
WHERE
  CHAR_COL = 10;

SELECT /* FIND_ME */
  *
FROM
  T1
WHERE
  CHAR_COL = '10';

SET AUTOTRACE OFF
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

The test case creates a simple table with 1,000,000 rows and then executes three SELECT statements.  Page 477 of Tom Kyte’s “Expert Oracle Database Architecture” book states the following:

“Case 4: We have indexed a character column. This column contains only numeric data. We query using the following syntax:

select * from t where indexed_column = 5

Note that the number 5 in the query is the constant number 5 (not a character string). The index on INDEXED_COLUMN is not used…”

Jonathan Lewis stated here (a blog article from 2006) that:

“It’s a great shame that Oracle Corp. decided to use the name “hints” for its optimizer directive mechanism.  “Hints” are not hints, they are interception points in the optimizer code path, and must be obeyed.”

So, which expert’s explanation is correct for Oracle Database 10.2.0.4 through 11.2.0.1 for this particular test case?  Neither?  Both?  Let’s take a look at the output that was written to the SQL*Plus screen:

SQL> SELECT /* FIND_ME */
  2    *
  3  FROM
  4    T1
  5  WHERE
  6    CHAR_COL = 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   108 |  4450   (2)| 00:00:54 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   108 |  4450   (2)| 00:00:54 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("CHAR_COL")=10)

The above shows that Tom Kyte’s book is correct – the primary key index on the column CHAR_COL is not used.  Continuing with the SQL*Plus output:

SQL> SELECT /*+ INDEX(T1) */  /* FIND_ME */
  2    *
  3  FROM
  4    T1
  5  WHERE
  6    CHAR_COL = 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 458899268

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |   108 |  3961   (2)| 00:00:48 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           |     1 |   108 |  3961   (2)| 00:00:48 |
|*  2 |   INDEX FULL SCAN           | SYS_C0010157 |     1 |       |  3960   (2)| 00:00:48 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_NUMBER("CHAR_COL")=10)

The above SQL statement is the same as the first, just with an INDEX hint.  The index hint was obeyed.  Jonathan Lewis’ blog article is correct – index hints are directives, so Oracle’s optimizer selected an index access path using the only available index – the index on the primary key column.

But now we have a serious problem.  What is the problem?  For a SQL statement involving a single table a higher calculated cost execution plan (4,450) with a higher estimated time (54 seconds) was selected rather than using the obviously less expensive execution plan with a lower calculated cost (3,961) and with a lower estimated time (48 seconds).  Interesting…

The SQL*Plus output continues:

SQL> SELECT /* FIND_ME */
  2    *
  3  FROM
  4    T1
  5  WHERE
  6    CHAR_COL = '10';

Execution Plan
----------------------------------------------------------
Plan hash value: 1657849122

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |   108 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           |     1 |   108 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0010157 |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CHAR_COL"='10')

Notice that the calculated cost decreased significantly when we constructed the query correctly, and the available index access path was automatically selected.

So, why did the optimizer not select the lowest cost access path?  Fortunately, the test case created a 10053 trace file that helps explain what happened.  In the 10053 trace file, we see that the optimizer transformed the original SQL statement a bit:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."CHAR_COL" "CHAR_COL","T1"."C2" "C2" FROM "TESTUSER"."T1" "T1" WHERE TO_NUMBER("T1"."CHAR_COL")=10

Now, with the transformed version of the SQL statement, it appears that we need a function based index on the CHAR_COL column that converts the column value to a number so that an index access path is possible.  Further down in the 10053 trace we find the following:

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 466 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
  MBRC: -1 blocks (default is 8 )

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 1000000  #Blks:  16217  AvgRowLen:  108.00
Index Stats::
  Index: SYS_C0010157  Col#: 1
    LVLS: 2  #LB: 3908  #DK: 1000000  LB/K: 1.00  DB/K: 1.00  CLUF: 201245.00
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Table: T1  Alias: T1
    Card: Original: 1000000.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  4450.40  Resp: 4450.40  Degree: 0
      Cost_io: 4394.00  Cost_cpu: 315488412
      Resp_io: 4394.00  Resp_cpu: 315488412
  Best:: AccessPath: TableScan
         Cost: 4450.40  Degree: 1  Resp: 4450.40  Card: 1.00  Bytes: 0

***************************************

In the above, we see that the optimizer immediately jumped to a full table scan access path and then immediately declared that a full table scan offered the lowest cost – the optimizer did not even consider an index access path.  Now, let’s compare the above with the SQL statement having a hinted access path:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ INDEX ("T1") */ "T1"."CHAR_COL" "CHAR_COL","T1"."C2" "C2" FROM "TESTUSER"."T1" "T1" WHERE TO_NUMBER("T1"."CHAR_COL")=10
...

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 466 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
  MBRC: -1 blocks (default is 8 )

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 1000000  #Blks:  16217  AvgRowLen:  108.00
Index Stats::
  Index: SYS_C0010157  Col#: 1
    LVLS: 2  #LB: 3908  #DK: 1000000  LB/K: 1.00  DB/K: 1.00  CLUF: 201245.00
    User hint to use this index
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Table: T1  Alias: T1
    Card: Original: 1000000.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
kkofmx: index filter:TO_NUMBER("T1"."CHAR_COL")=10

  Access Path: index (FullScan)
    Index: SYS_C0010157
    resc_io: 3911.00  resc_cpu: 227852122
    ix_sel: 1.000000  ix_sel_with_filters: 0.000001
 ***** Logdef predicate Adjustment ******
 Final IO cst 0.00 , CPU cst 50.00
 ***** End Logdef Adjustment ******
    Cost: 3960.67  Resp: 3960.67  Degree: 1
  Best:: AccessPath: IndexRange
  Index: SYS_C0010157
         Cost: 3960.67  Degree: 1  Resp: 3960.67  Card: 1.00  Bytes: 0

The hint provided in the SQL statement forced the optimizer to do something that is supposedly not possible.  Notice that unlike the previous output, the full table scan was not even considered because of the hint.

The short summary of the above: do things correct from the start to avoid confusion and unexplained performance problems later.

The 10053 trace files (PDF versions of the 10053 trace files):
Oracle Database 11.2.0.1 OR112_LOWEST_COST
Oracle Database 11.1.0.7 OR1117_LOWEST_COST
Oracle Database 10.2.0.4 OR1024_LOWEST_COST

——–

* Late edit May 28, 2010: In the most recent True or False quiz, Centinul provided a documentation reference that states that VARCHAR2 values are always converted to numbers when a number is implicitly compared to a VARCHAR2 value.