https://blog.csdn.net/weixin_41900819/article/details/79695420

–管理分区

应对哪种表应用分区功能；

1.大于2GB的表

2.含有1000万条记录以上的表，表中含有的数据越多，SQL操作的执行速度就会越慢。

3.将会含有大量数据的表。

4.强行拆分后可利于并行操作的表。

5.含有需要定期归档日志或删除部分的表。

一条可靠的原则是大大于2GB的表就可以应用分区功能。运行下面的查询命令可以显示数据库中占用空间的排名情况：

select * from (

select owner,segment_name,segment_type,partition_name,sum(bytes)/1024/1024 meg_tot

from dba_segments group by owner,segment_name,segment_type,partition_name

order by sum(extents) desc)

where rownum <=10

–创建分区表

Oracle 提供了一系列强大的分区功能，使用他们可以将表和索引拆分为较小的子集。例如，可以根据日期范围拆分表中的数据。

–分区策略

分区类型描述

范围根据日期,数值或字符创建分区

列表根据列表值创建分区

散列在没有明显分区键的情况下，以均分方式创建分区

组合组合使用多种分区方式

间隔当新分区键值超出现存最大范围时，通过自动分配新分区扩展范围分区

引用根据父表列为子表创建分区

虚拟列分区在虚拟列上创建分区

系统根据插入数据的应用程序创建分区

--按范围分区

使用 CREATE TABLE 语句中的 PARTITION BY RANGE 子句，可以定义居于范围的分区键。这可以确定用于控制分区划分的列。

使用 VALUES LESS THAN 子句可以定义范围分区的上边界。范围分区中的第一个分区没有下边界，所有小于VALUES LESS THAN 子句设置值的数据都会插入第一个分区。除了第一个分区外，其他分区的下边界都由前一个分区的上边界确定。

还可以使用 MAXVALUE 子句创建范围分区表的最高分区。所有分区键值高于较低范围的数据都会插入最顶层的 MAXVALUE 分区。

1.将分区键列设置为 number 类型

create table f_sales ( sales_amt number, d_date_id number)

partition by range (d_date_id) (

partition p_2012 values less than (20130101),

partition p_2013 values less than (20140101),

partition p_max values less than (maxvalue));

–在创建范围分区表时，可以不设置MAXVALUE分区。然而，如果不设置MAXVALUE分区，当插入不在任何范围内时，系统就会提示下面错误：

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

当该错误提示出现时，就必须添加一个能够容纳插入数据的范围分区。或者添加一个MAXVALUE分区。

–如果你使用Oracle Database 11g或更高的版本，可使用间隔分区策略。当插入数据超出范围值时，Oracle会自动添加分区/

--查询分区表信息

select table_name,partitioning_type,def_tablespace_name from user_part_tables where table_name='F_SALES';

--查询表中分区的信息

select table_name,partition_name,high_value from user_tab_partitions where table_name='F_SALES' order by table_name,partition_name;

2.将分区键列设置为TIMESTAMP类型

create table d_sales( sales_amt number,d_date_id date)

partition by range (d_date_id) (

partition p_2012 values less than (to_date('01-01-2012','dd-mm-yyyy')),

partition p_2014 values less than (to_date('01-01-2013','dd-mm-yyyy')),

partition p_max values less than (maxvalue));

–使用表空间存储分区

每个分区创建一个独立得表空间可以获得提高可用性和降低管理成本等好处。使用独立表空间可以控制分区得独立性，可以单独将分区设置为联机/脱机状态，不依赖其他分区对某个分区执行备份恢复操作。

要理解每个分区使用独立表空间的优点，可以考虑不用分区表的情况：

create tablespace p13_tbsp

datafile '/u02/datafile/p13_tbsp.dbf' size 100m

extent management local

uniform size 128k

segment space management auto;

--创建一个非分区表，存储到表空间

create table t_sales(

sales_amt number,

d_date_id number

) tablespace p1_tbsp;

--创建一个分区表，但是没有为分区设置表空间：

