深入理解Oracle索引(21)：視圖和小表是否應該加索引

      ㈠ 視圖能使用索引嗎
     

     

        答案是不能、為什麼呢？
        我們先要知道什麼是視圖？
        視圖、本質上講、就只是一條被儲存的SQL語句而已
        那有人就要問了、為什麼物化視圖可以哩？
        這個.....這個、這根本就是兩碼事嘛、兩個完全不同的概念
        物化視圖、本質上而言、是一張表、有資料

        所以、只要基表索引使用得當、便是對視圖的最大安慰和饋贈

 

hr@ORCL> create view v_employees as select * from employees;View created.hr@ORCL> create index idx_v_employeess on v_employees(EMPLOYEE_ID);create index idx_v_employeess on v_employees(EMPLOYEE_ID)                                 *ERROR at line 1:ORA-01702: a view is not appropriate here

 

     ㈡ 小表使用索引有意義嗎
        
        一般我們會認為、小表全表掃描可能效能更好、但是、請看下面一個簡單測試

 

hr@ORCL> select * from t where y='9999a';Execution Plan----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     3 |    90 |    57   (4)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| T    |     3 |    90 |    57   (4)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("Y"='9999a')Note-----   - dynamic sampling used for this statementStatistics----------------------------------------------------------          0  recursive calls          0  db block gets        249  consistent gets          0  physical reads          0  redo size        462  bytes sent via SQL*Net to client        385  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed/* y上建立索引 */hr@ORCL> create index idx_t_y on t (y);Index created.hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T',estimate_percent=>100,cascade=>TRUE,no_invalidate=>false);PL/SQL procedure successfully completed.hr@ORCL> select * from t where y='9999a';Execution Plan----------------------------------------------------------Plan hash value: 2903481642---------------------------------------------------------------------------------------| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |         |     1 |    11 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T       |     1 |    11 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IDX_T_Y |     1 |       |     1   (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("Y"='9999a')Statistics----------------------------------------------------------          0  recursive calls          0  db block gets          4  consistent gets          0  physical reads          0  redo size        466  bytes sent via SQL*Net to client        385  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed/* 邏輯讀從 249一下子卡到4、雖然這個表有10w行、但也絕對不是大表*/

 

        小表 DML 不會很多、因此、不會有維護索引帶來的額外開銷、這是第二個理由
        
        綜上、不使用索引訪問小表、效率未必是最高、這和表記錄大小、記錄的分布有很大關係

 

 

By David Lin 
2013-06-07
Good Luck