通过案例学调优之--分区表基本概念

通过案例学调优之--分区表基本概念

Introduction to Partitioning     

       Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables. However, after partitions are defined, DDL statements can access and manipulate individuals partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects. Also, partitioning is entirely transparent to applications.

       Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.

Partitioning is useful for many different types of applications, particularly applications that manage large volumes of data. OLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from performance and manageability.

分区表设计原则

• 表的大小：当表的大小超过1.5GB－2GB，或对于OLTP系统，表的记录超过1000万，都应考虑对表进行分区。 

• 数据访问特性：基于表的大部分查询应用，只访问表中少量的数据。对于这样表进行分区，可充分利用分区排除无关数据查询的特性。 

• 数据维护：按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需要考虑进行分区，以满足维护的需要。

• 数据备份和恢复： 按时间周期进行表空间的备份时，将分区与表空间建立对应关系。

• 只读数据：如果一个表中大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中，对于数据库的备份是非常有益的。 

• 并行数据操作：对于经常执行并行操作（如Parallel Insert,Parallel Update等）的表应考虑进行分区。 

• 表的可用性：当对表的部分数据可用性要求很高时，应考虑进行表分区。 

分区表的类型
Oracle 10g：

• Range Partitioning

• List Partitioning

• Hash Partitioning

• Composite Partitioning

• RANG-HASH

• RANG-LIST

Oracle 11g:

分区表常用视图

1、查询当前用户下有哪些是分区表：

SELECT * FROM USER_PART_TABLES;

2、查询当前用户下有哪些分区索引：

SELECT * FROM USER_PART_INDEXES;

3、查询当前用户下分区索引的分区信息：

SELECT * FROM USER_IND_PARTITIONS T

WHERE T.INDEX_NAME=xxx；

4、查询当前用户下分区表的分区信息：

SELECT * FROM USER_TAB_PARTITIONS T

WHERE T.TABLE_NAME=xxx；

5、查询某分区下的数据量：

SELECT COUNT(*) FROM TABLE_PARTITION PARTITION(TAB_PARTOTION_01);

6、查询索引、表上在那些列上创建了分区：

SELECT * FROM USER_PART_KEY_COLUMNS;

7、查询某用户下二级分区的信息（只有创建了二级分区才有数据）：

SELECT * FROM USER_TAB_SUBPARTITIONS;

分区表索引

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes). In general, you should use global indexes for OLTP applications and local indexes for data warehousing or DSS applications. Also, whenever possible, you should try to use local indexes because they are easier to manage. When deciding what kind of partitioned index to use, you should consider the following guidelines in order:

1. If the table partitioning column is a subset of the index keys, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 2.

2. If the index is unique, use a global index. If this is the case, you are finished. If this is not the case, continue to guideline 3.

3. If your priority is manageability, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 4.

4. If the application is an OLTP one and users need quick response times, use a global index. If the application is a DSS one and users are more interested in throughput, use a local index.

局部索引local index

1.        局部索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区说，一句话，局部索引的分区机制和表的分区机制一样。

2.        如果局部索引的索引列以分区键开头，则称为前缀局部索引。

3.        如果局部索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。

4.        前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。

5.        局部索引只支持分区内的唯一性，无法支持表上的唯一性，因此如果要用局部索引去给表做唯一性约束，则约束中必须要包括分区键列。

6.        局部分区索引是对单个分区的，每个分区索引只指向一个表分区，全局索引则不然，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区， 对分区表中的某个分区做truncate或者move，shrink等，可能会影响到n个全局索引分区，正因为这点，局部分区索引具有更高的可用性。

7.        位图索引只能为局部分区索引。

8.        局部索引多应用于数据仓库环境中。

全局索引global index

1.        全局索引的分区键和分区数和表的分区键和分区数可能都不相同，表和全局索引的分区机制不一样。

2.        全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即全局索引的索引列必须是以索引分区键作为其前几列。

3.        全局分区索引的索引条目可能指向若干个分区，因此，对于全局分区索引，即使只动，截断一个分区中的数据，都需要rebulid若干个分区甚至是整个索引。

4.        全局索引多应用于oltp系统中。

5.        全局分区索引只按范围或者散列hash分区，hash分区是10g以后才支持。

6.        oracle9i以后对分区表做move或者truncate的时可以用update global indexes语句来同步更新全局分区索引，用消耗一定资源来换取高度的可用性。

7.        表用a列作分区，索引用b做局部分区索引，若where条件中用b来查询，那么oracle会扫描所有的表和索引的分区，成本会比分区更高，此时可以考虑用b做全局分区索引

分区索引字典

DBA_PART_INDEXES   分区索引的概要统计信息，可以得知每个表上有哪些分区索引，分区索引的类新(local/global,)

Dba_ind_partitions    每个分区索引的分区级统计信息

Dba_indexesminusdba_part_indexes  可以得到每个表上有哪些非分区索引 

索引重建 

Alter index idx_name rebuild partition index_partition_name [online nologging]

需要对每个分区索引做rebuild，重建的时候可以选择online(不会锁定表),或者nologging建立索引的时候不生成日志，加快速度。

Alter index rebuild idx_name [online nologging]

对非分区索引，只能整个index重建