is there a way to alter index to unique ??
The poster asked whether an existing index can be altered to UNIQUE (or to clustered), wanting to drop the unique index, bulk-load data that may contain duplicates, rebuild the index, delete duplicates, then make it unique again. Replies said there's no ALTER-to-unique step needed: instead use a unique constraint/index together with START VIOLATIONS and filtering mode, so non-conforming rows land in the violations table. Art Kagel gave two recipes — either drop the index, load, START VIOLATIONS and recreate the UNIQUE index WITH FILTERING, or keep the index, START VIOLATIONS plus SET INDEXES ... TO FILTERING before loading — then delete the duplicates listed in the violations table. No follow-up confirmation from the poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi everyone, i have a question: Is there a way to modify or alter a index to unique?? This is because we are going to load data on tables where we could insert duplicated rows, si eliminate those rows, we would like to: 1- drop unique index 2- load the data 3- create index 4- Eliminate duplicated rows using the index 5- Alter index to unique Is there a way to alter an index to cluster?? Thanks in advanced. Note: I tried to use the set indexes disabled and then set indexes enabled, but i thought i could use the filtering option to eliminate the duplicated rows from the table, but just put them in a table it does not delete them from table.
You could do that little bit differently, as follows: 1) create a unique constraint 2) start violations table 3) load the data 4) violations table will contain the nonconforming rows. more details - http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqls.doc/ids _sqs_1211.htm HTH, Nilesh. ids-bounces@iiug.org wrote on 06/23/2009 04:31:53 PM: > [image removed] > > is there a way to alter index to unique ?? [16132]> > > LYNKZ MIKE > > to: > > ids > > 06/23/2009 04:34 PM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > Hi everyone, i have a question: Is there a way to modify or alter a index to > unique?? > > This is because we are going to load data on tables where we could insert > duplicated rows, si eliminate those rows, we would like to: > > 1- drop unique index > 2- load the data > 3- create index > 4- Eliminate duplicated rows using the index > 5- Alter index to unique > > Is there a way to alter an index to cluster?? > > Thanks in advanced. > > Note: > I tried to use the set indexes disabled and then set indexes enabled, but i > thought i could use the filtering option to eliminate the duplicatedrows from > the table, but just put them in a table it does not delete them from table. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
You could do that little bit differently, as follows: 1) create a unique constraint 2) start violations table 3) load the data 4) violations table will contain the nonconforming rows. more details - http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqls.doc/ids _sqs_1211.htm HTH, Nilesh. ids-bounces@iiug.org wrote on 06/23/2009 04:31:53 PM: Thanks for answer, that would slow the load?? because we would create a unique constraint while we are loading the data??? Thanks in advanced
Do it this way instead: Two scenarios: I: 1. Drop unique index 2. Load the data 3. START VIOLATIONS on the table. 4. Recreate the UNIQUE index WITH FILTERING. 5. Delete the duplicates from the violations tables. Scenario II is the same but you: 1. Keep the index in place, 2. START VIOLATIONS 3. Load the data 4. Delete the duplicates from the violations tables Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jun 23, 2009 at 5:31 PM, LYNKZ MIKE <yellr@telecom.com.co> wrote: > Hi everyone, i have a question: Is there a way to modify or alter a index > to > unique?? > > This is because we are going to load data on tables where we could insert > duplicated rows, si eliminate those rows, we would like to: > > 1- drop unique index > 2- load the data > 3- create index > 4- Eliminate duplicated rows using the index > 5- Alter index to unique > > Is there a way to alter an index to cluster?? > > Thanks in advanced. > > Note: > I tried to use the set indexes disabled and then set indexes enabled, but i > thought i could use the filtering option to eliminate the duplicated rows > from > the table, but just put them in a table it does not delete them from table. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5a57b2ee244046d0c3965
Oops, in II I forgot you have to use SET INDEXES <indexname> TO FILTERING; before loading the data. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jun 23, 2009 at 7:19 PM, Art Kagel <art.kagel@gmail.com> wrote: > Do it this way instead: > > Two scenarios: > > I: > > 1. Drop unique index > 2. Load the data > 3. START VIOLATIONS on the table. > 4. Recreate the UNIQUE index WITH FILTERING. > 5. Delete the duplicates from the violations tables. > > Scenario II is the same but you: > > 1. Keep the index in place, > 2. START VIOLATIONS > 3. Load the data > 4. Delete the duplicates from the violations tables > > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Oninit, the IIUG, nor any other > organization with which I am associated either explicitly or implicitly. > Neither do those opinions reflect those of other individuals affiliated > with any entity with which I am affiliated nor those of the entities > themselves. > > > > > On Tue, Jun 23, 2009 at 5:31 PM, LYNKZ MIKE <yellr@telecom.com.co> wrote: > >> Hi everyone, i have a question: Is there a way to modify or alter a index >> to >> unique?? >> >> This is because we are going to load data on tables where we could insert >> duplicated rows, si eliminate those rows, we would like to: >> >> 1- drop unique index >> 2- load the data >> 3- create index >> 4- Eliminate duplicated rows using the index >> 5- Alter index to unique >> >> Is there a way to alter an index to cluster?? >> >> Thanks in advanced. >> >> Note: >> I tried to use the set indexes disabled and then set indexes enabled, but >> i >> thought i could use the filtering option to eliminate the duplicated rows >> from >> the table, but just put them in a table it does not delete them from >> table. >> >> >> >> ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --001636c59879cd8dca046d0c4073