Adding Unique Index
A reader, March 09, 2017 - 11:34 am UTC
Hi Chris,
I tried creating Unique Index on table (table don't have any index).
But due to billions of records that table currently holding, it takes significant time to create composite unique constraint!!
Without Index option - Tried to code to restrict dupliactes also take time..
Confused how to get rid of this.
AMy be I am asking silly questions here, but could you suggest from your past experience how to tackle such cases.
March 09, 2017 - 1:40 pm UTC
You could try:
- Creating a normal index on your columns (with the online option)
- Then add the unique constraint, using this index and the novalidate option
Adding an unvalidated constraint is "instant" and will prevent new duplicates arriving in the table. You can then weed out the existing duplicates however you choose.
You can read more about this at:
https://blogs.oracle.com/sql/entry/how_to_find_and_delete Or you could look at re-creating the table as suggested below.
Unique constraint on monster table
John Hawksworth, March 09, 2017 - 12:04 pm UTC
When enforcing a unique requirement, always do it with a constraint, don't make the index yourself. That way the database will maintain the index behind the unique constraint, and automatically drop it with the constraint. Six months or a year from now, who will even know it exists, let alone think to remove it? Who will know what the index does and why it was made in the first place? I once spent a frustrating day trying to drop a unique constraint and failing. Someone had created an index manually, and that was left behind, after the constraint had been dropped.
Do you have not null constraints on each column of the constraint? If not, then do you know for certain what the behaviour will be if a null occurs in one or more column?
In order to apply the constraint, you'll first have to check for and remove duplicates from those billions of rows. Once again, if there's a null in one of the 4 rows, and the other three are identical, does this count as a duplicate?
I suggest that you look at "Create table as select" (CTAS) for cleaning up duplicates. The general approach is to make a copy using CTAS, delete from the source, and insert back into the source from the CTAS copy, removing duplicates as you go.
If your table is partitioned then consider processing on a partition basis, and doing partition swapping instead of huge DML statements.
All the best in your endeavours!
March 09, 2017 - 1:44 pm UTC
If you want to be sure of dropping the index supporting a unique constraint, use the drop index clause:
alter table ... drop constraint ... drop index
While it's "better" to let Oracle Database create the unique index in some cases it's impractical. Or a non-unique index already exists for other reasons. In which case it's much quicker to use that.
Good point on null. I'm not sure how partition swapping will help though. A value could be unique within a partition. But still appear in all the others! If you tackle them one-by-one you'll still be left with duplicates.
followup
A reader, March 10, 2017 - 1:22 pm UTC
"I'm not sure how partition swapping will help though. A value could be unique within a partition. But still appear in all the others!"
unless the index is global
March 10, 2017 - 4:28 pm UTC
That doesn't help you remove duplicates.
why not
A reader, March 10, 2017 - 4:35 pm UTC
why not since i am using /*+ ignore_row_on_dupkey_index(unique_cust) */
Unique constraint on monster table - 2
John Hawksworth, March 10, 2017 - 7:06 pm UTC
You're quite right, the partitioning won't help with finding duplicates. I suspect that if the table in question was partitioned by one of the four columns then it might.
I was just thinking of partition swapping as a fast way of "moving" the de-duplicated table back into the original.
I hadn't thought it through enough, so good catch Chris.