Oracle 11g新特性系统分区表

  在11g中有个新特性是系统的分区表,下面来做个实验:

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> drop table S_P_TEST purge;

SQL> CREATE TABLE S_P_TEST
    (
     col1 number,
     name varchar2(100)
    )
    PARTITION BY SYSTEM
    (
      PARTITION p1,
      PARTITION p2,
      PARTITION p3,
      PARTITION p4
   );

--插入时要指明分区
SQL> insert into S_P_TEST select object_id,object_name from dba_objects;
insert into S_P_TEST select object_id,object_name from dba_objects
            *
第 1 行出现错误:
ORA-14701: 对于按“系统”方法进行分区的表, 必须对 DML 使用分区扩展名或绑定变量
SQL> insert into S_P_TEST partition(p1) select object_id,object_name from dba_objects
    where object_type=‘TABLE‘;
已创建2115行。
SQL> insert into S_P_TEST partition(p2) select object_id,object_name from dba_objects
    where object_type=‘INDEX‘;
已创建2888行。
SQL> commit;
SQL> select count(1) from S_P_TEST;
  COUNT(1)
----------
      5003
SQL> select count(1) from S_P_TEST  partition(p1);
  COUNT(1)
----------
      2115
SQL> select count(1) from S_P_TEST  partition(p2);
  COUNT(1)
----------
      2888
--相同的数据可以插入到不同的分区中,这说明数据和分区没有任何关系
SQL> insert into S_P_TEST partition(p3) values(1,‘aa‘);
SQL> insert into S_P_TEST partition(p4) values(1,‘aa‘);
SQL> commit;
SQL> select * from S_P_TEST  partition(p3);
      COL1 NAME
---------- ------------------------------------------
         1 aa
SQL> select * from S_P_TEST  partition(p4);

      COL1 NAME
---------- -----------------------------------------
         1 aa

--传统的分区裁剪和分区智能关联无效,不能建立本地化的索引
SQL> ALTER TABLE S_P_TEST SPLIT PARTITION p1 at(1000)
     into(partition p3,partition p4);
ALTER TABLE S_P_TEST SPLIT PARTITION p1 at(1000)
            *
第 1 行出现错误:

ORA-14255: 未按范围, 列表, 组合范围或组合列表方法对表进行分区


--如果你知道数据在哪个表里面,做好要制定分区条件
SQL> set autotrace traceonly
SQL> select * from S_P_TEST where col1=1;
执行计划
----------------------------------------------------------
Plan hash value: 2020968526
-------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |     2 |   130 |    10   (0)| 00:00:01 |       |       |
|   1 |  PARTITION SYSTEM ALL|          |     2 |   130 |    10   (0)| 00:00:01 |     1 |     4 |
|*  2 |   TABLE ACCESS FULL  | S_P_TEST |     2 |   130 |    10   (0)| 00:00:01 |     1 |     4 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("COL1"=1)
Note
-----
   - dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         70  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
SQL> select * from S_P_TEST partition(p3) where col1=1;
执行计划
----------------------------------------------------------
Plan hash value: 1626227678
----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |     1 |    65 |     4   (0)| 00:00:01 |       |       |
|   1 |  PARTITION SYSTEM SINGLE|          |     1 |    65 |     4   (0)| 00:00:01 |     3 |     3 |
|*  2 |   TABLE ACCESS FULL     | S_P_TEST |     1 |    65 |     4   (0)| 00:00:01 |     3 |     3 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("COL1"=1)
Note
-----
   - dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        385  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)

          1  rows processed

  系统分区表的使用场景我想并不多,如果对传统的分区表数据的特性理解是:1.数据和分区是有联系的; 2.分区就是把很多的表粘在一起管理。那系统分区表只有第二个特性。

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。