Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Sequence and INSERT ALL INTO with parallelism problem

511898Jan 17 2007 — edited Jan 19 2007
I want to use the INSERT ALL INTO style of multiple-inserts (with 10gR2) to normalize a wide table into a skinny table. The large table has a few fk columns and 100 numeric columns. I want to insert them into another table as 100 rows in a single SQL pass.

CREATE TABLE WIDE_SOURCE(
FKEY_1 INTEGER,
FKEY_2 INTEGER,
FKEY_3 INTEGER,
AMT001 INTEGER,
AMT002 INTEGER,
...
AMT100 INTEGER);

CREATE TABLE SKINNY_DEST(
PKEY INTEGER NOT NULL,
FKEY_1 INTEGER,
FKEY_2 INTEGER,
FKEY_3 INTEGER,
AMT INTEGER,
CONSTRAINT PK_SKINNY_DEST PRIMARY KEY(PKEY)
);

CREATE SEQUENCE KEY_GEN MINVALUE 1 INCREMENT BY 100 START WITH 1;

INSERT ALL
INTO SKINNY_DEST VALUES(KEY_GEN.NEXTVAL+001, FKEY_1, FKEY_2, FKEY_3, AMT001)
INTO SKINNY_DEST VALUES(KEY_GEN.NEXTVAL+002, FKEY_1, FKEY_2, FKEY_3, AMT002)
INTO SKINNY_DEST VALUES(KEY_GEN.NEXTVAL+003, FKEY_1, FKEY_2, FKEY_3, AMT003)
...
INTO SKINNY_DEST VALUES(KEY_GEN.NEXTVAL+100, FKEY_1, FKEY_2, FKEY_3, AMT100)
SELECT * FROM WIDE_SOURCE;

With this query, each row in WIDE_SOURCE will generate 100 rows in SKINNY_DEST. I know that works. Unfortunately, it appears that the sequence isn't working the way I thought it should- with multiple nodes and parallel insert, there's some overlap in the generated key.

Is there a better way? I've considered 100 separate sequences, but that just seems plain silly. Am I trying to have my cake (the parallelism) and eat it too (by disregarding limitations of Oracle's sequence construct)?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 16 2007
Added on Jan 17 2007
7 comments
1,598 views