RE: Enable novalidate contains after import?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 23 May 2016 14:50:27 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282D50BB_at_EXMBX01.thus.corp>


Norman,

Looking at those figures, I would have expected 9 rather than 10 - but perhaps the 10th is a tablescan to generate stats.

Having said that, I've just created a table with PK, 4 other non-null columns, and basic stats gathered in 9i; then done an export, then imported to 11g and according to a session stats collection taken in a logoff trigger did just one tablescan for the entire import.

Are your not null constraints column declarations, or standard check constraints ? If the latter then you might be getting 8 scans for the check constraints, the ninth to add the not null declaration to the PK column and the 10th to create the PK index.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle



From: Norman Dunbar [oracle_at_dunbar-it.co.uk] Sent: 23 May 2016 14:56
To: Jonathan Lewis; oracle-l-freelists
Subject: RE: Enable novalidate contains after import?

Hi Jonathan,

Thanks.

I've had a check on a couple of tables using what turned up in longops. 10 full scans for a table with a single column pk constraint and 7 further not null constraints. I think that should make 9 full scans in total, if I've read your reply and blog post correctly?

I don't have any unique constraints ts on the table, and all indexes were precreated by the data import. The constraints are being created at the end with a norows dump file and everything possible set to N on the imp, except for constraints=Y.

They show=Y import was a disaster. 25mb of dump file created over 12 gb of output before I killed it. Most of the output is 'REM ' (2 Spaces after REM). For some reason. Another problem to figure out!

Cheers,
Norm.
--

Sent from my Android device with K-9 Mail. Please excuse my brevity.

--

http://www.freelists.org/webpage/oracle-l Received on Mon May 23 2016 - 16:50:27 CEST

Original text of this message