* What if the table is not valid for the query at all?
--> 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> 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=12264;
OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
------------------
12264
/1116be6_RoundRectangle2DFloat
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> alter session set create_stored_outlines=CAT1;
Session altered.
SQL> select * from tyu where object_id=12264;
OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
------------------
12264
/1116be6_RoundRectangle2DFloat
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=12264
SYS_OUTLINE_030613174417167 JYANG
CAT1 UNUSED
SQL> conn jtest/jtest@athena
Connected.
SQL> drop table tyu;
Table dropped.
SQL> create table tyu(c1 number);
Table created.
SQL>
SQL> select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%12264%';
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE_CATEGORY
----------------------------------------------------------------
select * from tyu where object_id=12264
select * from tyu where object_id=12264
select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%12264%'
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE_CATEGORY
----------------------------------------------------------------
EXPLAIN PLAN SET STATEMENT_ID='PLUS196794' FOR select * from tyu where object_id
=12264
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=12264;
select * from tyu where object_id=12264
*
ERROR at line 1:
ORA-00904: "OBJECT_ID": invalid identifier
SQL> alter session set sql_trace=false;
Session altered.
SQL> select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%12264%';
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE_CATEGORY
----------------------------------------------------------------
select * from tyu where object_id=12264
select * from tyu where object_id=12264
select * from tyu where object_id=12264
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE_CATEGORY
----------------------------------------------------------------
select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%12264%'
select sql_text,OUTLINE_CATEGORY from v$sql where sql_text like '%12264%'
EXPLAIN PLAN SET STATEMENT_ID='PLUS196794' FOR select * from tyu where object_id
=12264
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE_CATEGORY
----------------------------------------------------------------
6 rows selected.
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=12264
SYS_OUTLINE_030613174417167 JYANG
CAT1 USED
SQL> exec DBMS_OUTLN.DROP_BY_CAT('CAT1');
PL/SQL procedure successfully completed.
SQL> spool off