Flash Back Version Query

 

Flash Back Version Query

参考文档：

http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm

http://www.cnblogs.com/coohoo/archive/2011/01/16/1936888.html

Use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A row version is created whenever a COMMIT statement is executed.

<!--[if !supportLists]-->1.    <!--[endif]-->使用flashback query的前提

<!--[if !supportLists]-->Ø  <!--[endif]-->undo_management = auto，设置自动undo管理（AUM），该参数默认设置为：auto；

查询语句：show parameter undo;

<!--[if !supportLists]-->Ø  <!--[endif]-->设置足够大的undo_retention，undo_retention表示的是操作在undo表空间中保存的时间

<!--[if !supportLists]-->Ø  <!--[endif]-->设置足够大的undo表空间大小

 

<!--[if !supportLists]-->2.       <!--[endif]-->flashback query 的使用方法

<!--[if !supportLists]-->Ø  <!--[endif]-->先用 select current_scn from V$database; 语句查出当前的scn值，记录为scn1

<!--[if !supportLists]-->Ø  <!--[endif]-->执行一段insert，update，delete操作

<!--[if !supportLists]-->Ø  <!--[endif]-->用 select current_scn from V$database; 语句查出操作执行完毕后的scn值，记录为scn2

<!--[if !supportLists]-->Ø  <!--[endif]-->用语句

select   versions_startscn,versions_endscn,versions_operation,<查询表的字段名>  from <表名> versions between scn <操作开始前的scn> and <操作结束的scn>  where  versions_operation is not null

查出整个操作

示例数据：其中第一列为操作类型，后面为每列的数据

 

<!--[if !supportLists]-->3.       <!--[endif]-->性能比较

 

为了做性能的比较，我在本地用client setting表做实验，表格中是trigger方式的DDL语句

drop TABLE CLIENT_SETTING_INCREMENT;

