Re: Index thrashing still after Alter table move and new building of indexes.

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Fri, 23 Jul 2004 01:05:52 +0300
Message-ID: <33c801c47038$0e5864d0$0a879fd9@porgand>

> Alter table move repacks the table. You can verify this by moving a table
> that has had a significant number of deletes so that it takes fewer
extents
> to hold the rebuilt version.

Note that whilst alter table move is able to pack several sparsely populated blocks into one, a table might actually grow as a result of moving under certain circumstances:

(While rows do still fit into same amount of blocks after updating due PCTFREE, the alter table move will take PCTFREE into account as with regular inserts, thus incresing overall size of the table)

SQL> create table t (a) pctfree 50 tablespace t9 nocompress as select cast('x' as varchar2(100)) from sys.obj$;

Table created.

SQL>
SQL> analyze table t compute statistics;

Table analyzed.

SQL>
SQL> select blocks, avg_space from user_tables where table_name = 'T';

BLOCKS AVG_SPACE
---------- ----------

21 4161

SQL>

SQL> update t set a = 'xxxxxxxxxxxxxxxx';

7432 rows updated.

SQL>
SQL> analyze table t compute statistics;

Table analyzed.

SQL>
SQL> select blocks, avg_space from user_tables where table_name = 'T';

BLOCKS AVG_SPACE
---------- ----------

21 268

SQL>
SQL> alter table t move;

Table altered.

SQL>
SQL> analyze table t compute statistics;

Table analyzed.

SQL>
SQL> select blocks, avg_space from user_tables where table_name = 'T';

BLOCKS AVG_SPACE
---------- ----------

41 4066

SQL> Tanel.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Jul 22 2004 - 17:03:14 CDT