MSSQL网页编程之切磋实体化视图的革新机制

使为了数据安全，我们搭建了主从。但实时主从备份只能防止硬件问题，比如主库的硬盘损坏。但对于误操作，则无能为力。比如在主库误删一张表，或者一个update语句没有指定where条件，导致全表被更新。视图|革新
切磋实体化视图的革新机制



Author：Kamus

Mail：kamus@itpub.net

Date：2004年10月



明天给客户搭建汗青查询服务器，用oracle8i的snapshot完成，也就是9i的实体化视图。

随手做了一下trace，看了一下革新时分oracle背景是怎样事情的。





后期筹办，利用DBMS_SUPPORT包，这个包默许是没有安装的，必要运转上面的命令来手动安装。

SQL>conn/assysdba

SQL>@?dbmsadmindbmssupp.sql

SQL>GRANTexecuteONdbms_supportTOkamus;

SQL>CREATEPUBLICSYNONYMdbms_supportFORdbms_support;



先看疾速革新，测试表是T1，创立了快照日记，用于革新的视图是MV_T1，用户是KAMUS

实行trace：

SQL>connkamus

SQL>execdbms_support.start_trace(waits=>TRUE,binds=>TRUE);

SQL>execdbms_mview.refresh(list=>MV_T1);

SQL>execdbms_support.stop_trace;



然后tkprof天生trace了局的报表，上面只是节选了个中一部分。

实行一次dbms_mview.refresh，Oracle背景会实行13个userSQL和92个internalSQL，其实是一个冗杂的事情。



1。入手下手革新

BEGINdbms_mview.refresh(list=>MV_T1);END;



2。反省SNAP$表，确认以后用户是不是有必要革新的视图



3。在DBMS_LOCK_ALLOCATED数据字典中更新纪录，设置过时工夫

UPDATEDBMS_LOCK_ALLOCATEDSETEXPIRATION=SYSDATE+(:B1/86400)

WHERE

ROWID=:B2



4。反省大概会用到的dblink和一些初级行列的数据字典

5。反省表的相干束缚



6。反省几个初始化参数的值，包含_enable_refresh_schedule，_delay_index_maintain，compatible



7。将MLOG中一切没有标记为准时革新的纪录更新为立即革新

update"KAMUS"."MLOG$_T1"setsnaptime$$=:1

where

snaptime$$>to_date(2100-01-01:00:00:00,YYYY-MM-DD:HH24:MI:SS)



8。从头编译MV_T1实体化视图

ALTERSUMMARY"KAMUS"."MV_T1"COMPILE

这一步对照可疑，SQL中是没有altersummary找个命令的，假如是编译的话，那末便可能锁定工具，就有大概发生librarycachelock



9。反省要实行的SQL文，这一步对照风趣

SELECToperation#,cols,sql_txt,tabnum,fcmaskvec,ejmaskvec,setnum

FROM

