ITPub博客

首页 > 数据库 > Oracle > 和分区表相关的一点总结!

和分区表相关的一点总结!

Oracle 作者:410192979 时间:2016-03-22 21:47:41 0 删除 编辑
--创建2个结构完全相同,数据量几乎相同的表,只不过是分区表,一个是普通的heap表,
然后简单的做个访问时的性能对比,从而更好的理解如何合理的使用分区表
SQL> create table t(object_id number,object_name varchar2(30))
  2  partition by range(object_id)
  3  (
  4  partition p1 values less than(2000) tablespace users,
  5  partition p2 values less than(4000) tablespace users,
  6  partition p3 values less than(6000) tablespace users,
  7  partition p4 values less than(8000) tablespace users,
  8  partition p5 values less than(maxvalue) tablespace users
  9  );

表已创建。

SQL> insert into t select object_id,object_name from dba_objects;

已创建9848行。

SQL> insert into t select * from t;

已创建9848行。

SQL> insert into t select * from t;

已创建19696行。

SQL> insert into t select * from t;

已创建39392行。

SQL> insert into t select * from t;

已创建78784行。

SQL> insert into t select * from t;

已创建157568行。

SQL> insert into t select * from t;

已创建315136行。

SQL> insert into t select * from t;

已创建630272行。

SQL> commit;

提交完成。

SQL> create table t1 tablespace users as select object_id,object_name from dba_o
bjects;

表已创建。

SQL> insert into t1 select *from t1;

已创建9849行。

SQL> insert into t1 select *from t1;

已创建19698行。

SQL> insert into t1 select *from t1;

已创建39396行。

SQL> insert into t1 select *from t1;

已创建78792行。

SQL> insert into t1 select *from t1;

已创建157584行。

SQL> insert into t1 select *from t1;

已创建315168行。

SQL> insert into t1 select *from t1;

已创建630336行。

SQL> commit;

提交完成。

SQL>
--=====================================
1.先来看不垮分区访问时的性能对比,这里我们主要关注逻辑度(consistent gets),
由于第一次访问sql存在解析,因此我们对比时都看第二次访问时的情况,
下面的试验对比都是这样...
注意:目前2个表上都没有index
SQL> select * from t1 where object_id<2000;

已选择249984行。


执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   270K|    20M|  1069   (4)| 00:00:13 |
|*  1 |  TABLE ACCESS FULL| T1   |   270K|    20M|  1069   (4)| 00:00:13 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"<2000)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
        222  recursive calls
          0  db block gets
      21446  consistent gets
       4719  physical reads
          0  redo size
    7439711  bytes sent via SQL*Net to client
     183700  bytes received via SQL*Net from client
      16667  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     249984  rows processed

SQL> select * from t1 where object_id<2000;

已选择249984行。


执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   270K|    20M|  1069   (4)| 00:00:13 |
|*  1 |  TABLE ACCESS FULL| T1   |   270K|    20M|  1069   (4)| 00:00:13 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"<2000)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      21341  consistent gets
          0  physical reads
          0  redo size
    7439711  bytes sent via SQL*Net to client
     183700  bytes received via SQL*Net from client
      16667  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     249984  rows processed

SQL> select * from t where object_id<2000;

已选择249984行。


执行计划
----------------------------------------------------------
Plan hash value: 2931986080

--------------------------------------------------------------------------------

---------------

| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
Pstart| Pstop |

--------------------------------------------------------------------------------

---------------

|   0 | SELECT STATEMENT       |      |   241K|  7063K|   238  (19)| 00:00:03 |
      |       |

|   1 |  PARTITION RANGE SINGLE|      |   241K|  7063K|   238  (19)| 00:00:03 |
    1 |     1 |

|   2 |   TABLE ACCESS FULL    | T    |   241K|  7063K|   238  (19)| 00:00:03 |
    1 |     1 |

--------------------------------------------------------------------------------

---------------


Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
       1305  recursive calls
          0  db block gets
      17770  consistent gets
        890  physical reads
          0  redo size
    7439711  bytes sent via SQL*Net to client
     183700  bytes received via SQL*Net from client
      16667  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
     249984  rows processed

SQL> select * from t where object_id<2000;

已选择249984行。


执行计划
----------------------------------------------------------
Plan hash value: 2931986080

--------------------------------------------------------------------------------

---------------

| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
Pstart| Pstop |

--------------------------------------------------------------------------------

---------------

|   0 | SELECT STATEMENT       |      |   241K|  7063K|   238  (19)| 00:00:03 |
      |       |

|   1 |  PARTITION RANGE SINGLE|      |   241K|  7063K|   238  (19)| 00:00:03 |
    1 |     1 |

|   2 |   TABLE ACCESS FULL    | T    |   241K|  7063K|   238  (19)| 00:00:03 |
    1 |     1 |

--------------------------------------------------------------------------------

---------------


Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      17498  consistent gets
          0  physical reads
          0  redo size
    7439711  bytes sent via SQL*Net to client
     183700  bytes received via SQL*Net from client
      16667  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     249984  rows processed

SQL>
--========================
效果比较明显,很显然是通过访问分区表的性能更好一些,逻辑读21341大于17498,并且都没有发生物理读,
同时我们从执行计划也能看到访问t1时访问的是单个分区(PARTITION RANGE SINGLE),很明显如果我们能把数据
控制在一个分区之内的话那么分区表的性能要好于普通的heap表
--=============================
2.跨分区访问,依然是分区表的性能好,因为数据集中在了2个分区中,还是比访问
t1要少访问数据...
SQL> select * from t1 where object_id<4000;

已选择504448行。


执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   547K|    41M|  1070   (4)| 00:00:13 |
|*  1 |  TABLE ACCESS FULL| T1   |   547K|    41M|  1070   (4)| 00:00:13 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"<4000)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
      38318  consistent gets
          0  physical reads
          0  redo size
   15092711  bytes sent via SQL*Net to client
     370304  bytes received via SQL*Net from client
      33631  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     504448  rows processed

SQL> set timing on
SQL> select * from t1 where object_id<4000;

已选择504448行。

