操作blob字段是否會產生大量redo,答案是不會,下面來做一個實驗,測試數據庫版本是11.2.0.1.0:
--創建一張表做測試之用
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(); } }--做很多次
insert into test_blob select * from test_blob;
insert into test_blob select * from test_blob;
.......
commit;
--准備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之前blob的狀態
alter session set tracefile_identifier = 'Look_For_Me';
alter system switch logfile;
alter system switch logfile;
alter system dump datafile 5 block 3274932;
--update之後blob的狀態,同時測試一下,此時update產生了多少redo
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;
測試結果:我傳的圖片是5.1M,一共產生了350條數據,都把blob置為空以後,共產生了7.6M的redo,很顯然是blob的內容是沒有產生redo的。
分析原理,得借助分析dump block的內容,可以看到設置blob字段為null後產生的redo只是類似col 1: [84]這些信息。
blob設置為空以前:
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=1 offs=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]
00 54 00 01 01 0c 00 00 00 01 00 00 00 01 00 00 00 0e 48 41 00 40 05 00 00
00 02 76 12 a0 00 00 00 00 00 02 01 71 f8 bc 01 71 f8 bd 01 71 f8 be 01 71
f8 bf 01 71 f8 bb 01 ca 11 3d 01 ca 11 3e 01 ca 11 3f 01 ca 11 39 01 ca 11
3a 01 ca 11 3b 01 ca 11 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
blob設置為空之後:
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=1 offs=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