For primary keys there's an implicit NOT NULL on each column, and Oracle will
do full tablescan for each column in turn before doing the tablescan and sort
that creates the index. If they're very large tables being load in 11g then
possibly all three scans will be direct path. Nevertheless I would have
thought that sorting for the mandatory index for an enabled PK / UK would be
the biggest time component.
(See: https://jonathanlewis.wordpress.com/2016/01/27/add-primary-key/ for ;
confirmation of the excess tablescans).
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Toon Koppelaars [toon@xxxxxxxxxxx]
Sent: 23 May 2016 10:25
To: oracle@xxxxxxxxxxxxxxx
Cc: oracle-l-freelists
Subject: Re: Enable novalidate contains after import?
The unique and primary key constraints are backed by (unqiue) indexes. These
will be created anyways. I guess (but did not investigate) that's why Oracle
enables these constraints in enable mode.
So you're waiting for the indexes to be created. Once they are there,
adding/enabling the uk/pk constraints shouldn't take much time.
On Mon, May 23, 2016 at 11:17 AM, Norman Dunbar
<oracle@xxxxxxxxxxxxxxx<mailto:oracle@xxxxxxxxxxxxxxx>> wrote:
This is most likely not possible, at least according to the docs and a few
experiments that I've carried out.
I'm still migrating that Solaris 9i database to Azure on llg. I have huge
tables with over half a billion rows, primary key, unique and referential
integrity constraints.
Data are from production where it has been validated by these constraints
already, so I should be safe to enable the contains with novalidate in the
receiving database.
Prior to running 9i exp, I set the three types of constraints to novalidate and
exported. On import I see that while the referential integrity constraints do
get enabled novalidate, the unique and primary key one are always enabled in
validate mode, so the whole table is scanned to ensure that it is OK. This
wastes a good many hours doing checks that have, effectively, already been done
on the source database.
Does anyone know of a way to have the imp create these constraints but in
novalidate mode as per the source database?
I'm tempted to run a show=y constraints=y (indexes=n maybe) to generate a
script but that would require editing, or me writing a parser to edit it, to
get the constraints sql out and changed to novalidate. If nothing else exists.
I need to shave as much time as possible off the imports. So far I'm down from
over 89 hours to 34. I need more (less ) if possible.
Cheers,
Norm.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
--
Toon Koppelaars
RuleGen BV
Toon.Koppelaars@xxxxxxxxxxx
www.RuleGen.com<http://www.RuleGen.com>
TheHelsinkiDeclaration.blogspot.com<http://TheHelsinkiDeclaration.blogspot.com>
(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13<http://www.RuleGen.com/pls/apex/f?p=14265:13>