已用时间:  00: 00: 09.79

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   547K|    41M|  1070   (4)| 00:00:13 |
|*  1 |  TABLE ACCESS FULL| T1   |   547K|    41M|  1070   (4)| 00:00:13 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"<4000)

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      38236  consistent gets
          0  physical reads
          0  redo size
   15092711  bytes sent via SQL*Net to client
     370304  bytes received via SQL*Net from client
      33631  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     504448  rows processed

SQL> select * from t where object_id<4000;

已选择504448行。

已用时间:  00: 00: 09.85

执行计划
----------------------------------------------------------
Plan hash value: 1571388083

--------------------------------------------------------------------------------

-----------------

| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |

--------------------------------------------------------------------------------

-----------------

|   0 | SELECT STATEMENT         |      |   459K|    13M|   837  (53)| 00:00:11
|       |       |

|   1 |  PARTITION RANGE ITERATOR|      |   459K|    13M|   837  (53)| 00:00:11
|     1 |     2 |

|   2 |   TABLE ACCESS FULL      | T    |   459K|    13M|   837  (53)| 00:00:11
|     1 |     2 |

--------------------------------------------------------------------------------

-----------------


Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
         69  recursive calls
          1  db block gets
      36078  consistent gets
        946  physical reads
      48432  redo size
   15092711  bytes sent via SQL*Net to client
     370304  bytes received via SQL*Net from client
      33631  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     504448  rows processed

SQL> select * from t where object_id<4000;

已选择504448行。

已用时间:  00: 00: 10.12

执行计划
----------------------------------------------------------
Plan hash value: 1571388083

--------------------------------------------------------------------------------

-----------------

| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |

--------------------------------------------------------------------------------

-----------------

|   0 | SELECT STATEMENT         |      |   459K|    13M|   837  (53)| 00:00:11
|       |       |

|   1 |  PARTITION RANGE ITERATOR|      |   459K|    13M|   837  (53)| 00:00:11
|     1 |     2 |

|   2 |   TABLE ACCESS FULL      | T    |   459K|    13M|   837  (53)| 00:00:11
|     1 |     2 |

--------------------------------------------------------------------------------

-----------------


Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      35328  consistent gets
          0  physical reads
          0  redo size
   15092711  bytes sent via SQL*Net to client
     370304  bytes received via SQL*Net from client
      33631  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     504448  rows processed

SQL>
--======================
3.访问全部数据,我们发现访问heap表要比访问分区表是少了一些逻辑读:
88429<88573,其实这点差别对性能来说是无关紧要的,重要的是说明了一个问题,
那就是尽量让执行的sql少读、少些,这也是sql调整的最终目的,相差的这100
多逻辑读主要发生在查找metadata上...
SQL> select * from t1 ;

已选择1260672行。

已用时间:  00: 00: 26.70

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1482K|   111M|  1064   (3)| 00:00:13 |
|   1 |  TABLE ACCESS FULL| T1   |  1482K|   111M|  1064   (3)| 00:00:13 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
      88511  consistent gets
          0  physical reads
          0  redo size
   44940044  bytes sent via SQL*Net to client
     924869  bytes received via SQL*Net from client
      84046  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1260672  rows processed

SQL> select * from t1 ;

已选择1260672行。

已用时间:  00: 00: 24.59

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1482K|   111M|  1064   (3)| 00:00:13 |
|   1 |  TABLE ACCESS FULL| T1   |  1482K|   111M|  1064   (3)| 00:00:13 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      88429  consistent gets
          0  physical reads
          0  redo size
   44940044  bytes sent via SQL*Net to client
     924869  bytes received via SQL*Net from client
      84046  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1260672  rows processed

SQL> select * from t ;

已选择1260544行。

已用时间:  00: 00: 25.71

执行计划
----------------------------------------------------------
Plan hash value: 3557914527

--------------------------------------------------------------------------------

------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pst

art| Pstop |

--------------------------------------------------------------------------------

------------

|   0 | SELECT STATEMENT    |      |  1317K|    37M|  1094   (3)| 00:00:14 |
   |       |

|   1 |  PARTITION RANGE ALL|      |  1317K|    37M|  1094   (3)| 00:00:14 |
1 |     5 |

|   2 |   TABLE ACCESS FULL | T    |  1317K|    37M|  1094   (3)| 00:00:14 |
1 |     5 |

--------------------------------------------------------------------------------

------------


Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          7  recursive calls
          2  db block gets
      90275  consistent gets
       2133  physical reads
     114820  redo size
   39895221  bytes sent via SQL*Net to client
     924781  bytes received via SQL*Net from client
      84038  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1260544  rows processed

SQL> select * from t ;

已选择1260544行。

已用时间:  00: 00: 24.90

执行计划
----------------------------------------------------------
Plan hash value: 3557914527

--------------------------------------------------------------------------------

------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pst

art| Pstop |

--------------------------------------------------------------------------------

------------

|   0 | SELECT STATEMENT    |      |  1317K|    37M|  1094   (3)| 00:00:14 |
   |       |

|   1 |  PARTITION RANGE ALL|      |  1317K|    37M|  1094   (3)| 00:00:14 |
1 |     5 |

|   2 |   TABLE ACCESS FULL | T    |  1317K|    37M|  1094   (3)| 00:00:14 |
1 |     5 |

--------------------------------------------------------------------------------

------------


Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      88573  consistent gets
          0  physical reads
          0  redo size
   39895221  bytes sent via SQL*Net to client
     924781  bytes received via SQL*Net from client
      84038  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1260544  rows processed

SQL>
--==========================
有index的情况,创建global index:
有index其实也是一样的,首先通过index找到rowid,之后最终通过rowid
再去访问表,如果访问的数据尽可能的可以集中在一些分区内,那么访问
分区表的性能肯定要优于heap表
SQL> create index idx_t on t(object_id) tablespace users;

索引已创建。

已用时间:  00: 00: 10.21
SQL> create index idx_t1 on t1(object_id) tablespace users;

索引已创建。

已用时间:  00: 00: 06.89
SQL>
SQL> exec dbms_stats.gather_table_stats('SYS','T',CASCADE=>TRUE);

PL/SQL 过程已成功完成。

