Database Reference
In-Depth Information
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 12 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("X")=5)
As you can see, it full scanned the table. And even if we were to hint the following query, it uses the index, but not
for a
UNIQUE
SCAN
as we might expect—it is
FULL
SCANNING
this index:
EODA@ORA12CR1> explain plan for select /*+ INDEX(t t_pk) */ * from t where x = 5;
Explained.
EODA@ORA12CR1> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 180604526
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 12 | 3 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | T_PK | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("X")=5)
The reason lies in the last line of output there:
filter(TO_NUMBER("X")=5)
. There is an implicit function being
applied to the database column. The character string stored in
X
must be converted to a number prior to comparing to
the value 5. We cannot convert 5 to a string, since our NLS settings control what 5 might look like in a string (it is not
deterministic), so we convert the string into a number, and that precludes the use of the index to rapidly find this row.
If we simply compare strings to strings
EODA@ORA12CR1> explain plan for select * from t where x = '5';
Explained.
EODA@ORA12CR1> select * from table(dbms_xplan.display);
