dcsimg
Login | Register   
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


advertisement
 

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

Perform the massive DELETE operation as a swap delete with parallel execution. This technique can offer significant performance gains over a straight or in-place delete operation.


advertisement

WEBINAR:

On-Demand

Application Security Testing: An Integral Part of DevOps


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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date