根据前面的测试发现，在9.2中，Oracle还会根据物化视图日志中的修改矢量对包含LOB的物化视图进行字段级的刷新。也就是说，如果修改不设计LOB字段，物化视图的刷新也不会涉及LOB字段。

但是上一篇的测试结果发现，从10开始，物化视图的刷新不再根据字段级进行，即使基表没有修改LOB字段，刷新时也会同步LOB字段。

为什么Oracle的版本升级后，原有的功能反而去掉了，会不会是由于当前LOB字段内容很少，导致Oracle将这个字段和数据行存储在一起，从而导致了这个现象。

下面将LOB字段的内容长度扩展到32767，也就是PL/SQL中VARCHAR2所能支持的最大长度，显然这个长度的字段不可能放到任何一个BLOCK中。Oracle支持的最大BLOCK_SIZE就是32K，而LOB的长度已经达到了32K，且BLOCK中还有系统开销。也就是说，现在LOB字段是存储在数据行以外的地方，下面再次进行测试，这次为了能看到绑定变量的值，SQL_TRACE改为10046 EVENT LEVEL 12的方式：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> CREATE TABLE T
  2  (
  3     ID NUMBER,
  4     NAME VARCHAR2(30),
  5     CONTENTS CLOB,
  6     CONSTRAINT PK_T PRIMARY KEY (ID)
  7  );

Table created.

SQL> DECLARE
  2     V_STR VARCHAR2(32767) := RPAD('A', 32767, 'A');
  3  BEGIN
  4     INSERT INTO T VALUES (1, 'TEST', V_STR);
  5     COMMIT;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> CREATE MATERIALIZED VIEW LOG ON T;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST AS
  2  SELECT *
  3  FROM T;

Materialized view created.

SQL> UPDATE T
  2  SET NAME = 'ABC'
  3  WHERE ID = 1;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

Session altered.

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

Session altered.

SQL> SELECT A.VALUE || '/' || B.VALUE || '_ora_' || SPID || '.trc'
  2  FROM V$PARAMETER A, V$PARAMETER B, V$SESSION S, V$PROCESS P
  3  WHERE S.PADDR = P.ADDR
  4  AND A.NAME = 'user_dump_dest'
  5  AND B.NAME = 'instance_name'
  6  AND S.SID IN
  7     (
  8             SELECT SID
  9             FROM V$MYSTAT
 10             WHERE ROWNUM = 1
 11     );

A.VALUE||'/'||B.VALUE||'_ORA_'||SPID||'.TRC'
--------------------------------------------------------------------------------
/opt/ora10g/admin/test08/udump/test08_ora_32075.trc

检查对应的trace文件：

*** ACTION NAME:() 2009-10-28 15:32:54.196
*** MODULE NAME:(SQL*Plus) 2009-10-28 15:32:54.196
*** SERVICE NAME:(SYS$USERS) 2009-10-28 15:32:54.196
*** SESSION ID:(138.4170) 2009-10-28 15:32:54.196
WAIT #10: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1227260912301155
WAIT #10: nam='SQL*Net message from client' ela= 4452093 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1227260916753531
=====================
PARSING IN CURSOR #2 len=40 dep=0 uid=74 ct=47 lid=74 tim=1227260916753706 hv=227083342 ad='1d08910'
BEGIN DBMS_MVIEW.REFRESH('MV_T'); END;
END OF STMT
PARSE #2:c=0,e=74,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1227260916753700
BINDS #2:
=====================
.
.
.
=====================
PARSING IN CURSOR #6 len=78 dep=2 uid=74 ct=6 lid=74 tim=1227260916839785 hv=3148607377 ad='fb528898'
UPDATE "TEST"."MV_T" SET "ID" = :1,"NAME" = :2,"CONTENTS" = :3 WHERE "ID" = :1
END OF STMT
PARSE #6:c=0,e=155,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,tim=1227260916839782
=====================
.
.
.
BINDS #6:
kkscoacd
 Bind#0
  acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  acflg=13 fl2=0000 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=2a9735e7d0  bln=22  avl=02  flg=09
  value=1
 Bind#1
  acdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
  acflg=13 fl2=0000 frm=01 csi=852 siz=32 ff=0
  kxsbbbfp=2a9735e7e8  bln=32  avl=03  flg=09
  value="ABC"
 Bind#2
  acdty=01 mxl=4001(32767) mxlc=00 mal=00 scl=00 pre=00
  acflg=13 fl2=0000 frm=01 csi=852 siz=4000 ff=0
  kxsbbbfp=2a975bd000  bln=4000  avl=32767  flg=29
  value="AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"...
 Bind#3
  No oacdef for this bind.