create table f_sales(

sales_amt number,

d_date_id number

) tablespace p1_tbsp

partition by range(d_date_id)

( partition y2011 values less than (20120101),

partition y2012 values less than (20130101),

partition y2013 values less than (20140101));

这种情况下所有的分区都存储在一个表空间中。

这种方法优于使用非分区表的方法，使用该方法可以在不影响其他分区的情况下，对某个分区执行分区维护操作，从而确保分区的独立性。然而，该方法并没有完全利用分区功能的优势。

将每个分区都存储到独立的表空间中：

create table p_sales(

sales_amt number,

d_date_id number

)tablespace p1_tbsp

partition by range(d_date_id)(

partition y11 values less than (20120101)

tablespace p11_tbsp,

partition y12 values less than (20130101)

tablespace p12_tbsp,

partition y13 values less than (20140101)

tablespace p13_tbsp );

这样每个分区的数据库都存储到了它们单独拥有的表空间和响应的数据文件中。

--也可以设置其他存储功能，对表空间使用了PCTFREE,PCTUSED和NOLOGGING子句

create table p_sales(

sales_amt number,

d_date_id number

)tablespace p1_tbsp

partition by range(d_date_id)(

partition y11 values less than (20120101)

tablespace p11_tbsp pctfree 5 pctused 70 nologging,

partition y12 values less than (20130101)

tablespace p12_tbsp pctfree 5 pctused 70 nologging,

partition y13 values less than (20140101)

tablespace p13_tbsp pctfree 5 pctused 70 nologging);

– pctfree

为一个块保留的空间百分比，表示数据块在什么情况下可以被insert，默认是10，表示当数据块的可用空间低于10%后，就不可以被insert了，只能被用于update；

即：当使用一个block时，在达到pctfree之前，该block是一直可以被插入的，这个时候处在上升期。

– pctused

是指当块里的数据低于多少百分比时，又可以重新被insert，一般默认是40,即40%，即：当数据低于40%时，又可以写入新的数据，这个时候处在下降期。

--根据散列值分区

对无序和非关系数据使用列表分区策略的效果很好。

使用 partition by list 进行散列值分区：

create table f_sales (

sales_amt number,

d_date_id number,

state_code varchar2(3))

partition by list (state_code)(

partition reg_west values ('AZ','CA','CO','MT','OR','ID','UT'),

partition reg_mid values ('IA','KS','MI','MN','MO','NE','OH'),

partition reg_def values (default));

这个列表分区的分区键可以仅为一列。使用 DEFAULT列表可以设置存储不符合列表值得分区。如果你没有设置DEFAULT列表，当插入得数据不符合已定义得分区时，

系统就会显示错误提示。

–散列分区

有时大型表中没有明显能够用于分区的列，不论是根据范围还是根据列表。可以用序列为表添加代理主键，并且根据唯一主键以平均方式划分分区。这样做的原因是没有可用于划分分区的列，或者该表的需求重点是提高插入操作的效率。

使用散列分区可以根据内部算法，以平均方式划分分区。你无法控制散列算法和Oracle划分分区的方式。你只能设置分区的数量，而Oracle会根据散列键列均分数据。

使用 create table 语句的 partition by hash 子句，创建散列分区表：

create table f_sales(

sales_id number primary key,

sales_amt number)

partition by hash(sales_id)

partitions 2 store in (p11_tbsp,p12_tbsp) ;

drop table f_sales purge;

命名表空间的散列分区表：

create table f_sales(

sales_id number primary key,

sales_amt number)

partition by hash(sales_id)

(partition p1 /*tablespace p11_tbsp*/,

partition p2 /*tablespace p12_tbsp*/);

散列分区可以提升性能。拥有相同散列键值的行会存储到相同的分区。这意味着插入操作的效率特别高，因为散列算法可以确保能够通过一致的方式为分区分配数据。

而且，如果你经常选择某个特定的键值，Oracle就不得不仅通过检索一个分区查询数据。然而，如果你搜索多个范围的值，Oracle可能需要搜索所有分区，才能确定需要检索的行。因此，在散列分区表中执行范围搜索操作的效率会很低。

