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?
