How to skip constraints validation for a transaction permanently
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to skip constraints validation for a transaction permanently

  1. #1
    Join Date
    Jan 2015
    Posts
    3

    How to skip constraints validation for a transaction permanently

    Below is the techinical issue that I am trying to see if there is a way out: (I simplified the issue as much as I could to give you overall idea. )

    Platform:
    Oracle Version: 11.2.0.4
    OS: CentOS 6.4

    There are two schemas named SCH01 and SCH02 on databases DB01 and DB02 respectively. The schemas are totally identical and their definition match. The data in the schemas differ though. SCH01 schema has a table named DATA01 with ~10GB data, 23 indexes and 40 check constraints. The requirement is to copy some data (filtered out by predicates) from DATA01 table in SCH01 to DATA01 table in SCH02 schema and update one column value in SCH02.

    Only way that worked out so far is: (there are definitely other several ways like export/import etc but they have limitations in our case)
    1) set constraints deferred
    2) disable triggers on the target table
    3) select from source table and insert into destination table
    4) commit
    5) enable

    Observation: If the whole process takes 2hrs I can see it spending 1hr doing step 4 (i.e. commit). It is validating the constraints etc at this step.

    Issue: The data from the source schema is already trusted and validated against all constraints. Is there a way to skip constraint check when copying (only for the copy transaction) the data to destination schema? We can't disable all constraints on the destination permanently or even for few hours as the destination is also a live database and data needs to be validated which comes via other sessions. Set constraints deferred will not work as well as it will still do the constraint validation during commit.

    Any suggestions on how to skip constraint validation totally while doing commit (step 4 only from above)? Basically the question drills down to How to skip constraint check within a transaction?

    Thanks for reading.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,086
    Ideally you should be able to do a merge statement to update the data that you need to update without disabling
    any constraints. You may also want to look at the constraints and indexes and see if there are any indexes that
    can be combined, or constraints that can be dropped.

    23 indexes on a tables is a lot of indexes. How many of the indexes are on a single column. Are there combinations
    of columns in indexes that are redundant? Reducing the number of indexes should help, provided that you don't
    cause existing queries to take significantly longer.
    this space intentionally left blank

  3. #3
    Join Date
    Jan 2015
    Posts
    3
    >>Thanks for the reply
    Quote Originally Posted by gandolf989 View Post
    Ideally you should be able to do a merge statement to update the data that you need to update without disabling
    any constraints. You may also want to look at the constraints and indexes and see if there are any indexes that
    can be combined, or constraints that can be dropped.
    >>True but it is not happening any fast. Merge also tries to validate the data against constraints. The data that is being pushed is already validated against the same constraints at the source and there is no need for another validation at the target. But we can't disable the constraints permanently (db level) at the target as it is a live DB as well. It would be ideal if there is a way to disable constraints for the whole transaction.

    The highlevel steps we are trying to achieve: (I wish there is a way to accomplish this)
    1) start the copy transaction from source to target
    2) first thing we want this transaction to do is something like - disable all constraints at the target
    3) merge or insert or any other way to copy the data from source to target
    4) commit without validating the data against constraints in the target
    5) exit
    Note: During this whole process the constraints on the database should be enabled and validated for all other live transactions.

    23 indexes on a tables is a lot of indexes. How many of the indexes are on a single column. Are there combinations
    of columns in indexes that are redundant? Reducing the number of indexes should help, provided that you don't
    cause existing queries to take significantly longer.
    >>Sorry, I quoted wrong in my initial text and this table has 23 constraints and 8 indexes

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,086
    Going back to the title the only way to permanently skip constraint validation is to not have constraints.
    But that would leave you open to logical corruption. Hence, good database design is the best way to
    make your application work well. There are no short cuts...
    this space intentionally left blank

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,086
    Going back to the title the only way to permanently skip constraint validation is to not have constraints.
    But that would leave you open to logical corruption. Hence, good database design is the best way to
    make your application work well. There are no short cuts...
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width