Index suggestion from the access advisor

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…

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>