[10/16] 실습 - 1 RowMigration&&chaining

======================================

Row Migration

======================================

SYS@orcl>create table chain_test as (select * from hr.employees);

Table created.

SYS@orcl>select first_name, length(first_name)

  2  from chain_test;

FIRST_NAME           LENGTH(FIRST_NAME)

-------------------- ------------------

Britney                               7

~~~~~~~중략

Timothy                               7

Randall                               7

Sarah                                 5

107 rows selected.

SYS@orcl>analyze table chain_test compute statistics;

Table analyzed.

SYS@orcl>select table_name, chain_cnt from user_tables

  2  where table_name='CHAIN_TEST';

TABLE_NAME                      CHAIN_CNT

------------------------------ ----------

CHAIN_TEST                              0

SELECT a.name, b.value

   FROM v$statname a, v$mystat b

  WHERE a.statistic# = b.statistic#

  4      AND lower(a.name) = 'table fetch continued row';

NAME                                                                  VALUE

---------------------------------------------------------------- ----------

table fetch continued row                                                84

SYS@orcl>update chain_test

  2  set first_name='ABCDEFGHIJKLMNOPQRS';

107 rows updated.

SYS@orcl>select table_name, chain_cnt from user_tables

  2  where table_name='CHAIN_TEST';

TABLE_NAME                      CHAIN_CNT

------------------------------ ----------

CHAIN_TEST                              6

SELECT 'Chained or Migrated Rows = '||value

   FROM v$sysstat

  3    WHERE name = 'table fetch continued row';

'CHAINEDORMIGRATEDROWS='||VALUE

-------------------------------------------------------------------

Chained or Migrated Rows = 729

SELECT chain_cnt,

        round(chain_cnt/num_rows*100,2) pct_chained,

        avg_row_len, pct_free , pct_used

   FROM user_tables

  5   WHERE table_name = 'CHAIN_TEST';

 CHAIN_CNT PCT_CHAINED AVG_ROW_LEN   PCT_FREE   PCT_USED

---------- ----------- ----------- ---------- ----------

         6        5.61          85         10         40

======================================

Row Chaining

======================================

SYS@orcl>@?/rdbms/admin/utlchain.sql

Table created.

SYS@orcl>update chain_test

  2  set first_name = 'ABCDEFGHIJKLMNOPQRST';

107 rows updated.

SYS@orcl>commit;

Commit complete.

SYS@orcl>analyze table chain_test

  2  list chained rows

  3  into chained_rows;

Table analyzed.

SYS@orcl>desc chained_rows

 Name                                                              Null?    Type

 ----------------------------------------------------------------- -------- --------------------------------------------

 OWNER_NAME                                                                 VARCHAR2(30)

 TABLE_NAME                                                                 VARCHAR2(30)

 CLUSTER_NAME                                                               VARCHAR2(30)

 PARTITION_NAME                                                             VARCHAR2(30)

 SUBPARTITION_NAME                                                          VARCHAR2(30)

 HEAD_ROWID                                                                 ROWID

 ANALYZE_TIMESTAMP                                                          DATE

SYS@orcl>select count(*) from chained_rows;

  COUNT(*)

----------

         7

SYS@orcl>select table_name, head_rowid from chained_rows;

TABLE_NAME                     HEAD_ROWID

------------------------------ ------------------

CHAIN_TEST                     AAASVjAABAAAVupAA7

CHAIN_TEST                     AAASVjAABAAAVupABB

CHAIN_TEST                     AAASVjAABAAAVupABH

CHAIN_TEST                     AAASVjAABAAAVupABN

CHAIN_TEST                     AAASVjAABAAAVupABT

CHAIN_TEST                     AAASVjAABAAAVupABZ

CHAIN_TEST                     AAASVjAABAAAVupABf

7 rows selected.

create table new_chain

as

select *

from chain_test

where rowid in(

select head_rowid

from chained_rows

where table_name='CHAIN_TEST'

  9  );

SYS@orcl>select table_name, chain_cnt from user_tables

  2    where table_name= 'CHAIN_TEST';

TABLE_NAME                      CHAIN_CNT

------------------------------ ----------

CHAIN_TEST

SYS@orcl>analyze table chain_test compute statistics;

Table analyzed.

SYS@orcl>select table_name, chain_cnt from user_tables

  2    where table_name= 'CHAIN_TEST';

TABLE_NAME                      CHAIN_CNT

------------------------------ ----------

CHAIN_TEST                              7

delete from chain_test

where rowid in(

select head_rowid

from chained_rows

where table_name='CHAIN_TEST'

  6  );

7 rows deleted.

SYS@orcl>select table_name, chain_cnt from user_tables

  2    where table_name= 'CHAIN_TEST';

TABLE_NAME                      CHAIN_CNT

------------------------------ ----------

CHAIN_TEST                              7

SYS@orcl>analyze table chain_test compute statistics;

Table analyzed.

SYS@orcl>select table_name, chain_cnt from user_tables

  2    where table_name= 'CHAIN_TEST';

TABLE_NAME                      CHAIN_CNT

------------------------------ ----------

CHAIN_TEST                              0