* What if an index is added and really should be used?
--> What's ORACLE doing behind the scene?
SQL> conn jyang/jyang@athena
Connected.
SQL> show parameters cursor
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 1000
session_cached_cursors integer 0
SQL> drop table tyu;
Table dropped.
SQL> create table tyu as select object_id, object_name, object_type from dba_objects
2 where rownum < 100;
Table created.
SQL>
SQL> set autotrace on explain
SQL> select * from tyu where object_id=6371;
OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
------------------
6371
/10948dc3_PermissionImpl
JAVA CLASS
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TYU'
SQL> set autotrace off
SQL> alter session set create_stored_outlines=CAT1;
Session altered.
SQL> select * from tyu where object_id=6371;
OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
------------------
6371
/10948dc3_PermissionImpl
JAVA CLASS
SQL> alter session set create_stored_outlines=false;
Session altered.
SQL> commit;
Commit complete.
SQL> select sql_text,name,owner, category, used as used_or_not from dba_outlines where category='CAT1';
SQL_TEXT
--------------------------------------------------------------------------------
NAME OWNER
------------------------------ ------------------------------
CATEGORY USED_OR_N
------------------------------ ---------
select * from tyu where object_id=6371
SYS_OUTLINE_030613173329042 JYANG
CAT1 UNUSED
SQL> grant select on tyu to jtest;
Grant succeeded.
SQL> conn jtest/jtest@athena
Connected.
SQL> drop table tyu;
Table dropped.
SQL> create table tyu as select * from jyang.tyu;
Table created.
SQL> alter table tyu add constraint tyu_pk primary key (object_id);
Table altered.
SQL> set autotrace on explain
SQL> select * from tyu where object_id=6371;
OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
------------------
6371
/10948dc3_PermissionImpl
JAVA CLASS
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TYU'
2 1 INDEX (UNIQUE SCAN) OF 'TYU_PK' (UNIQUE)
SQL> set autotrace off
SQL> select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%6371%';
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE_CATEGORY
----------------------------------------------------------------
select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%6371%'
EXPLAIN PLAN SET STATEMENT_ID='PLUS196790' FOR select * from tyu where object_id
=6371
select * from tyu where object_id=6371
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE_CATEGORY
----------------------------------------------------------------
select * from tyu where object_id=6371
EXPLAIN PLAN SET STATEMENT_ID='PLUS196789' FOR select * from tyu where object_id
=6371
SQL>
SQL> alter session set use_stored_outlines=CAT1;
Session altered.
SQL> alter session set sql_trace=true;
Session altered.
SQL> select * from tyu where object_id=6371;
OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
------------------
6371
/10948dc3_PermissionImpl
JAVA CLASS
SQL> alter session set sql_trace=false;
Session altered.
SQL> select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%6371%';
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE_CATEGORY
----------------------------------------------------------------
select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%6371%'
select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%6371%'
EXPLAIN PLAN SET STATEMENT_ID='PLUS196790' FOR select * from tyu where object_id
=6371
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE_CATEGORY
----------------------------------------------------------------
select * from tyu where object_id=6371
select * from tyu where object_id=6371
CAT1
EXPLAIN PLAN SET STATEMENT_ID='PLUS196789' FOR select * from tyu where object_id
=6371
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE_CATEGORY
----------------------------------------------------------------
6 rows selected.
SQL> commit;
Commit complete.
SQL> conn jyang/jyang@athena
Connected.
SQL> select sql_text,name,owner, category, used as used_or_not from dba_outlines where category='CAT1';
SQL_TEXT
--------------------------------------------------------------------------------
NAME OWNER
------------------------------ ------------------------------
CATEGORY USED_OR_N
------------------------------ ---------
select * from tyu where object_id=6371
SYS_OUTLINE_030613173329042 JYANG
CAT1 USED
SQL> exec DBMS_OUTLN.DROP_BY_CAT('CAT1');
PL/SQL procedure successfully completed.
SQL> spool off