[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 索引的字段吧(乱猜)