基於Oracle的sql最佳化（1）

先建立表和索引
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>