已用时间:  00: 00: 06.00
SQL> exec dbms_stats.gather_table_stats('SYS','T1',CASCADE=>TRUE);

PL/SQL 过程已成功完成。

已用时间:  00: 00: 03.21
SQL>
--有global index跨分区访问
SQL> select /*+ index(t idx_t) */ * from t where object_id<3000;

已选择377984行。

已用时间:  00: 00: 07.75

执行计划
----------------------------------------------------------
Plan hash value: 3317799281

--------------------------------------------------------------------------------

----------------------------

| Id  | Operation                          | Name  | Rows  | Bytes | Cost (%CPU)

| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------

----------------------------

|   0 | SELECT STATEMENT                   |       |   368K|  7922K|   363K  (1)

| 01:12:41 |       |       |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T     |   368K|  7922K|   363K  (1)

| 01:12:41 | ROWID | ROWID |

|*  2 |   INDEX RANGE SCAN                 | IDX_T |   368K|       |   986   (1)

| 00:00:12 |       |       |

--------------------------------------------------------------------------------

----------------------------


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

   2 - access("OBJECT_ID"<3000)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     398707  consistent gets
          0  physical reads
          0  redo size
   12757072  bytes sent via SQL*Net to client
     277563  bytes received via SQL*Net from client
      25200  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     377984  rows processed

SQL> select /*+ index(t idx_t) */ * from t where object_id<3000;

已选择377984行。

已用时间:  00: 00: 09.43

执行计划
----------------------------------------------------------
Plan hash value: 3317799281

--------------------------------------------------------------------------------

----------------------------

| Id  | Operation                          | Name  | Rows  | Bytes | Cost (%CPU)

| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------

----------------------------

|   0 | SELECT STATEMENT                   |       |   368K|  7922K|   363K  (1)

| 01:12:41 |       |       |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T     |   368K|  7922K|   363K  (1)

| 01:12:41 | ROWID | ROWID |

|*  2 |   INDEX RANGE SCAN                 | IDX_T |   368K|       |   986   (1)

| 00:00:12 |       |       |

--------------------------------------------------------------------------------

----------------------------


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

   2 - access("OBJECT_ID"<3000)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     398707  consistent gets
          0  physical reads
          0  redo size
   12757072  bytes sent via SQL*Net to client
     277563  bytes received via SQL*Net from client
      25200  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     377984  rows processed

SQL>
--======================
--有global index不垮分区访问:
SQL> select /*+ index(t1 idx_t1) */ * from t1 where object_id<2000;

已选择249984行。

已用时间:  00: 00: 05.57

执行计划
----------------------------------------------------------
Plan hash value: 50753647

--------------------------------------------------------------------------------

------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time

     |

--------------------------------------------------------------------------------

------

|   0 | SELECT STATEMENT            |        |   245K|  5264K|   245K  (1)| 00:4

9:12 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |   245K|  5264K|   245K  (1)| 00:4

9:12 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1 |   245K|       |   522   (2)| 00:0

0:07 |

--------------------------------------------------------------------------------

------


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

   2 - access("OBJECT_ID"<2000)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     266985  consistent gets
          0  physical reads
          0  redo size
    8439590  bytes sent via SQL*Net to client
     183700  bytes received via SQL*Net from client
      16667  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     249984  rows processed

SQL> select /*+ index(t1 idx_t1) */ * from t1 where object_id<2000;

已选择249984行。

已用时间:  00: 00: 05.42

执行计划
----------------------------------------------------------
Plan hash value: 50753647

--------------------------------------------------------------------------------

------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time

     |

--------------------------------------------------------------------------------

------

|   0 | SELECT STATEMENT            |        |   245K|  5264K|   245K  (1)| 00:4

9:12 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |   245K|  5264K|   245K  (1)| 00:4

9:12 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1 |   245K|       |   522   (2)| 00:0

0:07 |

--------------------------------------------------------------------------------

------


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

   2 - access("OBJECT_ID"<2000)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     266985  consistent gets
          0  physical reads
          0  redo size
    8439590  bytes sent via SQL*Net to client
     183700  bytes received via SQL*Net from client
      16667  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     249984  rows processed

SQL> select /*+ index(t idx_t) */ * from t where object_id<2000;

已选择249984行。

已用时间:  00: 00: 04.93

执行计划
----------------------------------------------------------
Plan hash value: 2063514567

--------------------------------------------------------------------------------

----------------------------

| Id  | Operation                          | Name  | Rows  | Bytes | Cost (%CPU)

| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------

----------------------------

|   0 | SELECT STATEMENT                   |       |   259K|  5073K|  1241K  (1)

| 04:08:20 |       |       |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T     |   259K|  5073K|  1241K  (1)

| 04:08:20 |     1 |     1 |

|*  2 |   INDEX RANGE SCAN                 | IDX_T |  1260K|       |  3366   (1)

| 00:00:41 |       |       |

--------------------------------------------------------------------------------

----------------------------


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

   2 - access("OBJECT_ID"<2000)


统计信息
----------------------------------------------------------
         44  recursive calls
          0  db block gets
     262733  consistent gets
          0  physical reads
          0  redo size
    8439590  bytes sent via SQL*Net to client
     183700  bytes received via SQL*Net from client
      16667  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     249984  rows processed

SQL>
SQL> select /*+ index(t idx_t) */ * from t where object_id<2000;

已选择249984行。

已用时间:  00: 00: 04.62

执行计划
----------------------------------------------------------
Plan hash value: 2063514567

--------------------------------------------------------------------------------

----------------------------

| Id  | Operation                          | Name  | Rows  | Bytes | Cost (%CPU)

| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------

----------------------------

|   0 | SELECT STATEMENT                   |       |   259K|  5073K|  1241K  (1)

| 04:08:20 |       |       |

|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T     |   259K|  5073K|  1241K  (1)

| 04:08:20 |     1 |     1 |

|*  2 |   INDEX RANGE SCAN                 | IDX_T |  1260K|       |  3366   (1)

| 00:00:41 |       |       |

--------------------------------------------------------------------------------

