Importing large PS_JOB table with check constraints

From: Lou Avrami <avramil_at_concentric.net>
Date: Mon, 11 Jul 2005 15:11:46 -0400 (EDT)
Message-ID: <200507111911.PAA15619@captain.cnc.net>

Hello all,

I'm currently supporting a bunch of PeopleSoft 8.4x instances. Periodically we need to refresh our development and testing instances with production data. As part of this refresh process, I will take a production export dump and import it into the database to be refreshed.

A large portion of the time spent during the import process involves a single table, PS_JOB.

If you're not familiar with the PS_JOB table in a PeopleSoft implementation, it's a table that can have 200+ columns, with each column having a check constraint:

    CONSTRAINT "SYS_C001101956" CHECK(LENGTH(EMPLID)<=11),
    CONSTRAINT "SYS_C001101957" CHECK(LENGTH(DEPTID)<=10),
    CONSTRAINT "SYS_C001101958" CHECK(LENGTH(JOBCODE)<=6),
    CONSTRAINT "SYS_C001101959" CHECK(LENGTH(POSITION_NBR)<=8),
    CONSTRAINT "SYS_C001101960" CHECK(LENGTH(APPT_TYPE)<=1),
    CONSTRAINT "SYS_C001101961"

CHECK(LENGTH(POSITION_OVERRIDE)<=1), CONSTRAINT "SYS_C001101962"
CHECK(LENGTH(POSN_CHANGE_RECORD)<=1), CONSTRAINT "SYS_C001101963" CHECK(LENGTH(EMPL_STATUS)<=1), CONSTRAINT "SYS_C001101964" CHECK(LENGTH(ACTION)<=3),
....
....

It can take several hours for PS_JOB to be imported. It appears that most of the time is spent verifying the 2 million+ records against all of the check constraints.

I'm hoping that folks on the list can suggest some ways to speed up the import of PS_JOB.

I'm already doing an export with the options

direct=y
recordlength=65535

and importing with the same recordlength variable.

I was thinking of doing two imports:

     imp constraints=n indexes=n
     imp rows=n constraints=y indexes=y

In the end, would these actually same time?

Any other suggestions would be greatly appreciated.

Thanks,
Lou Avrami

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 11 2005 - 14:13:46 CDT