oracle11g_如何模擬產生行連結或行遷移chained_rows

wisdomone1發表於2013-05-21

---檢視塊大小為8192byte
SQL> show parameter db_block_size
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192


---建立一個表,記錄最大大小超過8192byte,則可以產生行遷鏈或行遷移
SQL> create table t_row_chain(a varchar2(4000),b varchar2(3000),c varchar2(4000));
 
Table created
 
SQL> insert into t_row_chain values('x','y','z');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> exec dbms_stats.gather_table_stats(user,'t_row_chain',cascade=>true);
 
PL/SQL procedure successfully completed
 
SQL> select table_name,chain_cnt from user_tables where table_name='T_ROW_CHAIN';
 
TABLE_NAME                      CHAIN_CNT
------------------------------ ----------
T_ROW_CHAIN                             0
 
SQL> update t_row_chain set a=rpad('a',4000,'c');
 
1 row updated
 
SQL> commit;
 
Commit complete
 
SQL> select length(a) from t_row_chain;
 
 LENGTH(A)
----------
      4000
 
SQL> update t_row_chain set b=rpad('a',3000,'c');
 
1 row updated
 
SQL> update t_row_chain set c=rpad('a',4000,'c');
 
1 row updated


SQL> commit;
 
Commit complete
 
--用dbms_stats不能查出行連結或行遷移
SQL>  exec dbms_stats.gather_table_stats(user,'t_row_chain',cascade=>true);
 
PL/SQL procedure successfully completed
 
SQL> select table_name,chain_cnt from user_tables where table_name='T_ROW_CHAIN';
 
TABLE_NAME                      CHAIN_CNT
------------------------------ ----------
T_ROW_CHAIN                             0

 

SQL> analyze table t_row_chain list chained rows;
 
analyze table t_row_chain list chained rows
 
ORA-01495: specified chain row table not found

--先建立儲存行連結或行遷移的表
SQL> @D:\oracle11g_64bit\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlchain.sql
 
Table created


SQL> analyze table t_row_chain list chained rows;
 
Table analyzed

---行連結產生了
SQL> select * from chained_rows;
 
OWNER_NAME                     TABLE_NAME                     CLUSTER_NAME                   PARTITION_NAME                 SUBPARTITION_NAME              HEAD_ROWID         ANALYZE_TIMESTAMP
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ -----------------
TBL_BCK                        T_ROW_CHAIN                                                                                  N/A                            AAASD5AAGAAAA4jAAA 2013-05-21 2224
 
 
 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-761687/,如需轉載,請註明出處,否則將追究法律責任。

相關文章