Oracle Query Performance – Impact of Single Quotes on Query Performance

oracle

We have a table as below 

CREATE TABLE "ABC"(
"ID" NUMBER (18, 0) NOT NULL,
"ID2" VARCHAR2(15 char) NULL,
"VERSION" VARCHAR2(30 char) NULL,
"TIMESTAMP" DATE  NULL,
 CONSTRAINT "PK_ABC" PRIMARY KEY ("ID")
);

ALTER TABLE ABC ADD  CONSTRAINT CK_ABC_ID2_VERSION UNIQUE(ID2, VERSION);

CREATE UNIQUE INDEX IDX1_ABC ON ABC 
(
    ID2, 
    VERSION
);



CREATE INDEX "IDX2_ABC" ON ABC
(
                VERSION ASC,
                ID2 ASC,
                TIMESTAMP ASC
);

say there is a high volume of data on the same table. Would you be able to help conclude which Query from below would have better performance and why

First query:

SELECT ID, ID2, VERSION, TIMESTAMP 
FROM ABC WHERE ID2=123 
  AND VERSION =54321

Second query:

SELECT ID, ID2, VERSION, TIMESTAMP 
FROM ABC 
WHERE ID2='123' 
  AND VERSION ='54321'

Best Answer

1)

SQL> explain plan for SELECT ID, ID2, VERSION, TIMESTAMP FROM ABC WHERE ID2=123 AND VERSION =54321;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1033171814

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   116 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ABC  |     1 |   116 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER("ID2")=123 AND TO_NUMBER("VERSION")=54321)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

17 rows selected.

As you can see, this is bad, because you have number literals but the type of the columns is varchar2. The columns are implicitly converted to number with TO_NUMBER. Becasue of this, the index can not be used, as the index contains columns ID2 and VERSION, and not TO_NUMBER(ID2) and TO_NUMBER(VERSION). A function-based index can be used in such a case, but that is not the right solution here.

2)

SQL> explain plan for SELECT ID, ID2, VERSION, TIMESTAMP FROM ABC WHERE ID2='123' AND VERSION ='54321';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1869257811

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

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

   2 - access("ID2"='123' AND "VERSION"='54321')

With proper types, everything works expected, the index can be used.

It is not a matter of single quotes, but a matter of using proper datatypes, so 2) is better.

Related Question