Re: Importing large PS_JOB table with check constraints

From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
To: avramil@xxxxxxxxxxxxxx
Date: Mon, 11 Jul 2005 14:12:11 -0600

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

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


--
Regards

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

References:
- Importing large PS_JOB table with check constraints
  - From: Lou Avrami

Re: Importing large PS_JOB table with check constraints

Other related posts: