Enable Constraint - Peformance Impact
The below statement consumes a huge amount of time for a table containing 70 million records.
ALTER TABLE <table-name> ENABLE CONSTRAINT <constraint-name>
Does table scan all rows while enabling the constraint.
Even though the constraint got enabled, the process just hung for more than 5 hours.
Any ideas on how this can be optimized
oracle oracle11g ddl database-administration
asked Nov 15 '18 at 18:02
TinyTiny
360730
add a comment |
The below statement consumes a huge amount of time for a table containing 70 million records.
ALTER TABLE <table-name> ENABLE CONSTRAINT <constraint-name>
Does table scan all rows while enabling the constraint.
Even though the constraint got enabled, the process just hung for more than 5 hours.
Any ideas on how this can be optimized
oracle oracle11g ddl database-administration
asked Nov 15 '18 at 18:02
TinyTiny
360730
1
"Does table scan all rows while enabling the constraint?" Yes.
– APC
Nov 15 '18 at 18:28
1
As for optimising it, there probably aren't that many options. Some details would be useful. What sort of constraint is it? Why was it disabled? What happened while it was disabled?
– APC
Nov 15 '18 at 18:29
1
Depends on how the constrains was disabled, see the documentation. But usually - YES - enabling the constraint causes scanning of all rows in order to validate the constraint.
– krokodilko
Nov 15 '18 at 18:29
@APC- it is a part of an existing stored procedure that we can enable this constraint .. It was disabled in order to update test data on some columns.. Post that it was enabled again.. But this step got hung and it took a huge amount of time
– Tiny
Nov 15 '18 at 18:32
1
But what sort of constraint was it? Check constraint? Primary key? Foreign key? And what is the perceived benefit of disabling the constraint before the update compared to doing the update with it enabled?
– APC
Nov 15 '18 at 18:38
add a comment |
The below statement consumes a huge amount of time for a table containing 70 million records.
ALTER TABLE <table-name> ENABLE CONSTRAINT <constraint-name>
Does table scan all rows while enabling the constraint.
Even though the constraint got enabled, the process just hung for more than 5 hours.
Any ideas on how this can be optimized
oracle oracle11g ddl database-administration
asked Nov 15 '18 at 18:02
TinyTiny
360730
The below statement consumes a huge amount of time for a table containing 70 million records.
ALTER TABLE <table-name> ENABLE CONSTRAINT <constraint-name>
Does table scan all rows while enabling the constraint.
Even though the constraint got enabled, the process just hung for more than 5 hours.
Any ideas on how this can be optimized
oracle oracle11g ddl database-administration
oracle oracle11g ddl database-administration
asked Nov 15 '18 at 18:02
TinyTiny
360730
asked Nov 15 '18 at 18:02
TinyTiny
360730
edited Nov 15 '18 at 18:28
Tiny
asked Nov 15 '18 at 18:02
TinyTiny
360730
asked Nov 15 '18 at 18:02
TinyTiny
360730
asked Nov 15 '18 at 18:02
TinyTiny
360730
360730
1
"Does table scan all rows while enabling the constraint?" Yes.
– APC
Nov 15 '18 at 18:28
1
As for optimising it, there probably aren't that many options. Some details would be useful. What sort of constraint is it? Why was it disabled? What happened while it was disabled?
– APC
Nov 15 '18 at 18:29
1
Depends on how the constrains was disabled, see the documentation. But usually - YES - enabling the constraint causes scanning of all rows in order to validate the constraint.
– krokodilko
Nov 15 '18 at 18:29
@APC- it is a part of an existing stored procedure that we can enable this constraint .. It was disabled in order to update test data on some columns.. Post that it was enabled again.. But this step got hung and it took a huge amount of time
– Tiny
Nov 15 '18 at 18:32
1
But what sort of constraint was it? Check constraint? Primary key? Foreign key? And what is the perceived benefit of disabling the constraint before the update compared to doing the update with it enabled?
– APC
Nov 15 '18 at 18:38
add a comment |
1
"Does table scan all rows while enabling the constraint?" Yes.
– APC
Nov 15 '18 at 18:28
1
As for optimising it, there probably aren't that many options. Some details would be useful. What sort of constraint is it? Why was it disabled? What happened while it was disabled?
– APC
Nov 15 '18 at 18:29
1
Depends on how the constrains was disabled, see the documentation. But usually - YES - enabling the constraint causes scanning of all rows in order to validate the constraint.
– krokodilko
Nov 15 '18 at 18:29
@APC- it is a part of an existing stored procedure that we can enable this constraint .. It was disabled in order to update test data on some columns.. Post that it was enabled again.. But this step got hung and it took a huge amount of time
– Tiny
Nov 15 '18 at 18:32
1
But what sort of constraint was it? Check constraint? Primary key? Foreign key? And what is the perceived benefit of disabling the constraint before the update compared to doing the update with it enabled?
– APC
Nov 15 '18 at 18:38
1
1
"Does table scan all rows while enabling the constraint?" Yes.
– APC
Nov 15 '18 at 18:28
"Does table scan all rows while enabling the constraint?" Yes.
– APC
Nov 15 '18 at 18:28
1
1
As for optimising it, there probably aren't that many options. Some details would be useful. What sort of constraint is it? Why was it disabled? What happened while it was disabled?
– APC
Nov 15 '18 at 18:29
As for optimising it, there probably aren't that many options. Some details would be useful. What sort of constraint is it? Why was it disabled? What happened while it was disabled?
– APC
Nov 15 '18 at 18:29
1
1
Depends on how the constrains was disabled, see the documentation. But usually - YES - enabling the constraint causes scanning of all rows in order to validate the constraint.
– krokodilko
Nov 15 '18 at 18:29
Depends on how the constrains was disabled, see the documentation. But usually - YES - enabling the constraint causes scanning of all rows in order to validate the constraint.
– krokodilko
Nov 15 '18 at 18:29
@APC- it is a part of an existing stored procedure that we can enable this constraint .. It was disabled in order to update test data on some columns.. Post that it was enabled again.. But this step got hung and it took a huge amount of time
– Tiny
Nov 15 '18 at 18:32
@APC- it is a part of an existing stored procedure that we can enable this constraint .. It was disabled in order to update test data on some columns.. Post that it was enabled again.. But this step got hung and it took a huge amount of time
– Tiny
Nov 15 '18 at 18:32
1
1
But what sort of constraint was it? Check constraint? Primary key? Foreign key? And what is the perceived benefit of disabling the constraint before the update compared to doing the update with it enabled?
– APC
Nov 15 '18 at 18:38
But what sort of constraint was it? Check constraint? Primary key? Foreign key? And what is the perceived benefit of disabling the constraint before the update compared to doing the update with it enabled?
– APC
Nov 15 '18 at 18:38
add a comment |
1 Answer
1
active
oldest
votes
As guys said before, depends on constrain type it is possibility skip validate existing data by ALTER TABLE ENABLE NOVALIDATE CONSTRAINT . And check this data by some additional procedure or query.
You can find documentation about that here https://docs.oracle.com/cd/B28359_01/server.111/b28310/general005.htm#ADMIN11546
edited Nov 15 '18 at 21:50
marc_s
582k13011241269
answered Nov 15 '18 at 21:38
Yevhen ZhovtonohYevhen Zhovtonoh
1764
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53325428%2fenable-constraint-peformance-impact%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
As guys said before, depends on constrain type it is possibility skip validate existing data by ALTER TABLE ENABLE NOVALIDATE CONSTRAINT . And check this data by some additional procedure or query.
You can find documentation about that here https://docs.oracle.com/cd/B28359_01/server.111/b28310/general005.htm#ADMIN11546
edited Nov 15 '18 at 21:50
marc_s
582k13011241269
answered Nov 15 '18 at 21:38
Yevhen ZhovtonohYevhen Zhovtonoh
1764
add a comment |
As guys said before, depends on constrain type it is possibility skip validate existing data by ALTER TABLE ENABLE NOVALIDATE CONSTRAINT . And check this data by some additional procedure or query.
You can find documentation about that here https://docs.oracle.com/cd/B28359_01/server.111/b28310/general005.htm#ADMIN11546
edited Nov 15 '18 at 21:50
marc_s
582k13011241269
answered Nov 15 '18 at 21:38
Yevhen ZhovtonohYevhen Zhovtonoh
1764
add a comment |
As guys said before, depends on constrain type it is possibility skip validate existing data by ALTER TABLE ENABLE NOVALIDATE CONSTRAINT . And check this data by some additional procedure or query.
You can find documentation about that here https://docs.oracle.com/cd/B28359_01/server.111/b28310/general005.htm#ADMIN11546
edited Nov 15 '18 at 21:50
marc_s
582k13011241269
answered Nov 15 '18 at 21:38
Yevhen ZhovtonohYevhen Zhovtonoh
1764
As guys said before, depends on constrain type it is possibility skip validate existing data by ALTER TABLE ENABLE NOVALIDATE CONSTRAINT . And check this data by some additional procedure or query.
You can find documentation about that here https://docs.oracle.com/cd/B28359_01/server.111/b28310/general005.htm#ADMIN11546
edited Nov 15 '18 at 21:50
marc_s
582k13011241269
answered Nov 15 '18 at 21:38
Yevhen ZhovtonohYevhen Zhovtonoh
1764
edited Nov 15 '18 at 21:50
marc_s
582k13011241269
edited Nov 15 '18 at 21:50
marc_s
582k13011241269
edited Nov 15 '18 at 21:50
marc_s
582k13011241269
582k13011241269
answered Nov 15 '18 at 21:38
Yevhen ZhovtonohYevhen Zhovtonoh
1764
answered Nov 15 '18 at 21:38
Yevhen ZhovtonohYevhen Zhovtonoh
1764
answered Nov 15 '18 at 21:38
Yevhen ZhovtonohYevhen Zhovtonoh
1764
1764
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53325428%2fenable-constraint-peformance-impact%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
"Does table scan all rows while enabling the constraint?" Yes.
– APC
Nov 15 '18 at 18:28
1
As for optimising it, there probably aren't that many options. Some details would be useful. What sort of constraint is it? Why was it disabled? What happened while it was disabled?
– APC
Nov 15 '18 at 18:29
1
Depends on how the constrains was disabled, see the documentation. But usually - YES - enabling the constraint causes scanning of all rows in order to validate the constraint.
– krokodilko
Nov 15 '18 at 18:29
@APC- it is a part of an existing stored procedure that we can enable this constraint .. It was disabled in order to update test data on some columns.. Post that it was enabled again.. But this step got hung and it took a huge amount of time
– Tiny
Nov 15 '18 at 18:32
1
But what sort of constraint was it? Check constraint? Primary key? Foreign key? And what is the perceived benefit of disabling the constraint before the update compared to doing the update with it enabled?
– APC
Nov 15 '18 at 18:38