详解Oracle数据货场中三种优化：分区、维度和物化视图

转 xiewmang 新浪博客 

本文主要介绍了Oracle数据货场中的三种优化：对分区的优化、维度优化和物化视图的优化，并给出了详细的优化代码，希望对您有所帮助。

 

我们在做数据库的项目时，对数据货场的优化，大约的原理只有两个：一是数据分块储藏，便于数据的转储和管教；二是其中处理，长进数据供给的速度。本文主要介绍了Oracle数据货场中的三种优化：对分区的优化、维度优化和物化视图的优化，基上面两个大约的原理，借助于数据货场的观念，罗列数据库的优化措施：
1．分区
在数据货场中，事实表，索引表，维度表分处于三个不同的表空间其中（在安排的时候，良好是安排到不同的磁盘上）。这么子做的起因即便便于并发垄断，其实数据货场和等闲的数据库之间未曾严厉的界限，重要还是安排上，当然oracle本身会为数据货场中的“事实表”生成“知识库”等垄断，保证更快的数据供给效率，其实能够借助于job和表面过程来调动存储过程告终。基于这个思路，本项目数据库中的索引将和事实表离别维护，当然这称不上分区（）。恳挚的分区是指下面的内容。
分区即便partition/subpartition，对于事实表本身来说，以月为单位作partition挂载到不同表空间上。翔实的示例如下：

  1. create table T_LOGNODE_RECORD  
  2. (  
  3. ……  
  4. CALLIN_HH NUMBER(2) not null,  
  5. CALLIN_DD NUMBER(2) not null,  
  6. CALLIN_MONTH NUMBER(2) not null,  
  7. CALLIN_YEAR NUMBER(4) not null,  
  8. MONTH_MOD NUMBER(1) not null  
  9. )  
  10. PARTITION BY LIST (MONTH_MOD)  
  11. (  
  12. PARTITION P0 VALUES (0) TABLESPACE TS0,  
  13. PARTITION P1 VALUES (1) TABLESPACE TS1,  
  14. PARTITION P2 VALUES (DEFAULT) TABLESPACE TS2  
  15. ); 

上述的代码中以MONTH_MOD字段作为分区规范，将T_LOGNODE_RECORD分到三个不同的表空间（TS0，TS1，TS2）中，这里有一个技巧，MONTH_MOD = 月份mod3,这么子能够无须要人工的维护哪个月分入哪个分区。当然容易的以月作为分区尔后，对于我们目前的数据库来说还是远远不够的，因为即便是一个月的数据依旧是很宏伟的，那么必需我们做SUBPARTITION，做法的示例为：

  1. create table T_LOGNODE_RECORD  
  2. (  
  3. ……  
  4. CALLIN_DD NUMBER(2) not null,  
  5. MONTH_MOD NUMBER(1) not null  
  6. )  
  7. PARTITION BY RANGE(MONTH_MOD)  
  8. SUBPARTITION BY LIST(CALLIN_DD)  
  9. SUBPARTITION TEMPLATE  
  10. (  
  11. SUBPARTITION SUBP1 values (1),  
  12. SUBPARTITION SUBP2 values (2),  
  13. SUBPARTITION SUBP3 values (3)  
  14. )  
  15. (  
  16. PARTITION P0 VALUES less than (1),  
  17. PARTITION P1 VALUES less than (2),  
  18. PARTITION P2 VALUES less than (3)  
  19. ); 

分区措施有三种：RANGE（范围），LIST（列表分区），HASH（哈希分区），必需解释的HASH分区，是oracle调用内置的hash函数来告终对数据的平衡散布，保证分区内部的数据量雷同，因而只必需制订分区的数量即可。
对于复合分区来说。不是所有分区款式都能够嵌套的，oracle只扶持范围列表分区，范围哈希分区，尤其的范围分区能够扶持多个字段range by(field1,field2)。对于本项目管用到的分区措施，只有示例中给出的两种，至于分区的利益，请察看google,^_^。给出垄断的sql示例如下：

  1. select * from T_LOGNODE_RECORD partition(P0)  
  2. update T_LOGNODE_RECORD partition(P0) t set…… 

