当前位置:首页 > 开发 > 数据库 > 正文

Oracle 11g System Partition(原创)

发表于: 2013-11-15   作者:czmmiao   来源:转载   浏览次数:
摘要: System PartitioningSystem partitioning is quite different from all other types of data partitioning. System partitioning is meant to enable application controlled table partitioning. Just for starters

System Partitioning
System partitioning is quite different from all other types of data partitioning. System partitioning is meant to enable application controlled table partitioning. Just for starters, there aren’t any partitioning keys when you use system partitioning. Under system partitioning the database lets you break a table down into meaningless partitions and you don’t control the partitioning ranges for the actual data placement. The application controls the partitioning and actual data placement.
Because a system-partitioned table doesn’t use partitioning keys, you can’t direct the mapping of the rows to a particular partition. Instead, the application must specify the actual partition in which the database must place a row. Thus, insert statements must use the partition information explicitly. It’s important to remind yourself that system partitioning doesn’t use any partitioning method and thus can’t distribute table rows to partitions. It’s the application’s job to do the data distribution to the partitions.
System partitioning provides the benefit of easier manageability that comes with equipartitioning a table. You can, for example, create a nested table as a system- partitioned table with the same partitions as the base table. System partitioning doesn’t support the normal partition pruning and partition-wise joins like the other types of partitioned tables. You thus lose the performance benefits inherent in partitioning a table.
A System Partitioning Example
As you would expect, system partitioning allows large tables to be broken down into smaller partitions, but unlike other partitioning schemes, the database has no control over the placement of rows during insert operations. The following example shows the creation of a system partitioned table.
SQL> CREATE TABLE system_partitioned_tab (
       id           NUMBER,
       code         VARCHAR2(10),
       description  VARCHAR2(50),
       created_date DATE
     )
     PARTITION BY SYSTEM
     (
       PARTITION part_1,
       PARTITION part_2
     );
The partition must be explicitly defined in all insert statements or an error is produced.
SQL> INSERT INTO system_partitioned_tab VALUES (1, 'ONE', 'One', SYSDATE);
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by
the System method
The PARTITION clause is used to define which partition the row should be placed in.
SQL> INSERT INTO system_partitioned_tab PARTITION (part_1) VALUES (1, 'ONE', 'One', SYSDATE);
SQL> INSERT INTO system_partitioned_tab PARTITION (part_2) VALUES (2, 'TWO', 'Two', SYSDATE);
SQL> COMMIT;
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'SYSTEM_PARTITIONED_TAB');
SQL> COLUMN table_name FORMAT A25
SQL> COLUMN partition_name FORMAT A20
SQL> COLUMN high_value FORMAT A10
SQL> SELECT table_name, partition_name, high_value, num_rows
       FROM user_tab_partitions
      ORDER BY table_name, partition_name;
    TABLE_NAME                PARTITION_NAME       HIGH_VALUE   NUM_ROWS
    ------------------------- -------------------- ---------- ----------
    SYSTEM_PARTITIONED_TAB    PART_1                                   1
    SYSTEM_PARTITIONED_TAB    PART_2                                   1
    2 rows selected.
Notice that the HIGH_VALUE for the partitions is blank.

In addition to the insert statement, the merge statement also requires that you specify the partition-extended syntax to identify the partition into which you want the database to place the merged partition rows. Here’s an example:
SQL> alter table sys_part_tab merge partitions part_1,part_2 into partition p1;
The PARTITION clause is optional for update and delete statements, but omitting this clause will force all partitions to be scanned, since there is no way perform automatic partition pruning when the database has no control over row placement. When the PARTITION clause is used, you must be sure to perform the operation against the correct partition.
SQL> DELETE FROM system_partitioned_tab PARTITION (part_2) WHERE id = 1;
0 rows deleted.
SQL> UPDATE system_partitioned_tab PARTITION (part_1) SET code = 'TWO' WHERE id = 2;
0 rows updated.
The PARTITION clause can also be used in queries to target specific partitions.
SQL> SELECT COUNT(*)
             FROM   system_partitioned_tab PARTITION (part_1);
       COUNT(*)
       ----------
        1
 1 row selected.
You can perform the following operations with a system-partitioned table:

  • Partition maintenance operations
  • All DML and DDL operations
  • Creation of local indexes, as long as they are not unique
  • Creation of local bitmapped indexes
  • Creation of global indexes

Conditions and restrictions on system partitioning include:

  • If you specify the PARTITION BY SYSTEM clause, but don't define partitions, a single partition is created with the name in the format of "SYS_Pn".
  • If you specify PARTITION BY SYSTEM PARTITIONS X clause, the database creates "X" partitions with the name in the format of "SYS_Pn". The range of allowable values for "n" is from 1 to 1024K-1.
  • System partitioning is not available for index-organized tables or a table that is part of a cluster.
  • System partitioning can play no part in composite partitioning.
  • You cannot split a system partition.
  • System partitioning cannot be specified in a CREATE TABLE ... AS SELECT statement.
  • To insert data into a system-partitioned table using an INSERT INTO ... AS subquery statement, you must use partition-extended syntax to specify the partition into which the values returned by the subquery will be inserted.
    as shown here:
    SQL> insert into table_name
         partition (
         PartitionName)
         dataobj_to_partition(base_table, :physical_partid))
         as SubQuery...

参考至:《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》

       http://www.oracle-base.com/articles/11g/partitioning-enhancements-11gr1.php

本文原创,转载请注明出处、作者

如有错误,欢迎指正

邮箱:czmcj@163.com

 

Oracle 11g System Partition(原创)

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
Overview In Oracle Database 10g, Oracle introduced the new Transparent Data Encryption (TDE)
Automatic Diagnostic Repository The automatic diagnostic repository tracks all problems and i
1.概述 Parttion by 关键字是Oracle中分析性函数的一部分,它和聚合函数不同的地方在于它能够返回一
1.启动sqlplus 2.请输入用户名: sqlplus/as sysdba 3.输入口令: 直接回车 4.连接到: Oracle Databas
在不知道Oracle11g密码的情况下, 在Dos命令下,可以用SQL>sqlplus/ as sysdba直接进行登录 登录
1、磁盘基础知识 1.1 物理结构 硬盘的物理结构一般由磁头与碟片、 电动机、主控芯片与排线等部件组
原文地址:http://www.2cto.com/database/201208/150620.html 一、Oracle 下载 注意Oracle分成两个文
oracle 11g 装好以后好久木有动了,今天找健健来一起研究,真是好。 先把oracle服务打开,这家伙太
一、Oracle 下载 注意Oracle分成两个文件,下载完后,将两个文件解压到同一目录下即可。 路径名称中
Overview of Table Clusters A table cluster is a group of tables that share common columns and
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号