----------------------------


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

   2 - access("OBJECT_ID"<2000)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     262727  consistent gets
          0  physical reads
          0  redo size
    8439590  bytes sent via SQL*Net to client
     183700  bytes received via SQL*Net from client
      16667  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     249984  rows processed

SQL>
--======================
SQL> exec dbms_stats.gather_table_stats('SYS','T',CASCADE=>TRUE);

PL/SQL 过程已成功完成。

已用时间:  00: 00: 05.82
SQL> exec dbms_stats.gather_table_stats('SYS','T1',CASCADE=>TRUE);

PL/SQL 过程已成功完成。

已用时间:  00: 00: 03.17
SQL>
--local index:
SQL> select /*+ index(t1 idx_t1) */ * from t1 where object_id<3000;

已选择377984行。

已用时间:  00: 00: 09.46

执行计划
----------------------------------------------------------
Plan hash value: 50753647

--------------------------------------------------------------------------------

------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time

     |

--------------------------------------------------------------------------------

------

|   0 | SELECT STATEMENT            |        |   370K|  7963K|   371K  (1)| 01:1

4:16 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |   370K|  7963K|   371K  (1)| 01:1

4:16 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1 |   370K|       |   786   (2)| 00:0

0:10 |

--------------------------------------------------------------------------------

------


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

   2 - access("OBJECT_ID"<3000)


统计信息
----------------------------------------------------------
        150  recursive calls
          0  db block gets
     403726  consistent gets
          0  physical reads
          0  redo size
   12757072  bytes sent via SQL*Net to client
     277563  bytes received via SQL*Net from client
      25200  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
     377984  rows processed

SQL> select /*+ index(t1 idx_t1) */ * from t1 where object_id<3000;

已选择377984行。

已用时间:  00: 00: 08.28

执行计划
----------------------------------------------------------
Plan hash value: 50753647

--------------------------------------------------------------------------------

------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time

     |

--------------------------------------------------------------------------------

------

|   0 | SELECT STATEMENT            |        |   370K|  7963K|   371K  (1)| 01:1

4:16 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |   370K|  7963K|   371K  (1)| 01:1

4:16 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1 |   370K|       |   786   (2)| 00:0

0:10 |

--------------------------------------------------------------------------------

------


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

   2 - access("OBJECT_ID"<3000)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     403706  consistent gets
          0  physical reads
          0  redo size
   12757072  bytes sent via SQL*Net to client
     277563  bytes received via SQL*Net from client
      25200  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     377984  rows processed

SQL> select /*+ index(t idx_t) */ * from t where object_id<3000;

已选择377984行。

已用时间:  00: 00: 07.17

执行计划
----------------------------------------------------------
Plan hash value: 3038871768

--------------------------------------------------------------------------------

----------------------------

| Id  | Operation                          | Name  | Rows  | Bytes | Cost (%CPU)

| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------

----------------------------

|   0 | SELECT STATEMENT                   |       |   368K|  7566K|   363K  (1)

| 01:12:40 |       |       |

|   1 |  PARTITION RANGE ITERATOR          |       |   368K|  7566K|   363K  (1)

| 01:12:40 |     1 |     2 |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T     |   368K|  7566K|   363K  (1)

| 01:12:40 |     1 |     2 |

|*  3 |    INDEX RANGE SCAN                | IDX_T |   368K|       |   783   (2)

| 00:00:10 |     1 |     2 |

--------------------------------------------------------------------------------

----------------------------


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

   3 - access("OBJECT_ID"<3000)


统计信息
----------------------------------------------------------
        195  recursive calls
          0  db block gets
     398680  consistent gets
          0  physical reads
          0  redo size
    5145251  bytes sent via SQL*Net to client
     277563  bytes received via SQL*Net from client
      25200  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
     377984  rows processed

SQL> select /*+ index(t idx_t) */ * from t where object_id<3000;

已选择377984行。

已用时间:  00: 00: 07.42

执行计划
----------------------------------------------------------
Plan hash value: 3038871768

--------------------------------------------------------------------------------

----------------------------

| Id  | Operation                          | Name  | Rows  | Bytes | Cost (%CPU)

| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------

----------------------------

|   0 | SELECT STATEMENT                   |       |   368K|  7566K|   363K  (1)

| 01:12:40 |       |       |

|   1 |  PARTITION RANGE ITERATOR          |       |   368K|  7566K|   363K  (1)

| 01:12:40 |     1 |     2 |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T     |   368K|  7566K|   363K  (1)

| 01:12:40 |     1 |     2 |

|*  3 |    INDEX RANGE SCAN                | IDX_T |   368K|       |   783   (2)

| 00:00:10 |     1 |     2 |

--------------------------------------------------------------------------------

----------------------------


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

   3 - access("OBJECT_ID"<3000)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     398633  consistent gets
          0  physical reads
          0  redo size
    5145251  bytes sent via SQL*Net to client
     277563  bytes received via SQL*Net from client
      25200  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     377984  rows processed

SQL>
--===============================
有local index不垮分区访问:
SQL> select /*+ index(t idx_t) */ * from t where object_id<2000;

已选择249984行。

已用时间:  00: 00: 04.93

执行计划
----------------------------------------------------------
Plan hash value: 3965956311

--------------------------------------------------------------------------------

----------------------------

| Id  | Operation                          | Name  | Rows  | Bytes | Cost (%CPU)

| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------

----------------------------

|   0 | SELECT STATEMENT                   |       |   254K|  4962K|   245K  (1)

| 00:49:11 |       |       |

|   1 |  PARTITION RANGE SINGLE            |       |   254K|  4962K|   245K  (1)

| 00:49:11 |     1 |     1 |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T     |   254K|  4962K|   245K  (1)

| 00:49:11 |     1 |     1 |

|*  3 |    INDEX RANGE SCAN                | IDX_T |   249K|       |   528   (2)

| 00:00:07 |     1 |     1 |

--------------------------------------------------------------------------------

----------------------------


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

   3 - access("OBJECT_ID"<2000)


统计信息
----------------------------------------------------------
         24  recursive calls
          0  db block gets
     262679  consistent gets
          0  physical reads
          0  redo size
    3406055  bytes sent via SQL*Net to client
     183700  bytes received via SQL*Net from client
      16667  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     249984  rows processed