--组合使用多种分区方法

Oracle 允许使用多种策略（组合分区）划分分区。例如，你有一个表并想要根据数值范围为其划分分区。但是你还想根据地区列表为每个分区划分子分区。

drop table f_sales purge;

create table f_sales(

sales_amt number,

state_code varchar2(3),

d_date_id number)

partition by range (d_date_id)

subpartition by list(state_code)

(partition p2011 values less than (20120101)(

subpartition p1_north values ('ID','OR'),

subpartition p1_sorth values ('AZ','NM')),

partition p2012 values less than (20130101)(

subpartition p2_north values ('ID','OR'),

subpartition p2_sorth values ('AZ','NM')));

查看分区表类型信息：

select table_name,partitioning_type,subpartitioning_type from user_part_tables where table_name='F_SALES';

查看分区和子分区的信息：

select table_name,partition_name,subpartition_name from user_tab_subpartitions where table_name='F_SALES' order by table_name,partition_name;

--根据需求创建分区

根据日期添加年间隔分区

create table f_sales (

sales_amt number,

d_date_dtt date)

partition by range(d_date_dtt)

interval(numtoyminterval(1,'YEAR'))

store in (p11_tbsp,p12_tbsp,p12_tbsp)

(partition p1 values less than (to_date('01-01-2013','dd-mm-yyyy'))

tablespace p12_tbsp);

第一个分区是在表空间P1_TBSP中创建的，当Oracle增加分区时，他会向STORE IN 子句中定义的表空间分配新建分区。

使用 INTERVAL (NUMTOYMINTERVAL(1，’YEAR’))子句设置的间隔是一年。如果插入表中的某个记录的D_DATE_DTT列值大于或等于01-01-2013，Oracle就会自动在表的

顶端增加新建分区。

--检查分区细节：

select table_name,partition_name,partition_position,interval,tablespace_name,high_value from user_tab_partitions where table_name='F_SALES'

order by table_name,partition_position;

--插入超出最高分区值的数据：

insert into f_sales values (1,sysdate+10000);

如果出现高值，Oracle自动创建了一个分区。如果不喜欢Oracle自动生成得分区名称，可以重命名它：

alter table f_sales rename partition sys_p1321 to p2;

当插入位于两个分区之间范围之间得值时，会在新建一个分区：

insert into f_sales values (2,sysdate+2000);

--根据日期增加星期间隔分区

还可以设置Oracle根据其他时间增加分区，如一个星期：

create table f_sales (

sales_amt number,

d_date_dtt date

)

partition by range (d_date_dtt)

interval (numtodsinterval(7,'day'))

store in (p11_tbsp,p12_tbsp,p13_tbsp)

(partition p1 values less than (to_date('01-01-2013','dd-mm-yyyy'))

tablespace p1_tbsp)

当在未来几个星期插入数据时，新的星期间隔分区就会自动创建，例如：

insert into f_sales values (1,sysdate+7);

insert into f_sales values (2,sysdate+14);

--根据日期增加日间隔分区

create table f_sales (

sales_amt number,

d_date_dtt number

)

partition by range (d_date_dtt)

interval (1)

(partition p1 values less than (20180320));

insert into f_sales values (1,20180321);

insert into f_sales values (2,20180322);

--管理分区

1

2

--移动分区

创建一个列表分区：

create table f_sales

(sales_amt number,

d_date_dtt number,

state_code varchar2(20))

partition by list (state_code)

(partition reg_west values ('AZ','CA','CO','MT','OR','ID','UT'),

partition reg_mid values('IA','KS','MI','MN','MO','NE','OH'),

partition reg_rest values (default));

在分区表上创建局部分区索引：

create index f_sales_index1 on f_sales (state_code) local;

创建全局非分区索引:

create index f_sales_index2 on f_sales (sales_amt,sales_id) global;

创建全局分区索引列：

create index f_fales_index3 on f_sales (sales_amt)

