分区表基础知识培训

www.oracledatabase12g.com

刘相兵 (Maclean Liu)[email protected]

分区表基础知识培训

www.oracledatabase12g.com www.oracledatabase12g.com

www.oracledatabase12g.com www.oracledatabase12g.comwww.oracledatabase12g.com www.oracledatabase12g.comwww.oracledatabase12g.com www.oracledatabase12g.com

About Me Email:[email protected]

Blog:www.oracledatabase12g.com

Oracle Certified Database Administrator Master 10g and 11g

Over 6 years experience with Oracle DBA technology

Over 7 years experience with Linux technology

Member Independent Oracle Users Group

Member All China Users Group

Presents for advanced Oracle topics: RAC, DataGuard, Performance Tuning and Oracle Internal.



了解分区表的基础概念了解分区索引的基础概念了解分区表统计信息概念其他注意事项



什么是 partition 分区？

MarJan

Feb

Sales

SQL

Application •将数据分成更小的块 (chunk)• 包括表和索引

•分区对于应用程序而言是透明的，无需修改SQL 语句

• Oracle SQL 优化器自动识别分区• 避免了大表的全表扫描

•丰富的分区选项• 包括 Range,Hash,List,Ref,composite

等



大型数据仓库 Oracle 特性使用情况



核心功能性能增强管理

Oracle8 Range partitioning

Global range indexes

“Static” partition pruning

Basic maintenance operations: add, drop, exchange

Oracle8i Hash and composite range-hash partitioning

Partition-wise joins

“Dynamic” pruning

Merge operation

Oracle9i List partitioning Global index maintenance

Oracle9i R2 Composite range-list partitioning

Fast partition split

Oracle10g Global hash indexes Local Index maintenance

Oracle10g R2 1M partitions per table “Multi-dimensional” pruning

Fast drop table

Oracle Partitioning 分区特性



Oracle Partitioning 分区特性核心功能性能增强管理

Oracle8 Range partitioning

Global range indexes

“Static” partition pruning

Basic maintenance operations: add, drop, exchange

Oracle8i Hash and composite range-hash partitioning

Partition-wise joins

“Dynamic” pruning

Merge operation

Oracle9i List partitioning Global index maintenance

Oracle9i R2 Composite range-list partitioning

Fast partition split

Oracle10g Global hash indexes Local Index maintenance

Oracle10g R2 1M partitions per table “Multi-dimensional” pruning

Fast drop table

Oracle Database 11g

More composite choicesREF PartitioningVirtual Column Partitioning

Interval PartitioningPartition Advisor



复合分区

Range List Hash

Range 11g 9i 8i

List 11g 11g 11g

例如列表 - 范围分区 :以国家分区后，再以月份分区



分区对性能的优化--

-Sa

les

Ta

ble

---

06-Apr

06-Mar

06-May

06-Feb

06-Jan

06-Jun

SQL 仅访问相关分区使用已知值做静态数据修剪动态修剪使用内部递归 SQL 以找到相关分区

最小化 IO 操作提供极大的性能收益

SWHERE sales_date BETWEEN ‘01-MAR-2006’ AND ‘31-MAY-2006’;

SELECT sum(sales_amount) FROM salesWHERE sales_date BETWEEN ‘01-MAR-2006’ AND ‘31-MAY-2006’;-2006’ AND ‘31-MAY-2006’;



分区精简

Sales TableSales Table

May 22May 22ndnd 20082008

May 23May 23rdrd 20082008

May 24May 24thth 20082008




May 21May 21stst 20082008

Select sum(sales_amount)From SALESWhere sales_date betweento_date(‘05/20/2008’,’MM/DD/YYYY’)Andto_date(‘05/23/2008’,’MM/DD/YYYY’);

2008 年 5 月 20 日到 5 月23 日的总销售额是多少？



静态精简

• 示例计划



动态分区精简针对复杂查询存在高级精简的机制

在运行时递归 SQL 语句会自动找出相关分区

关注执行计划中的 PSTAR/PSTOP 字段中的 KEY

SELECT sum(amount_sold)FROM sales s, times tWHERE t.time_id = s.time_id AND t.calendar_month_desc IN (‘MAR-04’,‘APR-04’,‘MAY-04’);


May 2004May 2004

June 2004June 2004

Jul 2004Jul 2004

Jan 2004Jan 2004

Feb 2004Feb 2004

Mar 2004Mar 2004

Apr 2004Apr 2004

Times TableTimes Table



动态分区精简嵌套循环

•示例计划



Q4’05 Q1’06 Q2’06 Q3’06

订单表 ( 按季度分区 )

其他分区不受影响 s not affected

分区对高可用的增强



分区对管理的增强

• 更小的可管理的数据块• 包括数据和索引元素

• 分区级别的操作• 备份和恢复• 数据加载

• 并行操作• 减少查询和操作的时间



范围分区 Range Partition

CREATE TABLE DEPT (DEPTNO NUMBER(2), DEPT_NAME VARCHAR2(30)) PARTITION BY RANGE(DEPTNO) (PARTITION D1 VALUES LESS THAN (10) TABLESPACE DEPT1, PARTITION D2 VALUES LESS THAN (20) TABLESPACE DEPT2, PARTITION D3 VALUES LESS THAN (MAXVALUE) TABLESPACE DEPT3);

