anat0lius
anat0lius

Reputation: 2265

Tips to migrate huge volume of data from one table to another?

We have to split one table into two.

Our currently working script looks like this:

  1. disable the constraints of the new tables
  2. INSERT INTO newTable1 SELECT... FROM oldTable
  3. INSERT INTO newTable2 SELECT... FROM oldTable WHERE fieldX IS NOT NULL
  4. re-enable constraints

In the mean time we also investigate the usage of parallelism (so far in our tests there were no gains for some reason...).

Apart from that, what else could we use to make a safe and fast migration in PROD?

Upvotes: 2

Views: 655

Answers (1)

Jon Heller
Jon Heller

Reputation: 36798

The two main tools for improving large table insert performance are direct-path writes and parallelism, and they work well together.

Direct-path writes put the data directly in the data files and bypass creating extra REDO and UNDO data. This features significantly reduces the IO, but it means that your new table is not recoverable until after your next full backup.

Parallelism can use extra CPU and IO resources to dramatically improve performance. But as pmdba said, there are lots of weird requirements and reasons why parallelism won't help. The most common issues are using the hints incorrectly and not using direct-path writes. Parallel reads are easy, but to enable parallel writes, direct-path writes are required.

Before you spend a day trying to read and understand the Using Parallel Execution chapter of the VLDB and Partitioning Guide, there is a simple way to try and test these two features. If you're lucky, all you need is the below code, and you may not even need to bother disabling and re-enabling contraints:

alter session enable parallel dml;
insert /*+ parallel(8) append */ into newtable select * from oldtable;
commit;

If that doesn't work, look at the explain plan, and you should see something like this:

alter session enable parallel dml;
explain plan for insert /*+ parallel(8) append */ into newtable select * from oldtable;
select * from table(dbms_xplan.display);


Plan hash value: 1569336347
 
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |          |     1 |    13 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                    |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000 |     1 |    13 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| NEWTABLE |       |       |            |          |  Q1,00 | PCWP |            |
|   4 |     OPTIMIZER STATISTICS GATHERING |          |     1 |    13 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR             |          |     1 |    13 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL            | OLDTABLE |     1 |    13 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------------------
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 8 because of hint

Look for four key items in the explain plan: "LOAD AS SELECT" instead of "LOAD CONVENTIONAL" means you are using direct path writes, the "PX" operation above the "TABLE ACCESS FULL" implies you are using parallel reads, the "PX" operation above the "LOAD AS SELECT" implies you are using parallel writes, and the "Degree of Parallelism" tells you how many parallel threads are requested.

If any of those items are missing, investigate the "Note" section and it will often explain why you didn't get what you asked for. If you're still having problems, post the full explain plan as text and we can help troubleshoot the issue.

Upvotes: 2

Related Questions