[转帖]通过分区进行优化(转)_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 3593 | 回复: 0   主题: [转帖]通过分区进行优化(转)        下一篇 
derek
注册用户
等级:中校
经验:1550
发帖:209
精华:0
注册:2011-7-21
状态:离线
发送短消息息给derek 加好友    发送短消息息给derek 发消息
发表于: IP:您无权察看 2011-8-3 16:48:31 | [全部帖] [楼主帖] 楼主

“分而治之”—没有能够比Oracle数据库的分区特性更好地阐释这个象征性原则的了。从版本8开始,您就可以将一个表或索引划分成多个段,然后将它们放在不同的表空间内。该表仍作为一个逻辑实体定址,各个分区则存储为单独的段,这就简化了数据操作。

在版本11中,引用分区、间隔分区、虚拟列分区以及扩展的组合分区等增强功能支持无限的分区设计可能性,并提高了可管理性。

如果您希望在分区的基础以及对您选择分区列或方案的决定有影响的因素上进一步提高,请参阅《Oracle Magazine》2006年9/10月刊上我的文章

扩展的组合分区

使用组合分区— Oracle8i

数据库中引入的方案—您可以在分区中创建子分区,从而进一步细分表。但在该情况下,您只能通过散列子分区对已按范围分区的表进行再次分区。Oracle9

i

中对组合分区进行了扩展,使之包括范围-列表子分区。

这些方案满足了大多数环境(但并非所有环境)的需要。例如,假设您有一个名为SALES的包含多列的表,其中包括两个特殊的列,它们是分区的候选列:state_code,它存储一个表示销售状态的两位代码,表面上用于计算销售税;以及product_code,一个用于识别销售记录所销售的产品的三位数字。用户通过对两列进行同等的筛选对该表进行查询,存档要求也基于这两列。应用分区决策的原则时,您会发现这两列都是合适的分区键候选者。

在Oracle数据库11g

中,您可以相当轻松地解决此问题。在该版本中,并不局限于范围-散列和范围-列表组合分区。您的选择实际上没有任何限制;您可以使用任何组合创建组合分区。

在这个示例中,您可以决定根据product_code(因为该列具有更多离散的值)对表进行列表分区,然后根据state_code再次进行列表分区。下面的代码示例显示了如何实现该操作:

create table sales
(
sales_id    number,
product_code number,
state_code  varchar2(2)
)
partition by list (product_code)
subpartition by list (state_code)
(
partition p101 values (101)
(
subpartition p101_ct values ('CT'),
subpartition p101_ny values ('NY'),
subpartition p101_def values (default)
),
partition p201 values (201)
(
subpartition p201_ct values ('CT'),
subpartition p201_ny values ('NY'),
subpartition p201_def values (default)
)
)


选择并不仅限于此处显示的方法。您还可以创建列表-范围组合分区。在上面的示例中,假设产品代码不是离散的,而是在一个范围内。您将希望根据state_code进行列表分区,然后再根据product_code划分子分区。下面是实现该操作的代码示例。

create table sales1
(
sales_id    number,
product_code number,
state_code  varchar2(2)
)
partition by list (state_code)
subpartition by range (product_code)
(
partition CT values ('CT')
(
subpartition ct_100 values less than (101),
subpartition ct_200 values less than (201)
),
partition NY values ('NY')
(
subpartition NY_100 values less than (101),
subpartition NY_200 values less than (201)
)
)


您也可以创建范围-范围组合分区,如果您有两个日期域,该方法将非常方便。例如,考虑一个用于销售处理系统的表,该表包括一个交易日期和一个交货日期。您可能希望根据一个日期进行范围分区,然后再根据另一个日期进行子范围分区。该方案允许您根据日期进行备份、存档和清除。

总而言之,您在Oracle数据库11g

中可以创建以下类型的组合分区:

    范围-范围
    范围-散列
    范围-列表
    列表-范围
    列表-散列
    列表-列表

引用分区

下面是设计分区方案过程中的一个典型问题:并非所有表都具有您需要根据其进行分区的列。假设您要创建一个销售系统,该系统包括两个简单的表(sales和customers):

create table customers
(
cust_id  number primary key,
cust_name varchar2(200),
rating   varchar2(1) not null
)
partition by list (rating)
(
partition pA values ('A'),
partition pB values ('B')
);


sales表的创建如下所示。它是customers表的一个子表。

create table sales
(
sales_id   number primary key,
cust_id    number not null,
sales_amt  number,
constraint fk_sales_01
foreign key (cust_id)
references customers
);


理想情况下,您希望用相同的方式对sales表和customers表分区:根据rating列进行列表分区。但有一个严重问题:sales表没有名为rating的列!那么如何根据一个不存在的列进行分区呢?

在Oracle数据库11g

中,您可以使用一个称为引用分区的新特性。下面的示例显示了如何将该特性应用于sales表:

create table sales
(
sales_id   number primary key,
cust_id    number not null,
sales_amt  number,
constraint fk_sales_01
foreign key (cust_id)
references customers
)
partition by reference (fk_sales_01);


