Oracle 高效的分区消除

1.高效的分区消除

成都创新互联-专业网站定制、快速模板网站建设、高性价比甘德网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式甘德网站制作公司更省心,省钱,快速模板网站建设找我们，业务覆盖甘德地区。费用合理售后完善，十年实体公司更值得信赖。

分区表存在的最大意义在于，可以有效地做到分区消除

分区表其实是将一个大对象分成了多个小对象

同样的语句查询有相同记录的表，分区表的查询代价仅为111，逻辑读仅为566，而普通表的代价却为1298，逻辑读为5990，性能方面有着天壤之别。差别如此之大，应该是和分区表查询只遍历了13个分区中的一个有关。在分区表查询的执行计划中p_start和p_stop都标记上9，表示只遍历了第9个分区。这样避开了对其余12个分区的查询，就是分区消除

测试针对普通表的增加了 area_code 条件的相同 SQL 语句：

组合分区导致范围定位得更小了，只产生了60个逻辑读，比之前的566个逻辑读还要少。至于普通表的查询，因为始终是全表扫描，所以逻辑读依然和之前差不多，达到5931个。

组合分区虽然只有60个逻辑读，比之前范围分区的566个要少得多，但是代价却差不多，甚至还略大于之前的，因为分区数过多，调用有开销。在试验中，表的总记录数不过10万，全表扫描开销都不是太大，这时Oracle内部调用的开销影响就相对较大。如果表是一张超级大表，比如有上亿条记录，那这些开销相比而言就可以忽略不计了，

分区表应用在大表上更合适，至少要大于100万条记录的表方可考虑。

如何管理oracle 的分区表和索引（续

一、创建分区表

1、范围分区

根据数据表字段值的范围进行分区

举个例子，根据学生的不同分数对分数表进行分区，创建一个分区表如下：create table range_fraction(

id number(8),

name varchar2(20),

fraction number(3),

grade number(2)

)

partition by range(fraction)

(

partition fraction_60 values less than(60), --不及格partition fraction_80 values less than(85), --及格partition fraction_100 values less than(maxvalue) --优秀)创建完分区表后向表中添加一些数据：declarename varchar2(10);

fraction number(5);

grade number(5);

i number(8):=1;

begin

for i in 1..100000 LOOP

SELECT CHR (ROUND (DBMS_RANDOM.VALUE (97, 122))) INTO NAME FROM DUAL;SELECT ABS(MOD(DBMS_RANDOM.RANDOM,101)) into fraction FROM DUAL;SELECT ABS(MOD(DBMS_RANDOM.RANDOM,10))+1 into grade FROM DUAL;insert into range_fraction values(seq_range_fraction.nextval ,name,fraction,grade);END LOOP;end;

查询分区表：--分别查询所有的，不及格的，中等的，优秀的成绩select * from range_fraction;select * from range_fraction partition(fraction_60) ;select * from range_fraction partition(fraction_80) ;select * from range_fraction partition(fraction_100) ;当我们的查询语句不指定分区的时候，如果分区字段出现在where条件之后，Oracle会自动根据字段值的范围扫描响应的分区：

select * from range_fraction where fraction30; 这句SQL执行的时候只会扫描不及格的分区select * from range_fraction where fraction80; 这句SQL执行的时候会扫描不及格和中等两个分区2、散列分区在范围分区中，分区字段的连续值通常出现在一个分区内，而在散列分区中，连续的字段值不一定存储在相同的分区中。散列分区把记录分布在比范围分区更多的分区上，这减少了I/O争用的可能性。

为了创建一个散列分区，应该用partition by hash语句代替partition by range子句，如下所示：

第一种为各个分区指定不同的表空间，表空间数量不用等于分区数量，当表空间数量大于分区数量的时候会循环写入各个表空间：

create table range_fraction1

(

id number(8),

name varchar2(20),

fraction number(3),

grade number(2)

)

partition by hash(fraction)

partitions 8

store in (users,tbs_haicheng)

第二种为每个分区指定一个分区名称并为其指定表空间：create table range_fraction1(

id number(8),

name varchar2(20),

fraction number(3),

grade number(1)

)

partition by hash(fraction)

(

partition p1 tablespace tbs_haicheng ,

partition p2 tablespace users

);

3、列表分区

还可以使用列表分区代替范围分区和散列分区。在列表分区中，告诉Oracle所有可能的值，并指定应当插入相应行的分区。

我们将1、2、3、4班级的数据放在一个分区，将6、7、8的数据放在一个分区，将其他的再放在一个分区，建表如下：

create table range_fraction1

(

id number(8),

name varchar2(20),

fraction number(3),

grade number(2)

)

partition by list(grade)

