| Disabling unique constraints and data warehousing query [message #639674] |
Tue, 14 July 2015 02:41  |
 |
GusGF
Messages: 10
Registered: July 2015
Location: UK
|
Junior Member |
|
|
In some material I was reading there was the following text below which left me puzzled. I don't understand what the author was getting at. How can a unique index be considered as costly in comparison to not having any index at all as is the case below? Surely this would mean queries would not have the advantage of an index and DML would be severely limited if at all.
EXCERPT
ALTER TABLE sales ADD CONSTRAINT sales_uk
UNIQUE (prod_id, cust_id, promo_id, channel_id, time_id) DISABLE VALIDATE;
This statement creates a unique constraint, but, because the constraint is disabled, a unique index is not required. This approach can be advantageous for many data warehousing environments because the constraint now ensures uniqueness without the cost of a unique index.
However, there are trade-offs for the data warehouse administrator to consider with DISABLE VALIDATE constraints. Because this constraint is disabled, no DML statements that modify the unique column are permitted against the sales table.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
