ITPub博客

首页 > Linux操作系统 > Linux操作系统 > clustor factor 组合索引

clustor factor 组合索引

原创 Linux操作系统 作者:xpj0515 时间:2011-04-30 20:11:38 0 删除 编辑
========================================================================
                                               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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-11-08

  • 博文量
    32
  • 访问量
    52752