global partition by range(sales_amt)

(partition pg1 values less than (25),

partition pg2 values less than (50),

partition pa3 values less than (maxvalue));

插入测试数据：

insert into f_sales values (26,1,'AZ');

insert into f_sales values (51,2,'IA');

如果要将某个分区移动到特定得表空间，可以使用 ALTER TABLE ... MOVE PARTITION 语句重定位分区：

alter table f_sales move partition reg_west tablespace p1_tbsp;

将分区移动到其他表空间是非常简单得操作。然而，在执行该操作时，应确保检查与表有关得所有索引得状态:

select b.table_name,a.index_name,a.partition_name,a.status,b.locality

from user_ind_partitions a, user_part_indexes b where a.index_name=b.index_name and table_name='F_SALES';

必须重建不可用索引。与手动重建索引不同，在移动分区时可以直接使用 update indexes 子句，自动重建与分区有关的索引：

alter table f_sales move partition reg_west tablespace p1_tbsp update indexes;

/*如果先使用了move 没有加 update indexes 子句，会造成F_FALES_INDEX3分区索引失效。必须手动重建。如果直接在MOVE的时候加上UPDATE INDEXES 则不会失效*/

手动重建索引：

alter index f_fales_index3 rebuild partition pg1;

alter index f_fales_index3 rebuild partition pg2;

alter index f_fales_index3 rebuild partition pa3;

--oracle 12C 开始，可以使用ONLINE子句设置所有索引在移动分区时更新；

alter table f_sales move partition reg_west online tablespace p1_tbsp;

--自动移动更新的行

默认情况下，Oralce不允许更改分区键，将分区中的行移动到其他分区。例如，下面的语句更改了分区键列(D_DATE_ID)的值，以使该分区中的行移动到其他分区中：

update f_sales set d_date_id = 20120301 where d_date_id = 20130301;

1

系统会显示下面的错误：

ORA-14402: updating partition key column would cause a partition change;

ORA-14402: 更新分区关键字列将导致分区的更改;

在这种情况下，使用 ALTER TABLE 语句的 ENABLE ROW MOVEMENT 子句，可以更改分区键，从而更改分区中所含的数据。

alter table f_sales enable row movement ;

这样就可以更新分区键，将分区中的行移动到其他分区，查询USER_TABLES 视图的ROW_MOVEMENT列，验证行移动功能：

select table_name,row_movement from user_tables where table_name='F_SALES';

禁用行移动功能：

alter table f_sales disable row movement;

--为现存的表创建分区

转换方法

create table as select * from

1.如果要在活动的产品数据库中创建这个表，应该规划一些停运时间，确保在迁移数据的过程中不出现活动事务。

2.使用 CREATE TABLE AS SELECT * FROM ORD_TABLE 语句，通过旧表创建新的分区表。

3.删除或重命名旧表。

4.将信标重命名为旧表的原名称。

创建一个未分区表：

create table f_saels (sales_mnt number,d_date_id number);

从旧表获取数据的新表：

create table f_sales_new

partition by range (d_date_id)

(partition p1 values less than (20130101),

partition p2 values less than (20140101),

partition pmax values less than (maxvalue))

nologging

parallel 4

as select * from f_saels;

这样就可以删除或重命名旧的非分区表，然后将新的分区表重命名未旧表的名称。

drop table f_saels purge;

rename f_sales_new to f_saels;

最后，微信表创建必须的约束，权限，索引和统计数据。

–增加分区

有时候在建表伊始很难预测需要创建多少个分区。典型的例子就是创建不带MAXVALUE分区的范围分区。你可能会因为分区表创建足够使用两年的分区，然后就忘掉了

这张表。在将来的某一天，应用的用户会报告系统显示了下列错误提示：

ORA-14400:inserted partition key dose not map to any partition ;

ORA-14400:插入的分区挂关键字未映射到任何分区;

–可以使用分区间隔功能，在插入数据超出范围上界时，使Oracle自动创建范围分区。

1.范围

