分区表和索引1-80页

全区分区索引，本地分区索引：

http://www.itpub.net/thread-429282-1-1.html

全局分区索引在一个索引分区中包含来自多个表分区的键，一个全局分区索引的分区键是分区表中中不同的或者指定一个范围的值。在创建全局分区索引的时候，必须定义分区键的范围和值。

组合分区：

在10g有两种：range-hash，range-list 注意顺序，根分区只能是range分区，子分区可以是hash，或者list。

如下，创建组合分区：

create table test1_1(

  transaction_id number primary key,

  transaction_date date

)                             ——注意没有分号。

partition by range(transaction_date) subpartition by hash(transaction_id)
     ——注意语法格式。

subpartitions 3 store in (tablespace01,tablespace02,tablespace03              ——绿色部分为关键词：subpartition 3 store in（）

)                                                                                                                                       ——俩个括号之间连着，没有分号。

(

 partition part_01 values less than (to_date('2009-01-01','yyyy-mm-dd')),

 partition part_02 values less than ( to_date('2010-01-01','yyyy-mm-dd')),

 partition part_03 values less than(maxvalue)

);

再来一个例子：

create table emp_sub_template(
  deptno number,
  empname varchar2(32),
  grade number
)
partition by range(deptno) subpartition by hash(empname)   
subpartition template(                                                    ——关键词：subpartition template（subpartitionatablespace ts1）
 subpartition a tablespace ts1,
 subpartition b tablespace ts2,
 subpartition c tablespace ts3,
 subpartition d tablespace ts4
)                                                                               ——两个括号连着，没有分号，或者逗号。
(
 partition p2 values less than(1000),
 partition p2 values less than(2000),
 partition p3 values less than(maxvalue)
);

还有一种写法，每个父partition里含有所有子subpartition的所有描述。不写了具体看p45页。

oracle 11g中分区表的功能有所增强，有增加了range-range ，list-range ， list-list ， list-hash 这四个新的。

普通表转化成分区表：

1、export/import 命令；

2、子查询插入分区表；

3、partition exchange method。

4、DBMS_REDEFINITION

涉及到的操作：

重命名表：rename emp to emp_old ; 

交换分区：partition exchange method

此方法只是对数据字典中分区和表的定义进行了修改，没有对数据进行修改，效率极高。适用于一个包含大量数据的表转换到分区表中的一个分区的操作，尽量在闲时进行操作。

      步骤如下：创建表分区P1，P2——创建表A（内部的数据是符合P1标准的数据）——创建表B（内部的数据是符合P2标准的数据）——用表A和P1进行交换,把表A的数据放到P1分区——用表B和P2进行交换,把表B的数据放到P2分区。

创建一个分区表：

   create table p_dba_test(

   object_id number, 

   created date)

   partition by range(created)

(

   partition p1 values less than (to_date('2010-09-1','yyyy-mm-dd')),

   partition p2 values less than (to_date('2012-04-23','yyyy-mm-dd'))

   );

创建两个对应的分区基表：

SQL> create table dba_p1 as select object_id,created from dba_test

          where created<to_date('2010-09-1','yyyy-mm-dd');

           SQL> create table dba_p2 as select object_id,created from dba_test 

                    where created <to_date('2012-04-23','yyyy-mm-dd')

                     and   created>to_date('2010-09-1','yyyy-mm-dd');

把两个基表与两个分区表进行交换：

                  SQL> alter table p_dba_test exchange partition p1 with table dba_p1;
                      表已更改。
                 SQL> alter table p_dba_test exchange partition p2 with table dba_p2;
                     表已更改。
                  SQL> select count(*) from p_dba_test partition(p1);
 

                     COUNT(*)
                      ----------
                      47521

SQL> select count(*) from p_dba_test partition(p2);

  COUNT(*)

----------

       834

SQL> select count(*) from dba_p1;

  COUNT(*)

----------

         0

                  SQL> select count(*) from dba_p2;
                         COUNT(*)

   ----------

         0

如果插入的数据不满足分区规划，会报ora-14400错误

使用在线重定义：DBMS_REDIFINITION

http://blog.csdn.net/tianlesoftware/article/details/6218693

In this Document
  Goal
  Solution

Platforms: 1-914CU;

Applies to:

Oracle Server - Standard Edition - Version: 9.2.0.4 and later   [Release: 9.2 and later ]
Information in this document applies to any platform.
"Checked for relevance on 29-Sep-2010"

Goal

The purpose of this document is to provide step by step instructions on how to convert unpartitioned table to partitioned one using dbms_redefinition package.

Solution

1) Create unpartitioned table with the name unpar_table

SQL> CREATE TABLE unpar_table ( 
id NUMBER(10), 
create_date DATE, 
name VARCHAR2(100) 
);

2) Apply some constraints to the table:

SQL> ALTER TABLE unpar_table ADD ( 
CONSTRAINT unpar_table_pk PRIMARY KEY (id) 
); 

SQL> CREATE INDEX create_date_ind ON unpar_table(create_date);

3) Gather statistics on the table:

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'unpar_table', cascade => TRUE);

4) Create a Partitioned Interim Table:

SQL> CREATE TABLE par_table ( 
id NUMBER(10), 
create_date DATE, 
name VARCHAR2(100) 
) 
PARTITION BY RANGE (create_date) 
(PARTITION unpar_table_2005 VALUES LESS THAN (TO_DATE('01/01/2005', 'DD/MM/YYYY')), 
PARTITION unpar_table_2006 VALUES LESS THAN (TO_DATE('01/01/2006', 'DD/MM/YYYY')), 
PARTITION unpar_table_2007 VALUES LESS THAN (MAXVALUE));

