分区表和普通表如何转换?分区表的优缺点有哪些?我们从分区表的定义入手，逐层了解认识分区表转换及优势。

　　什么是分区表?分区表的优缺点?

　　随着表的不断增大，对于新纪录的增加，查找，删除等(DML)的维护也更加困难。对于数据库中的超大型表，可以通过把它的数据分成若干个小表，从而简化数据库的管理活动，对于每一个简化后的小表，我们称为一个单个的分区。对于分区的访问，我们不需要使用特殊的SQL查询语句或特定的DML语句，而且可以单独的操作单个分区，而不是整张表。同时可以将不同分区的数据放置到不同的表空间，比如将不同年份的销售数据，放在不同的表空间，即年的销售数据存放到11年，12年的销售数据存放到12，依次类推，从而实现了分散存储，这将大大的简化大容量表的管理，提高查询性能及IO并发等。对于外部应用程序来说，虽然存在不同的分区，且数据位于不同的表空间，但罗技上仍然是一张表。

　　分区的条件：

　　共性：不同分区之间必须有相同的逻辑属性，比如表名，列名，数据类型，约束等

　　个性：各个分区可以有不同的物理属性，比如：pctfree，pctused tablespaces等

　　分区独立性：即使某些分区不可用，其他分区仍然可用。

　　分区的优点：

　　1.提高查询性能：只需要搜索特定分区，而非整张表，提高了查询速度。

　　2.节约维护时间：单个分区的数据装载，索引重建，备份，维护远远小于整张表的维护时间。

　　简单的说，要建立一个大表的分区本地索引，如何处理?

　　第一步：alter session set workare_size_policy=manual;

　　alter session set sort_area_size=1073741824;

　　alter session set sort_area_retained_size=1073741824;

　　alter session set db_file_multiblock_read_count=128;

　　第二步：建立一个索引，但是是失效的

　　SQL>create index index_local on test_part(object_id) local unusable;

　　然后再批量建这个索引就可以了：

　　declare

　　begin

