Dangers of dropping foreign key constraints?

Hi! I want to temporarily drop foreign key constraints on a table in a production environment. What dangers do I face? I want to drop them because I want to reduce the indexes on the table to be able to perform an "ALTER INDEX index-name TO CLUSTER"-operation. Such an operation requires that you only have one index on the table. Sent via Deja.com http://www.deja.com/ Before you buy.

Emm, I think you may be misinterpreting the book here. You can only have ONE cluster index. That's the rule. Other indexes are allowed to exist but as you can probably understand if you understand the mechanics, only index may be clustered. Possible reasons for dropping other indexes is that it may be quicker to manually rebuild the other indexes rather than suffer a potentially slower rebuild action by the engine itself. But don't rush into making that part of your ritual - firstly, test total time for both styles of rebuild - ie with and without drop/rebuild of other indexes (test BOTH TIMES on identically scrambled rows!) and then you will know the facts. After you know whether it is significantly faster (if it is) then I would only bother with that if the simpler alternative (plain old clustering without dropping other indexes) is too slow for your liking, and the alternative must have to be much faster to be worth the effort. Keep It Sweet and Simple c-eriks@algonet.se wrote in message <8vb0j7$209$1@nnrp1.deja.com>... >Hi! > >I want to temporarily drop foreign key constraints on a table in a >production environment. What dangers do I face? I want to drop them >because I want to reduce the indexes on the table to be able to perform >an "ALTER INDEX index-name TO CLUSTER"-operation. Such an operation >requires that you only have one index on the table.

In article <3a1a02d6$1@news.iprimus.com.au>, "Andrew Hamm" <ahamm@sanderson.net.au> wrote: > Emm, I think you may be misinterpreting the book here. You can only > have ONE cluster index. That's the rule. Other indexes are allowed to > exist but as you can probably understand if you understand the > mechanics, only index may be clustered. > OK! I managed to do a "ALTER INDEX index-name TO CLUSTER" without dropping any indexes. I didn't get the result I wanted though. The number of extents for the table wasn't reduced. I have achieved that when only having one index on the table and doing a "ALTER INDEX ...". This all aims at reducing the number of table extents in a production environment with users connected to the database. Sent via Deja.com http://www.deja.com/ Before you buy.

c-eriks@algonet.se wrote in message <8ve7si$n47$1@nnrp1.deja.com>... >OK! I managed to do a "ALTER INDEX index-name TO CLUSTER" without >dropping any indexes. I didn't get the result I wanted though. The >number of extents for the table wasn't reduced. I have achieved that >when only having one index on the table and doing a "ALTER INDEX ...". >This all aims at reducing the number of table extents in a production >environment with users connected to the database. I don't know if you are aware of the ALTER TABLE xxxxx NEXT SIZE yyyy; but I'd be expecting the ALTER INDEX TO CLUSTER statement to take that into account. I cannot find a cross-reference between the two statements in the books, however. Since the table will be physically rewritten during the clustering, I'd be very hopeful (and optimistic) that Informix has actually taken these numbers into account. What other numbers could they use? If you haven't done the NEXT SIZE statement then you are definitely at the mercy of the available extents, and if the data space is fairly scrambled already then you are very likely to get lots of little bits of spaces re-allocated. (see point down below about how the engine can glue multiple allocations together in some situations) Even if the NEXT SIZE statement does influence the ALTER INDEX TO CLUSTER operation, it MAY have the peculiar characteristic of allocating the first extent with the current, tiny FIRST extent size. But all remaining extents would get the new size so that wouldn't be much of a problem anyway. Finally, even if the NEXT SIZE statement (etc etc etc), and if there is no large pieces of space left for large allocations, then even under the influence of a NEXT SIZE statement, the extent allocations may be forced to pick up lots of tiny pieces simply because it cannot find any large pieces to satisfy the NEXT SIZE. If you are in that position (which you can check by studying the FREE pieces from an oncheck -pe) then you will be forced to reorganise your entire engine - and hopefully get more space allocated to the engine as well. If you have multiple databases then you should do them all, so that all tiny extents and free spaces may be eliminated. Here is the ritual: dbexport all databases drop all databases (be free from sin prior to this step;-) dbimport all databases (one at a time - not in parallel with multiple sessions!) perform UPDATE STATISTICS for all databases The special trick that you can get out of this is, when the engine allocates a new extent to a table, IF the new space is right next to the old space then it glues it together with the original extent, and so by the end of the dbimport, all tables of all databases will initially be in one extent each, of suitable size for holding the data. This, by the way is why you should not import two or more databases in parallel. The competing imports would be allocating independently and it would be impossible for the engine to glue new extents together if there were already other extents getting in the way. Once the databases are imported, you should jump in, look at the size of the new, single extent allocated to each table, think about how the tables might grow, and set the NEXT SIZE for all tables that need it. If you do this extent prediction work prior to the unload, you could calculate your chosen FIRST EXTENT to suit at least a few months growth, then use a perl script to inject those extent sizes into the .sql file of the exports prior to the dbimport. In this way, the tables will have enough room to get new rows, which means it will go for quite a while before they allocate the second extent. If you don't do this work prior to the operation, then most growing tables would probably do an allocation for a second extent fairly soon after using the new imports. Two extents are no problem however. All this depends on your comfort level with the extent issue and also with Perl of course. Summary: extent allocation is serious, a hassle to get familiar with the solutions, and of course requires work to setup databases properly from the beginning. That means you must be able to correctly predict the growth needs of tables and databases. If you are maintaining your own database that's a bit easier, but if you are maintaining other peoples (ie customers), then you have to be even better with the predictions. Further improvements would involve thoughtfully allocating tables to different dbspaces, different disks, etc and the exploitation of table fragmentation and PDQ for improved performance. That's really shifting into high gear, and unfortunately way too much to describe on a news group if you wish to learn about it. I must confess it's still a developing science here too, because just like everyone else, we have "more important" work to do. But I'm becoming satisfied with the direction we are taking. So much to do! Cheers. Hope this stuff is all clear and helpful. Sorry if I'm being too simplistic for your skill levels - it's hard to know a persons level of experience from a simple posting, so it's wise to assume less rather than more if you want to help.