INSERT INTO DEPT VALUES (1, ‘DEPT 1’);INSERT INTO DEPT VALUES (7, ‘DEPT 7’);INSERT INTO DEPT VALUES (10, ‘DEPT 10’);INSERT INTO DEPT VALUES (15, ‘DEPT 15’);INSERT INTO DEPT VALUES (22, ‘DEPT 22’);




SELECT * FROM DEPT;

DEPTNO DEPT

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

1 DEPT 1

7 DEPT 7

10 DEPT 10

15 DEPT 15

22 DEPT 22

SELECT * FROM DEPT PARTITION (D1);

DEPTNO DEPT

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

1 DEPT 1

7 DEPT 7





DEPTNO DEPT

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

10 DEPT 10

15 DEPT 15


DEPTNO DEPT

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

22 DEPT 22

ALTER TABLE DEPT DROP PARTITION D3;

Table altered.




SELECT * FROM DEPT;

DEPTNO DEPT

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

1 DEPT 1

7 DEPT 7

10 DEPT 10

15 DEPT 15 SELECT TABLE_NAME, PARTITIONED FROM USER_TABLES;

TABLE_NAME PAR

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

DEPT YES

TEMP NO




SELECT OWNER, TABLE_NAME, PARTITION_COUNT FROM SYS.DBA_PART_TABLES;

OWNER TABLE_NAME PARTITION_COUNT

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

TEMP DEPT 2

SELECT SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, TABLESPACE_NAME

FROM USER_SEGMENTS;

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME

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

TEMP TABLE USER_DATA

DEPT D1 TABLE PARTITION DEPT1

DEPT D2 TABLE PARTITION DEPT2



本地前缀分区索引

create index dept_index on dept (deptno)

local

(partition d1 tablespace dept2,

partition d2 tablespace dept3,

partition d3 tablespace dept1);

Index Created.



选择合适的分区索引类型



统计信息的收集

•CBO 下必须收集统计信息• 对于分区表使用动态收集 (dynamic sampling) 是不合适的

•对于空表执行各类查询，以便收集 column usage 列使用情况信息• 这帮助 oracle 自动统计机制了解哪些列上需要创建直方图 (histogram)

•在完成大规模的数据统计后记得收集一下统计信息

•每天晚上运行的 Gather_stats_job ，并非每次都把所有的大表都重新收集一遍statistics ，而是仅针对从上一次收集以来修改超过 10% 的表做重新收集。没有专家的建议情况下，不要禁用该自动收集统计信息作业！



统计信息的收集

•默认情况下 DBMS_STATS 为每张表都收集以下信息 , 可以利用 Granularity 参数控制

• Global stats table level( 表级 )• 分区级别 partition level • 子分区级别 subpartition level( 默认不收集 )

Optimizer 优化器在查询接触到 2 个或以上分区的情况下结合使用 global stats表级和 paratition level 分区级别的统计信息

Optimizer 优化器在查询做分区排除和只用一个分区即能满足查询访问的情况下使用分区级别的信息

Optimizer 优化器在查询做分区排除和只有一个子分区即可满足查询的情况下使用子分区级别的统计信息

Incremental Global StatisticsSales TableSales Table


May 23May 23rdrd 2008 2008

May 18May 18thth 2008 2008

May 19May 19thth 2008 2008

May 20May 20thth 2008 2008

May 21May 21stst 2008 2008

Sysaux Tablespace

S1

S2

S3

S4

S5

S6

1. Partition level stats are gathered & synopsis created

Global Statistic

2. Global stats generated by aggregating partition synopsis


Incremental Global Statistics Cont’d



May 23May 23rdrd 20082008

May 24May 24thth 2008 2008

May 18May 18thth 2008 2008

May 19May 19thth 2008 2008

May 20May 20thth 2008 2008

May 21May 21stst 2008 2008

Sysaux Tablespace

3. A new partition is added to the table & Data is Loaded

May 24May 24thth 2008 2008 S7 4. Gather partition statistics for new partition

S1

S2

S3

S4

S5

S6

5. Retrieve synopsis for each of the other partitions from Sysaux

Global Statistic

6. Global stats generated by aggregating the original partition synopsis with the new one




收集精确的统计信息的必要步骤

为表启用增量统计信息特性EXEC

DBMS_STATS.SET_TABLE_PREFS('SH’,'SALES','INCREMENTAL','TRUE');

在数据加载后使用 GATHER_TABLE_STATS 为表收集统计信息EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES');

以上命令会为分区收集统计信息并利用基于 partition level 的信息更新global statistics



注意事项

新加载数据收集所有数据变化的分区统计信息Exec

dbms_stats.gather_table_stats(‘MACLEAN’,’TAB’,GRANULARITY=>’SUBPARTITION’,PARTNAME=>’P_XXX_NewYork’);

Global statistics 会被更新



注意事项

增加或减少分区或子分区Add 、 Split 、 Merge 、 Truncate 、 Exchange 分区或子分区收集有变化的子分区的统计信息Exec

dbms_stats.gather_table_stats(‘MACLEAN’,’TAB’,GRANULARITY=>’SUBPARTITION’,PARTNAME=>’P_XXX_NewYork’);

Global statistics 会被更新



更多信息


Partition

orhttp://www.oracledatabase12g.com/archives/category/oracle/oracle-partitioning

http://www.oracledatabase12g.com/archives/category/oracle/oracle-partitioning



Question & Answer

If you have more questions later, feel free to ask

分区表基础知识培训

Technology

Transcript of 分区表基础知识培训