=====================
.
.
.
WAIT #6: nam='direct path write' ela= 4 file number=19 first dba=55396 block cnt=1 obj#=135714 tim=1227260916851660
WAIT #6: nam='direct path write' ela= 1 file number=19 first dba=55394 block cnt=1 obj#=135714 tim=1227260916851677
EXEC #6:c=10999,e=12047,p=0,cr=59,cu=101,mis=1,r=1,dep=2,og=1,tim=1227260916851880
FETCH #11:c=13999,e=15585,p=5,cr=73,cu=101,mis=0,r=1,dep=1,og=1,tim=1227260916851953
=====================
PARSING IN CURSOR #26 len=130 dep=1 uid=74 ct=6 lid=74 tim=1227260916852276 hv=642504755 ad='1b26a78'
UPDATE "TEST"."MV_T" SNA$ SET ( "CONTENTS" ) = (SELECT "CONTENTS"  FROM "TEST"."T" MAS$ WHERE MAS$."ID" = :1) WHERE SNA$."ID" = :1
END OF STMT
PARSE #26:c=0,e=193,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1227260916852273
BINDS #26:
kkscoacd
 Bind#0
  acdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  acflg=10 fl2=0000 frm=00 csi=00 siz=24 ff=0
  kxsbbbfp=2a9735ec50  bln=22  avl=02  flg=09
  value=1
 Bind#1
  No oacdef for this bind.
=====================
.
.
.
WAIT #26: nam='direct path read' ela= 5 file number=17 first dba=113544 block cnt=1 obj#=135714 tim=1227260916858835
WAIT #26: nam='direct path read' ela= 2 file number=17 first dba=113545 block cnt=1 obj#=135714 tim=1227260916858881
WAIT #26: nam='direct path read' ela= 2 file number=17 first dba=113546 block cnt=1 obj#=135714 tim=1227260916858961
WAIT #26: nam='direct path read' ela= 2 file number=17 first dba=113547 block cnt=1 obj#=135714 tim=1227260916859817
WAIT #26: nam='direct path read' ela= 2 file number=17 first dba=113548 block cnt=1 obj#=135714 tim=1227260916859851
WAIT #26: nam='direct path write' ela= 2 file number=20 first dba=46866 block cnt=2 obj#=135714 tim=1227260916862547
WAIT #26: nam='direct path write' ela= 1 file number=21 first dba=121172 block cnt=1 obj#=135714 tim=1227260916862561
EXEC #26:c=8999,e=11292,p=5,cr=120,cu=293,mis=1,r=1,dep=1,og=1,tim=1227260916863612
STAT #23 id=1 cnt=0 pid=0 pos=1 bj=0 p='HASH UNIQUE (cr=8 pr=0 pw=0 time=118 us)'
STAT #23 id=2 cnt=0 pid=1 pos=1 bj=0 p='FILTER  (cr=8 pr=0 pw=0 time=45 us)'
STAT #23 id=3 cnt=1 pid=2 pos=1 bj=135717 p='TABLE ACCESS FULL MLOG$_T (cr=7 pr=0 pw=0 time=23 us)'
STAT #23 id=4 cnt=1 pid=2 pos=2 bj=135716 p='INDEX UNIQUE SCAN PK_T (cr=1 pr=0 pw=0 time=9 us)'
=====================
.
.
.
=====================
PARSING IN CURSOR #23 len=11 dep=1 uid=0 ct=44 lid=0 tim=1227260916874048 hv=1180858989 ad='0'
COMMIT WORK
END OF STMT
PARSE #23:c=0,e=6,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=1227260916874045
WAIT #23: nam='log file sync' ela= 656 buffer#=5227 p2=0 p3=0 obj#=135714 tim=1227260916874745
XCTEND rlbk=0, rd_only=1
EXEC #23:c=0,e=692,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=1227260916874775
EXEC #2:c=111983,e=121116,p=11,cr=778,cu=478,mis=0,r=1,dep=0,og=1,tim=1227260916874869
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=135714 tim=1227260916875080
WAIT #2: nam='SQL*Net message from client' ela= 3678200 driver id=1650815232 #bytes=1 p3=0 obj#=135714 tim=1227260920553308
=====================
PARSING IN CURSOR #27 len=55 dep=0 uid=74 ct=42 lid=74 tim=1227260920553557 hv=524428051 ad='0'
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'
END OF STMT
PARSE #27:c=0,e=110,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1227260920553554
EXEC #27:c=0,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1227260920553650

测试发现，在处理的语句CURSOR #6时，Oracle在绑定变量的时候确实将LOB的内容填入。TRACE文件中只列出了LOB字段绑定变量的前255后字符，后面用省略号表示内容还没有结束。

从CURSOR #6的执行和等待时间上看，确实对LOB列进行了修改。更神奇的事情还在后面，Oracle居然又专门对LOB列进行了一次更新，在后面的CURSOR #26中，通过基表专门对LOB列进行了修改。