insert语句未曾波及。当然上述的垄断能够像等闲的Sql语句一样，不指定分区也能够举行，但在查询的时候请尽量能确定分区和子分区subpartition，这么子会让查询的速度长进百倍。能够剔除指定的分区drop可能truncate, EXPORT和IMPORT分区中数据，然而当举行分区的剔除垄断的时候，会使大局的索引（index）实效，必需重兴发生。
2．维度
Dimension这个算是oracle的进阶功能了,在PLSQL Developer未曾措施找到这个对象（然而能够厉行语句来生成该对象，然而无法举行后期的维护），只能在Toad中有它的身影。它是阐明数据维度字段之间的大小联系。标兵的慷慨是下面的年月日了。

  1. create table DIM_TIME  
  2. (  
  3. D_YEAR VARCHAR2(4) not null,  
  4. D_QUATER VARCHAR2(2) not null,  
  5. D_MONTH VARCHAR2(2) not null,  
  6. D_DAY DATE not null  
  7. )  
  8. CREATE DIMENSION DIM_TIME  
  9. LEVEL YEAR IS (DIM_TIME.D_YEAR)  
  10. LEVEL QUATER IS (DIM_TIME.D_QUATER)  
  11. LEVEL MONTH IS (DIM_TIME.D_MONTH)  
  12. LEVEL DAY IS (DIM_TIME.D_DAY)  
  13. HIERARCHY Y_Q_M_D  
  14. (  
  15. DAY CHILD OF  
  16. MONTH CHILD OF  
  17. QUATER CHILD OF YEAR  
  18. )  
  19. HIERARCHY Y_M_D  
  20. (  
  21. DAY CHILD OF  
  22. MONTH CHILD OF YEAR  
  23. ) 

发生了两条继承联系Y_Q_M_D和Y_M_D。当DIM_TIME本身的普查，以及与其他表作联合查询然后汇总时，月的汇总数据积极以日汇总的数据为基础，这么子大大长进数据汇总的速度。
可感受一张表发生多个维度表，一个维度中能够有多个继承联系。维度极大程度上长进了数据汇总的速度，使我们这个项目标数据普查分析的利器。
3.  物化视图（快照）
物化视图MATERIALIZED VIEW。划算开心的是PLSQL Developer和Toad上同时具有了这个对象的存在（Toad上可能将这个称为快照snapshots），然而令人烦闷的事情发生了，在PLSQL Developer上写成的MATERIALIZED VIEW在Toad中无法分辨，看来还要两个都用能力够。
物化视图，望文生义具有视图的属性，它是发生在切实表的基础上，将切实表中的数据笼统揭示，甚至创立的措施都几乎一样。然而它成为物化，就意味着它是实体对象，从某种含义上将，它更像一个其中表，能够像表格一样直接查询其中的数据（甚至就在table列表中能够看到它），然而它不但仅是其中表，因为它能够波及原始表的数据，并且oracle能够积极将针对原始表的查询汇总语句直接重定向到物化视图上，来晋级查询的速度。

  1. CREATE MATERIALIZED VIEW MV_LOG_RECORD  
  2. REFRESH FORCE  
  3. ON DEMAND  
  4. ENABLE QUERY REWRITE  
  5. AS  
  6. SELECT tcmy8.com. NODE_ID, Count(*) as Call_Times  
  7. from T_LOGNODE_RECORD t  
  8. GROUP BY t. T_LOGNODE_RECORD 

上面的SQL语句就曾经在T_LOGNODE_RECORD表上发生一个物化视图。REFRESH的参数 FORCE表示迫使刷新，还能够抉择的参数有fast和complete两种，fast是最快的刷新措施，表示增量刷新（要做到增量刷新，定然在物化视图中包括单一性的标明的字符，如主键和rowid等，当然即便包括了也无须定能够做到迅速刷新，因为我们项目中未曾波及到要刷新的物化视图，在此就不多言了）；complete表示全面的数据刷新，重新生成物化视图；force是抉择性的刷新，依据切实的情形来抉择是force还是complete。
ON DEMAND表示刷新引发的条件。上面的物化视图只有在用户发动查询哀求时，才会去刷新数据，即刻原始表中的数据取到物化视图的表中。还有一种措施即便 ON COMMIT，当用户垄断原始数据时，就刷新对应的物化视图，这种措施合乎对于及时性要求高的项目。万一必需做定时的刷新，即将用到下面的措施lr.lrheicha.com：
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48。
物化视图是能够横亘多个表的，也能够直接的查询物化试图，例如你能够：
Select * from MV_LOG_RECORD.

归纳
在上述的三种措施中，属于数据库功能优化的重要手段，当然还有许多能够做的细节的优化，例如尽量不要在你要查询的字段上面批准为空，尽量将联合查询的联系扼制为number型等等。
对了，万一做了维度表，做了物化试图，你去查询原始表的时候速度定然会添置么？答案是“不”，你要做的一个重要的事情，即便告诉oracle，它能够积极的优化查询，语句如下：

  1. ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;  
  2. ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED; 

关于Oracle数据货场中三种优化的知识就介绍到这里了，希望能够带给您一些收获，谢谢！

posted @ 2016-08-12 17:30  fkissx  阅读(701)  评论(0)  编辑  收藏  举报