这段代码创建了与���表customers中相同的分区。注意,虽然没有名为rating的列,但仍根据该列对表进行了分区。partition by reference (fk_sales_01)子句包括了分区定义中的外键名。该语句指示Oracle数据库11g

确认通过父表(在该示例中为customers)中使用的方案进行了分区。注意cust_id列的NOT NULL约束;这是引用分区所必需的。

如果您检查sales表中分区的分区边界:

SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES';
PARTITION_NAME HIGH_VALUE
--------------- -------------------------------
PA
PB


高值为空,这意味着此处的边界派生自父表。分区的名称与父表中的名称相同。您可以通过查询user_part_tables视图来检查分区的类型。一个名为ref_ptn_constraint_name的特殊列显示了外键约束名称。

SQL> select table_name, partitioning_type, ref_ptn_constraint_name
2 from user_part_tables
3 where table_name in ('CUSTOMERS','SALES');
TABLE_NAME                    PARTITION REF_PTN_CONSTRAINT_NAME
------------------------------ --------- --------------------------
CUSTOMERS                     LIST
SALES                         REFERENCE FK_SALES_01


如果您希望按照父表分区的方式对子表进行分区,但没有相同的列,您又不想仅仅为了分区而引入这些列,此时引用分区将非常方便。而且,您不必针对每个子表显式声明一个很长的分区子句。

间隔分区

范围分区允许您根据分区键列的值的范围创建分区。下面是一个按范围分区的表的示例:

create table sales6
(
sales_id   number,
sales_dt   date
)
partition by range (sales_dt)
(
partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd')),
partition p0702 values less than (to_date('2007-03-01','yyyy-mm-dd'))
);


您在此处仅针对2007年1月和2007年2月定义了分区,如果表中插入一条sales_dt在2007年3月的记录,会发生什么情况?插入将失败,并显示以下错误:

ORA-14400: inserted partition key does not map to any partition


显然,您需要针对2007年3月添加一个分区,然后才能插入一条记录。但通常说起来容易做起来难。您通常无法容忍事先创建大量分区,但其中很少一部分可能会产生此错误。

如果Oracle以某种方式自动察觉到对新分区的需要,然后创建它们,这样不是更好吗?Oracle数据库11g

可以,它可以使用一个称为间隔分区的特性。此时,您不必定义分区及它们的边界,只需定义一个定义了每个分区边界的间隔。下面是使用间隔分区的示例:

create table sales6
(
sales_id   number,
sales_dt   date
)
partition by range (sales_dt)
interval (numtoyminterval(1,'MONTH'))
(
partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd'))
);


注意子句:interval后面跟着时间间隔。您在此处指示Oracle为每个月份创建一个时间间隔。您已经为2007年1月的数据创建了名为p0701的初始分区。现在,假设您插入了一条包括2007年6月数据的记录:

SQL> insert into sales6 values (1,'01-jun-07');
1 row created.


Oracle不会返回错误,而是成功执行该语句。那么这条记录将转向何处?p0701分区不能包括该记录,我们没有为2007年6月定义分区。但此时,如果您检查该表的分区:

SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES6';
PARTITION_NAME HIGH_VALUE
--------------- ----------------------------------------------------------------
P0701          TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C

    ALENDAR=GREGORIA

SYS_P41        TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C

    ALENDAR=GREGORIA

注意名为SYS_P1、高值为2007年7月1日的分区,它最多可以容纳到6月底的数据。该分区是由Oracle动态创建的,并具有一个系统生成的名称。

现在,假设您输入一个小于最高值的值,如2007年5月1日。理想情况下,它应该具有自己的分区,因为您的分区时间间隔是一个月。

SQL> insert into sales6 values (1,'01-may-07');
1 row created.
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES6';
PARTITION_NAME HIGH_VALUE
--------------- ----------------------------------------------------------------
P0701          TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C

    ALENDAR=GREGORIA

SYS_P41        TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C

    ALENDAR=GREGORIA

SYS_P42        TO_DATE(' 2007-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_C

    ALENDAR=GREGORIA

注意新分区SYS_P42,其上限为6月1日,因此该分区可以保留2006年5月的数据。该分区是通过拆分SYS_P41分区创建的(针对6月份)。因此,当您定义一个间隔分区方案时,Oracle会自动创建和维护分区。

如果您希望将分区存储在特定表空间中,可以使用store in子句执行该操作:

interval (numtoyminterval(1,'MONTH'))
store in (TS1,TS2,TS3)


该子句以循环方式将分区存储在表空间TS1、TS2和TS3中。

应用程序开发人员如何定位特定分区?一种方法是知道名称,这种方法可能不可行,即使您知道名称,这种方法也非常容易出错。为了便于访问特定分区,Oracle数据库11g为分区SQL提供了一个新语法:

SQL> select * from sales6 partition for (to_date('15-may-2007','dd-mon-yyyy'));
SALES_ID SALES_DT
---------- ---------
1 01-MAY-07


注意新子句for (值),它允许您直接引用分区,而不必通过它们的确切名称进行显式调用。如果您希望截断或删除一个分区,可以调用这个扩展的分段语法。

以此方式创建表之后,DBA_PART_TABLES视图中的PARTITIONING_TYPE列会显示时间间隔。




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论