Optimize the Massive DELETE Operation in Oracle, Part 2 : Page 2

The SwapOps Package
The SwapOps package code extracts metadata from database objects, such as tables, indexes, constraints, etc. A previous 10-Minute Solution, "Use an Oracle API to Extract and Store Database Objects' DDL", described a MetaDataPkg package that allowed you to automate the process of extracting objects' definitions and storing them in memory structures (associative arrays). Since then, I made a small modification to that package by adding one more attribute (aSwapName) to type tMetaObject:

type tMetaObject is record
(
   aName         tString,
   aType         tString,
   aLogging      tString,
   aParallel     tString,
   aStatus       tString,
   aValidated    tString,
   aRely         tString,
   aSwapName     tString,
   aDDLString    tLongString
);

The aSwapName attribute holds the name for a swap object (e.g., for the TestData table, the aSwapName value will be TestData_T, and for the TestData primary key TestData_PK, the value will be TestData_PK_T, etc.).

The accompanying source codecontains the MetaDataPkg package's specification and body.

Now, let's look at the SwapOps package's specification:

create or replace package SwapOps as

  /*
   * Constants
   */
   cSwapSuffix constant char(2)  := '_X';
   cTempSuffix constant char(2)  := '_T';

  /*
   * GetMeta
   */
   function GetMeta
   (
      pName  in MetaDataPkg.tString,
      pForce in boolean := false
   )
   return MetaDataPkg.tFullMetaObject;


  /*
   * CreateObjects
   */
   procedure CreateObjects
   (
      pName    in MetaDataPkg.tString,
      pType    in MetaDataPkg.tString,
      pLogging in MetaDataPkg.tString := MetaDataPkg.cNo
   );


  /*
   * RenameObjects
   */
   procedure RenameObjects
   (
      pName       in MetaDataPkg.tString,
      pType       in MetaDataPkg.tString,
      pFromSuffix in MetaDataPkg.tString,
      pToSuffix   in MetaDataPkg.tString
   );


  /*
   * ExecuteInsert
   */
   procedure ExecuteInsert
   (
      pName  in MetaDataPkg.tString,
      pWhere in MetaDataPkg.tString := null
   );

  /*
   * ExecuteSQL
   */
   procedure ExecuteSQL
   (
      pSQLString in MetaDataPkg.tLongString
   );


  /*
   * SwapObjects
   */
   procedure SwapObjects
   (
      pName in MetaDataPkg.tString,
      pDrop in MetaDataPkg.tString := MetaDataPkg.cNo
   );


  /*
   * RestoreSettings
   */
   procedure RestoreSettings
   (
      pName         in MetaDataPkg.tString,
      pLogging      in MetaDataPkg.tString := MetaDataPkg.cNo,
      pIndexLogging in MetaDataPkg.tString := MetaDataPkg.cNo
   );


  /*
   * FullInsert
   */
   procedure FullInsert
   (
      pName         in MetaDataPkg.tString,
      pWhere        in MetaDataPkg.tString := null,
      pLogging      in MetaDataPkg.tString := MetaDataPkg.cNo,
      pIndexLogging in MetaDataPkg.tString := MetaDataPkg.cNo,
      pDrop         in MetaDataPkg.tString := MetaDataPkg.cNo
   );


  /*
   * FullSQL
   */
   procedure FullSQL
   (
      pName         in MetaDataPkg.tString,
      pSQLString    in MetaDataPkg.tLongString,
      pLogging      in MetaDataPkg.tString := MetaDataPkg.cNo,
      pIndexLogging in MetaDataPkg.tString := MetaDataPkg.cNo,
      pDrop         in MetaDataPkg.tString := MetaDataPkg.cNo
   );


  /*
   * DropObject
   */
   procedure DropObject
   (
     pObjectName  in MetaDataPkg.tString,
     pObjectType  in MetaDataPkg.tString,
     pTableClause in MetaDataPkg.tString := null
   );

  /*
   * LockTable
   */
   procedure LockTable
   (
      pTableName in MetaDataPkg.tString,
      pMode      in MetaDataPkg.tString := 'EXCLUSIVE'
   );



   function GetSwapName
   (
      pName    in MetaDataPkg.tString,
      pSuffix  in MetaDataPkg.tString := cSwapSuffix
   )
   return MetaDataPkg.tString;


   function GetRenameString
   (
      pFromName in MetaDataPkg.tString,
      pToName   in MetaDataPkg.tString,
      pType     in MetaDataPkg.tString,
      pTable    in MetaDataPkg.tString := null
   )
   return MetaDataPkg.tDBString;

end SwapOps;
/

At the top, I defined two constants: cSwapSuffix ('_T') and cTempSuffix ('_X'). That's where you could modify the package if you wanted to use you own scheme for naming swap objects and temp objects.

I used the following procedures to automate the six steps I outlined at the beginning. Their names are self-explanatory:

• The CreateObjects() procedure for Steps 1, 3, and 4
• The ExecuteInsert() or ExecuteSQL() procedures for Step 2
• The SwapObjects() procedure for Step 5
• The RestoreSettings() procedure for Step 6

The following is the difference between the ExecuteInsert() and ExecuteSQL()procedures:

• ExecuteInsert() accepts a string for the WHERE clause of an INSERT statement, constructs that statement, and then executes it.
• ExecuteSQL() is more general. It accepts any full SQL string, and you can use it to execute complex INSERT statements or other non-INSERT SQL statements such as UPDATE.

The SwapOps package also contains two wrapper procedures: FullInsert() and FullSQL(). These procedures call the above procedures—CreateObjects(), ExecuteInsert() or ExecuteSQL(), SwapObjects(), RestoreSettings()—in the right order.

Previous Page

Next Page

• Download The Code for this Article