＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
clustor factor
SYS@standby1/2002-01-01 00:58:27>create table test_tab as select rownum id,trunc(dbms_random.value(100,999)) t1,trunc(dbms_random.value(10000,99999)) t2,dbms_random.string('U',3) str1,dbms_random.string('U',5) str2 from dual connect by level <=1000000;
Table created.
Elapsed: 00:01:06.42
SYS@standby1/2002-01-01 01:02:35>col id for 9999999
SYS@standby1/2002-01-01 01:02:47>col t1 for 999999
SYS@standby1/2002-01-01 01:02:56>col t2 for 999999
SYS@standby1/2002-01-01 01:03:01>col str1 for a7
SYS@standby1/2002-01-01 01:03:09>col str2 for a7
SYS@standby1/2002-01-01 01:03:13>select * from test_tab where rownum<=10;
ID T1 T2 STR1 STR2
-------- ------- ------- ------- -------
1 108 80943 JXB AIRJK
2 773 59153 KEM MOMLT
3 796 89751 HXU QUULO
4 703 17214 QFH ZBQPZ
5 603 60430 PJJ LCMJP
6 104 62811 RNN ULDSM
7 619 99953 UAP BNMUC
8 509 20605 AAD DIKJD
9 330 81171 KDC EWBIA
10 518 67222 QJZ EPDVD
10 rows selected.
Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 01:04:24>analyze table test_tab compute statistics;
Table analyzed.
Elapsed: 00:00:17.56
SYS@standby1/2002-01-01 01:05:15>@table_detail.sql
Enter value for table_name: test_tab
TABLE_NAME PCT_FREE PCT_USED INI_TRANS INIT_EXT NEXT_EXT FREELISTS CHAIN_CNT ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
---------- -------- -------- --------- -------- -------- ---------- --------- -------- ------- ------------ -----------
TEST_TAB 10 40 1 64 1024 1 0 1000000 4005 90 27
1 row selected.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 15:41:04>create unique index pk_test_id on test_tab(id);
Index created.
Elapsed: 00:00:05.67
SYS@standby1/2011-04-29 17:54:14>explain plan for select * from test_tab where id<170000;
Explained.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 17:54:20>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 952433714
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 169K| 3154K| 1045 (1)| 00:00:13 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 169K| 3154K| 1045 (1)| 00:00:13 |
|* 2 | INDEX RANGE SCAN | PK_TEST_ID | 169K| | 359 (1)| 00:00:05 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<170000)
SYS@standby1/2011-04-29 17:54:05>explain plan for select * from test_tab where id<180000;
Explained.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 17:54:13>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 179K| 3339K| 1100 (2)| 00:00:14 |
|* 1 | TABLE ACCESS FULL| TEST_TAB | 179K| 3339K| 1100 (2)| 00:00:14 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<180000)
SYS@standby1/2011-04-29 17:54:22>create index ind_test_t1 on test_tab(t1);
Index created.
Elapsed: 00:00:07.65
SYS@standby1/2011-04-29 18:26:58>@index_statistics.sql
Enter value for tablename: test_tab
TYPE INDEX TABLE BLOCKS ROWS DIS_KEY KEY_ROWS LEAFBLK CLU_FACTOR
-------- -------------- ------------ ------- --------- ---------- ---------- ------- ----------
NORMAL IND_TEST_T1 TEST_TAB 4005 1000000 899 1112 2091 874010
NORMAL PK_TEST_ID TEST_TAB 4005 1000000 1000000 1 2088 4015
2 rows selected.
===============================================================================================================================
SYS@standby1/2011-04-29 17:58:52>explain plan for select * from test_tab where t1=100 or t1=101;
Explained.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 17:59:07>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2225 | 42275 | 1103 (2)| 00:00:14 |
|* 1 | TABLE ACCESS FULL| TEST_TAB | 2225 | 42275 | 1103 (2)| 00:00:14 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"=100 OR "T1"=101)
SYS@standby1/2011-04-29 17:58:37>explain plan for select * from test_tab where t1=100;
Explained.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 17:58:50>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2114646671
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1112 | 21128 | 978 (0)| 00:00:12 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1112 | 21128 | 978 (0)| 00:00:12 |
|* 2 | INDEX RANGE SCAN | IND_TEST_T1 | 1112 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"=100)
总结：当where过滤条件列的索引有一个很好的clustor factor时 也就是该数值接近于表的数据块数时
当查询条件的选择性大约17%以下时 会使用索引 而大于17％会使用全表扫描
当where过滤条件列的索引有一个较差的clustor factor时 也就是该数值接近于表的数据记录行数时
当查询条件的选择性大约1%以下时 会使用索引 而大于2％会使用全表扫描
这个主要基于全表扫描和使用索引查询各自的代价 这也就是基于成本优化器的最主要特点
上面查询的全表扫描成本00:00:14 以此为损益分界点
===============================================================================================================
索引连接index_join分析
SYS@standby1/2011-04-29 18:16:11>create index ind_test_t2 on test_tab(t2);
Index created.
Elapsed: 00:00:03.42
SYS@standby1/2011-04-29 18:20:50>create index ind_test_str1 on test_tab(str1);
Index created.
Elapsed: 00:00:06.79
SYS@standby1/2011-04-29 18:21:09>create index ind_test_str2 on test_tab(str2);
Index created.
Elapsed: 00:00:12.47
SYS@standby1/2011-04-29 18:26:58>@index_statistics.sql
Enter value for tablename: test_tab
TYPE INDEX TABLE BLOCKS ROWS DIS_KEY KEY_ROWS LEAFBLK CLU_FACTOR
-------- -------------- ------------ ------- --------- ---------- ---------- ------- ----------
NORMAL IND_TEST_STR2 TEST_TAB 4005 1000000 959277 1 2370 999730
NORMAL IND_TEST_STR1 TEST_TAB 4005 1000000 17576 56 2093 992960
NORMAL IND_TEST_T2 TEST_TAB 4005 1000000 89998 11 2231 998605
NORMAL IND_TEST_T1 TEST_TAB 4005 1000000 899 1112 2091 874010
NORMAL PK_TEST_ID TEST_TAB 4005 1000000 1000000 1 2088 4015
5 rows selected.
SYS@standby1/2011-04-29 19:03:13>select count(*) from test_tab where t1=100;
COUNT(*)
----------
1132
1 row selected.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:09:33>select count(*) from test_tab where str1 between 'AAA' and 'AGA';
COUNT(*)
----------
8793
1 row selected.
SYS@standby1/2011-04-29 19:09:24>explain plan for select * from test_tab where str1 between 'AAA' and 'AGA';
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:09:32>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2308511019
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 991 | 18829 | 989 (0)| 00:00:12 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 991 | 18829 | 989 (0)| 00:00:12 |
|* 2 | INDEX RANGE SCAN | IND_TEST_STR1 | 991 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STR1">='AAA' AND "STR1"<='AGA')
SYS@standby1/2011-04-29 19:05:52>explain plan for select * from test_tab where t1=100;
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:06:58>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2114646671
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1112 | 21128 | 978 (0)| 00:00:12 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1112 | 21128 | 978 (0)| 00:00:12 |
|* 2 | INDEX RANGE SCAN | IND_TEST_T1 | 1112 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"=100)
SYS@standby1/2011-04-29 19:11:22>explain plan for select /*+ index(test_tab ind_test_str1)*/ * from test_tab where str1 between 'AAA' and 'AGA' and t1=100;
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:11:27>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2308511019
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 989 (0)| 00:00:12 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 19 | 989 (0)| 00:00:12 |
|* 2 | INDEX RANGE SCAN | IND_TEST_STR1 | 991 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"=100)
2 - access("STR1">='AAA' AND "STR1"<='AGA')
SYS@standby1/2011-04-29 19:11:29>explain plan for select /*+ index(test_tab ind_test_t1)*/ * from test_tab where str1 between 'AAA' and 'AGA' and t1=100;
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:12:55>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2114646671
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 978 (0)| 00:00:12 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 19 | 978 (0)| 00:00:12 |
|* 2 | INDEX RANGE SCAN | IND_TEST_T1 | 1112 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STR1"<='AGA' AND "STR1">='AAA')
2 - access("T1"=100)
SYS@standby1/2011-04-29 19:12:57>explain plan for select /*+ index_join(test_tab ind_test_t1,ind_test_str1)*/ * from test_tab where str1 between 'AAA' and 'AGA' and t1=100;
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:14:41>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2114646671
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 978 (0)| 00:00:12 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 19 | 978 (0)| 00:00:12 |
|* 2 | INDEX RANGE SCAN | IND_TEST_T1 | 1112 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STR1"<='AGA' AND "STR1">='AAA')
2 - access("T1"=100)
SYS@standby1/2011-04-29 19:14:44>explain plan for select /*+ index_join(test_tab ind_test_t1,ind_test_str1)*/ t1,str1 from test_tab where str1 between 'AAA' and 'AGA' and t1=100;
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:15:36>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3556771121
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 6598 (1)| 00:01:20 |
| 1 | VIEW | index$_join$_001 | 1 | 6 | 6598 (1)| 00:01:20 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX FAST FULL SCAN| IND_TEST_T1 | 1 | 6 | 2631 (1)| 00:00:32 |
|* 4 | INDEX FAST FULL SCAN| IND_TEST_STR1 | 1 | 6 | 2634 (1)| 00:00:32 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID=ROWID)
3 - filter("T1"=100)
4 - filter("STR1"<='AGA' AND "STR1">='AAA')
总结：如果很容易就能分辨查询条件选择性的优劣，则只需选择一个最好的一个作为驱动查询条件即可，如果很难分辨，
则可以采用多个列相互组合来实现数据读取任务，这就是索引合并（使用提示 index_join）
原则：只有当索引具有相似的选择性时才比较有效 ，而在两个索引返回行数悬殊的情况下使用一个索引作为驱动查询条件，
而另外一个作为选择查询条件（过滤从数据块读取返回的数据），
概念：
驱动查询条件：在查询条件中起到缩减查询范围的条件 （对应执行计划的access）
过滤查询条件：在查询条件中起到检验并过滤数据的条件（对应执行计划的filter）
就我理解的话
以此为例
1 - filter("STR1"<='AGA' AND "STR1">='AAA')
2 - access("T1"=100)
where中出现的查询条件 当读取数据时，access查询条件只是返回满足T1=100的块
而 "STR1"<='AGA' AND "STR1">='AAA' 只是已经从物理文件中读取到符合access条件的块 然后再检验这些数据块中的记录是否符合条件
STR1"<='AGA' AND "STR1">='AAA' 符合的话就作为结果集返回 不符合的话就过滤掉 但是从物理文件读取相应的数据块到缓存的过程
是不可避免的，所以应该尽量将选择性强的列作为驱动查询条件
========================================================================================================================
组合索引条件‘between and ‘ 和 ‘＝‘分析（）
SYS@standby1/2011-04-29 19:31:11>select 26*26*26 from dual;
26*26*26
----------
17576
1 row selected.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:32:58>select 26*26*26*26*26 from dual;
26*26*26*26*26
--------------
11881376
1 row selected.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:24:55>create index con_test_t1_str1 on test_tab(t1,str1);
Index created.
Elapsed: 00:00:09.07
SYS@standby1/2011-04-29 19:25:14>create index con_test_str1_t1 on test_tab(str1,t1);
Index created.
Elapsed: 00:00:09.14
SYS@standby1/2011-04-29 19:26:28>create index con_test_t2_str2 on test_tab(t2,str2);
Index created.
Elapsed: 00:00:06.59
SYS@standby1/2011-04-29 19:27:44>create index con_test_str2_t2 on test_tab(str2,t2);
Index created.
Elapsed: 00:00:13.17
SYS@standby1/2011-04-29 19:28:16>@index_statistics.sql
Enter value for tablename: test_tab
TYPE INDEX TABLE BLOCKS ROWS DIS_KEY KEY_ROWS LEAFBLK CLU_FACTOR
-------- -------------- ------------ ------- --------- ---------- ---------- ------- ----------
NORMAL CON_TEST_STR2_ TEST_TAB 4087 1000000 1000000 1 3068 999728
T2
NORMAL CON_TEST_T2_ST TEST_TAB 4087 1000000 1000000 1 3067 999751
R2
NORMAL CON_TEST_STR1_ TEST_TAB 4087 1000000 969071 1 2647 999764
T1
NORMAL CON_TEST_T1_ST TEST_TAB 4087 1000000 969071 1 2652 999741
R1
NORMAL IND_TEST_STR2 TEST_TAB 4087 1000000 959277 1 2370 999730
NORMAL IND_TEST_STR1 TEST_TAB 4087 1000000 17576 56 2093 992960
NORMAL IND_TEST_T2 TEST_TAB 4087 1000000 89998 11 2231 998605
NORMAL IND_TEST_T1 TEST_TAB 4087 1000000 899 1112 2091 874010
NORMAL PK_TEST_ID TEST_TAB 4087 1000000 1000000 1 2088 4015
9 rows selected.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 19:34:43>explain plan for select * from test_tab where str1 between 'AAA' and 'AGA' and t1=100;
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:35:42>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4055099061
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 19 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CON_TEST_T1_STR1 | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"=100 AND "STR1">='AAA' AND "STR1"<='AGA')
当存在组合索引的时候，where条件都是驱动查询条件，
SYS@standby1/2011-04-29 19:35:44>explain plan for select /*+ index(con_test_str1_t1)*/ * from test_tab where str1 between 'AAA' and 'AGA' and t1=100;
Explained.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 19:39:19>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4055099061
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 19 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CON_TEST_T1_STR1 | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"=100 AND "STR1">='AAA' AND "STR1"<='AGA')
SYS@standby1/2011-04-29 19:40:47>explain plan for select * from test_tab where str1 between 'AAA' and 'AGA' and t1 in (select t1 from test_tab where t1 between 100 and 105);
Explained.
Elapsed: 00:00:00.03
SYS@standby1/2011-04-29 19:41:34>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2286756204
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 193K| 30 (7)| 00:00:01 |
| 1 | VIEW | VM_NWVW_2 | 49 | 193K| 30 (7)| 00:00:01 |
| 2 | HASH UNIQUE | | 49 | 1470 | 30 (7)| 00:00:01 |
|* 3 | HASH JOIN | | 49 | 1470 | 29 (4)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 7 | 189 | 12 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | CON_TEST_STR1_T1 | 7 | | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_TEST_T1 | 6680 | 20040 | 16 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"="T1")
5 - access("STR1">='AAA' AND "T1">=100 AND "STR1"<='AGA' AND "T1"<=105)
filter("T1"<=105 AND "T1">=100)
6 - access("T1">=100 AND "T1"<=105)
21 rows selected.
总结： 由于str1 between 'AAA' and 'AGA' 使用的是范围比较条件 而t1=100 使用的是等值比较条件 优先使用
即使使用了 /*+ index(con_test_str1_t1)*/提示还是 使用 CON_TEST_T1_STR1 组合索引
总结：当组合索引的第一个列没有出现在查询条件时 ，一般不会使用这个组合索引，可以采用一些措施
引导优化器按照跳跃式索引扫描（使用提示 index_ss）
组合索引创建的两个要点： 1。索引列的选择 2。列的顺序选择
原则：
1。该列是否经常出现在where条件中 （全局角度）
2。该列是否经常使用‘＝’比较查询条件 （有些情况 between 可以通过in 和子查询转化为 ‘＝’）
3。哪个列具有比较好的选择性
4。返回结果经常按照哪些列排序输出
5。何种列作为附加性列被添加（ 有效缩减查询范围 或者 select－list 条件可以使用索引连接（使用提示index_join） 仅从索引读取结果 不查询实际的表）
==============================================================================================================================
SYS@standby1/2011-04-29 19:44:07>explain plan for select * from test_tab where str1 between 'AAA' and 'AGA' and t1 in (100,101,102);
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:45:00>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3896746048
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 57 | 9 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 3 | 57 | 9 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | CON_TEST_T1_STR1 | 3 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("T1"=100 OR "T1"=101 OR "T1"=102) AND "STR1">='AAA' AND "STR1"<='AGA')
15 rows selected.
Elapsed: 00:00:00.02
SYS@standby1/2011-04-29 19:52:55>explain plan for select * from test_tab where str1 in (select str1 from test_tab where str1 between 'AAA' and 'AGA') and t1 between 100 and 102;
Explained.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 19:56:47>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3057769292
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 12135 | 14 (8)| 00:00:01 |
| 1 | VIEW | VM_NWVW_2 | 3 | 12135 | 14 (8)| 00:00:01 |
| 2 | HASH UNIQUE | | 3 | 90 | 14 (8)| 00:00:01 |
| 3 | NESTED LOOPS | | 3 | 90 | 13 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 3 | 81 | 9 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | CON_TEST_STR1_T1 | 3 | | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_TEST_STR1 | 1 | 3 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("STR1">='AAA' AND "T1">=100 AND "STR1"<='AGA' AND "T1"<=102)
filter("T1"<=102 AND "T1">=100)
6 - access("STR1"="STR1")
filter("STR1"<='AGA' AND "STR1">='AAA')
21 rows selected.
SYS@standby1/2011-04-29 20:00:30>explain plan for select * from test_tab where str1 between 'AAA' and 'AGA' and t1 between 100 and 102;
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 20:00:46>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3550294245
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 57 | 9 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 3 | 57 | 9 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CON_TEST_STR1_T1 | 3 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STR1">='AAA' AND "T1">=100 AND "STR1"<='AGA' AND "T1"<=102)
filter("T1"<=102 AND "T1">=100)
15 rows selected.
Elapsed: 00:00:00.03
SYS@standby1/2011-04-29 20:02:11>explain plan for select * from test_tab where str1 in (select str1 from test_tab where str1 between 'AAA' and 'AGA') and t1 in(100,101,102);
Explained.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 20:03:53>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2427603164
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 12135 | 16 (13)| 00:00:01 |
| 1 | VIEW | VM_NWVW_2 | 3 | 12135 | 16 (13)| 00:00:01 |
| 2 | HASH UNIQUE | | 3 | 90 | 16 (13)| 00:00:01 |
|* 3 | HASH JOIN | | 3 | 90 | 15 (7)| 00:00:01 |
| 4 | INLIST ITERATOR | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 3 | 81 | 9 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | CON_TEST_T1_STR1 | 3 | | 5 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IND_TEST_STR1 | 991 | 2973 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("STR1"="STR1")
6 - access(("T1"=100 OR "T1"=101 OR "T1"=102) AND "STR1">='AAA' AND "STR1"<='AGA')
7 - access("STR1">='AAA' AND "STR1"<='AGA')
21 rows selected.
Elapsed: 00:00:00.02
===============================================================================================＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
组合索引条件‘between and ‘ 和 ‘＝‘分析
等值比较对列组合顺序的影响 in的垫脚石作用（between 转化为‘＝’）
SYS@standby1/2011-04-29 19:45:01>explain plan for select * from test_tab where str1 between 'AAA' and 'AGA' and t1 in (100,101,102,103);
Explained.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 19:45:09>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3550294245
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 76 | 10 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 4 | 76 | 10 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CON_TEST_STR1_T1 | 4 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STR1">='AAA' AND "STR1"<='AGA')
filter("T1"=100 OR "T1"=101 OR "T1"=102 OR "T1"=103)
15 rows selected.
SYS@standby1/2011-04-29 19:45:10>explain plan for select * from test_tab where str1 in (select str1 from test_tab where str1 between 'AAA' and 'AGA') and t1 in (100,101,102,103);
Explained.
Elapsed: 00:00:00.02
SYS@standby1/2011-04-29 19:46:57>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3057769292
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 20225 | 16 (7)| 00:00:01 |
| 1 | VIEW | VM_NWVW_2 | 5 | 20225 | 16 (7)| 00:00:01 |
| 2 | HASH UNIQUE | | 5 | 150 | 16 (7)| 00:00:01 |
| 3 | NESTED LOOPS | | 5 | 150 | 15 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 4 | 108 | 10 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | CON_TEST_STR1_T1 | 4 | | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_TEST_STR1 | 1 | 3 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("STR1">='AAA' AND "STR1"<='AGA')
filter("T1"=100 OR "T1"=101 OR "T1"=102 OR "T1"=103)
6 - access("STR1"="STR1")
filter("STR1"<='AGA' AND "STR1">='AAA')
21 rows selected.
Elapsed: 00:00:00.03
SYS@standby1/2011-04-29 19:46:58>explain plan for select * from test_tab where str1 in (select str1 from test_tab where str1 between 'AAA' and 'AGA') and t1 in (100,101,102);
Explained.
Elapsed: 00:00:00.03
SYS@standby1/2011-04-29 19:47:31>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2427603164
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 12135 | 16 (13)| 00:00:01 |
| 1 | VIEW | VM_NWVW_2 | 3 | 12135 | 16 (13)| 00:00:01 |
| 2 | HASH UNIQUE | | 3 | 90 | 16 (13)| 00:00:01 |
|* 3 | HASH JOIN | | 3 | 90 | 15 (7)| 00:00:01 |
| 4 | INLIST ITERATOR | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 3 | 81 | 9 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | CON_TEST_T1_STR1 | 3 | | 5 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IND_TEST_STR1 | 991 | 2973 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("STR1"="STR1")
6 - access(("T1"=100 OR "T1"=101 OR "T1"=102) AND "STR1">='AAA' AND "STR1"<='AGA')
7 - access("STR1">='AAA' AND "STR1"<='AGA')
21 rows selected.
总结：如果没有为组合索引的第一列使用 “＝”比较的查询条件，则即使后面的查询条件使用“＝”
也不能达到缩减数据查询的作用，这些只是作用于扫描索引块（当索引占据的空间很大也很消耗资源），对
访问数据块照样能缩减查询范围
所以：应当结合列的查询条件和离散度来确定最佳组合顺序；
使用“＝”其实起到的就是跳跃扫描组合索引块
clustor factor
SYS@standby1/2002-01-01 00:58:27>create table test_tab as select rownum id,trunc(dbms_random.value(100,999)) t1,trunc(dbms_random.value(10000,99999)) t2,dbms_random.string('U',3) str1,dbms_random.string('U',5) str2 from dual connect by level <=1000000;
Table created.
Elapsed: 00:01:06.42
SYS@standby1/2002-01-01 01:02:35>col id for 9999999
SYS@standby1/2002-01-01 01:02:47>col t1 for 999999
SYS@standby1/2002-01-01 01:02:56>col t2 for 999999
SYS@standby1/2002-01-01 01:03:01>col str1 for a7
SYS@standby1/2002-01-01 01:03:09>col str2 for a7
SYS@standby1/2002-01-01 01:03:13>select * from test_tab where rownum<=10;
ID T1 T2 STR1 STR2
-------- ------- ------- ------- -------
1 108 80943 JXB AIRJK
2 773 59153 KEM MOMLT
3 796 89751 HXU QUULO
4 703 17214 QFH ZBQPZ
5 603 60430 PJJ LCMJP
6 104 62811 RNN ULDSM
7 619 99953 UAP BNMUC
8 509 20605 AAD DIKJD
9 330 81171 KDC EWBIA
10 518 67222 QJZ EPDVD
10 rows selected.
Elapsed: 00:00:00.01
SYS@standby1/2002-01-01 01:04:24>analyze table test_tab compute statistics;
Table analyzed.
Elapsed: 00:00:17.56
SYS@standby1/2002-01-01 01:05:15>@table_detail.sql
Enter value for table_name: test_tab
TABLE_NAME PCT_FREE PCT_USED INI_TRANS INIT_EXT NEXT_EXT FREELISTS CHAIN_CNT ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
---------- -------- -------- --------- -------- -------- ---------- --------- -------- ------- ------------ -----------
TEST_TAB 10 40 1 64 1024 1 0 1000000 4005 90 27
1 row selected.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 15:41:04>create unique index pk_test_id on test_tab(id);
Index created.
Elapsed: 00:00:05.67
SYS@standby1/2011-04-29 17:54:14>explain plan for select * from test_tab where id<170000;
Explained.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 17:54:20>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 952433714
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 169K| 3154K| 1045 (1)| 00:00:13 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 169K| 3154K| 1045 (1)| 00:00:13 |
|* 2 | INDEX RANGE SCAN | PK_TEST_ID | 169K| | 359 (1)| 00:00:05 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<170000)
SYS@standby1/2011-04-29 17:54:05>explain plan for select * from test_tab where id<180000;
Explained.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 17:54:13>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 179K| 3339K| 1100 (2)| 00:00:14 |
|* 1 | TABLE ACCESS FULL| TEST_TAB | 179K| 3339K| 1100 (2)| 00:00:14 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<180000)
SYS@standby1/2011-04-29 17:54:22>create index ind_test_t1 on test_tab(t1);
Index created.
Elapsed: 00:00:07.65
SYS@standby1/2011-04-29 18:26:58>@index_statistics.sql
Enter value for tablename: test_tab
TYPE INDEX TABLE BLOCKS ROWS DIS_KEY KEY_ROWS LEAFBLK CLU_FACTOR
-------- -------------- ------------ ------- --------- ---------- ---------- ------- ----------
NORMAL IND_TEST_T1 TEST_TAB 4005 1000000 899 1112 2091 874010
NORMAL PK_TEST_ID TEST_TAB 4005 1000000 1000000 1 2088 4015
2 rows selected.
===============================================================================================================================
SYS@standby1/2011-04-29 17:58:52>explain plan for select * from test_tab where t1=100 or t1=101;
Explained.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 17:59:07>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3962208483
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2225 | 42275 | 1103 (2)| 00:00:14 |
|* 1 | TABLE ACCESS FULL| TEST_TAB | 2225 | 42275 | 1103 (2)| 00:00:14 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"=100 OR "T1"=101)
SYS@standby1/2011-04-29 17:58:37>explain plan for select * from test_tab where t1=100;
Explained.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 17:58:50>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2114646671
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1112 | 21128 | 978 (0)| 00:00:12 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1112 | 21128 | 978 (0)| 00:00:12 |
|* 2 | INDEX RANGE SCAN | IND_TEST_T1 | 1112 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"=100)
总结：当where过滤条件列的索引有一个很好的clustor factor时 也就是该数值接近于表的数据块数时
当查询条件的选择性大约17%以下时 会使用索引 而大于17％会使用全表扫描
当where过滤条件列的索引有一个较差的clustor factor时 也就是该数值接近于表的数据记录行数时
当查询条件的选择性大约1%以下时 会使用索引 而大于2％会使用全表扫描
这个主要基于全表扫描和使用索引查询各自的代价 这也就是基于成本优化器的最主要特点
上面查询的全表扫描成本00:00:14 以此为损益分界点
===============================================================================================================
索引连接index_join分析
SYS@standby1/2011-04-29 18:16:11>create index ind_test_t2 on test_tab(t2);
Index created.
Elapsed: 00:00:03.42
SYS@standby1/2011-04-29 18:20:50>create index ind_test_str1 on test_tab(str1);
Index created.
Elapsed: 00:00:06.79
SYS@standby1/2011-04-29 18:21:09>create index ind_test_str2 on test_tab(str2);
Index created.
Elapsed: 00:00:12.47
SYS@standby1/2011-04-29 18:26:58>@index_statistics.sql
Enter value for tablename: test_tab
TYPE INDEX TABLE BLOCKS ROWS DIS_KEY KEY_ROWS LEAFBLK CLU_FACTOR
-------- -------------- ------------ ------- --------- ---------- ---------- ------- ----------
NORMAL IND_TEST_STR2 TEST_TAB 4005 1000000 959277 1 2370 999730
NORMAL IND_TEST_STR1 TEST_TAB 4005 1000000 17576 56 2093 992960
NORMAL IND_TEST_T2 TEST_TAB 4005 1000000 89998 11 2231 998605
NORMAL IND_TEST_T1 TEST_TAB 4005 1000000 899 1112 2091 874010
NORMAL PK_TEST_ID TEST_TAB 4005 1000000 1000000 1 2088 4015
5 rows selected.
SYS@standby1/2011-04-29 19:03:13>select count(*) from test_tab where t1=100;
COUNT(*)
----------
1132
1 row selected.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:09:33>select count(*) from test_tab where str1 between 'AAA' and 'AGA';
COUNT(*)
----------
8793
1 row selected.
SYS@standby1/2011-04-29 19:09:24>explain plan for select * from test_tab where str1 between 'AAA' and 'AGA';
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:09:32>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2308511019
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 991 | 18829 | 989 (0)| 00:00:12 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 991 | 18829 | 989 (0)| 00:00:12 |
|* 2 | INDEX RANGE SCAN | IND_TEST_STR1 | 991 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STR1">='AAA' AND "STR1"<='AGA')
SYS@standby1/2011-04-29 19:05:52>explain plan for select * from test_tab where t1=100;
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:06:58>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2114646671
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1112 | 21128 | 978 (0)| 00:00:12 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1112 | 21128 | 978 (0)| 00:00:12 |
|* 2 | INDEX RANGE SCAN | IND_TEST_T1 | 1112 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"=100)
SYS@standby1/2011-04-29 19:11:22>explain plan for select /*+ index(test_tab ind_test_str1)*/ * from test_tab where str1 between 'AAA' and 'AGA' and t1=100;
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:11:27>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2308511019
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 989 (0)| 00:00:12 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 19 | 989 (0)| 00:00:12 |
|* 2 | INDEX RANGE SCAN | IND_TEST_STR1 | 991 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"=100)
2 - access("STR1">='AAA' AND "STR1"<='AGA')
SYS@standby1/2011-04-29 19:11:29>explain plan for select /*+ index(test_tab ind_test_t1)*/ * from test_tab where str1 between 'AAA' and 'AGA' and t1=100;
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:12:55>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2114646671
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 978 (0)| 00:00:12 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 19 | 978 (0)| 00:00:12 |
|* 2 | INDEX RANGE SCAN | IND_TEST_T1 | 1112 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STR1"<='AGA' AND "STR1">='AAA')
2 - access("T1"=100)
SYS@standby1/2011-04-29 19:12:57>explain plan for select /*+ index_join(test_tab ind_test_t1,ind_test_str1)*/ * from test_tab where str1 between 'AAA' and 'AGA' and t1=100;
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:14:41>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2114646671
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 978 (0)| 00:00:12 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 19 | 978 (0)| 00:00:12 |
|* 2 | INDEX RANGE SCAN | IND_TEST_T1 | 1112 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STR1"<='AGA' AND "STR1">='AAA')
2 - access("T1"=100)
SYS@standby1/2011-04-29 19:14:44>explain plan for select /*+ index_join(test_tab ind_test_t1,ind_test_str1)*/ t1,str1 from test_tab where str1 between 'AAA' and 'AGA' and t1=100;
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:15:36>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3556771121
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 6598 (1)| 00:01:20 |
| 1 | VIEW | index$_join$_001 | 1 | 6 | 6598 (1)| 00:01:20 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX FAST FULL SCAN| IND_TEST_T1 | 1 | 6 | 2631 (1)| 00:00:32 |
|* 4 | INDEX FAST FULL SCAN| IND_TEST_STR1 | 1 | 6 | 2634 (1)| 00:00:32 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID=ROWID)
3 - filter("T1"=100)
4 - filter("STR1"<='AGA' AND "STR1">='AAA')
总结：如果很容易就能分辨查询条件选择性的优劣，则只需选择一个最好的一个作为驱动查询条件即可，如果很难分辨，
则可以采用多个列相互组合来实现数据读取任务，这就是索引合并（使用提示 index_join）
原则：只有当索引具有相似的选择性时才比较有效 ，而在两个索引返回行数悬殊的情况下使用一个索引作为驱动查询条件，
而另外一个作为选择查询条件（过滤从数据块读取返回的数据），
概念：
驱动查询条件：在查询条件中起到缩减查询范围的条件 （对应执行计划的access）
过滤查询条件：在查询条件中起到检验并过滤数据的条件（对应执行计划的filter）
就我理解的话
以此为例
1 - filter("STR1"<='AGA' AND "STR1">='AAA')
2 - access("T1"=100)
where中出现的查询条件 当读取数据时，access查询条件只是返回满足T1=100的块
而 "STR1"<='AGA' AND "STR1">='AAA' 只是已经从物理文件中读取到符合access条件的块 然后再检验这些数据块中的记录是否符合条件
STR1"<='AGA' AND "STR1">='AAA' 符合的话就作为结果集返回 不符合的话就过滤掉 但是从物理文件读取相应的数据块到缓存的过程
是不可避免的，所以应该尽量将选择性强的列作为驱动查询条件
========================================================================================================================
组合索引条件‘between and ‘ 和 ‘＝‘分析（）
SYS@standby1/2011-04-29 19:31:11>select 26*26*26 from dual;
26*26*26
----------
17576
1 row selected.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:32:58>select 26*26*26*26*26 from dual;
26*26*26*26*26
--------------
11881376
1 row selected.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:24:55>create index con_test_t1_str1 on test_tab(t1,str1);
Index created.
Elapsed: 00:00:09.07
SYS@standby1/2011-04-29 19:25:14>create index con_test_str1_t1 on test_tab(str1,t1);
Index created.
Elapsed: 00:00:09.14
SYS@standby1/2011-04-29 19:26:28>create index con_test_t2_str2 on test_tab(t2,str2);
Index created.
Elapsed: 00:00:06.59
SYS@standby1/2011-04-29 19:27:44>create index con_test_str2_t2 on test_tab(str2,t2);
Index created.
Elapsed: 00:00:13.17
SYS@standby1/2011-04-29 19:28:16>@index_statistics.sql
Enter value for tablename: test_tab
TYPE INDEX TABLE BLOCKS ROWS DIS_KEY KEY_ROWS LEAFBLK CLU_FACTOR
-------- -------------- ------------ ------- --------- ---------- ---------- ------- ----------
NORMAL CON_TEST_STR2_ TEST_TAB 4087 1000000 1000000 1 3068 999728
T2
NORMAL CON_TEST_T2_ST TEST_TAB 4087 1000000 1000000 1 3067 999751
R2
NORMAL CON_TEST_STR1_ TEST_TAB 4087 1000000 969071 1 2647 999764
T1
NORMAL CON_TEST_T1_ST TEST_TAB 4087 1000000 969071 1 2652 999741
R1
NORMAL IND_TEST_STR2 TEST_TAB 4087 1000000 959277 1 2370 999730
NORMAL IND_TEST_STR1 TEST_TAB 4087 1000000 17576 56 2093 992960
NORMAL IND_TEST_T2 TEST_TAB 4087 1000000 89998 11 2231 998605
NORMAL IND_TEST_T1 TEST_TAB 4087 1000000 899 1112 2091 874010
NORMAL PK_TEST_ID TEST_TAB 4087 1000000 1000000 1 2088 4015
9 rows selected.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 19:34:43>explain plan for select * from test_tab where str1 between 'AAA' and 'AGA' and t1=100;
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:35:42>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4055099061
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 19 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CON_TEST_T1_STR1 | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"=100 AND "STR1">='AAA' AND "STR1"<='AGA')
当存在组合索引的时候，where条件都是驱动查询条件，
SYS@standby1/2011-04-29 19:35:44>explain plan for select /*+ index(con_test_str1_t1)*/ * from test_tab where str1 between 'AAA' and 'AGA' and t1=100;
Explained.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 19:39:19>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4055099061
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 19 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CON_TEST_T1_STR1 | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"=100 AND "STR1">='AAA' AND "STR1"<='AGA')
SYS@standby1/2011-04-29 19:40:47>explain plan for select * from test_tab where str1 between 'AAA' and 'AGA' and t1 in (select t1 from test_tab where t1 between 100 and 105);
Explained.
Elapsed: 00:00:00.03
SYS@standby1/2011-04-29 19:41:34>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2286756204
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 193K| 30 (7)| 00:00:01 |
| 1 | VIEW | VM_NWVW_2 | 49 | 193K| 30 (7)| 00:00:01 |
| 2 | HASH UNIQUE | | 49 | 1470 | 30 (7)| 00:00:01 |
|* 3 | HASH JOIN | | 49 | 1470 | 29 (4)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 7 | 189 | 12 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | CON_TEST_STR1_T1 | 7 | | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_TEST_T1 | 6680 | 20040 | 16 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"="T1")
5 - access("STR1">='AAA' AND "T1">=100 AND "STR1"<='AGA' AND "T1"<=105)
filter("T1"<=105 AND "T1">=100)
6 - access("T1">=100 AND "T1"<=105)
21 rows selected.
总结： 由于str1 between 'AAA' and 'AGA' 使用的是范围比较条件 而t1=100 使用的是等值比较条件 优先使用
即使使用了 /*+ index(con_test_str1_t1)*/提示还是 使用 CON_TEST_T1_STR1 组合索引
总结：当组合索引的第一个列没有出现在查询条件时 ，一般不会使用这个组合索引，可以采用一些措施
引导优化器按照跳跃式索引扫描（使用提示 index_ss）
组合索引创建的两个要点： 1。索引列的选择 2。列的顺序选择
原则：
1。该列是否经常出现在where条件中 （全局角度）
2。该列是否经常使用‘＝’比较查询条件 （有些情况 between 可以通过in 和子查询转化为 ‘＝’）
3。哪个列具有比较好的选择性
4。返回结果经常按照哪些列排序输出
5。何种列作为附加性列被添加（ 有效缩减查询范围 或者 select－list 条件可以使用索引连接（使用提示index_join） 仅从索引读取结果 不查询实际的表）
==============================================================================================================================
SYS@standby1/2011-04-29 19:44:07>explain plan for select * from test_tab where str1 between 'AAA' and 'AGA' and t1 in (100,101,102);
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 19:45:00>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3896746048
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 57 | 9 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 3 | 57 | 9 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | CON_TEST_T1_STR1 | 3 | | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("T1"=100 OR "T1"=101 OR "T1"=102) AND "STR1">='AAA' AND "STR1"<='AGA')
15 rows selected.
Elapsed: 00:00:00.02
SYS@standby1/2011-04-29 19:52:55>explain plan for select * from test_tab where str1 in (select str1 from test_tab where str1 between 'AAA' and 'AGA') and t1 between 100 and 102;
Explained.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 19:56:47>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3057769292
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 12135 | 14 (8)| 00:00:01 |
| 1 | VIEW | VM_NWVW_2 | 3 | 12135 | 14 (8)| 00:00:01 |
| 2 | HASH UNIQUE | | 3 | 90 | 14 (8)| 00:00:01 |
| 3 | NESTED LOOPS | | 3 | 90 | 13 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 3 | 81 | 9 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | CON_TEST_STR1_T1 | 3 | | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_TEST_STR1 | 1 | 3 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("STR1">='AAA' AND "T1">=100 AND "STR1"<='AGA' AND "T1"<=102)
filter("T1"<=102 AND "T1">=100)
6 - access("STR1"="STR1")
filter("STR1"<='AGA' AND "STR1">='AAA')
21 rows selected.
SYS@standby1/2011-04-29 20:00:30>explain plan for select * from test_tab where str1 between 'AAA' and 'AGA' and t1 between 100 and 102;
Explained.
Elapsed: 00:00:00.00
SYS@standby1/2011-04-29 20:00:46>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3550294245
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 57 | 9 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 3 | 57 | 9 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CON_TEST_STR1_T1 | 3 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STR1">='AAA' AND "T1">=100 AND "STR1"<='AGA' AND "T1"<=102)
filter("T1"<=102 AND "T1">=100)
15 rows selected.
Elapsed: 00:00:00.03
SYS@standby1/2011-04-29 20:02:11>explain plan for select * from test_tab where str1 in (select str1 from test_tab where str1 between 'AAA' and 'AGA') and t1 in(100,101,102);
Explained.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 20:03:53>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2427603164
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 12135 | 16 (13)| 00:00:01 |
| 1 | VIEW | VM_NWVW_2 | 3 | 12135 | 16 (13)| 00:00:01 |
| 2 | HASH UNIQUE | | 3 | 90 | 16 (13)| 00:00:01 |
|* 3 | HASH JOIN | | 3 | 90 | 15 (7)| 00:00:01 |
| 4 | INLIST ITERATOR | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 3 | 81 | 9 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | CON_TEST_T1_STR1 | 3 | | 5 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IND_TEST_STR1 | 991 | 2973 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("STR1"="STR1")
6 - access(("T1"=100 OR "T1"=101 OR "T1"=102) AND "STR1">='AAA' AND "STR1"<='AGA')
7 - access("STR1">='AAA' AND "STR1"<='AGA')
21 rows selected.
Elapsed: 00:00:00.02
===============================================================================================＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
组合索引条件‘between and ‘ 和 ‘＝‘分析
等值比较对列组合顺序的影响 in的垫脚石作用（between 转化为‘＝’）
SYS@standby1/2011-04-29 19:45:01>explain plan for select * from test_tab where str1 between 'AAA' and 'AGA' and t1 in (100,101,102,103);
Explained.
Elapsed: 00:00:00.01
SYS@standby1/2011-04-29 19:45:09>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3550294245
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 76 | 10 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 4 | 76 | 10 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CON_TEST_STR1_T1 | 4 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STR1">='AAA' AND "STR1"<='AGA')
filter("T1"=100 OR "T1"=101 OR "T1"=102 OR "T1"=103)
15 rows selected.
SYS@standby1/2011-04-29 19:45:10>explain plan for select * from test_tab where str1 in (select str1 from test_tab where str1 between 'AAA' and 'AGA') and t1 in (100,101,102,103);
Explained.
Elapsed: 00:00:00.02
SYS@standby1/2011-04-29 19:46:57>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3057769292
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 20225 | 16 (7)| 00:00:01 |
| 1 | VIEW | VM_NWVW_2 | 5 | 20225 | 16 (7)| 00:00:01 |
| 2 | HASH UNIQUE | | 5 | 150 | 16 (7)| 00:00:01 |
| 3 | NESTED LOOPS | | 5 | 150 | 15 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 4 | 108 | 10 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | CON_TEST_STR1_T1 | 4 | | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_TEST_STR1 | 1 | 3 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("STR1">='AAA' AND "STR1"<='AGA')
filter("T1"=100 OR "T1"=101 OR "T1"=102 OR "T1"=103)
6 - access("STR1"="STR1")
filter("STR1"<='AGA' AND "STR1">='AAA')
21 rows selected.
Elapsed: 00:00:00.03
SYS@standby1/2011-04-29 19:46:58>explain plan for select * from test_tab where str1 in (select str1 from test_tab where str1 between 'AAA' and 'AGA') and t1 in (100,101,102);
Explained.
Elapsed: 00:00:00.03
SYS@standby1/2011-04-29 19:47:31>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2427603164
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 12135 | 16 (13)| 00:00:01 |
| 1 | VIEW | VM_NWVW_2 | 3 | 12135 | 16 (13)| 00:00:01 |
| 2 | HASH UNIQUE | | 3 | 90 | 16 (13)| 00:00:01 |
|* 3 | HASH JOIN | | 3 | 90 | 15 (7)| 00:00:01 |
| 4 | INLIST ITERATOR | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 3 | 81 | 9 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | CON_TEST_T1_STR1 | 3 | | 5 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IND_TEST_STR1 | 991 | 2973 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("STR1"="STR1")
6 - access(("T1"=100 OR "T1"=101 OR "T1"=102) AND "STR1">='AAA' AND "STR1"<='AGA')
7 - access("STR1">='AAA' AND "STR1"<='AGA')
21 rows selected.
总结：如果没有为组合索引的第一列使用 “＝”比较的查询条件，则即使后面的查询条件使用“＝”
也不能达到缩减数据查询的作用，这些只是作用于扫描索引块（当索引占据的空间很大也很消耗资源），对
访问数据块照样能缩减查询范围
所以：应当结合列的查询条件和离散度来确定最佳组合顺序；
使用“＝”其实起到的就是跳跃扫描组合索引块
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24890594/viewspace-694176/，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
最新文章
- 求一个字符串中某个子串出现的次数
- oracle正则表达式函数 substr instr简单分析
- 排序合并连接的使用原则和分析（use_merge）
- 嵌套循环连接的使用原则和分析（use_nl）
- index_join index_combine and_equal比较 及示例分析
- Oracle Sql提示学习笔记（转载）
- B－tree 和位图索引的各种查询条件 执行计划比较
- flashback database 闪回数据库（使用logmnr 查找合适闪回scn点）
- flashback 实现用户误操作解决方案（闪回查询和闪回版本 闪回事务查询）
- dataguard 物理standby的 failover
