oracle表優化方法教程

這篇文章主要講解了“oracle表優化方法教程”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“oracle表優化方法教程”吧！

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、添加數據，先只寫id，其他值為null

SQL>insert into t_pctfree(id) values(2);

1 row created.

SQL> commit;

Commit complete.

3、使用dump查看數據塊，確定id為2的數據只存在于一個塊中

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的數據，使當前塊剩余空間容納不下修改后的數據

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、查看修改后的塊狀態

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、發現已產生行遷移，源塊只留下了遷移塊的地址，通過nrid查看遷移目標塊信息

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、可以看到遷移目標塊只有hrid，沒有nrid，說明只出現了行遷移，沒有出現行鏈接

感謝各位的閱讀，以上就是“oracle表優化方法教程”的內容了，經過本文的學習后，相信大家對oracle表優化方法教程這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！