管理分区表与索引

自动更新索引

在使用alter table命令的时候带上update indexes，可以自动更新索引，不必手工重建索引。指定这个语句告诉数据库在执行维护操作ddl语句的时候，更新索引。

下面的语句提供update indexes语句

ADD PARTITION | SUBPARTITION

COALESCE PARTITION | SUBPARTITION

DROP PARTITION | SUBPARTITION

EXCHANGE PARTITION | SUBPARTITION

MERGE PARTITION | SUBPARTITION

MOVE PARTITION | SUBPARTITION

SPLIT PARTITION | SUBPARTITION

TRUNCATE PARTITION | SUBPARTITION

向范围分区表中添加分区

使用alter table ....add partition语句在结尾添加一个新分区。要是在开始和表的中间加一个分区，使用split partition语句。例如下面的

alter table sales add partitoin jan96 values less than('01-feb-1999') tablespace tsx;

本地和全局索引仍然可用。

向hash分区表添加分区

当你添加两个一个分区到hash分区表中，数据库使用现存的分区重新hash的行来操作新分区。下面是添加新分区的2中方法

ALTER TABLE scubagear ADD PARTITION;

ALTER TABLE scubagear
      ADD PARTITION p_named TABLESPACE gear5;

对于堆表上的索引，除非是使用了update indexes语句，否则新分区的本地索引和数据重新分配的索引都标记为unusable，必须要重建。所有的全局索引，或是分区的全局索引，都被标记为unusable。

向列表分区中添加一个分区

ALTER TABLE q1_sales_by_region
   ADD PARTITION q1_nonmainland VALUES ('HI', 'PR')
      STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3
      NOLOGGING;

本地和全局索引仍然可用

添加索引分区

alter index hgidx add partition p5;

coalescing 分区

coalescing分区时减少hash分区的一个方法或是减少范围=hash分区子分区的一个方法，总是是针对hash的。当hash分区coalesced，分区的内容重新分配到一个或多个存在的分区中。具体coalesced的分区是由数据库决定额。在数据重新分配后被删除。

这个索引的状态跟添加hash分区是一样的。

下面是减少一个分区的例子

ALTER TABLE ouu1
     COALESCE PARTITION;

下面是减少一个子分区的例子

ALTER TABLE diving MODIFY PARTITION us_locations
     COALESCE SUBPARTITION;

下面是coalescing一个hash分区索引的例子

ALTER INDEX hgidx COALESCE PARTITION

删除分区

范围分区，列表分区可以被删除，或是组合范围列表分区。hash分区或子分区只能coalesce.使用下面的语句来删除一个分区或子分区

ALTER TABLE ... DROP PARTITION to drop a table partition

ALTER TABLE ... DROP SUBPARTITION to drop a subpartition of a range-list partitioned table

如果要想数据保存在分区中，使用merge partition语句，而不是drop partition语句。

对于本地索引，上面的语句也会删除匹配的分区或子分区。所有的全局分区，或所有全局分区索引被标记为unusable。除非之使用了update indexes，或表是空的。表空的情况下，索引不受影响。

下面是删除一个分区表，包含了数据和全局索引的

方法1：

ALTER TABLE sales DROP PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD;
如果sales_aea_ix是一个全局分区索引，那么需要手工挨个的来重建分区索引

ALTER INDEX sales_area_ix REBUILD PARTITION jan99_ix;
ALTER INDEX sales_area_ix REBUILD PARTITION feb99_ix;
ALTER INDEX sales_area_ix REBUILD PARTITION mar99_ix;

方法2：

DELETE FROM sales WHERE TRANSID < 10000;
ALTER TABLE sales DROP PARTITION dec98;

方法3

ALTER TABLE sales DROP PARTITION dec98
     UPDATE INDEXES;

交换分区

通过交换分区，你可以把分区转换成一个非分区表，一个非分区表转换成一个分区表的分区。也可以将hash分区表转换成一个范围-hash分区表。或是转换范围-hash分区表到hash分区表。也可以将列表分区转换成范围-列表分区，或范围-列表分区转换成列表分区。

下面的例子中将分区的一个分区转换成了非分区表，stocks这个表可以是范围分区，hash分区，或列表分区。

ALTER TABLE stocks
    EXCHANGE PARTITION p3 WITH TABLE stock_table_3;

下面是hash分区和范围-hash分区表的交换

CREATE TABLE t1 (i NUMBER, j NUMBER)
     PARTITION BY HASH(i)
       (PARTITION p1, PARTITION p2);

Populate the table, then create a range-hash partitioned table as shown:

CREATE TABLE t2 (i NUMBER, j NUMBER)
     PARTITION BY RANGE(j)
     SUBPARTITION BY HASH(i)
        (PARTITION p1 VALUES LESS THAN (10)
            SUBPARTITION t2_pls1
            SUBPARTITION t2_pls2,
         PARTITION p2 VALUES LESS THAN (20)
            SUBPARTITION t2_p2s1
            SUBPARTITION t2_p2s2));

It is important that the partitioning key in table t1 is the same as the subpartitioning key in table t2.

To migrate the data in t1 to t2, and validate the rows, use the following statement:

ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1
     WITH VALIDATION;

合并分区

可以合并两个挨着的分区，不挨着的不能合并。下面是一个合并分区的例子

First, create a partitioned table and create local indexes.