SQL> select /*+ index(t idx_t) */ * from t where object_id<2000;

已选择249984行。

已用时间:  00: 00: 03.48

执行计划
----------------------------------------------------------
Plan hash value: 3965956311

--------------------------------------------------------------------------------

----------------------------

| Id  | Operation                          | Name  | Rows  | Bytes | Cost (%CPU)

| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------

----------------------------

|   0 | SELECT STATEMENT                   |       |   254K|  4962K|   245K  (1)

| 00:49:11 |       |       |

|   1 |  PARTITION RANGE SINGLE            |       |   254K|  4962K|   245K  (1)

| 00:49:11 |     1 |     1 |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T     |   254K|  4962K|   245K  (1)

| 00:49:11 |     1 |     1 |

|*  3 |    INDEX RANGE SCAN                | IDX_T |   249K|       |   528   (2)

| 00:00:07 |     1 |     1 |

--------------------------------------------------------------------------------

----------------------------


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

   3 - access("OBJECT_ID"<2000)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     262676  consistent gets
          0  physical reads
          0  redo size
    3406055  bytes sent via SQL*Net to client
     183700  bytes received via SQL*Net from client
      16667  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     249984  rows processed

SQL> select /*+ index(t1 idx_t1) */ * from t1 where object_id<2000;

已选择249984行。

已用时间:  00: 00: 05.40

执行计划
----------------------------------------------------------
Plan hash value: 50753647

--------------------------------------------------------------------------------

------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time

     |

--------------------------------------------------------------------------------

------

|   0 | SELECT STATEMENT            |        |   247K|  5306K|   247K  (1)| 00:4

9:29 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |   247K|  5306K|   247K  (1)| 00:4

9:29 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1 |   247K|       |   525   (2)| 00:0

0:07 |

--------------------------------------------------------------------------------

------


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

   2 - access("OBJECT_ID"<2000)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     266985  consistent gets
          0  physical reads
          0  redo size
    8439590  bytes sent via SQL*Net to client
     183700  bytes received via SQL*Net from client
      16667  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     249984  rows processed

SQL> select /*+ index(t1 idx_t1) */ * from t1 where object_id<2000;

已选择249984行。

已用时间:  00: 00: 05.70

执行计划
----------------------------------------------------------
Plan hash value: 50753647

--------------------------------------------------------------------------------

------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time

     |

--------------------------------------------------------------------------------

------

|   0 | SELECT STATEMENT            |        |   247K|  5306K|   247K  (1)| 00:4

9:29 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |   247K|  5306K|   247K  (1)| 00:4

9:29 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1 |   247K|       |   525   (2)| 00:0

0:07 |

--------------------------------------------------------------------------------

------


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

   2 - access("OBJECT_ID"<2000)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     266985  consistent gets
          0  physical reads
          0  redo size
    8439590  bytes sent via SQL*Net to client
     183700  bytes received via SQL*Net from client
      16667  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     249984  rows processed

SQL>
--=========================
SQL> create index idx_t_name on t(object_name) tablespace users;

索引已创建。

SQL>
--=========================
SQL> select index_name,status from dba_indexes where index_name='IDX_T_NAME';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_T_NAME                     VALID

SQL> alter table t truncate partition p4;

表被截断。
--在分区表上创建的global index(注意非分区),即使我们truncate partition
其实这个global index是不会自动维护的。
SQL> select index_name,status from dba_indexes where index_name='IDX_T_NAME';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_T_NAME                     UNUSABLE

SQL> COL OBJECT_NAME FORMAT A20
SQL> select object_name,status from dba_objects where object_name='IDX_T_NAME';

OBJECT_NAME          STATUS
-------------------- -------
IDX_T_NAME           VALID

SQL>
SQL> select bytes/1024/1024 m from dba_segments where segment_name='IDX_T_NAME';


         M
----------
        47

SQL> alter index idx_t_name rebuild;

索引已更改。

SQL> select bytes/1024/1024 m from dba_segments where segment_name='IDX_T_NAME';


         M
----------
        37

SQL> select index_name,status from dba_indexes where index_name='IDX_T_NAME';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_T_NAME                     VALID

SQL>
--==========================
验证一下dml对global index的影响,dml操作oracle会自动update global index(注意:并非global partition index),这个
比较好理解,也应该这样做...
SQL> select name,del_lf_rows,del_lf_rows_len from index_stats;

NAME                           DEL_LF_ROWS DEL_LF_ROWS_LEN
------------------------------ ----------- ---------------
IDX_T_NAME                             256           10112

SQL>
SQL> delete from t where object_id=5002;

已删除128行。

SQL> commit;

提交完成。

SQL> analyze index idx_t_name validate structure;

索引已分析

SQL> select name,del_lf_rows,del_lf_rows_len from index_stats;

NAME                           DEL_LF_ROWS DEL_LF_ROWS_LEN
------------------------------ ----------- ---------------
IDX_T_NAME                             384           15616

SQL>
SQL> select index_name,status from dba_indexes where index_name='IDX_T_NAME';

INDEX_NAME                     STATUS
------------------------------ --------
IDX_T_NAME                     VALID
--=========================
验证在分区表上创建主键和唯一索引的情况:
SQL> truncate table t ;

表被截断。

SQL> alter table t add pk number;

表已更改。

SQL> insert into t(pk,object_id,object_name) select rownum,object_id,object_name
from t1;

已创建1260672行。

SQL> commit;

提交完成。

SQL> alter table t add constraint pk_t primary key (pk);

表已更改。

SQL> alter table t drop constraint pk_t;

表已更改。

SQL> create unique index idx_t_pk on t(pk) tablespace users;

索引已创建。
SQL> create unique index idx_t_pk on t(pk) local tablespace users;
create unique index idx_t_pk on t(pk) local tablespace users
                                *
第 1 行出现错误:
ORA-14039: 分区列必须构成 UNIQUE 索引的关键字列子集


SQL> create unique index idx_t_pk on t(pk,object_id) local tablespace users;

索引已创建。

