[20160112]提示NUM_INDEX_KEY.txt

[20160112]提示NUM_INDEX_KEY.txt

--如果我們查詢,假設建立的索引是id1,id2的複合索引.
select * from t where id1=:x and id2 in(1,100);

--一般執行計劃透過索引access id1=:X,然後再過濾id2等於1和100的值.
--加入id1=:X很多,這樣索引的掃描範圍相對就大,邏輯讀也會增加.但是id2=1,100很少.
--其他id2等於2,3,99很多的情況下.

--如果改寫如下可以獲得很好的效能:
select * from t where id1=:x and id2=1
union all
select * from t where id1=:x and id2=100;

--這樣索引的掃描範圍就少.今天看電子書Apress.Expert.Oracle.SQL.Optimization.Deployment.and.Statistics.1430259779.pdf
--發現可以透過提示NUM_INDEX_KEY實現上面類似的功能,透過例子來說明:P303

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> alter session set statistics_level=all;
Session altered.

SELECT *
FROM hr.employees e
WHERE last_name = 'Grant' AND first_name IN ('Kimberely', 'Douglas')
ORDER BY last_name, first_name;

Plan hash value: 2077747057
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |      1 |        |       |     2 (100)|          |      2 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |      1 |      2 |   138 |     2   (0)| 00:00:01 |      2 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | EMP_NAME_IX |      1 |      1 |       |     1   (0)| 00:00:01 |      2 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / E@SEL$1
   2 - SEL$1 / E@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("LAST_NAME"='Grant')
       filter(("FIRST_NAME"='Douglas' OR "FIRST_NAME"='Kimberely'))

--說明例子的EMP_NAME_IX包括LAST_NAME,FIRST_NAME複合索引,可以發現執行計劃access("LAST_NAME"='Grant'),filter(("FIRST_NAME"='Douglas' OR "FIRST_NAME"='Kimberely')).

2.使用提示看看:
--摘要:
The hint NUM_INDEX_KEY Scan be used to indicate how many columns to use when performing an INDEX RANGE SCAN
when an INlist is present. The supplied hint specifies that two columns are used. This means that we need to run
two INDEX RANGE SCANoperations, driven by the INLIST ITERATORoperation. The first INDEX RANGE SCANuses
LAST_NAME = 'Grant'and FIRST_NAME = 'Douglas'as access predicates and the second INDEX RANGE SCANuses
LAST_NAME = 'Grant'and FIRST_NAME = 'Kimberly'as access predicates. I don't personally find the description of
the access predicates in the DBMS_XPLANdisplay particularly helpful in this case, so I hope my explanation has helped.

SELECT /*+ num_index_keys(e emp_name_ix 2) */
         *
    FROM hr.employees e
   WHERE last_name = 'Grant' AND first_name IN ('Kimberely', 'Douglas','AAAA')
ORDER BY last_name, first_name;

Plan hash value: 760619708
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |      1 |        |       |     2 (100)|          |      2 |00:00:00.01 |       6 |
|   1 |  INLIST ITERATOR             |             |      1 |        |       |            |          |      2 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |      3 |      2 |   138 |     2   (0)| 00:00:01 |      2 |00:00:00.01 |       6 |
|*  3 |    INDEX RANGE SCAN          | EMP_NAME_IX |      3 |      2 |       |     1   (0)| 00:00:01 |      2 |00:00:00.01 |       4 |
--------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / E@SEL$1
   3 - SEL$1 / E@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("LAST_NAME"='Grant' AND (("FIRST_NAME"='AAAA' OR "FIRST_NAME"='Douglas' OR "FIRST_NAME"='Kimberely')))

--這個提示不常用,做一個記錄.數字2應該表示access 索引的欄位吧(亂猜)