-- Create a Table with four partitions each on its own tablespace
-- Partitioned by range on the data column.
--
CREATE TABLE four_seasons
( 
        one DATE,
        two VARCHAR2(60),
        three NUMBER
)
PARTITION  BY RANGE ( one ) 
(
PARTITION quarter_one 
   VALUES LESS THAN ( TO_DATE('01-apr-1998','dd-mon-yyyy'))
   TABLESPACE quarter_one,
PARTITION quarter_two 
   VALUES LESS THAN ( TO_DATE('01-jul-1998','dd-mon-yyyy'))
   TABLESPACE quarter_two,
PARTITION quarter_three
   VALUES LESS THAN ( TO_DATE('01-oct-1998','dd-mon-yyyy'))
   TABLESPACE quarter_three,
PARTITION quarter_four
   VALUES LESS THAN ( TO_DATE('01-jan-1999','dd-mon-yyyy'))
   TABLESPACE quarter_four
);
-- 
-- Create local PREFIXED index on Four_Seasons
-- Prefixed because the leftmost columns of the index match the
-- Partition key 
--
CREATE INDEX i_four_seasons_l ON four_seasons ( one,two ) 
LOCAL ( 
PARTITION i_quarter_one TABLESPACE i_quarter_one,
PARTITION i_quarter_two TABLESPACE i_quarter_two,
PARTITION i_quarter_three TABLESPACE i_quarter_three,
PARTITION i_quarter_four TABLESPACE i_quarter_four
);

Next, merge partitions.

--
-- Merge the first two partitions 
--
ALTER TABLE four_seasons 
MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two
UPDATE INDEXES;

If you omit the UPDATE INDEXES clause from the preceding statement, then you must rebuild the local index for the affected partition.

-- Rebuild index for quarter_two, which has been marked unusable 
-- because it has not had all of the data from Q1 added to it.
-- Rebuilding the index will correct this.
--
ALTER TABLE four_seasons MODIFY PARTITION 
quarter_two REBUILD UNUSABLE LOCAL INDEXES;

移动分区

移动分区的作用：

1re-cluster数据，减少碎片

2移动分区到别的表空间

3修改创建时候的属性

4使用表压缩存放压缩数据

ALTER TABLE parts MOVE PARTITION depot2
     TABLESPACE ts094 NOLOGGING COMPRESS;

ALTER TABLE scuba_gear MOVE SUBPARTITION bcd_types 
     TABLESPACE tbs23 PARALLEL (DEGREE 2);

重建分区索引

ALTER INDEX scuba
   REBUILD SUBPARTITION bcd_types
   TABLESPACE tbs23 PARALLEL (DEGREE 2);

重命名分区

Rename a range, hash, or list partition, using the ALTER TABLE ... RENAME PARTITION statement. For example:

ALTER TABLE scubagear RENAME PARTITION sys_p636 TO tanks;

 
Renaming a Table Subpartition
Likewise, you can assign new names to subpartitions of a table. In this case you would use the ALTER TABLE ... RENAME PARTITION syntax.

truncate分区

alter table ..truncate partition来删除表分区所有记录。

Truncating Table Partitions Containing Data and Global Indexes
If the partition contains data and global indexes, use one of the following methods to truncate the table partition.
Method 1
Leave the global indexes in place during the ALTER TABLE ... TRUNCATE PARTITION statement. In this example, table sales has a global index sales_area_ix, which is rebuilt.
ALTER TABLE sales TRUNCATE PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD;

This method is most appropriate for large tables where the partition being truncated contains a significant percentage of the total data in the table.
Method 2
Issue the DELETE statement to delete all rows from the partition before you issue the ALTER TABLE ... TRUNCATE PARTITION statement. The DELETE statement updates the global indexes, and also fires triggers and generates redo and undo logs.
For example, to truncate the first partition, which has a partition bound of 10000, issue the following statements:
DELETE FROM sales WHERE TRANSID < 10000;
ALTER TABLE sales TRUNCATE PARTITION dec98;

This method is most appropriate for small tables, or for large tables when the partition being truncated contains a small percentage of the total data in the table.
Method 3
Specify UPDATE INDEXES in the ALTER TABLE statement. This causes the global index to be truncated at the time the partition is truncated.
ALTER TABLE sales TRUNCATE PARTITION dec98
     UPDATE INDEXES;
 
 
 

  
   
  
  
Truncating a Partition Containing Data and Referential Integrity Constraints
  
  
If a partition contains data and has referential integrity constraints, choose either of the following methods to truncate the table partition.
  
  
Method 1
  
  
Disable the integrity constraints, issue the ALTER TABLE ... TRUNCATE PARTITION statement, then reenable the integrity constraints:
  
  
ALTER TABLE sales
    DISABLE CONSTRAINT dname_sales1;
ALTER TABLE sales TRUNCATE PARTITTION dec94;
ALTER TABLE sales
    ENABLE CONSTRAINT dname_sales1;

  
  
split分区
  
  
列表分区的列子
  
  
ALTER TABLE sales_by_region 
   SPLIT PARTITION region_east VALUES ('CT', 'MA', 'MD') 
   INTO 
    ( PARTITION region_east_1 
         TABLESPACE tbs2,
      PARTITION region_east_2
        STORAGE (NEXT 2M PCTINCREASE 25)) 
   PARALLEL 5;

The literal value list for the original region_east partition was specified as:
PARTITION region_east VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')

The two new partitions are:
	region_east_1 with a literal value list of ('CT','MA','MD')
	region_east_2 inheriting the remaining literal value list of ('NY','NH','ME','VA','PA','NJ')
The following example splits a range-hash partition:
ALTER TABLE all_seasons SPLIT PARTITION quarter_1 
     AT (TO_DATE('16-dec-1997','dd-mon-yyyy'))
     INTO (PARTITION q1_1997_1 SUBPARTITIONS 4 STORE IN (ts1,ts3),
           PARTITION q1_1997­_2);