oracle优化--表优化（行链接产生实验）

一、通过一次插入一笔大于基本块大小（8K）的数据，制造一笔有行链接但没有行迁移的数据 SQL> insert into t_pctfree values (10002,dbms_random.string('u', 2000),dbms_random.string('u', 2000),dbms_random.string('u', 2000),dbms_random.string('u', 2000),dbms_random.string('u', 2000)); 1 row created. SQL> commit; Commit complete. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered. 二、使用dump确定此行数据有行链接但没有行迁移 SQL> select dbms_rowid.rowid_block_number(rowid) block_id from t_pctfree where id=10002;   BLOCK_ID ----------      51036 SQL> alter system dump datafile 5 block 51036; System altered. SQL> oradebug setmypid; Statement processed. SQL> oradebug tracefile_name; /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25082.trc [oracle@localhost ~]$ more /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25082.trc block_row_dump: tab 0, row 0, @0xfe4 tl: 4020 fb: --H-F--- lb: 0x1  cc: 3 nrid:  0x0140c758.1 col  0: [ 4]  c3 02 01 03 col  1: [2000]  55 51 46 4b 57 4c 4a 48 4b 47 4e 43 43 59 42 46 56 44 54 48 54 50 42 51 47  58 59 54 54 41 48 45 4e 5a 56 4c 47 44 4e 55 43 53 43 51 4d 57 4e 54 59 5a  48 48 4c 48 41 41 4e 44 45 51 4c 51 4c 58 47 4a 4f 4a 42 4f 41 59 57 57 58  46 4d 50 4a 45 57 46 42 45 48 48 46 41 44 5a 43 53 55 59 53 4a 42 52 5a 4f  4b 4f 52 56 56 46 4e 58 57 4a 46 4b 4a 46 56 54 59 51 59 4a 49 51 42 56 4e  53 48 59 4e 58 55 57 44 41 41 53 4c 4f 43 45 52 4b 4c 4d 46 58 4d 53 50 51 三、确认列1是否是刚才插入的数据 SQL> var n number; SQL> exec dbms_stats.convert_raw_value('c3020103',:n); PL/SQL procedure successfully completed. SQL> print :n N ----------      10002 因此可以确认此块（51036）存储有1、2两列信息，剩余列在另一个块上，而不像是行迁移时，块信息里只有nrid 四、查看被链接的块信息 SQL> select dbms_utility.DATA_BLOCK_ADDRESS_FILE(to_number('0140c758', 'xxxxxxxxxx')) file#,dbms_utility.DATA_BLOCK_ADDRESS_BLOCK(to_number('0140c758','xxxxxxxxxx')) block# from dual;      FILE#     BLOCK# ---------- ---------- 5 51032 SQL> alter system dump datafile 5 block 51032; System altered. SQL> oradebug setmypid; Statement processed. SQL> oradebug tracefile_name; /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_25101.trc tl: 6012 fb: -----L-- lb: 0x2  cc: 3 col  0: [2000]  4e 49 59 52 43 52 47 41 44 44 4e 54 43 5a 4d 4d 53 47 55 55 4d 56 55 44 55  4f 46 53 50 47 4d 50 52 43 45 51 57 52 41 44 4c 55 49 55 59 4c 57 58 5a 52  44 50 41 45 52 48 57 45 4b 53 58 54 50 56 47 52 4b 5a 52 4d 50 44 42 43 50  4b 48 51 41 54 47 49 48 50 4c 45 57 4f 48 4f 4a 55 4c 50 59 52 4d 53 55 50  59 4d 41 41 55 53 5a 48 56 52 41 44 4e 53 49 4a 58 4c 4a 4e 47 54 55 44 43  4f 56 4f 55 4a 41 46 44 42 55 41 42 48 58 47 56 57 52 55 47 52 44 4a 42 48