UPDATE GLOBAL INDEXES #2 - comp.databases.oracle.server

It is interesting that global and *non-partitioned* indexes can be kept valid during operations such as ALTER TABLE MOVE PARTITION using UPDATE GLOBAL INDEXES clause while similar option isn't available for non-partitioned table or for partitioned table with local indexes. For example: 1. Create a partitioned table with non-partitioned index. Execute ALTER TABLE MOVE PARTITION UPDATE GLOBAL INDEXES. The index is kept in VALID state when ALTER TABLE is running. 2. Create a non-partitioned table and an index on it. There is no way to keep the index VALID during ALTER TABLE MOVE operation. 3. Create a partitioned table and a local index on this table. There is no way to keep partition of this index VALID during ALTER TABLE MOVE PARTITION operation. So a partitioned table with a single partition is more flexible than a non-partitioned table: this single partition can be moved ONLINE, with all indexes maintained. It may be necessary to compress this table, or to rebuild table after purge, or to relocate it to a different tablespace. Why Oracle didn't make this functionality available for non- partitioned tables?

On Oct 18, 9:34 am, Vsevolod Afanassiev <vafanass...@yahoo.com> wrote: > It is interesting that global and *non-partitioned* indexes can be > kept valid during operations > such as ALTER TABLE MOVE PARTITION using UPDATE GLOBAL INDEXES clause > while similar option isn't available for non-partitioned table or for > partitioned table with local indexes. > > For example: > 1. Create a partitioned table with non-partitioned index. Execute > ALTER TABLE MOVE PARTITION UPDATE GLOBAL INDEXES. The index is kept in > VALID state when ALTER TABLE is running. > 2. Create a non-partitioned table and an index on it. There is no way > to keep the > index VALID during ALTER TABLE MOVE operation. > 3. Create a partitioned table and a local index on this table. There > is no way to keep partition > of this index VALID during ALTER TABLE MOVE PARTITION operation. > > So a partitioned table with a single partition is more flexible than a > non-partitioned table: this single partition can be moved ONLINE, with > all indexes maintained. > It may be necessary to compress this table, or to rebuild table after > purge, > or to relocate it to a different tablespace. > > Why Oracle didn't make this functionality available for non- > partitioned tables? They did. It's called 'alter index blah rebuild;' !! The 'update global indexes' option only really achieves what a rebuild would. It's just that it does it during the table move rather than after it. But it's not doing some completely new magic. The other way of looking at it is that you **need** the option for partitioned tables because rebuilding a thumping great index just because you moved a tiny piece of a table isn't exactly a good idea. But an unpartitioned table is either moved in its entirety or not at all, so you either rebuild the "global" index on it or not at all. You don't need an option that says, 'Let me move all this large table and prevent me from having to rebuild the entire index by taking steps which amount to rebuilding the entire index'. It just doesn't make sense.

On Oct 18, 3:04 pm, hjr.pyth...@gmail.com wrote: > On Oct 18, 9:34 am, Vsevolod Afanassiev <vafanass...@yahoo.com> wrote: > > > > > > > It is interesting that global and *non-partitioned* indexes can be > > kept valid during operations > > such as ALTER TABLE MOVE PARTITION using UPDATE GLOBAL INDEXES clause > > while similar option isn't available for non-partitioned table or for > > partitioned table with local indexes. > > > For example: > > 1. Create a partitioned table with non-partitioned index. Execute > > ALTER TABLE MOVE PARTITION UPDATE GLOBAL INDEXES. The index is kept in > > VALID state when ALTER TABLE is running. > > 2. Create a non-partitioned table and an index on it. There is no way > > to keep the > > index VALID during ALTER TABLE MOVE operation. > > 3. Create a partitioned table and a local index on this table. There > > is no way to keep partition > > of this index VALID during ALTER TABLE MOVE PARTITION operation. > > > So a partitioned table with a single partition is more flexible than a > > non-partitioned table: this single partition can be moved ONLINE, with > > all indexes maintained. > > It may be necessary to compress this table, or to rebuild table after > > purge, > > or to relocate it to a different tablespace. > > > Why Oracle didn't make this functionality available for non- > > partitioned tables? > > They did. It's called 'alter index blah rebuild;' !! > > The 'update global indexes' option only really achieves what a rebuild > would. It's just that it does it during the table move rather than > after it. But it's not doing some completely new magic. > > The other way of looking at it is that you **need** the option for > partitioned tables because rebuilding a thumping great index just > because you moved a tiny piece of a table isn't exactly a good idea. > But an unpartitioned table is either moved in its entirety or not at > all, so you either rebuild the "global" index on it or not at all. You > don't need an option that says, 'Let me move all this large table and > prevent me from having to rebuild the entire index by taking steps > which amount to rebuilding the entire index'. It just doesn't make > sense.- Hide quoted text - > > - Show quoted text - I want to be able to move a non-partitioned table and have indexes maintained in the process. Yes, there is 'alter index rebuild online' statement but there is no 'alter table move online' statement. Example where it would be helpful: one of my applications creates 3 new tables every day. The tables get populated within 24-48 hours after creation and then remain nearly static for the next 100 days, then they get dropped. In order to reduce space usage we compress tables after 30 days by doing 'alter table move compress', but it makes indexes unusable, and they have to be rebuild. So we need an outage to rebuild the indexes. If these tables were created a partitioned tables with singe partition we would be able to do it without outage.

On Oct 18, 1:34 am, Vsevolod Afanassiev <vafanass...@yahoo.com> wrote: > It is interesting that global and *non-partitioned* indexes can be > kept valid during operations > such as ALTER TABLE MOVE PARTITION using UPDATE GLOBAL INDEXES clause > while similar option isn't available for non-partitioned table or for > partitioned table with local indexes. > > For example: > 1. Create a partitioned table with non-partitioned index. Execute > ALTER TABLE MOVE PARTITION UPDATE GLOBAL INDEXES. The index is kept in > VALID state when ALTER TABLE is running. > 2. Create a non-partitioned table and an index on it. There is no way > to keep the > index VALID during ALTER TABLE MOVE operation. > 3. Create a partitioned table and a local index on this table. There > is no way to keep partition > of this index VALID during ALTER TABLE MOVE PARTITION operation. > > So a partitioned table with a single partition is more flexible than a > non-partitioned table: this single partition can be moved ONLINE, with > all indexes maintained. > It may be necessary to compress this table, or to rebuild table after > purge, > or to relocate it to a different tablespace. > > Why Oracle didn't make this functionality available for non- > partitioned tables? Rainbow Tables for CAIN: - only for system account - up to 8 characters passwords; - 100 torrent files; - Total: 625 MB* 100; - success rate 100% - Maximum crack time: 5 seconds for each file; check CAIN for details; - generated by winrtgen parameters are given below: oracle_oracle#1-8_0_2400x40000000_system#000.rt oracle_oracle#1-8_0_2400x40000000_system#001.rt oracle_oracle#1-8_0_2400x40000000_system#002.rt ............................ oracle_oracle#1-8_0_2400x40000000_system#099.rt email for details, demo, and downloading url's! (Only system account) oracle hashes challenges are welcome! please make the payment FIRST! happy crackin'

On Oct 18, 4:02 pm, Vsevolod Afanassiev <vafanass...@yahoo.com> wrote: > On Oct 18, 3:04 pm, hjr.pyth...@gmail.com wrote: > > > > > On Oct 18, 9:34 am, Vsevolod Afanassiev <vafanass...@yahoo.com> wrote: > > > > It is interesting that global and *non-partitioned* indexes can be > > > kept valid during operations > > > such as ALTER TABLE MOVE PARTITION using UPDATE GLOBAL INDEXES clause > > > while similar option isn't available for non-partitioned table or for > > > partitioned table with local indexes. > > > > For example: > > > 1. Create a partitioned table with non-partitioned index. Execute > > > ALTER TABLE MOVE PARTITION UPDATE GLOBAL INDEXES. The index is kept in > > > VALID state when ALTER TABLE is running. > > > 2. Create a non-partitioned table and an index on it. There is no way > > > to keep the > > > index VALID during ALTER TABLE MOVE operation. > > > 3. Create a partitioned table and a local index on this table. There > > > is no way to keep partition > > > of this index VALID during ALTER TABLE MOVE PARTITION operation. > > > > So a partitioned table with a single partition is more flexible than a > > > non-partitioned table: this single partition can be moved ONLINE, with > > > all indexes maintained. > > > It may be necessary to compress this table, or to rebuild table after > > > purge, > > > or to relocate it to a different tablespace. > > > > Why Oracle didn't make this functionality available for non- > > > partitioned tables? > > > They did. It's called 'alter index blah rebuild;' !! > > > The 'update global indexes' option only really achieves what a rebuild > > would. It's just that it does it during the table move rather than > > after it. But it's not doing some completely new magic. > > > The other way of looking at it is that you **need** the option for > > partitioned tables because rebuilding a thumping great index just > > because you moved a tiny piece of a table isn't exactly a good idea. > > But an unpartitioned table is either moved in its entirety or not at > > all, so you either rebuild the "global" index on it or not at all. You > > don't need an option that says, 'Let me move all this large table and > > prevent me from having to rebuild the entire index by taking steps > > which amount to rebuilding the entire index'. It just doesn't make > > sense.- Hide quoted text - > > > - Show quoted text - > > I want to be able to move a non-partitioned table and have indexes > maintained in the process. > Yes, there is 'alter index rebuild online' statement but there is no > 'alter table move online' statement. > Example where it would be helpful: one of my applications creates 3 > new tables every day. > The tables get populated within 24-48 hours after creation and then > remain nearly > static for the next 100 days, then they get dropped. In order to > reduce space usage > we compress tables after 30 days by doing 'alter table move compress', > but it makes indexes unusable, and they have to be rebuild. So we need > an outage to rebuild the indexes. > If these tables were created a partitioned tables with singe partition > we would be able to do it > without outage. You're asking for two different things: a move online and a 'maintain indexes' option. The 'maintain indexes' option for a non-partitioned table is meaningless for the reasons I explained before: a maintain index is simply (in effect) a 'rebuild index' but affecting only the part of the index that relates to the bit of the table being moved. But with a non-partitioned index, that's the entire index anyway... so there's no effective difference between a 'maintain index' option and an 'alter index...rebuild' one. The outage you experience when having to rebuild your 30-day-old indexes is no different to the outage you would experience from having to move a 30-day-old partition, actually. It's just that with the partitioned table, the outage isn't noticed because the rest of the table remains open for business. But with a non- partitioned table, there is no "rest of the table" that could be open for business. But try doing DML on the partitioned being moved as its being moved, will you?! As for moving online... you want the option to allow people to do DML on a table that is having every row moved and whilst the disk(s) that table is stored on is being hammered to death? Again, it makes sense for a partitioned table, because you might want to move the three-year- old bit of the table whilst leaving the 'up-to-date stuff' bit of it fully open for business. But with a non-partitioned table, it's an I/O non-starter! Neither option makes much sense for a non-partitioned table.