　　for j in (select u.index_name,u.partition_name,

　　3.节约维护成本：可以单独备份和恢复每个分区：

　　4.均衡IO：将不同的分区映射到不同的磁盘以平衡IO，提高并发。

　　分区表的几种分区方式和时间中使用场景

　　ORACLE分区类型：范围分区、散列分区、列表分区、组合分区

　　可以对索引和表分区，全局索引只能按照范围分区，可以将其定义在任何类型的分区或非分区表上。通常全局索引比局部索引需要更多的维护。

　　1.Range分区：行映射到基于列值范围的分区。

　　Range分区，又称为范围分区，基于分区键值的范围将数据映射到所建立的分区上。这是最通用的分区类型。

　　创建范围分区时，必须指定一下内容：

　　分区方法：range

　　分区列：标识分区边界的分区描述。

　　使用range分区的时候，要记住几条规则：

　　每个分期都包含values less than字名，定义了分区的上层边界。任何等于和大于分区键值的二进制值都被添加到下一个高层分区中。

　　所有分区，除了第一个，如果低于values less than所定义的下层边界，都放在前面的分区中。

　　范围分区案例：

　　create table t_part (a integer)

　　partition by range(a)

　　(partition p1 values less than (5),

　　partition p2 values less than (6),

　　partition p3 values less than (7),

　　partition p4 values less than (8)

　　);

　　insert into t_part values (5);

　　如何查询分区是否是范围分区?

　　select partitioning_type from user_part_tables where table_name='T_PART';

　　SQL> select partitioning_type from user_part_tables where table_name='T_PART';

　　PARTITIONING_TYPE

　　-----------------

　　RANGE

　　批量move到另外一个表空间

　　---当移动一个表的多个分区时的脚本

　　begin

　　for x in (select partition_name from user_tab_partitions where table_name='T_PART')

　　loop

　　execute immediate 'alter table t_part move partition ' || x.partition_name tablespace test;

　　end loop;

　　end;

　　/

　　2、HASH分区

　　何为hash分区?hash分区能够很容易对数据进行分区，因为语法比较简单，很容易实现。在下面这种情况下，使用hash分区比range分区更好：实现不知道需要将多少数据映射到给定的范围的时候，分区的范围大小很难确定，或者很难平衡的时候，range分区使数据得到不希望的聚集时性能特性，如并行DML、分区剪枝和分区连接很重要的时候。

　　创建散列分区时，必须指定一下信息。

　　分区方法：hash

　　分区列：分区数量或单独的分区描述

　　分裂、删除和合并分区不能应用于hash分区，但是，hash分区能够合并和添加

　　创建hash分区

　　create table daodao

　　(usernumber varchar2(32) not null)

　　partition by hash (usernumber)

　　(

　　partition p1 tablespace users，

　　partition p2 tablespace users,

　　partition p3 tablespace users，

　　partition p4 tablespace users，

　　);

　　简单一点的语法创建hash分区：

　　create table daodao1 (usernumber varchar2(32)) partition by hash (usernumber) partitions 8;

　　hash分区一般使用的场景：

　　是分区的热点块：

　　第一：解决高并发，热点块问题，为什么可以解决高并发，热点块呢?

　　由于分区在物理上是独立分开的，也就说不同的分区肯定不同一个数据块，这样当你插入数据的时候，由于插入到不同的分区，也就是出入到了不同的数据块。

　　例如：

　　SQL> create table t as select object_id from dba_objects where 1=2;

　　Table created

　　SQL> insert into t select rownum from dual connect by level < 10;

　　9 rows inserted

　　SQL> commit;

　　Commit complete

　　SQL> select dbms_rowid.rowid_block_number(rowid),object_id from t;

　　DBMS_ROWID.ROWID_BLOCK_NUMBER( OBJECT_ID

　　------------------------------ ----------

　　8645 1

　　8645 2

　　8645 3

　　8645 4

　　8645 5

　　8645 6

　　8645 7

　　8645 8

　　8645 9

　　9 rows selected

　　SQL> create table t_part(object_id number) partition by hash(object_id) partitions 2;

　　Table created

　　SQL> insert into t_part select rownum from dual connect by level < 10;

　　9 rows inserted

　　SQL> select dbms_rowid.rowid_block_number(rowid),object_id from t_part;

　　DBMS_ROWID.ROWID_BLOCK_NUMBER( OBJECT_ID

　　------------------------------ ----------

　　87474 2

　　87474 5

　　87474 6

　　87474 8

　　86450 1

　　86450 3

　　86450 4

　　86450 7

　　86450 9

　　9 rows selected

　　SQL> select ora_hash(object_id,1)+1,object_id from t_part;

　　ORA_HASH(OBJECT_ID,1)+1 OBJECT_ID

　　----------------------- ----------

　　1 2

　　1 5

　　1 6

　　1 8

　　2 1

　　2 3

　　2 4

　　2 7

　　2 9

　　9 rows selected

　　SQL> select partition_name from dba_segments where segment_name='T_PART';

　　PARTITION_NAME

　　------------------------------

　　SYS_P41

　　SYS_P42

　　SQL> select * from t_part partition(SYS_P41);

　　OBJECT_ID

　　----------

　　2

　　5

　　6

　　8

　　第二：解决索引高度问题，离散数据

　　3.List分区：列表分区

　　List分区可以控制如何将行映射到分区中去。可以在每个分区的键上定义离散的值，不同于range分区和hash分区。

　　range分区与分区相关联，为分区列假设了一个值的自然范围，故不可能将该值范围以外的分区组织到一起。

　　hash分区时不允许对数据的划分进行控制，因为系统使用的是散列函数来划分数据的。

　　List分区的优点在于按照自然的分区方式将无序和不相关的数据集合分组。

　　List分区不支持多列分区，如果将表按列分区，那么分区键就只能有表的一个单独列组成。

　　list分区时必须指定的以下内容：

　　分区方法：list

　　分区列：分区描述，每个描述指定一串文字值(值的列表)，它们是分区列(它们限定将被包括在分区中的行)的离散值：

　　create table sales_list

　　(provcode number(5))

　　partition by list (provcode)

　　(partition p1 values (1),

　　partition p2 values (2),

　　partition p3 values (3),

　　partition p4 values (DEFAULT)

　　);

　　1.添加分区：

　　alter table T add partition P_2005_04 values less than (to_date('2005-05-01'.'yyyy-MM-dd'))

　　(

　　subpartition P_2005_04_P1013 values('P1013'),

　　subpartition P_2005_04_P1013 values('P1014'),

　　subpartition P_2005_04_P1013 values('P1015'),

　　subpartition P_2005_04_P1013 values('P1016')

　　)

　　2.删除分区

　　alter table T drop partition p_2005_04;

　　截断一个分区表中的一个分区的数据：

　　alter table sales3 truncate partition sp1;

　　这种方式会使全局分区索引无效

　　alter table sales3 truncate partition sp1 update indexes

　　这种方式全局分区索引不会失效

　　3.查看一个表是不是分区表

　　SQL> select table_name,partitioned from user_tables;

　　TABLE_NAME PARTITIONED

　　------------------------------ -----------

　　DAODAO NO

　　T NO

　　T_PART YES

　　DAODAO_BIG NO

　　8 rows selected

　　另：如何知道与分区相关的数据字典视图?

　　SQL> select table_name from dictionary where table_name like '%PART%';

　　TABLE_NAME

　　------------------------------

　　DBA_SUBPARTITION_TEMPLATES

　　DBA_SUBPART_COL_STATISTICS

　　DBA_SUBPART_HISTOGRAMS

　　DBA_SUBPART_KEY_COLUMNS

　　DBA_TAB_PARTITIONS

　　DBA_TAB_SUBPARTITIONS

　　USER_PARTIAL_DROP_TABS

　　USER_PART_COL_STATISTICS

　　USER_PART_HISTOGRAMS

　　USER_PART_INDEXES

　　USER_PART_KEY_COLUMNS

　　USER_PART_LOBS

　　USER_PART_TABLES

　　ALL_PARTIAL_DROP_TABS

　　ALL_PART_COL_STATISTICS

　　ALL_PART_HISTOGRAMS

　　ALL_PART_INDEXES

　　ALL_PART_KEY_COLUMNS

　　ALL_PART_LOBS

　　ALL_PART_TABLES

　　TABLE_NAME

　　------------------------------

　　DBA_MVIEW_DETAIL_PARTITION

　　DBA_MVIEW_DETAIL_SUBPARTITION

　　DBA_PARTIAL_DROP_TABS

　　DBA_PART_COL_STATISTICS

　　DBA_PART_HISTOGRAMS

　　DBA_PART_INDEXES

　　DBA_PART_KEY_COLUMNS

　　DBA_PART_LOBS

　　DBA_PART_TABLES

　　DBA_IND_PARTITIONS

　　DBA_IND_SUBPARTITIONS

　　DBA_LOB_PARTITIONS

　　DBA_LOB_SUBPARTITIONS

　　USER_SUBPARTITION_TEMPLATES

　　USER_SUBPART_COL_STATISTICS

　　USER_SUBPART_HISTOGRAMS

　　USER_SUBPART_KEY_COLUMNS

　　USER_TAB_PARTITIONS

　　USER_TAB_SUBPARTITIONS

　　USER_IND_PARTITIONS

　　USER_IND_SUBPARTITIONS

　　TABLE_NAME

　　------------------------------

　　USER_LOB_PARTITIONS

　　USER_LOB_SUBPARTITIONS

　　USER_MVIEW_DETAIL_PARTITION

　　USER_MVIEW_DETAIL_SUBPARTITION

　　ALL_SUBPARTITION_TEMPLATES

　　ALL_SUBPART_COL_STATISTICS

　　ALL_SUBPART_HISTOGRAMS

　　ALL_SUBPART_KEY_COLUMNS

　　ALL_TAB_PARTITIONS

　　ALL_TAB_SUBPARTITIONS

　　ALL_IND_PARTITIONS

　　ALL_IND_SUBPARTITIONS

　　ALL_LOB_PARTITIONS

　　ALL_LOB_SUBPARTITIONS

　　ALL_MVIEW_DETAIL_PARTITION

　　ALL_MVIEW_DETAIL_SUBPARTITION

　　57 rows selected