分区表（一）

分区表和普通表
一、分区表
随着表的不断增大，对于新纪录的增加，查找，删除等（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