(

partition p1 values(1,2,3,4) tablespace tbs_haicheng ,partition p2 values(5,6,7,8) tablespace users,?

1

partition p3 values(default)

4、组合分区(创建子分区)

即分区的分区。例如可以先进行范围分区，再对各个范围分区创建列表分区。

对于非常大的表来说，这种组合分区是一种把数据分成可管理和可调整的组成部分的有效方法。

举个例子：按照分数范围分区后再将ID散列分区：

create table range_fraction1

(

id number(8),

name varchar2(20),

fraction number(3),

grade number(1)

)

partition by range(fraction)

subpartition by hash(id)

subpartitions 4

(

partition fraction_60 values less than(60), --不及格partition fraction_80 values less than(85), --及格partition fraction_100 values less than(maxvalue) --优秀)二、索引分区

在分区表上可以建立三种类型的索引：1和普通表一样的全局索引；2.全局分区索引；3.本地分区索引1.建立普通的索引create index index_fraction on range_fraction(fraction);2.建立本地分区索引（就是一个索引分区只能对应一个表分区）?

create index local_index_fraction on range_fraction(fraction) local;3.建立全局分区索引（属于散列索引分区，就是一个索引分区可能指向多个表分区）?

create index global_index_fraction on range_fraction(fraction)GLOBAL partition by range(fraction)(

part_01 values less than(1000),

part_02 values less than(MAXVALUE)

);

三、管理分区表

1、增加分区

对于范围分区来说，添加一个分区，必须该分区划定的界限高于原来的最大界限，也就是说只能往上加，不能往下加。那么对于用maxvalue关键字创建的范围分区就不能增加分区了举例：

create table range_fraction

(

id number(8),

name varchar2(20),

fraction number(3),

grade number(2)

)

partition by range(fraction)

(

partition fraction_60 values less than(40), --不及格partition fraction_80 values less than(60), --及格partition fraction_100 values less than(80) --优秀)对于该分区我们增加一个分区：

ALTER TABLE range_fraction ADD PARTITION fraction_100 VALUES LESS THAN (100);为列表分区添加一个分区：

create table range_fraction

(

id number(8),

name varchar2(20),

fraction number(3),

grade number(2)

)

partition by list(grade)

(

partition p1 values(1,2,3) tablespace tbs_haicheng ,partition p2 values(4,5,6) tablespace users);ALTER TABLE range_fraction ADD partition p3 VALUES (7,8);我们再为p3分区新增两个表分区值：

ALTER TABLE range_fraction MODIFY PARTITION p3 ADD VALUES(9,10);然后再将p3分区的表分区值中的10删掉：

ALTER TABLE range_fraction MODIFY PARTITION p3 DROP VALUES(10);为哈希分区添加一个子分区：

ALTER TABLE TABLENAME ADD PARTITION PARTNAME;添加一个子分区的格式：

ALTER TABLE TABLENAME MODIFY PARTITION PARTNAME ADD SUBPARTITION SUBPARTNAME;2、删除分区删除分区比较简单，格式如下：

ALTER TABLE ... DROP PARTITION part_name;3、分区合并合并父分区格式：ALTER TABLE TABLENAME MERGE PARTITIONS p1-1, p1-2 INTO PARTITION p1 UPDATE INDEXES;如果省略了UPDATE INDEXES 的话需要为受影响的分区重建索引合并子分区的格式：

ALTER TABLE TABLENAME

MERGE SUBPARTITIONS part_1_sub_2, part_1_sub_3 INTO SUBPARTITION part_1_sub_2 UPDATE INDEXES;4、转换分区可以将分区表转换成非分区表，或者几种不同分区表之间的转换。如下：

CREATE TABLE hash_part02 AS SELECT * FROMhash_example WHERE 1=2;ALTER TABLE hash_example EXCHANGE PARTITIONpart02 WITH TABLE hash_part02;这时，分区表hash_example中的part02分区的资料将被转移到hash_part02这个非分区表中。

Oracle创建分区表操作

1、创建语句

create table p(id number)

partition by range(id)

(partition p1 values less than(100) tablespace t1,

partition p2 values less than(200) tablespace t2,

partition p3 values less than(300) tablespace t3);

2、添加分区

alter table p add partition p4 values less than (400) tablespace t4;

3、清除分区数据

alter table p trunc partition p1;

4、删除分区

alter table p drop partition p1;

ORACLE分区表用什么方法删除一个分区的所有数据

这个要看你的业务要不要备份保留这个分区的数据，如果不需要保留，可以直接truncate/drop分区的，如果要保留，可以用交换分区方法；

具体步骤：

1.不保留，直接删除：

alter

table

table_name

drop/truncate

partition

partition_name;

具体用drop还是truncate，得你自己衡量，drop的话原来的分区和数据直接就没有了，truncate的话，只是数据没有了，分区还在。

2.保留数据作为备份，交换分区

2.1创建一个表结构一样的表：

create

table

teable_bak

as

select

*

from

table_name

where

1=2;

--只要表结构，不要数据；

2.2分区交换

alter

table

table_name

exchange

partition

partition_name

with

table

table_bak;

这样执行后，分区partition_name的数据就会交换到table_bak中，table_bak的数据（刚刚建的空表）就会到分区里面去。

oracle表分区和索引分区

一 分区表技术概述

二 分区索引技术概述

⑴ 本地前缀分区索引

适用场景：

如果历史数据整理非常频繁、而且不能承受全局分区索引重建的长时间带来的索引不可用

同时、日常交易性能尚能接受、则建议设计为本地非前缀分区索引

注意：本地分区索引不能保证唯一性（除非分区键是约束的一部分）

缺点：

主要体现在数据的高可用性方面

当DROP分区后、全局分区索引则全部INVALID、除非REBULID

但数据量越大、重建索引的时间越长

一般来讲，如果需要将数据按照某个值逻辑聚集，多采用范围分区。如基于时间数据的按“年”、“月”等分区就是很典型的例子。在许多情况下，范围分区都能利用到分区消除特性（ = = = between…and 等筛选条件下）。

如果在表里无法找到一个合适的属性来按这个属性完成范围分区，但你又想享受分区带来的性能与可用性的提升，则可以考虑使用散列分区。（适合使用 = IN 等筛选条件）

如果数据中有一列或有一组离散值，且按这一列进行分区很有意义，则这样的数据就很适合采用列表分区。

如果某些数据逻辑上可以进行范围分区，但是得到的范围分区还是太大，不能有效管理，则可以考虑使用组合分区（范围分区+hash 或范围分区+列表分区）。

create table products_table

(

id number(2),

name varchar2(50),

sale_date date

)

partition by range(sale_date)

interval (numtoyminterval(1,'month'))

(

partition p_month_1 values less than (to_date('2016-01-01','yyyy-mm-dd'))

)

如图，取 products_table 中的 sale_date 列作为分区键创建按月自增分区；

所有销售时间在 ‘2016-01-01’之前的记录都会被放入 p_month_1 分区；

销售时间在‘2016-01-01’之后的记录在插入时Oracle会自动创建记录所属月的分区；

比如当有销售时间分别为 2016年1月20日 与 2016年2月20日 的两条记录插入时，Oracle会分别创建一个上限值为 ‘2016-01-31’的分区和一个上限值为‘2016-02-29’的分区来存储这两条记录

ORACLE表分区

一.表分区策略

1.识别大表

采用ANALYZE TABLE语句进行分析，然后查询数据字典获得相应的数据量。

2.大表如何分区

可根据月份，季度以及年份等进行分区；

3.分区的表空间规划

要对每个表空间的大小进行估计

二.创建表分区

a.创建范围分区的关键字是'RANGE'

1.范围分区

create table ware_retail_part --创建一个描述商品零售的数据表

(

id integer primary key,--销售编号

retail_date date,--销售日期

ware_name varchar2(50)--商品名称

)

partition by range(retail_date)

(

--2011年第一个季度为part_01分区

partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace TEMP01,

--2011年第二个季度为part_02分区

partition par_02 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace TEMP01,

--2011年第三个季度为part_03分区

partition par_03 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace TEMP01,

--2011年第四个季度为part_04分区

partition par_04 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace TEMP01

);

2.创建散列分区

3.组合分区：

4.interval 分区

三.创建索引分区

索引分区分为本地索引分区和全局索引分区，全局索引不反应基础表的结构，要分区只能进行范围分区。

创建索引分区要参照表分区

四.分区技术简介

优点：

1.减少维护工作量

2.增强数据的可用性

3.均衡I/O,提升性能

4.提高查询速度

5.分区对用户保持透明，用户感觉不到分区的存在。

五,管理表分区

1.添加表分区

ALTER TABLE...ALTER PARATITION

2.合并表分区

3.删除分区

ALTER TABLE...DROP PARTITION

删除分区时，里面的数据也会被删除。

-创建表和分区

create table sales--创建一个销售记录表

(

id number primary key,--记录编号

goodsname varchar2(10),--商品名

saledate date--销售日期

)

partition by range(saledate)--按照日期分区

(

--第一季度数据

partition part_sea1 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace tbsp_1,

--第二季度数据

partition part_sea2 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace tbsp_2,

--第三季度数据

partition part_sea3 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace tbsp_1,

--第四季度数据

partition part_sea4 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace tbsp_2

);

--创建局部索引

create index index_3_4 on sales(saledate)

local(

partition part_seal tablespace tbsp_1,

partition part_sea2 tablespace tbsp_2,

partition part_sea3 tablespace tbsp_1,

partition part_sea4 tablespace tbsp_2

);

--并入分区

alter table sales merge partitions part_sea3,part_sea4 into partition part_sea4;

--重建局部索引

alter table sales modify partition part_sea4 rebuild unusable local indexes;

六.管理索引分区

删除索引：DROP PARTITION

重建分区：REBUILT PARTITION

更名索引分区：RENAME PARTITION

分割索引分区：SPLIT PARTITION

新闻名称：python函数打出图形,python字符画图形
路径分享：http://chengdu.cdxwcx.cn/article/hcsjde.html