您好,登錄后才能下訂單哦!
這篇文章主要講解了“oracle表優(yōu)化方法教程”,文中的講解內(nèi)容簡單清晰,易于學(xué)習(xí)與理解,下面請大家跟著小編的思路慢慢深入,一起來研究和學(xué)習(xí)“oracle表優(yōu)化方法教程”吧!
1、建立實驗表
create table t_pctfree
(
id number,
name varchar2(2000),
name1 varchar2(2000),
name2 varchar2(2000),
name3 varchar2(2000),
name4 varchar2(2000)
)
tablespace users
pctfree 10;
Table created.
2、添加數(shù)據(jù),先只寫id,其他值為null
SQL>insert into t_pctfree(id) values(2);
1 row created.
SQL> commit;
Commit complete.
3、使用dump查看數(shù)據(jù)塊,確定id為2的數(shù)據(jù)只存在于一個塊中
SQL> select dbms_rowid.rowid_block_number(rowid) block_id from t_pctfree where id=2;
BLOCK_ID
----------
47547
SQL> alter system dump datafile 5 block 51031;
System altered.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc
[oracle@localhost trace]$ more /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc
tab 0, row 0, @0x1f92
tl: 2 fb: --HDFL-- lb: 0x2
tab 0, row 1, @0x1f8c
tl: 6 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 2] c1 03
tab 0, row 2, @0x1f86
SQL> var n number;
SQL> exec dbms_stats.convert_raw_value('c103',:n);
PL/SQL procedure successfully completed.
SQL> print :n
N
----------
2
4、修改id為2的數(shù)據(jù),使當(dāng)前塊剩余空間容納不下修改后的數(shù)據(jù)
SQL> update scott.t_pctfree set name=dbms_random.string('u', 2000) where id=2;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
.
5、查看修改后的塊狀態(tài)
SQL> select dbms_rowid.rowid_block_number(rowid) block_id from t_pctfree where id=2;
BLOCK_ID
----------
47547
SQL> alter system dump datafile 5 block 51031;
System altered.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc
[oracle@localhost trace]$ more /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc
tab 0, row 1, @0xdec
tl: 9 fb: --H----- lb: 0x1 cc: 0
nrid: 0x0140c75b.0
6、發(fā)現(xiàn)已產(chǎn)生行遷移,源塊只留下了遷移塊的地址,通過nrid查看遷移目標(biāo)塊信息
SQL> select dbms_utility.DATA_BLOCK_ADDRESS_FILE(to_number('0140c75b', 'xxxxxxxxxx')) file#,dbms_utility.DATA_BLOCK_ADDRESS_BLOCK(to_number('0140c75b','xxxxxxxxxx')) block# from dual;
FILE# BLOCK#
---------- ----------
551035
SQL> alter system dump datafile 5 block 51035;
System altered.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc
[oracle@localhost trace]$ more /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_26496.trc
tab 0, row 0, @0x17a1
tl: 2015 fb: ----FL-- lb: 0x1 cc: 2
hrid: 0x0140b9bb.1
col 0: [ 2] c1 03
col 1: [2000]
5a 52 57 58 53 54 45 4a 50 4e 56 43 4c 55 4e 4e 4d 47 59 49 51 50 44 41 41
4b 4d 47 56 52 49 5a 51 55 47 54 54 5a 51 41 49 5a 55 57 43 58 46 42 54 55
7、可以看到遷移目標(biāo)塊只有hrid,沒有nrid,說明只出現(xiàn)了行遷移,沒有出現(xiàn)行鏈接
感謝各位的閱讀,以上就是“oracle表優(yōu)化方法教程”的內(nèi)容了,經(jīng)過本文的學(xué)習(xí)后,相信大家對oracle表優(yōu)化方法教程這一問題有了更深刻的體會,具體使用情況還需要大家實踐驗證。這里是億速云,小編將為大家推送更多相關(guān)知識點(diǎn)的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。