Oracle Enterprise Disable Constraints Option
Posted: Tue Aug 12, 2014 7:49 am
The Oracle Enterprise Stage Properties panel offers a "Disable Constraints" option. It also offers the Index Mode options of Rebuild and Maintain. I am loading to an Oracle table with a Primary Key Constraint and of course a Unique index. When I set Disable Constraints = True, the log states "Table contains no constraints that need to be disabled before loading the table." When I try to eliminate the Index Mode option I get a fatal error "Indexes on table preclude direct parallel loading unless an index option is included. Add an index option or drop indexes and rerun step." Oracle documentation states that "When the database is using a UNIQUE or PRIMARY KEY index to enforce a constraint, and constraints associated with that index are dropped or disabled, the index is dropped, unless you specify otherwise."
So... IF the Disable Constraints option is behaving as Oracle says it does, I should not need an Index Mode option. And I don't understand why DataStage responds to the Disable Constraints option with "Table contains no constraints.....".
All this has come up because the DBA is requesting that rather than rebuild the indexes on our loads, we drop and recreate them. I do not understand why and am in the process of figuring out if that is even possible and to help make my argument against such a request. It seems to me that a constraint is there for a purpose and that there is not a good reason to circumvent it. On the other hand, it seems DS is not behaving consistently with what I would expect, as described above. Any observations or explanations from any of you experts?
So... IF the Disable Constraints option is behaving as Oracle says it does, I should not need an Index Mode option. And I don't understand why DataStage responds to the Disable Constraints option with "Table contains no constraints.....".
All this has come up because the DBA is requesting that rather than rebuild the indexes on our loads, we drop and recreate them. I do not understand why and am in the process of figuring out if that is even possible and to help make my argument against such a request. It seems to me that a constraint is there for a purpose and that there is not a good reason to circumvent it. On the other hand, it seems DS is not behaving consistently with what I would expect, as described above. Any observations or explanations from any of you experts?