在处理范围分区表时，如果没有定义MAXVALUE分区，可以使用 ALTER TABLE … ADD PARTITION 语句，在表的上界增加分区。如果你不能确定当前的上界值，可以

查询数据字典:

select table_name,partition_name,high_value from user_tab_partitions where table_name=UPPER('&TABLE_NAME') order by table_name,partition_name;

--在表分区的上界添加一个分区：

alter table f_sales add partition p_2014 values less than (20150101) tablespace p14_tbsp;

--从Oracle Database 12c 开始，同时可以向表中添加多个分区，例如：

alter table f_sales add partition p_2015 values less than (20160101) tablespace p15_tbsp,

partition p_2016 values less than (20170101) tablespace p16_tbsp;

–如果现存的分区表设置了MAXVALUE分区，就无法向该表中添加分区。在这种情况下，必须拆分现存的分区。

2.列表

在处理列表分区表时，只有在没有定义 DEFAULT 分区的情况下，才能增加新分区。

向列表分分区表添加一个分区：

alter table f_sales add partition reg_east values ('GA');

1

–Oracle Database 12c 开始，可以同时添加多个分区：

alter table f_sales add partition reg_mid_east values ('TN'),

partition reg_north values ('NY');

3.散列

处理散列分区表时，使用ADD PARTITION 子句可以增加分区：

alter table f_sales add partition p5 update indexes;

1

为散列分区表增加了分区后，就应该检查索引，确保它们仍旧拥有VALID状态：

select b.table_name,a.index_name,a.partition_name,a.status,b.locality from user_ind_partitions a ,

user_part_indexes b where a.index_name=b.index_name and table_name=upper('&part_table');

检查全局非分区索引的状态：

select index_name,status from user_indexes where table_name=upper('&TABLE_NAME');

1

–通过现存的表交换分区

交换分区是以透明方式向大型分区表加载数据的常用技巧。使用独立表与现存的分区交换数据，从而在不影响表中其他分区的可用性和操作性能的情况下，添加

装满数据的新分区。

创建一个范围分区表：

create table f_sales

(sales_amt number,d_date_id number)

partition by range (d_date_id)

(partition p1 values less than (20120101),

partition p2 values less than (20130101),

partition p3 values less than (20140101));

--在D_DATE_ID列上创建一个本地位图索引

create bitmap index d_date_id_fk1 on f_sales(d_date_id) local;

--向表中添加一个存储新数据的分区

alter table f_sales add partition p4 values less than (20150101);

--创建一个中转表，将符合新建分区范围条件的数据插入这个中转表

create table workpart( sales_amt number,d_date_id number);

--插入测试数据

insert into workpart values (100,20140201);

insert into workpart values (120,20140520);

使用与 f_sales 表上位图索引匹配的结构，在workpart表上创建物位图索引：

create bitmap index d_date_id_fk2 on workpart(d_date_id);

交换workpart表与P_2014分区的数据：

alter table f_sales exchange partition p4

with table workpart including indexes without validation;

–验证结果

select * from f_sales partition(p4);

--验证索引是否依旧有效

select index_name,partition_name,status from user_ind_partitions;

--查看局部索引段

select segment_name,segment_type,partition_name from user_segments where segment_name in ('F_SALES','D_DATE_ID_FK1');

–重命名分区

使用 ALTER TABLE 语句重命名分区：

alter table f_sales rename partition p4 to p_2014;

使用 alter index 重命名索引分区：

alter index d_date_id_fk1 rename partition p2 to p_2012;

–验证重命名的分区相关信息：

select table_name,partition_name,tablespace_name from user_tab_partitions;

–验证分区索引信息：

select index_name,partition_name,status,high_value,tablespace_name from user_ind_partitions;

–拆分范围分区

使用 ALTER TABLE ... SPLIT PARTITION 拆分现存的范围分区：

alter table f_sales split partition p2 at (20120601)

into (partition p2a,partition p2) update indexes;

–验证分区索引的状态：

select index_name,partition_name,status from user_ind_partitions;

--拆分列表分区

