This short article will focus on simple hints/list to help you make decision how to manipulate large data in Oracle for both DML and DDL operations using parallel option.

If you want to execute DML for large number of rows on a table in Oracle using parallel option you should consider/check following things:

• use DIRECT mode to generate smaller redo logs
• turn on NOLOGGING option to generate smaller archivelogs
• activate parallel option for DML
• set UNUSABLE state for indexes and rebuild them in parallel later
• disable constraints before load and enable after load
• instead of DML execute DDL
• turn off triggers on the table you are trying to manipulate
• verify explain plan before execution
• check if compression is turned on the table you are trying to manipulate
• verify instance parameters for parallel option
• specify partition/subpartition name to avoid hangs for independent loads to the same table
• partition manipulated objects to calculate data in memory instead in TEMP tablespace

If you want to execute DDL in Oracle in parallel you should consider/check following things:

• use DIRECT mode to generate smaller redo logs
• activate parallel option
• turn on NOLOGGING option
• verify explain plan before execution
• verify instance parameters for parallel option
• partition input objects to calculate data in memory instead in TEMP tablespace

Have a fun 🙂

Tomasz