clustor factor 组合索引

最新推荐文章于 2024-09-11 14:42:31 发布
最新推荐文章于 2024-09-11 14:42:31 发布
阅读量90 收藏
点赞数
文章标签： 人工智能
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
                                               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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24890594/viewspace-694176/

cpfengyo322322
关注
Two-factor authentication (2FA) is required for your GitHub account解决方案
weixin_43178406的博客
01-27 4万+
 本文主要介绍了Two-factor authentication (2FA) is required for your GitHub account的解决方案，希望能对使用github的同学们有所帮助。 文章目录 1. 问题描述 2. 解决方案
常见集群(Cluster)软件和技术解析
架构师技术联盟
09-13 3480
集群就是通过软件将一组服务器作为一个整体向客户提供资源。这些单个的服务器就是集群的节点。当对外提供资源的节点故障后，集群中其余的节点能够将资源接管起来，继续对客户提供资源。      集群技术的核心就是资源访问控制。由于集群中所有节点都可以访问集群对外共享的资源，当多个节点同时操作同一个资源的时候，就可能引发问题。例如节点A要对共享的存储进行写数据操作，如果节点B也要对相同位置的存储进行写操作，则
Redis Clustor(集群)
zhangzhanpu的博客
06-23 970
redis集群
分布式Redis深度历险-Clustor
weixin_34023982的博客
09-10 104
本文为分布式Redis深度历险系列的第三篇，主要内容为Redis的Clustor，也就是Redis集群功能。 更多文章见个人博客：github.com/farmerjohng… Redis集群是Redis官方提供的分布式方案，整个集群通过将所有数据分成16384个槽来进行数据共享。 集群基础实现 一个集群由多个Redis节点组成，不同的节点通过CLUSTOR MEET命令进行连接： CLUSTOR...
系列一：Hadoop 2.2 环境配置（单机配置） Single Clustor
GeeWu的小空间
02-10 890
安装JDK 7.xx或者JDK 6.xxx 先从网站上下载：http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html CentOS安装可以直接使用：JDK.rpm。例如：jdk-7u45-linux-x64.rpm Ubuntu安装可以使用：JDK.tar.gz。例如：jdk-7u4
Cluster - 基本概念
热门推荐
weixin_30394633的博客
06-24 1万+
1.1 什么是集群 　　简单的说，集群（cluster）就是一组计算机，它们作为一个整体向用户提供一组网络资源。这些单个的计算机系统就是集群的节点（node）。一个理想的集群是，用户从来不会意识到集群系统底层的节点，在他/她们看来，集群是一个系统，而非多个计算机系统。并且集群系统的管理员可以随意增加和删改集群系统的节点。 　　更详细的说，集群（一组协同工作的计算机）是充...
factor-returns.csv
06-03
《机器学习视角解析“factor-returns.csv”数据集》 在现代金融领域，机器学习技术的应用日益广泛，尤其是在投资策略的构建和优化中。这里我们关注的“factor-returns.csv”文件，是一个与金融因子回报相关的重要...
factor-shell:用 Factor 编程语言编写的 shell
06-28
Factor Shell 是一个基于 Factor 编程语言构建的命令行解释器，它提供了与传统 Unix shell 类似的交互式环境。这个项目被命名为 "factor-shell"，表明它是 Factor 语言的一个扩展，用于增强用户在命令行界面下的操作...
factor_model.m
09-11
在PCA算法提取主成分后建立因子模型投资组合，配合博文【FM】Portfolio Optimization Using Factor Models使用
生成式人工智能在新加坡的发展现状和地位
wukangjupingbb的博客
09-11 850
依据国际隐私专业人员协会(IAPP)的相关报告，2020年，上述两机构更新了该模型框架，发布了第二版，并推出了《组织实施和自我评估指南》，帮助组织评估其人工智能治理实践与型框架的匹配程度，还发布了《案例汇编》，展示了组织如何实施负责任的人工智能治理实践。2019年，金融管理局宣布与金融行业合作创建Veritas框架，为金融机构提供可验证的方法，将FEAT原则纳入其人工智能和数据分析驱动的解决方案中。新加坡在人工智能治理方面采取了部门性的方法，即通过各个行业的监管机构来管理人工智能的使用。
【提效工具】AI工作流的1-10个实际落地场景
万物皆有灵
09-09 1298
在这个快速发展的数字时代，人工智能（AI）正在以惊人的速度改变我们的工作方式。无论你是开发者、教育工作者还是内容创作者，AI工作流提示词助手都能为你提供无与伦比的支持。今天，我们将深入探讨，20个实际落地场景，帮助你更好地理解如何利用这些工具提升工作效率和创造力。💡。
从AI到大数据，数字技术服务平台全栈赋能企业升级
最新发布
smjt2024的博客
09-11 488
海量的数据中蕴含着丰富的信息和价值，数字技术服务平台能够收集、整理和分析这些数据，为企业提供精准的市场洞察和决策支持。企业可以通过参加树莓集团举办的培训课程和研讨会，了解最新的数字技术趋势和应用案例，提升自身的数字技术水平。从技术咨询、解决方案设计到系统实施和运维，数字技术服务平台为企业提供一站式的服务，让企业无需担心技术难题，专注于自身的核心业务。树莓集团的数字技术服务平台，以其先进的技术、专业的服务和丰富的经验，为企业提供了全栈赋能。在数字时代，让我们共同利用数字技术服务平台，开启企业升级的新篇章。
【自然语言处理】第二章现代语言学基础
Ausgelebt的博客
09-07 1261
从语言使用时的外部环境来看句子在交际中实际传递的信息、发挥的功能，就是调用语用系统对句子的语义做出更全面的解释。语用系统的本质是基于经验的逻辑推理。
图神经网络（2）预备知识
m0_63860007的博客
09-07 1205
对于接触过数据结构和算法的读者来说，图并不是一个陌生的概念。如果图里的边不仅表示连接关系，而且具有表示连接强弱的权重，则这个。从图的某个顶点出发，沿着图中的边访问每个顶点且只访问一次，这叫作。在图上模拟一个热扩散的过程，φ(t)是图上每个顶点的热量分布，热量传播。如果一个图的每个边都有一个方向，则称这个图为有向图。图的同构指的是两个图完全等价。很显然，这个大于等于0的，所以L是半正定的。拉普拉斯矩阵的另一个重要作用是作为图上的离散拉普拉斯算子。然地，我们把拉普拉斯算子的特征函数换成拉普拉斯矩阵的特征。
深度学习算法，该如何深入，举例说明
liyy614的博客
09-10 1098
深度学习算法的深入学习可以从理论和实践两个方面进行。理论上，深入理解深度学习需要掌握数学基础（如线性代数、概率论、微积分）、机器学习基础和深度学习框架原理。实践上，可以通过实现和优化深度学习模型来提升技能。
Python 检测人脸筛选指定尺寸人脸图片
刚刚入门的小码农
09-09 664
主要功能是处理一个指定文件夹中的所有图像文件（.jpg和.png），并根据图像中检测到的人脸特征，筛选和移动符合条件的图像。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值