SQL> drop index idx_t_pk;

索引已删除。
SQL> alter table t add constraint pk_t primary key (pk)  using index local;
alter table t add constraint pk_t primary key (pk)  using index local
*
第 1 行出现错误:
ORA-14039: 分区列必须构成 UNIQUE 索引的关键字列子集


SQL> alter table t add constraint pk_t primary key (pk,object_id)  using index l
ocal;

表已更改。

SQL> alter table t drop constraint pk_t;

表已更改。

SQL> create  index idx_t_pk on t(pk) local tablespace users;

索引已创建。

SQL> drop index idx_t_pk;

索引已删除。

SQL>
结论:不管我们在创建unique index还是添加primary key时,只要我们
想把index创建成local分区index,那么就应该至少包含分区列...而不是象
网上有些人说的在分区表上创建primary key或者nuique index时必须包含分区列。
--=======================
ddl对local partition index的影响,oracle会自动维护ddl对local partition index的影响:
SQL> select name,del_lf_rows,del_lf_rows_len from index_stats;

NAME                           DEL_LF_ROWS DEL_LF_ROWS_LEN
------------------------------ ----------- ---------------
IDX_T                                    0               0

SQL> alter table t truncate partition p4;

表被截断。

SQL> analyze index idx_t validate structure;

索引已分析

SQL> select name,del_lf_rows,del_lf_rows_len from index_stats;

NAME                           DEL_LF_ROWS DEL_LF_ROWS_LEN
------------------------------ ----------- ---------------
IDX_T                                    0               0
--看来通过analyze index idx_t validate structure;没法验证ddl对local partition index的维护,因为
在truncate partition前后index_stats里的数据没有变化
SQL> select index_name,partition_name from dba_ind_partitions where index_name i
n ('IDX_T','IDX_T1','IDX_T_NAME');

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
IDX_T                          P5
IDX_T                          P4
IDX_T                          P3
IDX_T                          P2
IDX_T                          P1

SQL> alter table t drop partition p4;

表已更改。

SQL> select index_name,partition_name from dba_ind_partitions where index_name i
n ('IDX_T','IDX_T1','IDX_T_NAME');

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
IDX_T                          P5
IDX_T                          P3
IDX_T                          P2
IDX_T                          P1
--drop partition之后和该分区相关的local partition index也被drop了...
SQL> select object_name,subobject_name,last_ddl_time from dba_objects where obje
ct_name='IDX_T';

OBJECT_NAM SUBOBJECT_NAME                 LAST_DDL_TIME
---------- ------------------------------ -------------------
IDX_T      P1                             2010/11/02 12:55:38
IDX_T      P2                             2010/11/02 12:55:38
IDX_T      P3                             2010/11/02 12:55:38
IDX_T      P5                             2010/11/02 12:55:38
IDX_T                                     2010/11/02 13:42:46

SQL> alter table t truncate partition p3;

表被截断。

SQL> select object_name,subobject_name,last_ddl_time from dba_objects where obje
ct_name='IDX_T';

OBJECT_NAM SUBOBJECT_NAME                 LAST_DDL_TIME
---------- ------------------------------ -------------------
IDX_T      P1                             2010/11/02 12:55:38
IDX_T      P2                             2010/11/02 12:55:38
IDX_T      P3                             2010/11/02 13:50:29
IDX_T      P5                             2010/11/02 12:55:38
IDX_T                                     2010/11/02 13:50:29
--通过ddl里的时间我们可以清楚的发现在truncate partition时oracle维护了
其对应的local index,因为p3对应的ddl时间由原来的12:55:38变成了13:50:29
SQL> select  segment_name,partition_name,bytes/1024/1024 m from dba_segments whe
re segment_name='IDX_T';

SEGMENT_NAME         PARTITION_NAME                          M
-------------------- ------------------------------ ----------
IDX_T                P1                                      5
IDX_T                P2                                      5
IDX_T                P3                                  .0625
IDX_T                P5                                      6

SQL> alter table t truncate partition p2;

表被截断。

SQL> select  segment_name,partition_name,bytes/1024/1024 m from dba_segments whe
re segment_name='IDX_T';

SEGMENT_NAME         PARTITION_NAME                          M
-------------------- ------------------------------ ----------
IDX_T                P1                                      5
IDX_T                P2                                  .0625
IDX_T                P3                                  .0625
IDX_T                P5                                      6

SQL>
--当然通过local index的大小我们也可以清楚的观查到,local partition index
p2的大小在truncate p2之前是5m,之后变成了0.625m,可见oracle维护了local index
--======================
dml对global partition index的影响:

SQL> select name,del_lf_rows,del_lf_rows_len from index_stats;

NAME                           DEL_LF_ROWS DEL_LF_ROWS_LEN
------------------------------ ----------- ---------------
IDX_T                                    0               0

SQL> delete from t where object_id=100;

已删除128行。

SQL> commit;

提交完成。

SQL> analyze index idx_t_g validate structure;

索引已分析

SQL> select name,del_lf_rows,del_lf_rows_len from index_stats;

NAME                           DEL_LF_ROWS DEL_LF_ROWS_LEN
------------------------------ ----------- ---------------
IDX_T_G                                127            2565

SQL>
--很显然oracle会自动维护dml对global partition index的维护,这个其实
不用验证也没有问题,如果不能自动维护dml,那么怎么使用这种类型的index
--=========================
dll对global partition index的影响:
SQL> select object_name,subobject_name,last_ddl_time from dba_objects where obje
ct_name='IDX_T_G';

OBJECT_NAM SUBOBJECT_NAME                 LAST_DDL_TIME
---------- ------------------------------ -------------------
IDX_T_G    GP1                            2010/11/02 14:17:49
IDX_T_G    GP2                            2010/11/02 14:17:49
IDX_T_G    GP3                            2010/11/02 14:17:49
IDX_T_G    GP4                            2010/11/02 14:17:49
IDX_T_G    GP5                            2010/11/02 14:17:49
IDX_T_G    GP6                            2010/11/02 14:17:49
IDX_T_G    GP_MAX                         2010/11/02 14:17:49
IDX_T_G                                   2010/11/02 14:17:49