alter table f_sales split partition reg_mid values ('IA','KS','MI','MN') into

(partition reg_mid_a,partition reg_mid_b) update indexes;

--合并分区

alter table f_sales merge partitions REG_MID_A,REG_MID_B into partition reg_mid update indexes;

--如果想快速合并分区，可以不加 update indexes 参数，合并完索引后 alter index rebuild partition 重建索引;

–删除分区

查看表下的分区

select segment_name,segment_type,partition_name from user_segments where segment_name=upper('&TABLE_NAME');

使用 ALTER TABLE … DROP PARTITION 语句从表中删除分区。

alter table f_sales drop partition reg_rest;

--在删除分区时，还需要重建所有全局索引。

alter table f_sales drop partition reg_rest update global indexes;

--如果要删除子分区，可以使用 drop subpartition

alter table f_sales drop subpartition p1_sorth update global indexes;

--通过 user_tab_partitions 验证分区是否呗删除

select table_name,partition_name from user_tab_partitions;

--Oracle 不允许删除组合分区表中的所有子分区。每个分区中至少要保留一个子分区

--统计分区数据 Command Windows 执行，Sql Windows 无法执行

exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname => 'F_SALES',partname => 'P2011');

--从Oracle Database 11g 开始,在生成全局统计信息时，可以使用Oracle仅扫描新增的分区，DBMS_STATS 软件包可以启动该功能：

exec dbms_stats.set_table_prefs (user,'F_SALES','INCREMENTAL','TRUE');

--执行成功后查看表的首选信息：

select dbms_stats.get_prefs('INCREMENTAL',tabname=>'F_SALES') from dual;

--删除分区中的行

首先确定要删除数据的分区名称：

select segment_name,segment_type,partition_name from user_segments where partition_name is not null ;

使用 ALTER TABLE ... TRUNCATE PARTITION 语句可以删除分区中的所有记录。

alter table f_sales truncate partition Y12;

--截断表会使全局索引失效，可以在使用truncate命令时，更新全局索引：

alter table f_sales truncate partition Y12 update global indexes ;

--如果需要回滚事务，可以使用DELETE语句

delete from f_sales partition(Y12);

–操作分区表中的数据

从特定的分区中选择行：

select * from f_sales partition(y12);

如果要从两个或者多个分区中选择数据，可以使用UNION子句：

select * from f_sales partition(P2011)

union

select * from f_sales partition(P2012);

--查看键值属于哪个分区，并显示改分区的数据

select * from f_sales partition for (20120101);

--还可以更新和删除分区行：

update f_sales partition(p2012) set sales_amt=200;

update f_sales partition for (20120101) set sales_amt=200;

–索引分区

使用表分区策略创建索引分区

--测试本地分区索引的概念

--创建测试表，含有两个分区

create table f_sales (sales_id number, sales_amt number,d_date_id number)1c

partition by range(d_date_id)(

partition p2012 values less than (20130101),

partition p2013 values less than (20140101)

);

--插入测试数据

insert into f_sales values (1,20,20120322);

insert into f_sales values (2,33,20120504);

insert into f_sales values (3,72,20120101);

insert into f_sales values (4,12,20130322);

insert into f_sales values (5,98,20130702);

commit;

--创建本地索引

create index f_sales_fk1 on f_sales (d_date_id) local;

--查看分区索引的信息

select index_name,table_name,partitioning_type from user_part_indexes where table_name='F_SALES';

--查看本地分区索引的信息

select index_name,partition_name,tablespace_name from user_ind_partitions where index_name='F_SALES_FK1';

--在创建索引时，将本地索引存储到不同的表空间当中

create index f_sales_fk1 on f_sales (d_date_id) local

(partition p2012 tablespace users,

partition p2013 tablespace p1_tbsp);

如果在创建本地分区索引时设置分区信息，那么必须使用索引分区编号与表分区编号匹配。

Oracle 会自动同步本地索引分区和表分区。无法但如增加或删除本地索引分区。在增加或删除表分区时，Oracle会自动执行处理本地索引的操作。Oracle 会自动

