Does the operation of the BLOB field under Oracle produce a large number of redo

Whether the Operation BLOB field will produce a lot of redo, the answer is no , here is an experiment, the test database version is 11.2.0.1.0:

--Create a table for testing

CREATE TABLE Test_blob
(
ID number,
Tupian blob
);

Import Java.io.fileinputstream;import Java.io.outputstream;import Java.sql.connection;import Java.sql.drivermanager;import Java.sql.resultset;import Java.sql.sqlexception;import Java.sql.Statement;import    Oracle.sql.blob;public class Blobexample {static final String Driver_class = "Oracle.jdbc.driver.OracleDriver";    Static final String Connectionurl = "JDBC:ORACLE:THIN:@10.10.15.25:1521:ORCL";    Static final String UserID = "Test";    Static final String UserPassword = "Test";        private void Inserttestblob () {Connection conn=null;        Statement Stm=null;        ResultSet Rs=null;        Blob blob = null;        FileInputStream Fin=null;        OutputStream Out=null;            try{conn = drivermanager.getconnection (Connectionurl, UserID, UserPassword);            STM = Conn.createstatement ();            Conn.setautocommit (FALSE);            String sql = "INSERT into Test_blob values (1,empty_blob ())";            Stm.executeupdate (SQL); rs =Stm.executequery ("Select Tupian from Test_blob WHERE id=1 for UPDATE");           Fin = new FileInputStream ("d://20130317.jpg");           byte[] Blobbuf = new byte[(int) fin.available ()];           Fin.read (BLOBBUF);           Fin.close ();            if (Rs.next ()) {blob = (Oracle.sql.BLOB) rs.getblob (1);            out = Blob.getbinaryoutputstream ();            Out.write (BLOBBUF);            Out.close ();           Conn.commit ();        }}catch (Exception e) {e.printstacktrace ();                }finally{try {rs.close ();                Stm.close ();            Conn.close ();            } catch (SQLException e) {e.printstacktrace ();        }}} public static void Main (String args[]) {blobexample blobclobexample = new Blobexample ();    Blobclobexample.inserttestblob (); }}

--many times.
INSERT INTO Test_blob select * from Test_blob;

INSERT INTO Test_blob select * from Test_blob;

INSERT INTO Test_blob select * from Test_blob;

.......

Commit

--Prepare to dump block

Select rowID,
Dbms_rowid.rowid_object (ROWID) object_id,
DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) file_id,
Dbms_rowid.rowid_block_number (ROWID) block_id,
Dbms_rowid.rowid_row_number (ROWID) num
From Test_blob;

--update Status of BLOBs before

Alter session Set Tracefile_identifier = ' Look_for_me ';
alter system switch logfile;
alter system switch logfile;
Alter system dump DATAFILE 5 block 3274932;

--update the state of the blob, and test it at the same time, how many redo is generated by the update?
Select Name, value
From V$mystat, V$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = ' redo Size '
Update Test_blob Set Tupian = null;
Commit

Select Name, value
From V$mystat, V$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = ' redo Size '

alter system switch logfile;
alter system switch logfile;
Alter session Set Tracefile_identifier = ' look_for_me1 ';
Alter system dump DATAFILE 5 block 3274932;

Test result: I pass the picture is 5.1M, altogether produced 350 data, all put the blob to empty later, produced a total of 7.6M redo, it is obvious that the content of BLOB is not produce redo.

Analysis principle, with the help of analysis of the contents of the dump block, you can see that the Set BLOB field null after the redo is just like Col 1: [84] This information.

Blob is set to empty before:

Block Header Dump:0x0171f8b4
Object ID on Block? Y
seg/obj:0x17f1d csc:0x9a8.7256c728 itc:2 flg:e typ:1-DATA
brn:0 bdba:0x171f8b0 ver:0x01 opc:0
inc:0 exflg:0

Itl Xid Uba Flag Lck SCN/FSC
0x01 0x0003.01b.000033cf 0x00c001f5.132c.39--u-1 FSC 0x0000.7256c775
0x02 0x0000.000.00000000 0x00000000.0000.00----0 FSC 0x0000.00000000
Bdba:0x0171f8b4
Data_block_dump,data header at 0x2b35c4c56064
===============
Tsiz:0x1f98
hsiz:0x14
pbl:0x2b35c4c56064
76543210
flag=--------
Ntab=1
Nrow=1
Frre=-1
fsbo=0x14
Fseo=0x1f12
Avsp=0x1f29
Tosp=0x1f29
0XE:PTI[0] Nrow=1Offs=0
0X12:PRI[0] Offs=0x1f12
Block_row_dump:
tab 0, row 0, @0x1f12
tl:91 FB:--h-fl--lb:0x1 Cc:2
Col 0: [2] C1 02
Col 1: [84]
In the 0c, the 48 41 00 40 05 00 00 for the xx xx
A0-xx-xx-xx-f8 BC F8 BD F8 be 01 71
F8 BF F8 BB 11 CA 3d, CA 3e, CA one 3f, CA
3a, CA 3b, CA, 3c
Lob
Locator:
LENGTH:84 (84)
Version:1
Byte length:1
lobid:00.00.00.01.00.00.00.0e.48.41
flags[0x01 0x0c 0x00 0x00]:
Type:blob
Storage:basicfile
Enable Storage in Row
Characterset format:implicit
Partitioned Table:no
Options:readwrite
Inode:
Size:64
flag:0x05 [Valid Inodeinrow (ESiR)]
Future:0x00 (should be ' 0x00 ')
blocks:630
bytes:4768
version:00000.0000000002
DBA array[12]:
0X0171F8BC 0X0171F8BD 0x0171f8be 0X0171F8BF
0X0171F8BB 0x01ca113d 0x01ca113e 0x01ca113f
0x01ca1139 0x01ca113a 0x01ca113b 0x01ca113c

............... ............... ............... ...............

............... .............................................

End dump data blocks Tsn:6 file#: 5 minblk 3274932 maxblk 3274932

After the blob is set to empty:

Block Header Dump:0x0171f8b4
Object ID on Block? Y
seg/obj:0x17f1d csc:0x9a8.7256c99f itc:2 flg:e typ:1-DATA
brn:0 bdba:0x171f8b0 ver:0x01 opc:0
inc:0 exflg:0

Itl Xid Uba Flag Lck SCN/FSC
0x01 0X0003.01B.000033CF 0x00c001f5.132c.39 C---0 SCN 0x09a8.7256c775
0x02 0x0001.003.0000315e 0x00c0218e.1348.3a--u-1 FSC 0x0052.7256c9a7
Bdba:0x0171f8b4
Data_block_dump,data header at 0x2b7ad6cce464
===============
Tsiz:0x1f98
hsiz:0x14
pbl:0x2b7ad6cce464
76543210
flag=--------
Ntab=1
Nrow=1
Frre=-1
fsbo=0x14
fseo=0x1f0c
Avsp=0x1f29
tosp=0x1f7b
0XE:PTI[0] Nrow=1Offs=0
0X12:PRI[0] offs=0x1f0c
Block_row_dump:
tab 0, row 0, @0x1f0c
Tl:6 FB:--h-fl--lb:0x2 cc:1
Col 0: [2] C1 02

............................................................

............................................................

End dump data blocks Tsn:6 file#: 5 minblk 3274932 maxblk 3274932