AnySQL.net

    下面的测试是基于Kernel Internal Trigger的实体化视图日志(Primary Key+Sequence):

SQL> INSERT INTO T_OBJECTS SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 10001;

9424 rows created.

Elapsed: 00:00:01.38

Statistics
----------------------------------------------------------
      11249  recursive calls
      41052  db block gets
      16575  consistent gets
         15  physical reads
    8266284  redo size
        674  bytes sent via SQL*Net to client
        606  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
       9424  rows processed

    下面的测试是基于Kernel Internal Trigger的实体化视图日志(Primary Key):

SQL> INSERT INTO T_OBJECTS SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 10001;

9425 rows created.

Elapsed: 00:00:00.86

Statistics
----------------------------------------------------------
      10855  recursive calls
      40835  db block gets
      16383  consistent gets
          9  physical reads
    8181892  redo size
        679  bytes sent via SQL*Net to client
        606  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
       9425  rows processed

    下面的测试是基于自定义Trigger的实体化视图日志(Primary Key):

SQL> INSERT INTO T_OBJECTS SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 10001;

9424 rows created.

Elapsed: 00:00:00.90

Statistics
----------------------------------------------------------
      10372  recursive calls
      40274  db block gets
      16144  consistent gets
          5  physical reads
    7997420  redo size
        678  bytes sent via SQL*Net to client
        606  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       9424  rows processed

    下面这一段是用于生成实体第视图日志的自定义Trigger:

CREATE OR REPLACE TRIGGER TLOG$_T_OBJECTS
BEFORE INSERT OR UPDATE OR DELETE ON T_OBJECTS
FOR EACH ROW
BEGIN
  IF INSERTING THEN
     INSERT INTO MLOG_T_OBJECTS (OBJECT_ID, DMLTYPE$$) VALUES (:NEW.OBJECT_ID,'I');
  ELSIF UPDATING THEN
     IF (:OLD.OBJECT_ID = :NEW.OBJECT_ID) THEN
       INSERT INTO MLOG_T_OBJECTS (OBJECT_ID, DMLTYPE$$) VALUES (:NEW.OBJECT_ID,'U');
     ELSE
       INSERT INTO MLOG_T_OBJECTS (OBJECT_ID, DMLTYPE$$) VALUES (:NEW.OBJECT_ID,'D');
       INSERT INTO MLOG_T_OBJECTS (OBJECT_ID, DMLTYPE$$) VALUES (:NEW.OBJECT_ID,'I');
     END IF;
  ELSE
     INSERT INTO MLOG_T_OBJECTS (OBJECT_ID, DMLTYPE$$) VALUES (:NEW.OBJECT_ID,'D');
  END IF;
END;
/

    在10g的版本下, 居然看不出什么差别, 看来PL/SQL的执行效率的确是很高了.