`

Oracle 11g System Partition(原创)

 
阅读更多

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

 

0
0
分享到:
评论

相关推荐

    ORACLE SYSTEM表空间已满解决方案.docx

    ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/yourdb/system01.dbf' RESIZE 10G; ``` 2. **优化数据存储**:对表进行重新组织,删除不必要的数据或归档历史数据。 3. **调整数据库设计**:例如,将大表...

    ORACLE 10G 学习资源

    Oracle 10G 是一款强大的关系型数据库管理系统,它的架构设计和存储机制对于理解数据库的运作至关重要。在Oracle 10G中,数据是以块(Block)为基本单位进行存储和管理的。块是数据库I/O操作的最小单位,一旦数据库...

    oracle_10g_创建数据库和表用户名和密码

    Oracle 10g 默认创建了五个表空间:SYSTEM、SYSAUX、UNDO、USERS 和 TEMPORARY。用户可以根据应用系统的规模及其所要存放对象创建多个表空间,以区分用户数据和系统数据。 表(TABLE)是数据库中存放用户数据的...

    oracle dataguard failover

    SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) OVER (PARTITION BY thread#) AS LAST FROM V$ARCHIVED_LOG; ``` 2. **复制最近的归档重做日志文件**:如果可能,将每个主数据库重做线程的最新归档重做...

    linux oracle9i 安装图解

    - 手动分区(Manually partition with Disk Druid)以确保Oracle有足够的空间。创建以下分区: - /boot: 300M - /: 1500M - swap: 1G - /home: 2G - /tmp: 1G - /usr: 10.5G - 不设置启动密码,直接“Next”...

    ORACLE分区

    根据提供的文件信息,本文将详细解释Oracle分区技术及其在解决UNDOTBS01.DBF文件过大问题中的应用方法,并进一步探讨Oracle分区的不同类型及其应用场景。 ### Oracle 分区概述 Oracle分区是一种将大表或索引分割成...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    2. oracle11G自带一个卸载批处理\app\Administrator\product\11.2.0\dbhome_1\deinstall\deinstall.bat 3. 运行该批处理程序将自动完成oracle卸载工作,最后手动删除\app文件夹(可能需要重启才能删除) 4. 运行...

    ORACLE和SQL语法区别归纳

    - SQL标准有用户和权限管理,Oracle则在此基础上增加了角色(ROLE)、系统权限(SYSTEM PRIVILEGE)和对象权限(OBJECT PRIVILEGE)等更细致的安全控制。 综上所述,虽然Oracle基于SQL标准,但在很多方面都进行了...

    oracle日积月累

    例如,`analytic_function: RANK() OVER (PARTITION BY column ORDER BY another_column)`将根据指定列排序并为每个分区分配一个唯一的排名。 2. **Oracle函数大全**: Oracle提供了一系列内置函数,如字符串函数...

    oracle试题

    1. **默认表空间**:在Oracle中,如果在创建用户时没有指定DEFAULT TABLESPACE,那么Oracle将自动分配`SYSTEM`表空间作为用户的默认表空间。因此,正确答案是C.SYSTEM。 2. **内存区**:SGA(System Global Area)...

    Oracle调优总结

    使用 DBMS_SYSTEM 包可以跟踪某个 Session 的 SQL 语句,了解 Session 中的 SQL 语句的执行情况。 七、SQL 调整的关键点 SQL 调整的关键点是提高 SQL 语句的 response time,使得 SQL 语句的执行速度尽量快。可以...

    oracle期末考试题库

    ### 11. 联机备份 在进行联机备份时,数据库必须处于 `ARCHIVELOG` 模式,并且可以被所有用户访问。例如: ```sql ALTER DATABASE ARCHIVELOG; -- 设置为归档模式 ``` ### 12. 使用备份控制文件 在使用备份控制文件...

    oracle期末考试试题及答案.doc

    1. SGA (System Global Area) 是Oracle数据库的一个重要组成部分,它包含了数据库运行所需的多个内存结构,如数据缓冲区、日志缓冲区和共享池等。PGA (Private Global Area) 则是每个Oracle数据库进程的专用内存区域...

    Oracle数据库面试题及答案

    知识点:SYSTEM tablespace 是 Oracle 数据库中的一个默认 tablespace。 18. 创建用户时,需要赋予新用户什么权限才能使它联上数据库。 答案:CONNECT 知识点:CONNECT 是一种权限,用于允许用户连接数据库。 19. ...

    oracle管理常用sql脚本

    在Oracle数据库管理中,SQL(Structured Query Language)脚本扮演着至关重要的角色,尤其是在日常维护、性能监控和问题排查方面。以下是一些Oracle管理中常用的SQL脚本及其相关的知识点: 1. **数据查询与操作**:...

    oracle基础练习.docx

    Oracle的不同版本如8i、9i、10g、11g和12c提供了不同级别的功能和性能。其中,企业版是功能最全的,而Express Edition(XE)则为轻量级的个人版。Oracle默认使用的端口是1521,同时8080端口也可能被使用。如果与其他...

    oracle分页查询

    为了解决这个问题,可以使用`ROW_NUMBER()`函数配合`PARTITION BY`和`ORDER BY`子句,这是Oracle 12c引入的`FETCH NEXT ... ROWS ONLY`语法,使得分页更加灵活和高效: ```sql SELECT * FROM ( SELECT a.*, ROW_...

Global site tag (gtag.js) - Google Analytics