管理本地索引分区。这与为本地索引分配表空间的方式无关。

本地索引通常在数据库和DSS环境中使用。如果需要频繁使用分区的列（或列组合）执行查询命令，就适合使用本地索引。该方法可以使Oracle使用适当的索引和

表分区，快速检索数据。

本地索引分两种类型：局部前缀和局部非前缀。局部前缀索引是指索引中的最左侧列与表分区键匹配。

--前缀索引与分区键匹配

create index f_sales_fk1 on f_sales (d_date_id) local;

--非前缀索引与分区键不匹配

create index f_sales_idx1 on f_sales (sales_id) local;

通过user_part_indexes视图的ALIGNMENT列可以验证索引的类型：

select index_name,table_name,alignment,locality from user_part_indexes;

–使用与表不同的分区策略创建索引分区

使用与表不同的分区策略创建的分区索引称为全局索引。全局索引中的条目可以指向基础表中的任何分区，可以在任何类型的分区表上创建全局索引。

可以使用范围分区策略也可以使用散列分区策略创建全局索引。使用关键字GLOBAL可以使用与表分区策略不同的方式创建索引。在创建范围分区全局索引时，必须设置

MAXVALUE 分区。

create index f_sales_gidx1 on f_sales(sales_amt)

global partition by range (sales_amt)

(partition pg1 values less than (25),

partition pg2 values less than (50),

partition pg3 values less than (maxvalue));

--散列全局索引

create index f_sales_gidx2 on f_sales (sales_id)

global partition by hash(sales_id) partition 4;

通常，维护全局索引比维护本地索引更困难，建议避免使用全局索引，尽可能使用本地索引。

Oracle 不会自动维护全局索引（像维护局部索引那样）。在使用全局索引时，必须手动添加和删除索引分区。许多对基础分区表执行的维护操作都要重建全局索引分区

–分区修剪

分区修建可以大幅度提高查询分区表的操作性能。如果SQL查询命令专门根据分区键访问表，那么Oracle仅会搜索含有查询操作所需要的分区（而不会访问含有这些数据

的分区，即修剪掉这些分区）。

--创建示例分区表：

create table f_sales(

sales_id number,

sales_amt number,

d_date_id number

)tablespace p1_tbsp

partition by range(d_date_id)(

partition y11 values less than (20120101)

tablespace p11_tbsp,

partition y12 values less than (20130101)

tablespace p12_tbsp,

partition y13 values less than (20140101)

tablespace p13_tbsp );

在分区键列上创建本地索引

create index f_sales_fk1 on f_sales(d_date_id) local;

--插入测试数据

insert into f_sales values (1,100,20110202);

insert into f_sales values (2,200,20120202);

insert into f_sales values (3,300,20130202);

--查看分区修剪得过程

set autotrace trace explain

select sales_amt from f_sales where d_date_id='20120202';

xecution Plan

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

Plan hash value: 2403904524

xecution Plan

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

Plan hash value: 2403904524

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

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

| 0 | SELECT STATEMENT | | 26 | | |

| 1 | PARTITION RANGE SINGLE | | 26 | 2 | 2 |

| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | F_SALES | 26 | 2 | 2 |

|* 3 | INDEX RANGE SCAN | F_SALES_FK1 | | 2 | 2 |

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

在这个输出结果中，Pstart标识初始时访问得分区是2号分区。Pstop表名最后访问得分区也是2号分区。

如果查询操作中没有分区键，那么表中得所有分区都会被访问；

select * from f_sales;

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

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

| 0 | SELECT STATEMENT | | 117 | | |

| 1 | PARTITION RANGE ALL| | 117 | 1 | 3 |

| 2 | TABLE ACCESS FULL | F_SALES | 117 | 1 | 3 |

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

在这个结果中，Oracle初始时访问得分区是1号分区，而最后一个访问得分区是3号分区。这意味着不修建分区得情况下，查询操作会访问1号到3号得所有分区。

ITPub博客

分区表管理

最新文章