Short Experiment : Oracle chained rows

 Dealing with chained rows simple experiment

Simple experiment:

Simple table created from demo tables XEMP1.

The updated rows until ora error 1489 generated.:

update xemp1 set ename=ename||'abcdabcdabsdfajsdkfkasdjfkasj;dfkljasdkfjas;ldfjsdlfkjas;lfjas;dfjasldkfjasldfjasldkfjasldkjfaslkdjfasldfjasldfjabcdabcdabsdfajsdkfkasdjfkasj;dfkljasdkfjas;ldfjsdlfkjas;lfjas;dfjasldkfjasldfjasldkfjasldkjfaslkdjfasldfjasldfjabcdabcdabsdfajsdkfkasdjfkasj;dfkljasdkfjas;ldfjsdlfkjas;lfjas;dfjasldkfjasldfjasldkfjasldkjfaslkdjfasldfjasldfj' where rownum<50000
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long


SQL> commit;

Size :

select sum(bytes)/power(1024,2) from dba_segments where segment_name='XEMP1'

SUM(BYTES)/POWER(1024,2)
------------------------
                     591

ANALYZE TABLE xemp1 COMPUTE STATISTICS;

Table analyzed.

Select /*Chained rows*/ owner, table_name, num_rows, chain_cnt, (chain_cnt*100/num_rows) pct, empty_blocks, blocks

from dba_tables

where chain_cnt > 0

and owner not in ('SYS','SYSTEM')

/

SQL> SQL> SQL> SQL> SQL>   2    3    4    5

OWNER        TABLE_NAME             NUM_ROWS  CHAIN_CNT PCT Empty Blks     Blks

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

SCOTT        XEMP1                   7421212     518965   7        124   75,524

alter table xemp1 enable row movement;

Alter table XEMP1 shrink space compact;

elect /*Chained rows*/ owner, table_name, num_rows, chain_cnt, (chain_cnt*100/num_rows) pct, empty_blocks, blocks
from dba_tables
where chain_cnt > 0
and owner not in ('SYS','SYSTEM')
/
SQL> SQL> SQL> SQL> SQL>   2    3    4    5
OWNER        TABLE_NAME             NUM_ROWS  CHAIN_CNT PCT Empty Blks     Blks
------------ -------------------- ---------- ---------- --- ---------- --------
SCOTT        XEMP1                   7421212     518965   7        124   75,524

Tried shrink no effect on chained rows, just for the heck of it :-)

alter table xemp1 move;

Table altered.

SQL> select /*Chained rows*/ owner, table_name, num_rows, chain_cnt, (chain_cnt*100/num_rows) pct, empty_blocks, blocks
from dba_tables
where chain_cnt > 0
and owner not in ('SYS','SYSTEM')
/
  2    3    4    5
no rows selected   <=============target achieved.

/

--------------More later