通過案例學(xué)調(diào)優(yōu)之--分區(qū)表基本概念

通過案例學(xué)調(diào)優(yōu)之--分區(qū)表基本概念

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.

分區(qū)表設(shè)計(jì)原則

• 表的大小：當(dāng)表的大小超過1.5GB－2GB，或?qū)τ?strong>OLTP系統(tǒng)，表的記錄超過1000萬(wàn)，都應(yīng)考慮對(duì)表進(jìn)行分區(qū)。 

• 數(shù)據(jù)訪問特性：基于表的大部分查詢應(yīng)用，只訪問表中少量的數(shù)據(jù)。對(duì)于這樣表進(jìn)行分區(qū)，可充分利用分區(qū)排除無關(guān)數(shù)據(jù)查詢的特性。 

• 數(shù)據(jù)維護(hù)：按時(shí)間段刪除成批的數(shù)據(jù)，例如按月刪除歷史數(shù)據(jù)。對(duì)于這樣的表需要考慮進(jìn)行分區(qū)，以滿足維護(hù)的需要。

• 數(shù)據(jù)備份和恢復(fù)： 按時(shí)間周期進(jìn)行表空間的備份時(shí)，將分區(qū)與表空間建立對(duì)應(yīng)關(guān)系。

• 只讀數(shù)據(jù)：如果一個(gè)表中大部分?jǐn)?shù)據(jù)都是只讀數(shù)據(jù)，通過對(duì)表進(jìn)行分區(qū)，可將只讀數(shù)據(jù)存儲(chǔ)在只讀表空間中，對(duì)于數(shù)據(jù)庫(kù)的備份是非常有益的。 

• 并行數(shù)據(jù)操作：對(duì)于經(jīng)常執(zhí)行并行操作（如Parallel Insert,Parallel Update等）的表應(yīng)考慮進(jìn)行分區(qū)。 

• 表的可用性：當(dāng)對(duì)表的部分?jǐn)?shù)據(jù)可用性要求很高時(shí)，應(yīng)考慮進(jìn)行表分區(qū)。 

分區(qū)表的類型
Oracle 10g：

• Range Partitioning

• List Partitioning

• Hash Partitioning

• Composite Partitioning

• RANG-HASH

• RANG-LIST

Oracle 11g:

分區(qū)表常用視圖

1、查詢當(dāng)前用戶下有哪些是分區(qū)表：

SELECT * FROM USER_PART_TABLES;

2、查詢當(dāng)前用戶下有哪些分區(qū)索引：

SELECT * FROM USER_PART_INDEXES;

3、查詢當(dāng)前用戶下分區(qū)索引的分區(qū)信息：

SELECT * FROM USER_IND_PARTITIONS T

WHERE T.INDEX_NAME=xxx；

4、查詢當(dāng)前用戶下分區(qū)表的分區(qū)信息：

SELECT * FROM USER_TAB_PARTITIONS T

WHERE T.TABLE_NAME=xxx；

5、查詢某分區(qū)下的數(shù)據(jù)量：

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

6、查詢索引、表上在那些列上創(chuàng)建了分區(qū)：

SELECT * FROM USER_PART_KEY_COLUMNS;

7、查詢某用戶下二級(jí)分區(qū)的信息（只有創(chuàng)建了二級(jí)分區(qū)才有數(shù)據(jù)）：

SELECT * FROM USER_TAB_SUBPARTITIONS;

分區(qū)表索引

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.        局部索引一定是分區(qū)索引，分區(qū)鍵等同于表的分區(qū)鍵，分區(qū)數(shù)等同于表的分區(qū)說，一句話，局部索引的分區(qū)機(jī)制和表的分區(qū)機(jī)制一樣。

2.        如果局部索引的索引列以分區(qū)鍵開頭，則稱為前綴局部索引。

3.        如果局部索引的列不是以分區(qū)鍵開頭，或者不包含分區(qū)鍵列，則稱為非前綴索引。

4.        前綴和非前綴索引都可以支持索引分區(qū)消除，前提是查詢的條件中包含索引分區(qū)鍵。

5.        局部索引只支持分區(qū)內(nèi)的唯一性，無法支持表上的唯一性，因此如果要用局部索引去給表做唯一性約束，則約束中必須要包括分區(qū)鍵列。

6.        局部分區(qū)索引是對(duì)單個(gè)分區(qū)的，每個(gè)分區(qū)索引只指向一個(gè)表分區(qū)，全局索引則不然，一個(gè)分區(qū)索引能指向n個(gè)表分區(qū)，同時(shí)，一個(gè)表分區(qū)，也可能指向n個(gè)索引分區(qū)， 對(duì)分區(qū)表中的某個(gè)分區(qū)做truncate或者move，shrink等，可能會(huì)影響到n個(gè)全局索引分區(qū)，正因?yàn)檫@點(diǎn)，局部分區(qū)索引具有更高的可用性。

7.        位圖索引只能為局部分區(qū)索引。

8.        局部索引多應(yīng)用于數(shù)據(jù)倉(cāng)庫(kù)環(huán)境中。

全局索引global index

1.        全局索引的分區(qū)鍵和分區(qū)數(shù)和表的分區(qū)鍵和分區(qū)數(shù)可能都不相同，表和全局索引的分區(qū)機(jī)制不一樣。

2.        全局索引可以分區(qū)，也可以是不分區(qū)索引，全局索引必須是前綴索引，即全局索引的索引列必須是以索引分區(qū)鍵作為其前幾列。

3.        全局分區(qū)索引的索引條目可能指向若干個(gè)分區(qū)，因此，對(duì)于全局分區(qū)索引，即使只動(dòng)，截?cái)嘁粋€(gè)分區(qū)中的數(shù)據(jù)，都需要rebulid若干個(gè)分區(qū)甚至是整個(gè)索引。

4.        全局索引多應(yīng)用于oltp系統(tǒng)中。

5.        全局分區(qū)索引只按范圍或者散列hash分區(qū)，hash分區(qū)是10g以后才支持。

6.        oracle9i以后對(duì)分區(qū)表做move或者truncate的時(shí)可以用update global indexes語(yǔ)句來同步更新全局分區(qū)索引，用消耗一定資源來?yè)Q取高度的可用性。

7.        表用a列作分區(qū)，索引用b做局部分區(qū)索引，若where條件中用b來查詢，那么oracle會(huì)掃描所有的表和索引的分區(qū)，成本會(huì)比分區(qū)更高，此時(shí)可以考慮用b做全局分區(qū)索引

分區(qū)索引字典

DBA_PART_INDEXES   分區(qū)索引的概要統(tǒng)計(jì)信息，可以得知每個(gè)表上有哪些分區(qū)索引，分區(qū)索引的類新(local/global,)

Dba_ind_partitions    每個(gè)分區(qū)索引的分區(qū)級(jí)統(tǒng)計(jì)信息

Dba_indexesminusdba_part_indexes  可以得到每個(gè)表上有哪些非分區(qū)索引 

索引重建 

Alter index idx_name rebuild partition index_partition_name [online nologging]

需要對(duì)每個(gè)分區(qū)索引做rebuild，重建的時(shí)候可以選擇online(不會(huì)鎖定表),或者nologging建立索引的時(shí)候不生成日志，加快速度。

Alter index rebuild idx_name [online nologging]

對(duì)非分區(qū)索引，只能整個(gè)index重建