Re: How to do faster DML

On Sun, Feb 4, 2024 at 9:25 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:

> 1. Load the children before attaching them to the parent.
> 2. Create the child indices, PK and FKs before attaching to the parent.
> 3. Do step 2 in multiple parallel jobs. (cron is your friend.)
> 4. Attach the children to the "naked" (no PK, no FK, no indices) parent.
> 5. Use the ONLY keyword on CREATE INDEX and ALTER TABLE to add the
> indices, PK and FK to the parent *after* step 4.
>
>>
>>
>>
>>
Thank You.

Can you please help me to understand these steps a bit more accurately.

Say we have a parent table already having data in it and also a primary
key defined.We will load the child table first , by dropping the Primary
key and Foreign keys, so that data load will be faster.

Then we will create the primary key index on the child table. When you said
using multiple parallel jobs, do you mean creating the PK index on each
partition separately from different sessions rather than creating on the
table using a single statement or some other faster way to create the PK
index?

Now the last step is attaching the PK and FK of the above child table to
the parent. This will validate each and every row for the uniqueness and
also to ensure the foreign key is present in the parent table. Won't this
take a longer time in this step?