June 2009

Monthly Archive

June 20, 2009

Adding partitions to table

Posted by vinayaknp under Oracle, Tech
Leave a Comment 

Recently, we had performance issues in our SQL code and one of the ways we thought was to increase the number of partitions.

The way to do it is to give one alter table query for each partition. And also the best practice is to have the number of partitions as a power of 2. So we increased it from 8 to 16

ALTER TABLE table_name ADD PARTITION

This will add the partition and also recalculate the rows that need to be moved to the new partition.

Note: If there are Local indexes in the partitions, they are invalidated due to the new partitions added and they need to be made usable

ALTER TABLE list_customers MODIFY PARTITION asia
REBUILD UNUSABLE LOCAL INDEXES;

 

June 20, 2009

Finding rows in table partition

Posted by vinayaknp under Oracle, Tech
Leave a Comment 

There are cases when we would need to know how many rows are present in each partition. This would help us in figuring out if the partition strategy is working fine or not


SELECT object_name, subobject_name, DBMS_MView.PMarker(dta.rowid), count(1)
from TABLE_NAME dta, all_objects ao
where ao.object_id = DBMS_MView.PMarker(dta.rowid)
group by object_name, subobject_name, DBMS_MView.PMarker(dta.rowid)
order by 1,2 ;