已选择8行。

SQL> select  segment_name,partition_name,bytes/1024/1024 m from dba_segments whe
re segment_name='IDX_T_G';

SEGMENT_NAME         PARTITION_NAME                          M
-------------------- ------------------------------ ----------
IDX_T_G              GP1                                 .0625
IDX_T_G              GP2                                 .0625
IDX_T_G              GP3                                 .0625
IDX_T_G              GP4                                 .0625
IDX_T_G              GP5                                 .0625
IDX_T_G              GP6                                 .0625
IDX_T_G              GP_MAX                                 12

已选择7行。

SQL> alter table t truncate partition p5;

表被截断。

SQL> select object_name,subobject_name,last_ddl_time from dba_objects where obje
ct_name='IDX_T_G';

OBJECT_NAM SUBOBJECT_NAME                 LAST_DDL_TIME
---------- ------------------------------ -------------------
IDX_T_G    GP1                            2010/11/02 14:17:49
IDX_T_G    GP2                            2010/11/02 14:17:49
IDX_T_G    GP3                            2010/11/02 14:17:49
IDX_T_G    GP4                            2010/11/02 14:17:49
IDX_T_G    GP5                            2010/11/02 14:17:49
IDX_T_G    GP6                            2010/11/02 14:17:49
IDX_T_G    GP_MAX                         2010/11/02 14:17:49
IDX_T_G                                   2010/11/02 14:17:49

已选择8行。

SQL> select  segment_name,partition_name,bytes/1024/1024 m from dba_segments whe
re segment_name='IDX_T_G';

SEGMENT_NAME         PARTITION_NAME                          M
-------------------- ------------------------------ ----------
IDX_T_G              GP1                                 .0625
IDX_T_G              GP2                                 .0625
IDX_T_G              GP3                                 .0625
IDX_T_G              GP4                                 .0625
IDX_T_G              GP5                                 .0625
IDX_T_G              GP6                                 .0625
IDX_T_G              GP_MAX                                 12

已选择7行。

SQL>
--global index partition GP_MAX记录的数据>9000,而表分区p5的数据>8000,
在truncate p5之后对比GP_MAX前后的ddl修改时间和段大小,发现都没有任何变化,
我就认为ddl对global index partition不自动维护吧,实在没有太好的验证办法,当然
可以dump index的结构来看,感兴趣的自己测试一下吧。
其实从下面查询index的状态就可以看出来,此时的index状态全部变成了UNUSABLE:
SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_T_G                        GP1                            UNUSABLE
IDX_T_G                        GP2                            UNUSABLE
IDX_T_G                        GP3                            UNUSABLE
IDX_T_G                        GP4                            UNUSABLE
IDX_T_G                        GP5                            UNUSABLE
IDX_T_G                        GP6                            UNUSABLE
IDX_T_G                        GP_MAX                         UNUSABLE

已选择7行。

SQL>
尝试重建这个global partition index :
SQL> alter index idx_t_g rebuild;
alter index idx_t_g rebuild
            *
第 1 行出现错误:
ORA-14086: 不能将分区索引作为整体重建


SQL>
--看来只能一个一个重建了:
SQL> alter index idx_t_g rebuild partition gp1 ;

索引已更改。

SQL> alter index idx_t_g rebuild partition gp2 ;

索引已更改。

SQL> alter index idx_t_g rebuild partition gp3 ;

索引已更改。

SQL> alter index idx_t_g rebuild partition gp4 ;

索引已更改。

SQL> alter index idx_t_g rebuild partition gp5 ;

索引已更改。

SQL> alter index idx_t_g rebuild partition gp6 ;

索引已更改。

SQL> alter index idx_t_g rebuild partition gp_max ;

索引已更改。

SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_T_G                        GP1                            USABLE
IDX_T_G                        GP2                            USABLE
IDX_T_G                        GP3                            USABLE
IDX_T_G                        GP4                            USABLE
IDX_T_G                        GP5                            USABLE
IDX_T_G                        GP6                            USABLE
IDX_T_G                        GP_MAX                         USABLE

已选择7行。

SQL>
--重建之后状态变成了USABLE,注意只要我们truncate一个分区,那么global
partition index的分区index的状态全部变成UNUSABLE
如何自动维护global partition index:
SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_T_G                        GP1                            USABLE
IDX_T_G                        GP2                            USABLE
IDX_T_G                        GP3                            USABLE
IDX_T_G                        GP4                            USABLE
IDX_T_G                        GP5                            USABLE
IDX_T_G                        GP6                            USABLE
IDX_T_G                        GP_MAX                         USABLE

已选择7行。

SQL> alter table t truncate partition p1 update global indexes;

表被截断。

SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_T_G                        GP1                            USABLE
IDX_T_G                        GP2                            USABLE
IDX_T_G                        GP3                            USABLE
IDX_T_G                        GP4                            USABLE
IDX_T_G                        GP5                            USABLE
IDX_T_G                        GP6                            USABLE
IDX_T_G                        GP_MAX                         USABLE

已选择7行。

SQL>
显然在ddl语句truncate后面加上update global indexes全局分区index的状态都变成了USABLE
,很显然oracle自动为了index,不过如果分区表和分区index比较大的话,那么update global indexes
的处理时间可能会很长,通过锁定表的时间也会很长,所以使用update global indexes子句要慎重。
如果全局分区index的状态原来就是UNUSABLE,那么即使加上update global indexes子句,oracle也不会
自动维护index:
SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_T_G                        GP1                            UNUSABLE
IDX_T_G                        GP2                            UNUSABLE
IDX_T_G                        GP3                            UNUSABLE
IDX_T_G                        GP4                            UNUSABLE
IDX_T_G                        GP5                            UNUSABLE
IDX_T_G                        GP6                            UNUSABLE
IDX_T_G                        GP_MAX                         UNUSABLE

已选择7行。

SQL> alter table t truncate partition p1 update global indexes;

表被截断。

