Skip to Main Content
  • Questions

  • Does Foreign Key constraint (No Validate) impact on performance?

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Rajendra.

Asked: September 22, 2017 - 12:14 pm UTC

Last updated: September 23, 2017 - 4:07 am UTC

Version: 11G

Viewed 1000+ times

You Asked

We have imported data from PROD to UAT environment. While import foreign key constraints enabled as No Validate. Both the tables parent table and child tables have huge number of rows.

How can we tune the queries referencing to these two tables, since whenever we are using these two tables in join it is taking considerable time to fetch the rows.

Thanks in Advance,

Rajendra Sowani.

and Connor said...

Constraints that are not validated can change the way your queries are executed.

See this article for some examples why

http://www.oracle.com/technetwork/testcontent/o39asktom-096149.html

So you can either do:

alter table XXX modify constraint enable validate

(which does not lock the table)

or look at setting the RELY property, which is you telling the database that even though validation has not been done, the database can "rely" on the data being valid.

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.