Re: Was: Rebuilding Indexes, Now: KEEP INDEX
but if you direct load dups into a table with a unique cons/index, won't the index be left as 'UNUSABLE' thus necessitating an index rebuild anyway. If the index was non-unique, then this is not a problem, but in this case, you don't need KEEP INDEX anyway. Happy New Year Cheers Connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) you can insert dups via sqlloader using direct=true so in my case, this would indeed be helpful and without the keep index I lose the index when I do an alter table drop constraint Keep index sounds like it will help me in this scenario: primary key constraint with unique index insert dups via sqlloader direct=true drop constraint with keep index recreate constraint with exceptions into exceptions table delete dups re-enable constraint this doesn't happen often, and we are working to fix the app so it doesn't put the dups into the input file for the sqlload. However, until it gets fixed, I need to do the above so that we actually have usable indexes on the partitioned fact tables --- Connor McDonald [EMAIL PROTECTED] wrote: I'm a little doubtful about the value of 'keep index'. Consider the scenarios: unique constraint, non-unique index: - keep index redundant because its kept anyway unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) So far, the only use for KEEP INDEX I've found is the scenario where you: - decided that column(s) X was the primary key - created a unique index on it - created a primary key constraint on it - loaded the data - decided actually X was NOT the primary key, just a unique value - decided that X could allow nulls as well - dropped the primary kept, kept the index and then added a unique constraint... I would contend that this is a rare occurrence ? Cheers Connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: sigh. I need to find time to read ALL the docs. Yeah, that'll happen. If I can find a parallel universe where time runs at a different rate. Thanks, I'll test this out as well. --- Arup Nanda [EMAIL PROTECTED] wrote: In 9.2, you can keep the index by using the KEEP INDEX key words. ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX This will keep the index but drop the constraint. Talk about having your cake and eating it too...;) HTH Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 27, 2002 4:39 PM it'll have to wait until Monday, I'm not at work until then. I'll try it with a non-unique then Hey, if it works, it saves me tons of time, I learn something new and I had fun developing the single SQL statement to rebuild the constraint and index. Win-win Rachel --- Denny Koovakattu [EMAIL PROTECTED] wrote: I don't have access to 9.2.0.1 right now. But can you try creating a non- unique index instead of the unique index. If you create a unique index, it gets dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it shouldn't get dropped. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: 9.2.0.1 Solaris, and yes, it does drop it I created a unique index in the primary key columns I created the primary key constraint without specifying an index I checked that the index existed, it did I dropped the primary key constraint I checked that the index existed, it didn't try it I tried various combinations before posting this note --- Denny Koovakattu [EMAIL PROTECTED] wrote: If you build a separate index to enforce the primary key, Oracle shouldn't drop it when you disable or drop the primary key. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I
Re: Was: Rebuilding Indexes, Now: KEEP INDEX
Yes, but at least the index definition will be preserved so that you could do a simple ALTER INDEX ... REBUILD rather than finding and firing off a script. Particularly useful if you have a lots of partitioned indexes. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 30, 2002 9:19 AM but if you direct load dups into a table with a unique cons/index, won't the index be left as 'UNUSABLE' thus necessitating an index rebuild anyway. If the index was non-unique, then this is not a problem, but in this case, you don't need KEEP INDEX anyway. Happy New Year Cheers Connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) you can insert dups via sqlloader using direct=true so in my case, this would indeed be helpful and without the keep index I lose the index when I do an alter table drop constraint Keep index sounds like it will help me in this scenario: primary key constraint with unique index insert dups via sqlloader direct=true drop constraint with keep index recreate constraint with exceptions into exceptions table delete dups re-enable constraint this doesn't happen often, and we are working to fix the app so it doesn't put the dups into the input file for the sqlload. However, until it gets fixed, I need to do the above so that we actually have usable indexes on the partitioned fact tables --- Connor McDonald [EMAIL PROTECTED] wrote: I'm a little doubtful about the value of 'keep index'. Consider the scenarios: unique constraint, non-unique index: - keep index redundant because its kept anyway unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) So far, the only use for KEEP INDEX I've found is the scenario where you: - decided that column(s) X was the primary key - created a unique index on it - created a primary key constraint on it - loaded the data - decided actually X was NOT the primary key, just a unique value - decided that X could allow nulls as well - dropped the primary kept, kept the index and then added a unique constraint... I would contend that this is a rare occurrence ? Cheers Connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: sigh. I need to find time to read ALL the docs. Yeah, that'll happen. If I can find a parallel universe where time runs at a different rate. Thanks, I'll test this out as well. --- Arup Nanda [EMAIL PROTECTED] wrote: In 9.2, you can keep the index by using the KEEP INDEX key words. ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX This will keep the index but drop the constraint. Talk about having your cake and eating it too...;) HTH Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 27, 2002 4:39 PM it'll have to wait until Monday, I'm not at work until then. I'll try it with a non-unique then Hey, if it works, it saves me tons of time, I learn something new and I had fun developing the single SQL statement to rebuild the constraint and index. Win-win Rachel --- Denny Koovakattu [EMAIL PROTECTED] wrote: I don't have access to 9.2.0.1 right now. But can you try creating a non- unique index instead of the unique index. If you create a unique index, it gets dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it shouldn't get dropped. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: 9.2.0.1 Solaris, and yes, it does drop it I created a unique index in the primary key columns I created the primary key constraint without specifying an index I checked that the index existed, it did I dropped the primary key constraint I checked that the index existed, it didn't try it I tried various combinations before posting this note --- Denny Koovakattu [EMAIL PROTECTED] wrote: If you build a separate index to enforce the primary key, Oracle shouldn't drop it when you disable or drop the primary key. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]:
Re: Was: Rebuilding Indexes, Now: KEEP INDEX
yes but :) It's a partitioned index. Yes, the partition goes into an UNUSABLE state. If I drop the constraint without keep index and without saving off the statement to rebuild it properly, I drop the ENTIRE index and I end up with a non-partitioned index in the schema owner's default tablespace when I rebuild the constraint. So if I use KEEP INDEX, yes I'll need to rebuild the partition, but I won't have to rebuild the entire index and I won't have to save off the SQL to rebuild it properly. As the number of rows grows, rebuilding the entire index becomes time-prohibitive. Of course, I've already written that SQL statement, but that was fun. I'd still rather do the work properly and in a more efficient manner. --- Connor McDonald [EMAIL PROTECTED] wrote: but if you direct load dups into a table with a unique cons/index, won't the index be left as 'UNUSABLE' thus necessitating an index rebuild anyway. If the index was non-unique, then this is not a problem, but in this case, you don't need KEEP INDEX anyway. Happy New Year Cheers Connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) you can insert dups via sqlloader using direct=true so in my case, this would indeed be helpful and without the keep index I lose the index when I do an alter table drop constraint Keep index sounds like it will help me in this scenario: primary key constraint with unique index insert dups via sqlloader direct=true drop constraint with keep index recreate constraint with exceptions into exceptions table delete dups re-enable constraint this doesn't happen often, and we are working to fix the app so it doesn't put the dups into the input file for the sqlload. However, until it gets fixed, I need to do the above so that we actually have usable indexes on the partitioned fact tables --- Connor McDonald [EMAIL PROTECTED] wrote: I'm a little doubtful about the value of 'keep index'. Consider the scenarios: unique constraint, non-unique index: - keep index redundant because its kept anyway unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) So far, the only use for KEEP INDEX I've found is the scenario where you: - decided that column(s) X was the primary key - created a unique index on it - created a primary key constraint on it - loaded the data - decided actually X was NOT the primary key, just a unique value - decided that X could allow nulls as well - dropped the primary kept, kept the index and then added a unique constraint... I would contend that this is a rare occurrence ? Cheers Connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: sigh. I need to find time to read ALL the docs. Yeah, that'll happen. If I can find a parallel universe where time runs at a different rate. Thanks, I'll test this out as well. --- Arup Nanda [EMAIL PROTECTED] wrote: In 9.2, you can keep the index by using the KEEP INDEX key words. ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX This will keep the index but drop the constraint. Talk about having your cake and eating it too...;) HTH Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 27, 2002 4:39 PM it'll have to wait until Monday, I'm not at work until then. I'll try it with a non-unique then Hey, if it works, it saves me tons of time, I learn something new and I had fun developing the single SQL statement to rebuild the constraint and index. Win-win Rachel --- Denny Koovakattu [EMAIL PROTECTED] wrote: I don't have access to 9.2.0.1 right now. But can you try creating a non- unique index instead of the unique index. If you create a unique index, it gets dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it shouldn't get dropped. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: 9.2.0.1 Solaris, and yes, it does drop it I created a unique index in the primary key columns I created the primary key constraint without specifying an index I checked that the index existed, it did I dropped the primary key constraint I checked that the index existed, it didn't try it
Re: Was: Rebuilding Indexes, Now: KEEP INDEX
unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) you can insert dups via sqlloader using direct=true so in my case, this would indeed be helpful and without the keep index I lose the index when I do an alter table drop constraint Keep index sounds like it will help me in this scenario: primary key constraint with unique index insert dups via sqlloader direct=true drop constraint with keep index recreate constraint with exceptions into exceptions table delete dups re-enable constraint this doesn't happen often, and we are working to fix the app so it doesn't put the dups into the input file for the sqlload. However, until it gets fixed, I need to do the above so that we actually have usable indexes on the partitioned fact tables --- Connor McDonald [EMAIL PROTECTED] wrote: I'm a little doubtful about the value of 'keep index'. Consider the scenarios: unique constraint, non-unique index: - keep index redundant because its kept anyway unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) So far, the only use for KEEP INDEX I've found is the scenario where you: - decided that column(s) X was the primary key - created a unique index on it - created a primary key constraint on it - loaded the data - decided actually X was NOT the primary key, just a unique value - decided that X could allow nulls as well - dropped the primary kept, kept the index and then added a unique constraint... I would contend that this is a rare occurrence ? Cheers Connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: sigh. I need to find time to read ALL the docs. Yeah, that'll happen. If I can find a parallel universe where time runs at a different rate. Thanks, I'll test this out as well. --- Arup Nanda [EMAIL PROTECTED] wrote: In 9.2, you can keep the index by using the KEEP INDEX key words. ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX This will keep the index but drop the constraint. Talk about having your cake and eating it too...;) HTH Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 27, 2002 4:39 PM it'll have to wait until Monday, I'm not at work until then. I'll try it with a non-unique then Hey, if it works, it saves me tons of time, I learn something new and I had fun developing the single SQL statement to rebuild the constraint and index. Win-win Rachel --- Denny Koovakattu [EMAIL PROTECTED] wrote: I don't have access to 9.2.0.1 right now. But can you try creating a non- unique index instead of the unique index. If you create a unique index, it gets dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it shouldn't get dropped. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: 9.2.0.1 Solaris, and yes, it does drop it I created a unique index in the primary key columns I created the primary key constraint without specifying an index I checked that the index existed, it did I dropped the primary key constraint I checked that the index existed, it didn't try it I tried various combinations before posting this note --- Denny Koovakattu [EMAIL PROTECTED] wrote: If you build a separate index to enforce the primary key, Oracle shouldn't drop it when you disable or drop the primary key. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows Rachel --- Jared Still [EMAIL
Re: Was: Rebuilding Indexes, Now: KEEP INDEX
Connor Connor McDonald wrote: I'm a little doubtful about the value of 'keep index'. Consider the scenarios: unique constraint, non-unique index: - keep index redundant because its kept anyway unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) As it's done now it can be useful for some real life cases, some of them can be very rare. However keep option was introduced, so it could mean that somebody spent at least some time to design it or make a stub(?), right? I think the reasons are: . To have consistent statement semantics (DROP / KEEP) . Perhaps this functionality will be extended in the further Oracle RDBMS editions . Performance of Exchanging Partitions can be improved, please refer to docs/metalink. . In case some apps. table reorganization needs to be done e.g. primary key is based in unique index, however the constraint has to be extened -- suppose an application is going to support multi-organization feature or whatever else as a temporary solution KEEP index can be used -- I suspect that it sounds like not well thought design but life is cruel -- sometimes is good to have indexes... Thread participants have provided some examples already. Kind Regards, -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