ALTER FRAGMENT ON TABLE table_name INIT IN <dbspace or frag expression>; Tends to be faster than ALTER INDEX TO CLUSTER and has the advantage (when such is an advantage) of keeping the natural order and locality of the data rows. The main problem is likely a too small NEXT SIZE. Art S. Kagel c-eriks@algonet.se wrote: > > In article <3a1a02d6$1@news.iprimus.com.au>, > "Andrew Hamm" <ahamm@sanderson.net.au> wrote: > > Emm, I think you may be misinterpreting the book here. You can only > > have ONE cluster index. That's the rule. Other indexes are allowed to > > exist but as you can probably understand if you understand the > > mechanics, only index may be clustered. > > > > OK! I managed to do a "ALTER INDEX index-name TO CLUSTER" without > dropping any indexes. I didn't get the result I wanted though. The > number of extents for the table wasn't reduced. I have achieved that > when only having one index on the table and doing a "ALTER INDEX ...". > This all aims at reducing the number of table extents in a production > environment with users connected to the database. > > Sent via Deja.com http://www.deja.com/ > Before you buy.

In article <3A22E5FD.49D607F5@bloomberg.net>, kagel@bloomberg.net wrote: > ALTER FRAGMENT ON TABLE table_name INIT IN <dbspace or frag> expression>; > > Tends to be faster than ALTER INDEX TO CLUSTER and has the advantage > (when such is an advantage) of keeping the natural order and locality > of the data rows. > > The main problem is likely a too small NEXT SIZE. > > Art S. Kagel Can this be done with users logged on and working against the database? Do I have to drop any indexes on the table before executing the operation? Christian Eriksson > > c-eriks@algonet.se wrote: > > > > In article <3a1a02d6$1@news.iprimus.com.au>, > > "Andrew Hamm" <ahamm@sanderson.net.au> wrote: > > > Emm, I think you may be misinterpreting the book here. You can > > > only have ONE cluster index. That's the rule. Other indexes are > > > allowed to exist but as you can probably understand if you > > > understand the mechanics, only index may be clustered. > > > > > > > OK! I managed to do a "ALTER INDEX index-name TO CLUSTER" without > > dropping any indexes. I didn't get the result I wanted though. The > > number of extents for the table wasn't reduced. I have achieved that > > when only having one index on the table and doing a "ALTER > > INDEX ...". This all aims at reducing the number of table extents > > in a production environment with users connected to the database. > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.

c-eriks@algonet.se wrote in message <8vvpp9$8is$1@nnrp1.deja.com>... > >Can this be done with users logged on and working against the database? >Do I have to drop any indexes on the table before executing the >operation? > >Christian Eriksson Altering the next size is merely changing a setting so it doesn't need any consideration of machine load or user load, and no other changes need to be done. Actually performing the recluster needs to be considered depending on the size of the table, but you seem to have done the clustering a few times so it looks like you are probably safe on the issues there.

But my table isn't currently fragmented (as by the Informix feature). Can the "ALTER FRAGMENT ON TABLE table_name INIT IN <dbspace or frag expression>;" be used anyway? (To me it seems that "ALTER FRAGMENT statement INIT FRAGMENT clause" should be used to "Create a fragmented table from a single nonfragmented table"). /Christian In article <3A22E5FD.49D607F5@bloomberg.net>, kagel@bloomberg.net wrote: > ALTER FRAGMENT ON TABLE table_name INIT IN <dbspace or frag> expression>; > > Tends to be faster than ALTER INDEX TO CLUSTER and has the advantage > (when such is an advantage) of keeping the natural order and locality > of the data rows. > > The main problem is likely a too small NEXT SIZE. > > Art S. Kagel Sent via Deja.com http://www.deja.com/ Before you buy.