SQL>
SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_T_G                        GP1                            UNUSABLE
IDX_T_G                        GP2                            UNUSABLE
IDX_T_G                        GP3                            UNUSABLE
IDX_T_G                        GP4                            UNUSABLE
IDX_T_G                        GP5                            UNUSABLE
IDX_T_G                        GP6                            UNUSABLE
IDX_T_G                        GP_MAX                         UNUSABLE

已选择7行。
--==========================
下面简单对比一下存在global partition index的分区表和heap表的性能:
SQL> desc t1
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------

OBJECT_ID                                          NUMBER
OBJECT_NAME                                        VARCHAR2(128)

SQL> desc t
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------

OBJECT_ID                                          NUMBER
OBJECT_NAME                                        VARCHAR2(30)
PK                                                 NUMBER

SQL> insert into t select t1.*,rownum from t1;

已创建1260672行。

SQL> commit;

提交完成。

SQL> truncate table t1;

表被截断。

SQL> alter table t1 add pk number;

表已更改。

SQL> insert into t1 select * from t;

已创建1260672行。

SQL> commit;

提交完成。

SQL> select index_name,partition_name,status from dba_ind_partitions where index
_name in ('IDX_T_G');

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
IDX_T_G                        GP1                            USABLE
IDX_T_G                        GP2                            USABLE
IDX_T_G                        GP3                            USABLE
IDX_T_G                        GP4                            USABLE
IDX_T_G                        GP5                            USABLE
IDX_T_G                        GP6                            USABLE
IDX_T_G                        GP_MAX                         USABLE

已选择7行。

SQL> create index idx_t1_pk on t1(pk) tablespace users;

索引已创建。

SQL> set autotrace traceonly
SQL>
--===========================
SQL> select * from t1 where pk<3000;

已选择2999行。


执行计划
----------------------------------------------------------
Plan hash value: 4165294307

--------------------------------------------------------------------------------

---------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T

ime     |

--------------------------------------------------------------------------------

---------

|   0 | SELECT STATEMENT            |           | 63401 |  1362K|   224   (1)| 0

0:00:03 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1        | 63401 |  1362K|   224   (1)| 0

0:00:03 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1_PK | 11412 |       |    28   (0)| 0

0:00:01 |

--------------------------------------------------------------------------------

---------


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

   2 - access("PK"<3000)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        423  consistent gets
          8  physical reads
          0  redo size
     113183  bytes sent via SQL*Net to client
       2574  bytes received via SQL*Net from client
        201  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2999  rows processed

SQL> select * from t1 where pk<3000;

已选择2999行。


执行计划
----------------------------------------------------------
Plan hash value: 4165294307

--------------------------------------------------------------------------------

---------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T

ime     |

--------------------------------------------------------------------------------

---------

|   0 | SELECT STATEMENT            |           | 63401 |  1362K|   224   (1)| 0

0:00:03 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1        | 63401 |  1362K|   224   (1)| 0

0:00:03 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1_PK | 11412 |       |    28   (0)| 0

0:00:01 |

--------------------------------------------------------------------------------

---------


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

   2 - access("PK"<3000)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        423  consistent gets
          0  physical reads
          0  redo size
     113183  bytes sent via SQL*Net to client
       2574  bytes received via SQL*Net from client
        201  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2999  rows processed

SQL> select * from t where pk<3000;

已选择2999行。


执行计划
----------------------------------------------------------
Plan hash value: 2375091741

--------------------------------------------------------------------------------

-------------------------------

| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%C

PU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------

-------------------------------

|   0 | SELECT STATEMENT                    |         | 63125 |  1294K|    62
(0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE ITERATOR           |         | 63125 |  1294K|    62
(0)| 00:00:01 |     1 |     2 |

|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T       | 63125 |  1294K|    62
(0)| 00:00:01 | ROWID | ROWID |

|*  3 |    INDEX RANGE SCAN                 | IDX_T_G | 11362 |       |    16
(0)| 00:00:01 |     1 |     2 |

--------------------------------------------------------------------------------

-------------------------------


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

   3 - access("PK"<3000)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        430  consistent gets
         20  physical reads
        668  redo size
     101244  bytes sent via SQL*Net to client
       2574  bytes received via SQL*Net from client
        201  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2999  rows processed

SQL> select * from t where pk<3000;

已选择2999行。


执行计划
----------------------------------------------------------
Plan hash value: 2375091741

--------------------------------------------------------------------------------

-------------------------------

| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%C

PU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------

-------------------------------

|   0 | SELECT STATEMENT                    |         | 63125 |  1294K|    62
(0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE ITERATOR           |         | 63125 |  1294K|    62
(0)| 00:00:01 |     1 |     2 |

|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T       | 63125 |  1294K|    62
(0)| 00:00:01 | ROWID | ROWID |

|*  3 |    INDEX RANGE SCAN                 | IDX_T_G | 11362 |       |    16
(0)| 00:00:01 |     1 |     2 |

--------------------------------------------------------------------------------

-------------------------------


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

   3 - access("PK"<3000)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        422  consistent gets
          0  physical reads
          0  redo size
     101244  bytes sent via SQL*Net to client
       2574  bytes received via SQL*Net from client
        201  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2999  rows processed

SQL>
发现逻辑读取相差不大,原因是,跨的分区比较少,其实想一想能差在哪儿呢?
首先都是通过在index里找rowid,其次通过rowid在表里或者分区里找数据,在index里
找到的rowid数量就差不多,因为我们的数据量一样大,而且数据完全一致,差别可能就是
通过访问分区表可能查找metadata需要多消耗几个逻辑读而已
--===========================
通过对比分区表和普通表,简单的测试了性能和对分区表存在index时的维护,最后我们不得不说
使用分区表很多时候不一定能提高性能,主要是维护起来方便,如果我们能把访问的数据集中
在一个或者有限的几个分区里,那么性能肯定比访问普通的全表要好,还是那句话
让执行的sql尽可能的少读、少写,这样才是提高sql性能的关键,就像anlinew提到的公式:
T=S/V,其实少读、少写的目的就是减小S.

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

请登录后发表评论 登录
全部评论
王燕辉! 一个80后的IT从业者! 紧跟时代最前沿技术!

注册时间:2014-03-08

  • 博文量
    154
  • 访问量
    115063