柯南君 :Oracle 分区技术 之 如何支撑大数据操作?
时间:2022-03-14 02:27
前段时间,看了罗女士( 资深技术顾问 - Oracle 中国 顾问咨询部)关于《大批量数据处理技术的演讲》视频,感觉受益良多,结合多年的知识积累,柯南君给大家分享一下:
交流内容:
一、Oracle的分区技术
(一)分区技术内容
1. 什么是分区?
分区就是将一个非常大的table或者index 按照某一列的值,分解为更小的,易于管理的逻辑片段---分区。将表或者索引分区不会影响SQL语句以及DML(见备注)语句,就和使用非分区表一样,每个分区拥有自己的segment(见备注),因为,DDL(见备注)能够将比较大的任务分解为更小的颗粒。分区表只有定义信息,只有每个存放数据的分区才有各自的segment。就好象拥有多个相同列名,列类型的一个大的视图。
4)分区表索引的分类:
① Local Prefixed index 局部前缀索引(local prefixed index):在这些索引中,分区键在索引定义的前几列上。例如,一个表在名为LOAD_DATE 的列上进行区间分区, 该表上的局部前缀索引就是采用LOAD_DATE作为其索引列列表中的第一列。
② Local Non-prefiexed index 局部非前缀索引(local nonprefixed index):这些索引不以分区键作为其列列表的前几列。索引可能包含分区键列,也可能不包含。
注意: 这两类索引(Local Prefixed index 与 Local Non-prefiexed index )都可以进行分区消除,前提是查询的条件中包含索引分区键,它们都支持惟一性(只要局部非前缀索引包含分区键列)。
局部索引与表的分区数一致,如果新增一个分区,新增加的分区局部索引也会自动创建。全局索引则不行(即需要重建全局索引)。
【案例分析】: --下面通过实验来说明索引的分区消除
<strong> </strong>--创建一个分区表 CREATE TABLE partitioned_table ( a int, b int, data char(20)) PARTITION BY RANGE (a) ( PARTITION part_1 VALUES LESS THAN(2) tablespace gcomm, --以a字段进行分区,小于等于2的存在分区1,小于等于3的存在分区2 PARTITION part_2 VALUES LESS THAN(3) tablespace gmapdata ) --创建一个本地前缀索引 create index local_prefixed on partitioned_table (a,b) local; --创建一个本地非前缀索引 create index local_nonprefixed on partitioned_table (b) local; --向表中插入数据 insert into partitioned_table select mod(rownum-1,2)+1, rownum, 'x' from all_objects; --分析表 begin dbms_stats.gather_table_stats ( user,'PARTITIONED_TABLE',cascade=>TRUE );end; --以sys用户登录后 将gmapdata表空间置为离线 alter tablespace gmapdata offline;--分区2的数据包括其索引等都被置为离线状态
<p> select * from partitioned_table where a = 1 and b = 1; A B DATA ---- ------ -------------------- 1 1 x --将之前的plan_table表的数据清除 delete from plan_table; --生成统计信息 explain plan for select * from partitioned_table where a = 1 and b = 1; --查看统计信息结果 select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1622054381 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Pstart| Pstop -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 28 | | 1 | PARTITION RANGE SINGLE | | 1 | 28 | 1 1 | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | PARTITIONED_TABLE | 1 | 28 | 1 1 |* 3 | INDEX RANGE SCAN | LOCAL_preFIXED | 1 | | 1 1 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"=1 AND "B"=1) 注:可以进行查询,可以通过本地前缀索引将分区2消除 由于分区2采用的表空间为gmapdata,而这个表空间在上述已将其离线,通过本地前缀索引在查询的时候将分区2消除,只在第一个分区进行查询,因此该查询能够成功查询。 <u> 再看下面的一个查询: </u> select * from partitioned_table where b = 1; <span style="color: red;">提示:ora-00376:此时无法读取文件11 </span> <span style="color: red;"> ora-01110:数据文件11:‘D:\ORACE|PRODUCT\10.2.0\ORADATA\FGISDBGMAPDATA.DBF’</span> delete from plan_table; explain plan for select * from partitioned_table where b = 1; select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 440752652 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Pstart| Pstop -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 28 | | 1 | PARTITION RANGE ALL | | 1 | 28 | 1 2 | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | PARTITIONED_TABLE | 1 | 28 | 1 2 |* 3 | INDEX RANGE SCAN | LOCAL_NONpreFIXED | 1 | | 1 2 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("B"=1) 注:当查询谓词只有b,即采用非前缀索引,而且查询的条件中又不含分区键a,因此在查询时无法将分区2消除,导致在查询分区2时提示数据文件不在。 <span style="font-family: Helvetica, Tahoma, Arial, sans-serif;">将本地前缀索引删掉后: </span><span style="font-family: Helvetica, Tahoma, Arial, sans-serif;"> </span></p><p>drop index local_prefixed; select * from partitioned_table where a = 1 and b = 1; A B DATA ---- ------ -------------------- 1 1 x delete from plan_table; explain plan for select * from partitioned_table where a = 1 and b = 1; select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 904532382 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Pstart| Pstop -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 28 | | 1 | PARTITION RANGE SINGLE | | 1 | 28 | 1 1 |* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 28 | 1 1 |* 3 | INDEX RANGE SCAN | LOCAL_NONpreFIXED | 1 | | 1 1 -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("A"=1) 3 - access("B"=1) 注:本地前缀索引删除后,采用本地非前缀索引进行如上查询也可以成功。可见本地非前缀索引也可以进行消除分区,主要取决于谓词。该表利用a字段 进行分区,因此主要谓词中有a字段的查询,就可以成功查询。<strong> </strong></p><p></p><p><strong><span style="font-size:10px;">-局部索引和惟一约束介绍:</span></strong> </p><p>CREATE TABLE partitioned ( load_date date, id int, constraint partitioned_pk primary key(id) ) PARTITION BY RANGE (load_date) ( PARTITION part_1 VALUES LESS THAN( to_date('01/01/2000','dd/mm/yyyy') ) , PARTITION part_2 VALUES LESS THAN( to_date('01/01/2001','dd/mm/yyyy') )) select segment_name, partition_name, segment_type from user_segments where segment_name like 'PARTITIONED%'; SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE -------------------- --------------------- ------------------ PARTITIONED PART_1 TABLE PARTITION PARTITIONED PART_2 TABLE PARTITION PARTITIONED_PK INDEX <span style="color: red;">注:PARTITIONED_PK 索引没有进行分区,因此可以保证唯一性</span><br style="color: red;" /> </p><p><strong>删掉表重新创建并建立一个本地索引后在创建一个唯一索引</strong> </p><p>drop table partitioned CREATE TABLE partitioned ( timestamp date, id int) PARTITION BY RANGE (timestamp) (PARTITION part_1 VALUES LESS THAN( to_date('01-1-2000','dd-mm-yyyy') ) , PARTITION part_2 VALUES LESS THAN( to_date('01-1-2001','dd-mm-yyyy') ) ) create index partitioned_idx on partitioned(id) local;--创建一个本地索引 select segment_name, partition_name, segment_type from user_segments where segment_name like 'PARTITIONED%'; SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE ------------------------ ------------------------------ ------------------ PARTITIONED PART_1 TABLE PARTITION PARTITIONED PART_2 TABLE PARTITION PARTITIONED_IDX PART_1 INDEX PARTITION PARTITIONED_IDX PART_2 INDEX PARTITION alter table partitioned add constraint partitioned_pk primary key(id);--在id上增加一个全局索引 提示:此列列表已有索引 <strong>注:分区索引无法 保证唯一性,因为如果要保证分区索引的唯一性,即分区1有id=1,那么分区2中就不能有id=1,而我们如果做了这个限制,往不同 分区进行插数据就会降低分区表的灵活性。</strong> </p>
③ Global Prefixed index
全局索引使用一种有别于底层表的机制进行分区。表可以按一个TIMESTAMP 列划分为10 个分区,而这个表上的一个全局索引可以按REGION 列划分 为5 个分区。与局部索引不同,全局索引只有一类,这就是前缀全局索引(prefixed global index)。如果全局索引的索引键未从该索引的分区键开始 ,这是不允许的。这说明,不论用什么属性对索引分区,这些属性都必须是索引键的前几列。 drop table partitioned CREATE TABLE partitioned ( timestamp date, id int ) PARTITION BY RANGE (timestamp) ( PARTITION part_1 VALUES LESS THAN ( to_date('01-1-2000','dd-mm-yyyy') ) , PARTITION part_2 VALUES LESS THAN ( to_date('01-1-2001','dd-mm-yyyy') ) ) create index partitioned_index on partitioned(id) GLOBAL partition by range(id) (partition part_1 values less than(1000), partition part_2 values less than (MAXVALUE) --全局索引必须指定最大值,否则会提示:ORA-14021:必须指定所有列的MAXVALUE ) 注:全局索引有一个要求,即最高分区(最后一个分区)必须有一个值为MAXVALUE 的分区上界。这可以确保底层表中的所有行都能放在这个索引中。 全局索引可以创建一个唯一索引: alter table partitioned add constraint partitioned_pk primary key(id);--创建唯一索引成功 注:该唯一索引是通过创建的全局索引来保证唯一,可以通过删除其索引的错误来说明 drop index partitioned_index;提示:ora-02429:无法删除用于强制唯一/主键的索引。 --以下例子说明了全局索引必须是前缀的 create index partitioned_index2 on partitioned(timestamp,id) GLOBAL partition by range(id)--以id为分区键 那么其索引就必须将id置到最前面 (partition part_1 values less than(1000), partition part_2 values less than (MAXVALUE) ) 提示:ORA-14038:全局分区索引必须是前缀 数据仓库与全局索引: 数据仓库一般是通过数据的滑入划出进行管理(即旧数据划出,新数据滑入)。一个分区表中,如果进行分区的增删改操作会造成全局索引失效。因此, 采用何种索引要根据系统的要求。 实验1:分区的滑入滑出导致全局索引失效,局部索引仍有效 --创建分区表 CREATE TABLE partitioned ( timestamp date, id int ) PARTITION BY RANGE (timestamp) ( PARTITION fy_2004 VALUES LESS THAN ( to_date('01-1-2005','dd-mm-yyyy') ) , PARTITION fy_2005 VALUES LESS THAN ( to_date('01-1-2006','dd-mm-yyyy') ) ) --对两个分区都插入数据 insert into partitioned partition(fy_2004) select to_date('31-12-2004', 'dd-mm-yyyy') - mod(rownum,360), object_id from all_objects; insert into partitioned partition(fy_2005) select to_date('31-12-2005', 'dd-mm-yyyy') - mod(rownum,360), object_id from all_objects; --分别创建一个本地索引和全局索引 create index partitioned_idx_local on partitioned(id) LOCAL; create index partitioned_idx_global on partitioned(timestamp) GLOBAL; --创建一个新表(用于装载分区划出的数据) create table fy_2004 (timestamp date, id int); create table fy_2005 (timestamp date, id int); create index fy_2004_idx on fy_2004(id);create index fy_2005_idx on fy_2005(id); --创建一个新表并插入数据 create table fy_2006 ( timestamp date, id int ); insert into fy_2006 select to_date('31-12-2006', 'dd-mm-yyyy') - mod(rownum,360), object_id from all_objects; create index fy_2006_idx on fy_2006(id) nologging; create table fy_2007 ( timestamp date, id int ); insert into fy_2007 select to_date('31-12-2007', 'dd-mm-yyyy') - mod(rownum,360), object_id from all_objects; create index fy_2007_idx on fy_2007(id) nologging; --将分区fy_2004的数据放到表fy_2004中,并删除该分区 alter table partitioned exchange partition fy_2004 with table fy_2004 including indexes without validation; alter table partitioned drop partition fy_2004; --创建一个新分区,用于装载新数据 alter table partitioned add partition fy_2006 values less than ( to_date('01-12-2007','dd-mm-yyyy') ); alter table partitioned exchange partition fy_2006 with table fy_2006 including indexes without validation; --最后查看索引的情况 select index_name, status from user_indexes where table_name='PARTITIONED'; 1 PARTITIONED_IDX_LOCAL N/A 2 PARTITIONED_IDX_GLOBAL UNUSABLE --发现全局索引已失效 如果强制用其全局索引,会导致无法查询 set autotrace on explain select /*+ index( partitioned PARTITIONED_IDX_GLOBAL ) */ count(*) from partitioned where timestamp between sysdate - 50 and sysdate; ORA-01502: 索引 'LTTFM.PARTITIONED_IDX_GLOBAL' 或这类索引的分区处于不可用状态 --直接进行查询,则会进行全表扫描 select count(*) from partitioned where timestamp between sysdate-50 and sysdate; 实验2:全局索引失效的解决办法: 1)可对索引进行重建, 2)直接在进行分区删改的时候 加上更新索引的字句(UPDATE GLOBAL INDEXES): --删除、交换分区时可加上索引更新的字句,增加一个分区不用进行更新索引,因为新增加的分区空行 alter table partitioned exchange partition fy_2004 with table fy_2004 including indexes without validation UPDATE GLOBAL INDEXES alter table partitioned drop partition fy_2004 UPDATE GLOBAL INDEXES 注:如果在对分区进行操作时加上了 UPDATE GLOBAL INDEXES 更新索引的字句,那么全局索引就不会失效。 实验3:比较索引重建和更新索引所占用的资源情况: begin runStats_pkg.rs_start;end; alter table partitioned exchange partition fy_2004 with table fy_2004 including indexes without validation; alter table partitioned drop partition fy_2004; alter table partitioned add partition fy_2006 values less than (to_date('01-1-2007','dd-mm-yyyy') ); alter table partitioned exchange partition fy_2006 with table fy_2006 including indexes without validation; alter index partitioned_idx_global rebuild; --采用索引重建的方法 begin runStats_pkg.rs_middle;end; alter table partitioned exchange partition fy_2005 with table fy_2005 including indexes without validation update global indexes; alter table partitioned drop partition fy_2005 update global indexes; alter table partitioned add partition fy_2007 values less than ( to_date('01-1-2008','dd-mm-yyyy') ); alter table partitioned exchange partition fy_2007 with table fy_2007 including indexes without validation update global indexes; begin runStats_pkg.rs_stop;end; --采用索引更新的方法 输出的结果: Run1 ran in 936 hsecs Run2 ran in 1101 hsecs run 1 ran in 85.01% of the time 实验结果:其结果表明对全局索引进行更新要花更长时间。但是如果说系统不允许中断的话,那么还是应该采取索引更新的方法。④ Non Partition Index 备注:
Global索引的分区不同与表分区
Local索引的分区与表分区相同
An index is prefixed if it is partitioned on a left prefix of the index columns.
分区表上的非分区索引等同于Global索引
分区索引字典:
DBA_PART_INDEXES 分区索引的概要统计信息,可以得知每个表上有哪些分区索引,分区索引的类新(local/global,)
Dba_ind_partitions每个分区索引的分区级统计信息
Dba_indexesminusdba_part_indexes,可以得到每个表上有哪些非分区索引
5)分区选择的策略
6)分区表设计原则
① 表的大小:当表的大小超过1.5GB-2GB,或对于OLTP系统,表的记录超过1000万,都应考虑对表进行分区。
② 数据访问特性:基于表的大部分查询应用,只访问表中少量的数据。对于这样表进行分区,可充分利用分区排除无关数据查询的特性。
③ 数据维护:按时间段删除成批的数据,例如按月删除历史数据。对于这样的表需要考虑进行分区,以满足维护的需要。
④ 数据备份和恢复: 按时间周期进行表空间的备份时,将分区与表空间建立对应关系。
⑤ 只读数据:如果一个表中大部分数据都是只读数据,通过对表进行分区,可将只读数据存储在只读表空间中,对于数据库的备份是非常有益的。
⑥ 并行数据操作:对于经常执行并行操作(如Parallel Insert,Parallel Update等)的表应考虑进行分区。
⑦ 表的可用性:当对表的部分数据可用性要求很高时,应考虑进行表分区。
7)分区表的管理功能
① 分区的增加(ADD)
② 分区的删除(DROP)
③ 分区的合并(MERGE)
④ 分区的清空(TRUNCATE)
⑤ 分区的交换(EXCHANGE)
⑥ 分区的压缩(COALESE)
⑦ 分区的移动(MOVE)
⑧ 分区的分离(SPLIT)
⑨ 修改分区的Default Attribute
分区的更名(RENAME)
8)分区索引的管理功能
① 分区索引的删除(DROP)
② 分区索引的修改(MODIFY)
③ 分区索引Default Attribute的修改
④ 分区索引的重建(REBUILD)
⑤ 分区索引的更名(RENAME)
⑥ 分区索引的分离(SPLIT)
⑦ 分区索引的Unusable
9)“滚动窗口”操作 - 大量数据高速装载
分区索引字典
DBA_PART_INDEXES 分区索引的概要统计信息,可以得知每个表上有哪些分区索引,分区索引的类新(local/global,)
Dba_ind_partitions每个分区索引的分区级统计信息
Dba_indexesminusdba_part_indexes,可以得到每个表上有哪些非分区索引
2.Oracle的分区的交换功能
1)交换功能分类
① 通过交换数据段,实现分区和非分区表的数据交换。以及子分区和分区表的数据交换第一步:1:00数据的加载
insert into BF_DXX_stage(SJ,TEXT3) values(to_date(‘2004.03.02‘,‘YYYY.MM.DD‘),‘大撒反对撒‘);
第二步:建立context 索引
CREATE INDEX IDX_ BF_DXX _STAGE ON BF_DXX_stage(text3)
INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS
(‘LEXER MYLEXER STORAGE MYSTORE FILTER CTXSYS.NULL_FILTER MEMORY 100M‘) parallel 4;
第三步:partition的交换
alter table BF_DXX exchange partition p2 with table BF_DXX_stage including indexes; 3.迁移表空间(Transportable Tablespace)技术简介 1)
备注:
第一步:exp transport_tablespace=yes
第二步:FTP 数据文件和dmp文件
第三步:imp transport_tablespace=yes
2) 迁移表空间技术的作用
① 业务系统数据向数据仓库系统的迁移
② 对业务系统和数据仓库系统的数据进行定期归档
③ 数据仓库向数据集市的数据迁移
④ 数据对外发布
⑤ 按表空间进行时间点的数据恢复(TSPITR)
3)迁移表空间技术的优点
① 性能大大高于export/import或PL/SQL编写的程序
② 由于Dmp文件只包含表空间的结构信息,因此该技术的真正开销在于数据文件的传输。
对源系统的影响非常小
③ 只需要将被迁移的表空间设置为只读方式
④ 可同时传输索引数据,避免在目的数据库中重建索引
4)分区交换的应用--- ETL
① 在源系统中,将需要抽取的数据以如下语句形式,抽取到建立在单独表空间上的中间表中:
CREATE TABLE ... AS SELECT …
INSERT /*+ APPEND */ AS SELECT …
② 以TTS方式将中间表的表空间传输到数据仓库之中。
exp transportable_tablespace=Yes …
FTP 中间表表空间的数据文件
imp transportable_tablespace=Yes …
③ 在数据仓库中对中间表进行各种数据归并等清洗工作,并建立需要的各种索引。
④ 通过exchange技术,将中间表数据及索引直接交换到分区表中。
⑤ Alter table <分区表> exchange partition <分区名> with table <中间表> including indexes;
5)分区交换的应用---重复记录删除
① 问题描述: 在使用SQL*Loader进行数据加载sor_acct_dcc_saamt_c表时,由于操作失误,重复加载,导致分区ETL_LOAD_DATE_0606出现重复记录,也使得两个唯一 索引:IDX_SAACNAMT_C_1,IDX_SAACNAMT_C_2的ETL_LOAD_DATE_0606分区不可用(UNUSABLE)。
用户在试图重新创建该分区索引时,出现如下错误:
SQL> alter index IDX_SAACNAMT_C_2 rebuild partition ETL_LOAD_DATE_0606;
alter index IDX_SAACNAMT_C_2 rebuild partition ETL_LOAD_DATE_0606
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
② 在试图删除该分区的重复记录时,又出现如下错误:
SQL> delete from sor_acct_dcc_saamt_c partition(ETL_LOAD_DATE_0606)
where rowid not in (select min(rowid) from sor_acct_dcc_saamt_c partition(ETL_LOAD_DATE_0606) group by ETL_LOAD_DATE, CUST_ACCT_NO, SA_CURR_COD, SA_CURR_IDEN);
*
ORA-01502: index ‘GYFX.IDX_SAACNAMT_C_1‘ or partition of such index is in unusable state
③ 简单办法是彻底删除这两个唯一索引,重新创建。
数据量大,时间太长。
影响系统的可用性。
更完备的解决方式
创建一个与sor_acct_dcc_saamt_c结构一样的临时表test。
SQL> create table test as select * from sor_acct_dcc_saamt_c where 1=2;
将sor_acct_dcc_saamt_c表分区ETL_LOAD_DATE_0606数据交换到临时表test。
SQL> alter table sor_acct_dcc_saamt_c exchange partition ETL_LOAD_DATE_0606 with table test;
④ 更完备的解决方式
删除test中的重复记录
delete from test
where rowid not in (select min(rowid) from test group by ETL_LOAD_DATE, CUST_ACCT_NO, SA_CURR_COD, SA_CURR_IDEN);
因为test表没有任何索引,可避免上述ORA-01502错误。
将临时表test数据交换回sor_acct_dcc_saamt_c表分区ETL_LOAD_DATE_0606 。
alter table sor_acct_dcc_saamt_c exchange partition ETL_LOAD_DATE_0606 with table test;
⑤ 更完备的解决方式
重新创建创建该分区索引IDX_SAACNAMT_C_1,IDX_SAACNAMT_C_2
alter index IDX_SAACNAMT_C_1 rebuild partition ETL_LOAD_DATE_0606 tablespace ETL0_R_LOAD_IDX_200606;
alter index IDX_SAACNAMT_C_2 rebuild partition ETL_LOAD_DATE_0606 tablespace ETL0_R_LOAD_IDX_200606;
此时重复记录已经删除,可避免上述ORA-01452错误
3.分区的评估
1)性能方面
相应速度
资源消耗(CPU、内存、I/O)
性能分析工具的使用:Oracle Trace, Autotrace, TKPROF
2) 其它方面
数据迁移能力
数据备份和恢复
数据扩展性(Add, Drop, Exchange, Merge, …)
数据高可用性