先創建表和索引

create table emp_temp as select * from emp;

create index idx_mgr_temp on emp_temp(mgr);

create index idx_deptno_temp on emp_temp(deptno);

執行sql

select * from emp_temp where mgr>100 and deptno >100;

查看優化器模式：

SQL>? show parameter optimizer_mode;

NAME???????????????????????????????? TYPE??????? VALUE

------------------------------------ ----------- ------------------------------

optimizer_mode?????????????????????? string????? ALL_ROWS

SQL>? select name, value from v$parameter where name='optimizer_mode';

NAME

--------------------------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

optimizer_mode

ALL_ROWS

修改

SQL> alter session set optimizer_mode='RULE';? (scott)

SQL> set autotrace traceonly explain;

SQL> select * from emp_temp where mgr>100 and deptno >100;

Execution Plan

----------------------------------------------------------

Plan hash value: 1670750536

-------------------------------------------------------

| Id? | Operation?????????????????? | Name??????????? |

-------------------------------------------------------

|?? 0 | SELECT STATEMENT??????????? |???????????????? |

|*? 1 |? TABLE ACCESS BY INDEX ROWID| EMP_TEMP??????? |

|*? 2 |?? INDEX RANGE SCAN????????? | IDX_DEPTNO_TEMP |

-------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("MGR">100)

2 - access("DEPTNO">100)

Note

-----

- rule based optimizer used (consider using cbo)

此時如果我們發現走deptno的索引沒有mgr的效率高，我們該怎么辦？

SQL> select * from emp_temp where mgr>100 and deptno+0>100;

Execution Plan

----------------------------------------------------------

Plan hash value: 2973289657

----------------------------------------------------

| Id? | Operation?????????????????? | Name???????? |

----------------------------------------------------

|?? 0 | SELECT STATEMENT??????????? |????????????? |

|*? 1 |? TABLE ACCESS BY INDEX ROWID| EMP_TEMP???? |

|*? 2 |?? INDEX RANGE SCAN????????? | IDX_MGR_TEMP |

----------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("DEPTNO"+0>100)

2 - access("MGR">100)

Note

-----

- rule based optimizer used (consider using cbo)

SQL>

來自 “ ITPUB博客 ” ，鏈接：http://blog.itpub.net/29674916/viewspace-2132832/，如需轉載，請注明出處，否則將追究法律責任。