Re: rebuild indexes
- From: Justin Mungal <justin@xxxxxxx>
- To: maureen.english@xxxxxxxxxx
- Date: Sat, 19 Apr 2014 00:08:02 -0500
I would go with SHRINK SPACE over TABLE MOVE as well, for reasons already
mentioned. Here are some examples of me playing with SHRINK SPACE that you
might find useful.
"*The following is a brief example of creating a table, freeing space
within it, and then shrinking it and verified that the space has been
freed. The arithmetic is based on an article by Jonathan Lewis:
http://jonathanlewis.wordpress.com/2007/11/23/table-rebuilds/
<http://jonathanlewis.wordpress.com/2007/11/23/table-rebuilds/>. *
set linesize 150
col avg_row_len format 999,999,999,999
col num_rows format 999,999,999,999
col blocks format 999,999,999,999
col block_bytes format 999,999,999,999
col est_row_bytes format 999,999,999,999
drop table justin.test;
create table justin.test as (select * from dba_objects);
exec dbms_stats.gather_table_stats('JUSTIN', 'TEST');
select tb.AVG_ROW_LEN, tb.NUM_ROWS, tb.BLOCKS,
tb.blocks * ts.block_size block_bytes,
tb.AVG_ROW_LEN * tb.NUM_ROWS est_row_bytes,
100*(1-(tb.AVG_ROW_LEN * tb.NUM_ROWS)/(tb.blocks * ts.block_size))
est_free_space_pct
from dba_tables tb, dba_tablespaces ts
where tb.tablespace_name = ts.tablespace_name
and owner='JUSTIN'
and table_name='TEST'
/
delete from justin.test where owner='SYS';
exec dbms_stats.gather_table_stats('JUSTIN', 'TEST');
select tb.AVG_ROW_LEN, tb.NUM_ROWS, tb.BLOCKS,
tb.blocks * ts.block_size block_bytes,
tb.AVG_ROW_LEN * tb.NUM_ROWS est_row_bytes,
100*(1-(tb.AVG_ROW_LEN * tb.NUM_ROWS)/(tb.blocks * ts.block_size))
est_free_space_pct
from dba_tables tb, dba_tablespaces ts
where tb.tablespace_name = ts.tablespace_name
and owner='JUSTIN'
and table_name='TEST'
/
alter table justin.test enable row movement;
alter table justin.test shrink space;
exec dbms_stats.gather_table_stats('JUSTIN', 'TEST');
select tb.AVG_ROW_LEN, tb.NUM_ROWS, tb.BLOCKS,
tb.blocks * ts.block_size block_bytes,
tb.AVG_ROW_LEN * tb.NUM_ROWS est_row_bytes,
100*(1-(tb.AVG_ROW_LEN * tb.NUM_ROWS)/(tb.blocks * ts.block_size))
est_free_space_pct
from dba_tables tb, dba_tablespaces ts
where tb.tablespace_name = ts.tablespace_name
and owner='JUSTIN'
and table_name='TEST'
/
*The results follow.*
Table dropped.
SQL> SQL>
Table created.
SQL> SQL>
PL/SQL procedure successfully completed.
SQL> SQL> 2 3 4 5 6 7 8 9
AVG_ROW_LEN NUM_ROWS BLOCKS BLOCK_BYTES
EST_ROW_BYTES EST_FREE_SPACE_PCT
---------------- ---------------- ---------------- ----------------
---------------- ------------------
97 75,466 1,101 9,019,392
7,320,202 18.8392965
SQL> SQL>
31792 rows deleted.
SQL> SQL>
PL/SQL procedure successfully completed.
SQL> SQL> 2 3 4 5 6 7 8 9
AVG_ROW_LEN NUM_ROWS BLOCKS BLOCK_BYTES
EST_ROW_BYTES EST_FREE_SPACE_PCT
---------------- ---------------- ---------------- ----------------
---------------- ------------------
98 43,674 1,101 9,019,392
4,280,052 52.546114
SQL> SQL>
Table altered.
SQL> SQL>
Table altered.
SQL> SQL>
PL/SQL procedure successfully completed.
SQL> SQL> 2 3 4 5 6 7 8 9
AVG_ROW_LEN NUM_ROWS BLOCKS BLOCK_BYTES
EST_ROW_BYTES EST_FREE_SPACE_PCT
---------------- ---------------- ---------------- ----------------
---------------- ------------------
98 43,674 626 5,128,192
4,280,052 16.5387723"
Cheers,
Justin
Other related posts:
