Re: Importing large PS_JOB table with check constraints

I see you are using Peopleosft with unicode enabled. But that is beside the point. Is there a particular reason for using export/import to refresh the development and testing databases rather than a databases clone - or better stil "rman duplicate"? Not only are the latter a lot faster than export/import, "rman duplicate" can test your recoverability at the same time.

Lou Avrami wrote:
> 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
>

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l