sys.snap_refop$WHERE((operation#>=0ANDoperation#<=6)ORoperation#

IN(10,12,13))ANDsowner=:1ANDvname=:2ANDinstsite=:3ORDER

BYtabnum,setnum,operation#

关于一个MV革新将会利用到SQL全体存在这张表中。

假如是fast革新，那末关于查询mlog表，查询基表的数据，insert、update、delete实体化视图都分离有一句SQL。

个中operation#字段值的罕见寄义以下：

0：查询mlog表

1：关于实体化视图的delete操纵

2：查询基表的最新数据

3：关于实体化视图的update操纵

4：关于实体化视图的insert操纵

假如是complete革新，那末只要一笔记录，是基于基表的全表insert操纵，operation#是7。

此处的实行企图显现是关于snap_refop$的全表扫描，假如体系中存在大批必要refresh的实体化视图，无疑是影响功能的。



10。获得必要更新的纪录主键

SELECTDISTINCTLOG$."IDATE"

FROM

(SELECTMLOG$."IDATE"FROM"KAMUS"."MLOG$_T1"MLOG$WHERE"SNAPTIME$$">:1

AND("DMLTYPE$$"!=I))LOG$WHERE(LOG$."IDATE")NOTIN(SELECT

MAS_TAB$."IDATE"FROM"T1""MAS_TAB$"WHERELOG$."IDATE"=MAS_TAB$."IDATE")

注重到这里利用了distinct，也就是我们能够推测，假如在一次革新之前关于统一笔记录作了屡次的修正，那末革新操纵只必要作一次，就是取得基表中该笔记录的最新值就能够了。

IDATE字段是我的测试表中的主键。

"DMLTYPE$$"!=I暗示不是insert的操纵。

此处的实行企图显现关于mlog表举行了一次全表扫描，假如有大批的更新操纵，无疑又是影响功能的一步。



11。获得基表中以后必要革新的纪录一切字段的最新值

SELECTCURRENT$."IDATE",CURRENT$."C"

FROM

(SELECT"T1"."IDATE""IDATE","T1"."C""C"FROM"T1""T1")CURRENT$,(SELECT

DISTINCTMLOG$."IDATE"FROM"KAMUS"."MLOG$_T1"MLOG$WHERE"SNAPTIME$$">

:1AND("DMLTYPE$$"!=D))LOG$WHERECURRENT$."IDATE"=LOG$."IDATE"

这一步操纵暗示，mlog中只存储修正操纵触及到的纪录主键，别的的字段值仍旧会到基表中往作查询。

此处的实行企图显现关于mlog表再一次作了全表扫描。



12。用获得的最新值更新实体化视图

UPDATE"KAMUS"."MV_T1"SET"IDATE"=:1,"C"=:2

WHERE

"IDATE"=:1

这一步仍旧对照奇异，由于我的测试中只作了insert，并没有update的操纵，难道oracle在革新时，其实不管是不是存在update的操纵，城市例行作一次视图数据的更新？不外此处更新会利用实体化视图中的主键，速率应当时很快的。



13。将获得的最新值拔出到实体化视图中

INSERTINTO"KAMUS"."MV_T1"("IDATE","C")

VALUES

(:1,:2)

这步才到了真正要完成的目标上，呵呵。



14。更新一批数据字典，标明革新已完成



15。删除mlog表中已革新过的纪录

deletefrom"KAMUS"."MLOG$_T1"

where

snaptime$$<=:1

这一步操纵是对照泯灭资本的，利用delete，发生redo和undo，没法下降mlog表的HWM标记，同时又是一次全表扫描，假如常常有大批更新产生，最好能准时作mlog表的truncate举措，不然这一步操纵大概会愈来愈慢。



至此，一次实体化视图的疾速革新算是完整停止了。



我们持续看一下完整革新的背景机制。

1-8步跟疾速革新基础不异。

9。反省要实行的SQL文

SELECToperation#,cols,sql_txt

FROM

sys.snap_refop$WHEREoperation#=7ANDsowner=:1ANDvname=:2AND

instsite=:3

能够看到间接往找operation#=7的SQL了，这就是完整革新必要利用的SQL。



10。反省完整革新触及到的束缚，索引，触发器



11。删除实体化视图中的原无数据

deletefrom"KAMUS"."MV_T"

这一步让我很惊奇，记得文档中说应当是truncate操纵，可是此处显现的是delete？如许的话，完整革新的价值其实是很年夜了。



12。拔出基表中一切数据

INSERT/*+BYPASS_RECURSIVE_CHECK*/INTO"KAMUS"."MV_T"("X")SELECT"T"."X"

FROM"T""T"

这里利用到的提醒/*+BYPASS_RECURSIVE_CHECK*/，是否是在实践使用中能够进步INSERT的效力呢？



13。更新一批数据字典，标明革新已完成



14。假如在基表上创立了革新日记mlog表，那末Oracle不论此次革新是否是完整革新，城市往作一次删除mlog表中数据的操纵。假如没有创立过mlog，那末这一步将被省略。以是假如决意利用完整革新，那末就不要在基表上创立革新日记了，免得无谓的资本损耗。


但我们知道，若使用statement，并没有上述需要的数据。试想binlog中记录了一句updatetsetf1=3whereid=3。怎么恢复呢？

在select语句中可以使用groupby子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息，另外，可以使用having子句限制返回的结果集。

需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。

groupby子句可以将查询结果分组，并返回行的汇总信息Oracle按照groupby子句中指定的表达式的值分组查询结果。

可能有的朋友会抱怨集成的orderby，其实如果使用ranking函数,Orderby是少不了的。如果担心Orderby会影响效率，可以为orderby的字段建立聚集索引，查询计划会忽略orderby操作（因为本来就是排序的嘛）。

作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！

仓酷云

在select语句中可以使用groupby子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息，另外，可以使用having子句限制返回的结果集。

如果是将来做数据库的开发设计，就应该详细学习T-SQL的各种细节，包括T-SQL的程序设计、存储过程、触发器以及具体使用某个开发语言来访问数据库。

很多书籍啊，不过个人认为看书太慢，还不如自己学。多做实际的东西，就会遇到很多问题，网上搜下解决问题。不断重复这个过程，在配合sql的F1功能。