5) Start the Redefinition Process:

a) Check the redefinition is possible using the following command:

SQL> EXEC Dbms_Redefinition.can_redef_table(USER, 'unpar_table');

b)If no errors are reported, start the redefintion using the following command:

SQL> BEGIN 
DBMS_REDEFINITION.start_redef_table( 
uname => USER,  
orig_table => 'unpar_table', 
int_table => 'par_table'); 
END; 
/

Note: This operation can take quite some time to complete.

c) Optionally synchronize new table with interim name before index creation:

SQL> BEGIN 
dbms_redefinition.sync_interim_table( 
uname => USER,  
orig_table => 'unpar_table', 
int_table => 'par_table'); 
END; 
/

d) Create Constraints and Indexes:

SQL> ALTER TABLE par_table ADD ( 
CONSTRAINT unpar_table_pk2 PRIMARY KEY (id) 
); 

SQL> CREATE INDEX create_date_ind2 ON par_table(create_date);

e) Gather statistics on the new table:

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'par_table', cascade => TRUE);

f) Complete the Redefintion Process:

SQL> BEGIN 
dbms_redefinition.finish_redef_table( 
uname => USER, 
orig_table => 'unpar_table', 
int_table => 'par_table'); 
END; 
/

At this point the interim table has become the "real" table and their names have been switched in the name dictionary. 

g) Remove original table which now has the name of the interim table:

SQL> DROP TABLE par_table; 

h)Rename all the constraints and indexes to match the original names.

ALTER TABLE unpar_table RENAME CONSTRAINT unpar_table_pk2 TO unpar_table_pk; 
ALTER INDEX create_date_ind2 RENAME TO create_date_ind;

i) Check whether partitioning is successful or not:

SQL> SELECT partitioned 
FROM user_tables 
WHERE table_name = 'unpar_table'; 

PAR 
--- 
YES 

1 row selected. 

SQL> SELECT partition_name 
FROM user_tab_partitions 
WHERE table_name = 'unpar_table'; 

PARTITION_NAME 
------------------------------ 
unpar_table_2005 
unpar_table_2006 
unpar_table_2007 

3 rows selected.

Please not that the 9i redefinition procedures has some restrictions: 

* There must be enough space to hold two copies of the table. 
* Primary key columns cannot be modified. 
* Tables must have primary keys. 
* Redefinition must be done within the same schema. 
* New columns added cannot be made NOT NULL until after the redefinition operation. 
* Tables cannot contain LONGs, BFILEs or User Defined Types. 
* Clustered tables cannot be redefined. 
* Tables in the SYS or SYSTEM schema cannot be redefined. 
* Tables with materialized view logs or materialized views defined on them cannot be redefined. 
* Horizontal sub setting of data cannot be performed during the redefinition.

使用导入导出建立分区白表：

步骤：将普通表dump出来——创建分区表——将dump文件导入数据。

注意两点：分区表导出的dump文件要比普通表导出的大；分区表导入的时间要比普通表时间长。

导出表的时候，对应的索引，注释，和权限都会被一起导出，但要保障有足够的空间，否则会出现一个写失败的错误。我们可以用所有表所占的空间估计一下，这个估计值不准，因为这个是查询的数据库内所有的表的大小（查询结果不包括 lob ， VARRAY,分区表）：

select  sum(bytes)/1024/1024 from user_segment where segment_type='TABLE' ;  ——注意table是大写的，小写的没有查询结果。

在执行导出表之前，先要运行catexp.sql或者catalog.sql。这两个脚本有如下功能：

1、在数据字典里创建必要的导出视图。

2、创建角色： EXP_FULL_DATABASE。

3、分派所有必需的权限给EXP_FULL_DATABASE角色。

4、把EXP_FULL_DATABASE角色授权给dba角色。

5、记录已安装catexp.SQL脚本的版本号。

概念：

集群因子（clustering factor）： 集群因子位于USER_INDEXES视图，该列反映了数据相对于已经建立索引的列是否显得有序，如果集群因子的值接近索引中的树叶块数目，表中的数据就越有序，如果集群因子的值接近于表中的行数，则表中的数据就不是很有序。

快速全局扫描（Fast Full Scan）：这个选项允许oracle执行一个全局索引扫描操作，快速全局扫描读取B*树索引上的树叶块。初始化文件中的:

DB_FILE_MULTIBLOCK_READ_COUNT 参数可以控制同时被读取的块的数目。

跳跃式扫描（index skip scan）：是Oracle9i用来提高性能的新特性，对于使用复合索引的数据库应用程序意义尤为重大。 复合索引（又称为连接索引）是一个包含多个字段的索引。在ORACLE9i以前的ORACLE版本里，只有那些在WHERE子句里引用整个索引或者是引用索引的一个或多个前导字段的查询，才能使复合索引有效而提高检索效率。而在ORACLE9i里，一个复合索引即使在一次查询中没有使用前导字段，也可能通过“索引跳跃式扫描”被有效引用。在一次跳跃式扫描中，每个复合索引前导字段的独特值（DISTINCT
VALUE）只会被搜索一次，ORACLE9i会在复合索引前导字段每个独特值区间结合WHERE子句中的其它复合索引字段搜索符合条件的目标记录，这种做法的结果导致了在索引中的跳跃式扫描。跳跃式扫描参考：http://blog.csdn.net/changyanmanman/article/details/7708653