conn hr/hr drop table t1 purge ; drop table t2 purge ; create table t1 nologging parallel 2 as select rownum id, owner, name from dba_source where rownum < 50001 ; alter table t1 noparallel; create index nui_t1_line_own on t1(id, owner) nologging parallel 2; alter index nui_t1_line_own noparallel; BEGIN dbms_stats.gather_table_stats(ownname => USER, tabname => 'T1', degree => 2, estimate_percent => dbms_stats.auto_sample_size, cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', granularity => 'ALL'); COMMIT; END; / create table t2 nologging parallel 2 as select * from t1 ; alter table t2 noparallel; create index nui_t2_line_own on t2(id, owner) nologging parallel 2; alter index nui_t2_line_own noparallel; BEGIN dbms_stats.gather_table_stats(ownname => USER, tabname => 'T2', degree => 2, estimate_percent => dbms_stats.auto_sample_size, cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', granularity => 'ALL'); COMMIT; END; / create or replace view vw_hard_coded_hints as select /*+ RULE */ t1.owner, t1.name, t2.id from t1, t2 where t1.id = t2.id ; conn hr/hr set timing on set autotrace traceonly set linesize 2000 -- less selective filter, because of the hint in the view RBO is forced -- note part gives you information that RBO is used -- select * from vw_hard_coded_hints where owner = 'SYS' ; 105854 rows selected. Elapsed: 00:00:02.43 Execution Plan ---------------------------------------------------------- Plan hash value: 4014837343 ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | |* 3 | INDEX RANGE SCAN | NUI_T1_OWNER | |* 4 | INDEX UNIQUE SCAN | UI_T2_ID | ----------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."OWNER"='SYS') 4 - access("T1"."ID"="T2"."ID") Note ----- - rule based optimizer used (consider using cbo) Statistics ---------------------------------------------------------- 362 recursive calls 0 db block gets 233588 consistent gets 644 physical reads 0 redo size 2025470 bytes sent via SQL*Net to client 78016 bytes received via SQL*Net from client 7058 SQL*Net roundtrips to/from client 10 sorts (memory) 0 sorts (disk) 105854 rows processed -- more selective filter, because of the hint in the view RBO is again forced -- select * from vw_hard_coded_hints where owner = 'OUTLN' ; 6 rows selected. Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 4014837343 ----------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | |* 3 | INDEX RANGE SCAN | NUI_T1_OWNER | |* 4 | INDEX UNIQUE SCAN | UI_T2_ID | ----------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."OWNER"='OUTLN') 4 - access("T1"."ID"="T2"."ID") Note ----- - rule based optimizer used (consider using cbo) Statistics ---------------------------------------------------------- 8 recursive calls 0 db block gets 23 consistent gets 2 physical reads 0 redo size 624 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6 rows processed -- a hint(optimizer directive) made a dream come true -- alter session set "_optimizer_ignore_hints" = TRUE ; -- less selective filter, CBO is in charge and prefers another plan select * from vw_hard_coded_hints where owner = 'SYS' ; 105854 rows selected. Elapsed: 00:00:02.42 Execution Plan ---------------------------------------------------------- Plan hash value: 3489716497 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16731 | 490K| 1023 (9)| 00:00:04 | |* 1 | HASH JOIN | | 16731 | 490K| 1023 (9)| 00:00:04 | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 16731 | 408K| 166 (4)| 00:00:01 | |* 3 | INDEX RANGE SCAN | NUI_T1_OWNER | 16849 | | 47 (5)| 00:00:01 | | 4 | INDEX FULL SCAN | UI_T2_ID | 378K| 1849K| 830 (7)| 00:00:03 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."ID"="T2"."ID") 3 - access("T1"."OWNER"='SYS') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 233521 consistent gets 0 physical reads 0 redo size 2025470 bytes sent via SQL*Net to client 78016 bytes received via SQL*Net from client 7058 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 105854 rows processed -- more selective filter, CBO is in charge and prefers another plan -- select * from vw_hard_coded_hints where owner = 'OUTLN' ; 6 rows selected. Elapsed: 00:00:00.01 Execution Plan ---------------------------------------------------------- Plan hash value: 3489716497 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16731 | 490K| 1023 (9)| 00:00:04 | |* 1 | HASH JOIN | | 16731 | 490K| 1023 (9)| 00:00:04 | | 2 | TABLE ACCESS BY INDEX ROWID| T1 | 16731 | 408K| 166 (4)| 00:00:01 | |* 3 | INDEX RANGE SCAN | NUI_T1_OWNER | 16849 | | 47 (5)| 00:00:01 | | 4 | INDEX FULL SCAN | UI_T2_ID | 378K| 1849K| 830 (7)| 00:00:03 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."ID"="T2"."ID") 3 - access("T1"."OWNER"='OUTLN') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 21 consistent gets 0 physical reads 0 redo size 624 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6 rows processed