Duplicate Oracle constraints

Remove duplicate Oracle constraints in PeopleSoft Unicode databases

Problem

• Ever used the Oracle import tool to load data into an existing table ?
• .....and you are running a PeopleSoft Unicode database ?

Then chances are you have duplicate constraints on your columns that unnecessarily slow down your database on write operations !

Solution

This small script will find the duplicates and generate another script to remove them. Run using the owner of the database objects, usually SYSADM (check PS.PSDBOWNER)....

js_delete_duplicate_constraints.sql (application/octet-stream, 3.9 kB, info)

Sample run

SQL*Plus: Release 9.2.0.1.0 - Production on Sa Jun 19 18:46:25 2004
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected.
drop table js_constraints
           *
ERROR at line 1:
ORA-00942: table or view does not exist


Table created.
PL/SQL procedure successfully completed.
Index created.

drop table js_constraints_min
* ERROR at line 1: ORA-00942: table or view does not exist

Table created.
1234 rows updated.
1134 rows deleted.

'ALTERTABLE'||C.OWNER||'.'||
-----------------------------------------------------------------------------
alter table SYSADM.PS_JOB drop constraint SYS_C00646416 --dup SYS_C00641318 /
alter table SYSADM.PS_JOB drop constraint SYS_C00646417 --dup SYS_C00641319 /
alter table SYSADM.PS_JOB drop constraint SYS_C00646418 --dup SYS_C00641320 /
alter table SYSADM.PS_JOB drop constraint SYS_C00646419 --dup SYS_C00641321 /
.
.
.
123 rows selected.

You can safely ignore those errors when trying to drop tables, they are just temporary tables used for ths script.

Run edit the generated SQL script C:\js_drop_duplicate_constraints.sql, remove all the non ALTER TABLE ... DROP lines and run it:

alter table SYSADM.PS_JOB drop constraint SYS_C00646416 --dup SYS_C00641318 /
alter table SYSADM.PS_JOB drop constraint SYS_C00646417 --dup SYS_C00641319 /
alter table SYSADM.PS_JOB drop constraint SYS_C00646418 --dup SYS_C00641320 /
alter table SYSADM.PS_JOB drop constraint SYS_C00646419 --dup SYS_C00641321 /
Table altered.
Table altered.
Table altered.
Table altered.

You will notice a considerable performance improvement difference on write operations - as a results of a massive decrease of Oracle recursive calls. And don't forget to use CONSTRAINTS=N next time you import data into an existing PeopleSoft table.

Background

PeopleTools 8.1x and 8.2x generates Oracle column definitions for VARCHAR2 column that are three times the length of the PeopleSoft field definitions to hold those funny characters that are represented in Unicode in by more than one byte. In extreme cases your Unicode text fields in bytes may be three times the number of characters in that field. Even with one 2-byte character a 3 character text field may be 4 bytes !

However, PeopleSoft also creates an Oracle check constraint on the same column to warrant that the actual length in characters in that columns never exceeds the length defined for that field in Application Designer. If you import into an existing table using the Oracle import tool the default for constraints is to import them (CONSTRAINTS=Y, see imp -?). So unless you explicitly set CONSTRAINTS=N during an Oracle import, those constraints will be imported on top of the already existing constraints for that column. Do that a few times and you will notice that your import runs a bit slower every time - that's because of adding the same constraint over and over again. Granted this does not happen too often in a live system but I have come across training databases where the data was refreshed regularly using TRUNCATE TABLE and Oracle import where the same constraint existed 18 times !