分区表本地索引与全局索引效率对比

关于分区表测试， 测试表 BPTNHIST_DAY ，按天分区，一共630个分区，大小为20G 。

    分区键为 AC_DT ,无主键 ，JRNNO 上有索引。

 

    说明：CASE 1  CASE 2 时 BPTNHIST_DAY 表上在 JRNNO 字段上是分区索引 ，分区键不在任何索引中

            CASE 3  时 BPTNHIST_DAY 表上在 JRNNO 字段上是全局索引 ，分区键不在任何索引中

 

    目的： 1.测试在分区键上无索引，当where 条件中有分区键做谓词时，是否能自动走相应分区。

             2.测试where 条件中JRNNO + 分区键做谓词时与单独JRNNO做谓词时的效率差异，此时JRNNO上为分区索引。

             3.测试where 条件中JRNNO + 分区键做谓词时与单独JRNNO做谓词时的效率差异，此时JRNNO上为全局索引。

             4.测试全局索引与分区索引的效率差异。

 

结论：

    以下所有情形的详细执行结果请看附件，关注Elapsed  、Execution Plan 、Statistics （consistent gets ）

    不管是哪种情形，在此次测试中的执行时间都没差异，但在使用全局索引时consistent gets 要比使用分区索引并where 条件中无分区键时要小一个固定值 。

 

CASE 1:

    分区键上无索引，当where 条件中有分区键做谓词时，执行计划能自动定位到相应分区。分区键上无需单独建索引。

    SQL 语句：

    select count(*) from MPRO2.BPTNHIST_DAY where AC_DT='20100522' ;

 

CASE 2:

    WHERE 条件中  有分区键，走 分区索引JRNNO 比 WHERE 条件中  无分区键，走 分区索引JRNNO 效率要高。

    当遇到这种情况，如果能将结果确定在某一个分区中时，在书写SQL时尽量把分区键加入WHERE 条件中。

   

    当where 条件中有分区键时，执行计划直接定位到相应的分区（PARTITION RANGE SINGLE ），然后定位到索引分区（TABLE ACCESS BY LOCAL INDEX ROWID ）

    最后执行对此分区索引的范围扫描（INDEX RANGE SCAN ），

    而where 条件中没有分区键的时候，执行计划是扫描所有分区索引（PARTITION RANGE ALL ）。

    SQL 语句：

    WHERE 条件中  有分区键，走 分区索引JRNNO ：（效率高）

    select count(*) from MPRO2.BPTNHIST_DAY where jrnno=1371263 and   AC_DT='20100522' ;

    WHERE 条件中  无分区键，走 分区索引JRNNO ：（效率低）

    select count(*) from MPRO2.BPTNHIST_DAY where jrnno=1371263 

 

CASE 3:

   WHERE 条件中  有分区键，走 全局索引JRNNO 比 WHERE 条件中  无分区键，走 全局索引JRNNO 效率无差别。

   SQL 语句：

    WHERE 条件中  有分区键，走 全局索引JRNNO ：（相同）

    select count(*) from MPRO2.BPTNHIST_DAY where jrnno=1371263 and   AC_DT='20100522' ;

    WHERE 条件中  无分区键，走 全局索引JRNNO： （相同）

    select count(*) from MPRO2.BPTNHIST_DAY where jrnno=1371263 

 

CASE 4:

   结合CASE 2 和CASE 3的测试结果，

   

   WHERE 条件中  有分区键，走 分区索引JRNNO 时与全局索引效率一样 。

   WHERE 条件中  无分区键，走 分区索引JRNNO 时比全局索引效率要低 。

分区键上无INDEX 测试：
SQL> select count(*) from MPRO2.BPTNHIST_DAY where AC_DT='20100522' ;

  COUNT(*)
----------
    577349

Elapsed: 00:00:00.52

Execution Plan
----------------------------------------------------------
Plan hash value: 3276195859

--------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |              |     1 |     6 |  2867   (1)| 00:00:52 |       |       |
|   1 |  SORT AGGREGATE         |              |     1 |     6 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|              |   578K|  3392K|  2867   (1)| 00:00:52 |   387 |   387 |
|*  3 |    TABLE ACCESS FULL    | BPTNHIST_DAY |   578K|  3392K|  2867   (1)| 00:00:52 |   387 |   387 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("AC_DT"=20100522)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5920  consistent gets
       1784  physical reads
          0  redo size
        517  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

WHERE 条件中 有分区键 走分区索引JRNNO
SQL> select count(*) from MPRO2.BPTNHIST_DAY where jrnno=1371263 and   AC_DT='20100522' ;

  COUNT(*)
----------
         1

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 4289208296

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |    12 |     2   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                     |              |     1 |    12 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE            |              |     1 |    12 |     2   (0)| 00:00:01 |   387 |   387 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| BPTNHIST_DAY |     1 |    12 |     2   (0)| 00:00:01 |   387 |   387 |
|*  4 |     INDEX RANGE SCAN                | INDX_JRNNO   |     1 |       |     1   (0)| 00:00:01 |   387 |   387 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("AC_DT"=20100522)
   4 - access("JRNNO"=1371263)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

WHERE 条件中 无分区键 走分区索引JRNNO
SQL> select count(*) from MPRO2.BPTNHIST_DAY where jrnno=1371283 ;

  COUNT(*)
----------
         9

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1123190756

---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |     1 |     6 |   611   (0)| 00:00:12 |       |       |
|   1 |  SORT AGGREGATE      |            |     1 |     6 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|            |    33 |   198 |   611   (0)| 00:00:12 |     1 |   610 |
|*  3 |    INDEX RANGE SCAN  | INDX_JRNNO |    33 |   198 |   611   (0)| 00:00:12 |     1 |   610 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("JRNNO"=1371283)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        848  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

WHERE 条件中有分区键  使用JRNNO上的全局索引
SQL> select count(*) from MPRO2.BPTNHIST_DAY where jrnno=1371263 and   AC_DT='20100522' ;

  COUNT(*)
----------
         1

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2097666864

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |    12 |   107   (0)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE                     |              |     1 |    12 |            |          |       |       |
|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| BPTNHIST_DAY |     1 |    12 |   107   (0)| 00:00:02 |   387 |   387 |
|*  3 |    INDEX RANGE SCAN                 | INDX_JRNNO   |     1 |       |     3   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("AC_DT"=20100522)
   3 - access("JRNNO"=1371263)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

使用JRNNO上的全局索引
SQL> select count(*) from MPRO2.BPTNHIST_DAY where jrnno=1371283 ;

  COUNT(*)
----------
         9

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3217367056

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |            |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| INDX_JRNNO |    33 |   198 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("JRNNO"=1371283)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/308563/viewspace-664311/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/308563/viewspace-664311/