Migrate the index segment to another tablespace

Migrate the index segment to another tablespace

 

Inorder to increase the  index full scan performance and reduce scan table time, i do migrate the index segment to another tablespace which block_size is 16k, the original is 8k. This operation can reduce the scan io for index and increase the speed for select operation.

 

Suppose the oracle default tablespace block_size is 8k.

 

Steps of this event

 

[sql] 
Configure the db_16k_cache_size:  
  
ALTER SYSTEM SET DB_16K_CACHE_SIZE=2048M;  
  
Create the 16k tablespace:  
  
  
CREATE TABLESPACE  CLICINDTBS DATAFILE '+DATA' SIZE 2048M     
AUTOEXTEND OFF UNIFORM SIZE 1M   
SEGMENT SPACE MANAGEMENT AUTO；  
  
Rebuild the target index  
  
ALTER INDEX index_name REBUILD TABLESPACE  CLICINDTBS;  

 

 

That is all.