CREATE TABLE CLIENT_SETTING_INCREMENT

  (

     "ACCOUNT_ID"    NUMBER(19,0),

    "CONTENT_ID"    NUMBER(*,0),

    "CONTEXT_ID"    NUMBER(*,0),

    "PROPERTY_ID"   NUMBER(*,0),

    "SETTING_TYPE"  NUMBER(*,0),

    "SETTING_VALUE" VARCHAR2(4000 BYTE),

    CHANGE_FLAGS        VARCHAR2(8 BYTE),   --U for update, I for insert, D for delete.

    CONSTRAINT "CLIENT_SET_INC_CONST_PKEY" PRIMARY KEY ("ACCOUNT_ID", "CONTENT_ID", "CONTEXT_ID", "PROPERTY_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDEX_LARGE" ENABLE,

    CONSTRAINT "CLIENT_SET_INC_AID_CONST_FKEY" FOREIGN KEY ("ACCOUNT_ID") REFERENCES "Z7ADMIN"."ACCOUNT" ("ID") ON

                DELETE CASCADE ENABLE

  );

 

--create a row level trigger to track the changes on CLIENT_SETTING

drop trigger CLIENT_SETTING_INC_TRIGGER;

CREATE OR REPLACE TRIGGER CLIENT_SETTING_INC_TRIGGER

AFTER  UPDATE OR DELETE OR INSERT ON CLIENT_SETTING

FOR EACH ROW

 

DECLARE

                change_flags     VARCHAR2(8); -- U, D, I

  num              NUMBER(2,0);

 

BEGIN

  num := 0;

 

  CASE

                WHEN UPDATING THEN

                    select count(1) into num from CLIENT_SETTING_INCREMENT where ACCOUNT_ID=:NEW.ACCOUNT_ID and CONTENT_ID=:NEW.CONTENT_ID and CONTEXT_ID=:NEW.CONTEXT_ID and PROPERTY_ID = :NEW.PROPERTY_ID;

 

                    IF num > 0 THEN

                                    update CLIENT_SETTING_INCREMENT set SETTING_TYPE=:NEW.SETTING_TYPE ,SETTING_VALUE=:NEW.SETTING_VALUE ,  CHANGE_FLAGS='U'

                                        where ACCOUNT_ID=:NEW.ACCOUNT_ID and CONTENT_ID=:NEW.CONTENT_ID and CONTEXT_ID=:NEW.CONTEXT_ID and PROPERTY_ID = :NEW.PROPERTY_ID;

        ELSE

            Insert into CLIENT_SETTING_INCREMENT (ACCOUNT_ID,CONTENT_ID,CONTEXT_ID,PROPERTY_ID,SETTING_TYPE,SETTING_VALUE,CHANGE_FLAGS )

                values (:NEW.ACCOUNT_ID, :NEW.CONTENT_ID, :NEW.CONTEXT_ID, :NEW.PROPERTY_ID, :NEW.SETTING_TYPE, :NEW.SETTING_VALUE, 'U');

        END IF;

 

                WHEN DELETING THEN

                    select count(1) into num from CLIENT_SETTING_INCREMENT where ACCOUNT_ID=:OLD.ACCOUNT_ID and CONTENT_ID=:OLD.CONTENT_ID and CONTEXT_ID=:OLD.CONTEXT_ID and PROPERTY_ID = :OLD.PROPERTY_ID;

dbms_output.put_line(num);

        IF num > 0 THEN

            update CLIENT_SETTING_INCREMENT set CHANGE_FLAGS='D'  where ACCOUNT_ID=:OLD.ACCOUNT_ID and CONTENT_ID=:OLD.CONTENT_ID and CONTEXT_ID=:OLD.CONTEXT_ID and PROPERTY_ID = :OLD.PROPERTY_ID;

        ELSE

         Insert into CLIENT_SETTING_INCREMENT (ACCOUNT_ID,CONTENT_ID,CONTEXT_ID,PROPERTY_ID,SETTING_TYPE,SETTING_VALUE,CHANGE_FLAGS )

                values (:OLD.ACCOUNT_ID,  :OLD.CONTENT_ID, :OLD.CONTEXT_ID, :OLD.PROPERTY_ID, :OLD.SETTING_TYPE, :OLD.SETTING_VALUE, 'D');

 

        END IF;

 

 

                WHEN INSERTING THEN

                                  Insert into CLIENT_SETTING_INCREMENT (ACCOUNT_ID,CONTENT_ID,CONTEXT_ID,PROPERTY_ID,SETTING_TYPE,SETTING_VALUE,CHANGE_FLAGS )

                values (:NEW.ACCOUNT_ID, :NEW.CONTENT_ID, :NEW.CONTEXT_ID, :NEW.PROPERTY_ID, :NEW.SETTING_TYPE, :NEW.SETTING_VALUE, 'I');

  END CASE;

 

END;

 

<!--[if !supportLists]-->Ø  <!--[endif]-->CLIENT_SETTING_INCREMENT中1000000数据，10000操作

使用trigger 花费35621ms。

               不使用trigger 花费31746ms。

<!--[if !supportLists]-->Ø  <!--[endif]-->CLIENT_SETTING_INCREMENT中2000000数据，100000操作

使用trigger 花费372923。

               不使用trigger 花费294024。

<!--[if !supportLists]-->4.       <!--[endif]-->项目的应用

考虑到sinc map项目中trigger可能会影响用户操作的响应时间，我们是否能够用flashback来减少对用户操作的影响，用户操作的记录可以用以下语句得到，对每一行都只会得到最新的操作记录。

select * from (select versions_operation,account_id,CONTENT_ID,CONTEXT_ID,PROPERTY_ID,SETTING_TYPE,SETTING_VALUE, RANK() OVER (PARTITION BY account_id,CONTENT_ID,CONTEXT_ID,PROPERTY_ID order by versions_startscn desc,versions_endscn desc) rank1   from z7admin.client_setting versions between scn 1005591 and 10405591

where  versions_operation is not null) where  rank1  = 1