Bikle.com

Ajax Demo:

Plain HTML
(no Ajax here):

of URLs:

shrinkEventTable.html 10-18-2001 Bikle --------------------- Transforming a Large Table Into a Partitioned Table This page documents my efforts to partition a large, rapidly growing table named EVENT. This page has served me well as both a demonstration and a guide for other times I've worked to transform a "normal" table into a partitioned table. Also this page demonstrates another idea. Sometimes a DBA is asked to backup a large number of rows in a large table and then delete those rows from the large table once they are backed up. For small tables, this type of maintenance is trivial because the DBA needs not worry about rollback segments; he could do it with two SQL commands. For large tables, conducting this type of activity requires a little bit of knowledge so the DBA avoids blowing up the rollback segments or filling the disk partition which collects archived redologs. This table was first brought to my attention on 9-10-2001: todoFromBillB2001-0910.txt BillB raised the issue again in an e-mail on 10-18-2001: billb-email2001-1018.txt My main idea behind shrinking the EVENT table may be summarized via a list of steps I display below: -Determine the size of the EVENT table -Locate a directory which would be suitable for holding an export of the EVENT table -Craft an export script to copy appropriate rows from the EVENT table into an export file -Run the script -Locate objects dependent on the EVENT table -Build an empty copy of the EVENT table named EMPTY_P_EVENT (make it a partitioned table) -Rename EVENT table to EVENTBIG -Rename EMPTY_P_EVENT to EVENT -Attach objects dependent on the EVENT table to EVENT -Verify that software dependent on the EVENT table works okay -Copy appropriate rows from EVENTBIG into EVENT -Verify that software dependent on the EVENT table works okay Some details associated with the above steps are linked below: -Determine the size of the EVENT table EVENTsize.html -Locate a directory which would be suitable for holding an export of the EVENT table exportInfo.html -Craft an export script to copy appropriate rows from the EVENT table into an export file eventExportScript.html -Run the script A screen dump of us running the script is linked below: eventExportScreenDump.txt -Locate objects dependent on the EVENT table eventDependencies.html -Build an empty copy of the EVENT table named EMPTY_P_EVENT (make it a partitioned table) crEmptyEvent.html -Rename EVENT table to EVENTBIG renameSD.txt -Rename EMPTY_P_EVENT to EVENT renameSD2.txt -Attach objects dependent on the EVENT table to EVENT crBitmapIdx.txt RecompileObj.txt -Verify that software dependent on the EVENT table works okay -Copy appropriate rows from EVENTBIG into EVENT copy2event.txt -Verify that software dependent on the EVENT table works okay