Vsevolod Afanassiev wrote: > > On Oct 18, 3:04 pm, hjr.pyth...@gmail.com wrote: > > On Oct 18, 9:34 am, Vsevolod Afanassiev <vafanass...@yahoo.com> wrote: > > > > > > > > > > > > > It is interesting that global and *non-partitioned* indexes can be > > > kept valid during operations > > > such as ALTER TABLE MOVE PARTITION using UPDATE GLOBAL INDEXES clause > > > while similar option isn't available for non-partitioned table or for > > > partitioned table with local indexes. > > > > > For example: > > > 1. Create a partitioned table with non-partitioned index. Execute > > > ALTER TABLE MOVE PARTITION UPDATE GLOBAL INDEXES. The index is kept in > > > VALID state when ALTER TABLE is running. > > > 2. Create a non-partitioned table and an index on it. There is no way > > > to keep the > > > index VALID during ALTER TABLE MOVE operation. > > > 3. Create a partitioned table and a local index on this table. There > > > is no way to keep partition > > > of this index VALID during ALTER TABLE MOVE PARTITION operation. > > > > > So a partitioned table with a single partition is more flexible than a > > > non-partitioned table: this single partition can be moved ONLINE, with > > > all indexes maintained. > > > It may be necessary to compress this table, or to rebuild table after > > > purge, > > > or to relocate it to a different tablespace. > > > > > Why Oracle didn't make this functionality available for non- > > > partitioned tables? > > > > They did. It's called 'alter index blah rebuild;' !! > > > > The 'update global indexes' option only really achieves what a rebuild > > would. It's just that it does it during the table move rather than > > after it. But it's not doing some completely new magic. > > > > The other way of looking at it is that you **need** the option for > > partitioned tables because rebuilding a thumping great index just > > because you moved a tiny piece of a table isn't exactly a good idea. > > But an unpartitioned table is either moved in its entirety or not at > > all, so you either rebuild the "global" index on it or not at all. You > > don't need an option that says, 'Let me move all this large table and > > prevent me from having to rebuild the entire index by taking steps > > which amount to rebuilding the entire index'. It just doesn't make > > sense.- Hide quoted text - > > > > - Show quoted text - > > I want to be able to move a non-partitioned table and have indexes > maintained in the process. > Yes, there is 'alter index rebuild online' statement but there is no > 'alter table move online' statement. > Example where it would be helpful: one of my applications creates 3 > new tables every day. > The tables get populated within 24-48 hours after creation and then > remain nearly > static for the next 100 days, then they get dropped. In order to > reduce space usage > we compress tables after 30 days by doing 'alter table move compress', > but it makes indexes unusable, and they have to be rebuild. So we need > an outage to rebuild the indexes. > If these tables were created a partitioned tables with singe partition > we would be able to do it > without outage. dbms_redefinition -- Connor McDonald Co-author: "Mastering Oracle PL/SQL - Practical Solutions" Co-author: "Oracle Insight - Tales of the OakTable" web: http://www.oracledba.co.uk web: http://www.oaktable.net email: connor_mcdonald@yahoo.com "Semper in excremento, sole profundum qui variat." ------------------------------------------------------------