Table Reorganization

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t
2 as
3 select * from all_objects;

Table created.

SQL> insert into t select * from t;

75458 rows created.

SQL> insert into t select * from t;

150916 rows created.

SQL> commit;

Commit complete.

SQL> create index idx_t on t(OBJECT_ID);

Index created.

Index is valid:

SQL> select index_name,index_type,status
2 from user_indexes
3 where index_name='IDX_T';

INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IDX_T NORMAL VALID

SQL> set linesize 200;
SQL> set autotrace traceonly explain;
SQL> select * from t where object_id=9;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 632 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 632 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T | 4 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=9)

Note
-----
- dynamic sampling used for this statement (level=2)

SQL> set autotrace off;

SQL> analyze table t compute statistics;

Table analyzed.

Check the HWM:

SQL> select blocks, extents from dba_segments where segment_name='T';

BLOCKS EXTENTS
---------- ----------
4352 49

SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='T';

BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
4292 59 301832


SQL> delete from t where mod(OBJECT_ID,2)=0;

150920 rows deleted.

Issue the move command to lower high water mark:

SQL> alter table t move;

Table altered.

Index will become unusable, need to rebuild:

SQL>
SQL> select index_name,index_type,status
2 from user_indexes
3 where index_name='IDX_T';

INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IDX_T NORMAL UNUSABLE

SQL> set autotrace traceonly explain;
SQL> select * from t where object_id=9;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 400 | 1167 (1)| 00:00:15 |
|* 1 | TABLE ACCESS FULL| T | 4 | 400 | 1167 (1)| 00:00:15 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=9)

SQL> set autotrace off;

High water mark gets lowered:

SQL> select blocks, extents from dba_segments where segment_name='T';

BLOCKS EXTENTS
---------- ----------
2176 32

SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='T';

BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
4292 59 301832

Rebuild index:

SQL> alter index idx_t rebuild;

Index altered.

SQL> set autotrace traceonly explain;
SQL> select * from t where object_id=9;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 400 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 400 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T | 4 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=9)

SQL> set autotrace off;





Aside, when trying to shrink table, it complains that tablespace segment type is wrong.
Looks like it will work if segment type is AUTO.


SQL> alter table t enable row movement ;

Table altered.

SQL> alter table t shrink space cascade;
alter table t shrink space cascade
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type


SQL> alter table t disable row movement ;

Table altered.

SQL>