Test case :
create table t(x varchar2(8) primary key,
y varchar2(30));
insert into t(x,y) select
to_char(rownum,'FM00000000'),
object_name from all_objects where rownum<1e4;
commit;
exec dbms_stats.gather_table_stats(user,'T')
One user wants to filter on x but does not do the casting properly
SQL> select * from t where x=00000001;
X Y
-------- ------------------------------
00000001 CON$
He received the expected data.
Let's check his plan
SQL> explain plan for
select * from t where x=00000001;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------
Plan hash value: 2153619298
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------
Predicate Information
(identified by operation id):
-----------------------------------
1 - filter(TO_NUMBER("X")=00000001)
Obviously, he is not using the primary key index. He should use single quotes literal
select * from t where x='00000001'
Okay, let's tune 😉
SQL> VAR start_time VARCHAR2(32)
SQL> VAR end_time VARCHAR2(32)
SQL> exec select to_char(sysdate,
'MM-DD-YYYY HH24:MI:SS') into :start_time
from dual
SQL> select * from t where x=00000001;
X Y
-------- ------------------------------
00000001 CON$
SQL> exec select to_char(sysdate,
'MM-DD-YYYY HH24:MI:SS') into :end_time
from dual
SQL> VAR task_id NUMBER
SQL> VAR task_name VARCHAR2(32)
SQL> EXEC :task_name := 'ADV01'
SQL> EXEC DBMS_ADVISOR.CREATE_TASK (
DBMS_ADVISOR.SQLACCESS_ADVISOR,
:task_id, :task_name)
SQL> exec DBMS_ADVISOR.SET_TASK_PARAMETER
(:task_name, 'EXECUTION_TYPE', 'INDEX_ONLY')
SQL> exec DBMS_ADVISOR.SET_TASK_PARAMETER
(:task_name, 'VALID_TABLE_LIST', 'SCOTT.T')
SQL> exec DBMS_ADVISOR.SET_TASK_PARAMETER
(:task_name, 'START_TIME', :start_time)
SQL> exec DBMS_ADVISOR.SET_TASK_PARAMETER
(:task_name, 'END_TIME', :end_time)
SQL> exec DBMS_SQLTUNE.CREATE_SQLSET ('STS01')
SQL> declare
c DBMS_SQLTUNE.SQLSET_CURSOR;
begin
open c for select value(t) from table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE) t;
DBMS_SQLTUNE.LOAD_SQLSET('STS01', c);
end;
SQL> exec DBMS_ADVISOR.ADD_STS_REF
(:task_name, null, 'STS01')
SQL> EXEC DBMS_ADVISOR.EXECUTE_TASK (:task_name)
SQL> select
dbms_advisor.get_task_script(:TASK_NAME)
from dual;
DBMS_ADVISOR.GET_TASK_SCRIPT(:TASK_NAME)
----------------------------------------------
Rem SQL Access Advisor: Version 11.2.0.4.0 -
Rem
Rem Username: SCOTT
Rem Task: TASK_54589
Rem Execution date:
Rem
CREATE INDEX "SCOTT"."T_IDX$$_D53D0000"
ON "SCOTT"."T"
(TO_NUMBER("X"))
COMPUTE STATISTICS;
I have retrieved the index suggestion from the SQL Cache for the table T.
Let's blindly implement it...
SQL> CREATE INDEX "SCOTT"."T_IDX$$_D5150000"
ON "SCOTT"."T"
(TO_NUMBER("X"))
COMPUTE STATISTICS;
SQL> explain plan for
select * from t where x=00000001
Explain complete.
SQL> select * from table(dbms_xplan.display)
PLAN_TABLE_OUTPUT
---------------------------------------------
Plan hash value: 4112678587
-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | T_IDX$$_D5150000 |
-----------------------------------------------
Predicate Information
(identified by operation id):
-----------------------------------------------
2 - access(TO_NUMBER("X")=00000001)
Much better. But ...
SQL> insert into t(x) values('UNKNOWN');
insert into t(x) values('UNKNOWN')
Error at line 1
ORA-01722: invalid number
Adding a function-based-index on to_number(x) to the table also implies that no number is allowed in that column. This is an application change. Be aware...