detached index rebuild question

HI, A customer has the data cleaning requirement base on the datetime column, so they want to fragment some tables, but some tables has detached index; they want to know after they detach one partition, Do those detached index will be dropped and recreate ? or julstly delete some index item that belong to the partition being detached? if IDS drop and recreate the detached index, the customer afraid those transaction that use the detached index will be failed . thanks for your time.

We do this quite a bit. The indexes are detached and each index partition just drops away - no need to rebuild the index. Especially effective in v12 which provides a rolling window so you don't have to manually drop stuff, the engine does it for you. j. On 7/14/16 11:31 AM, CHUAN LU wrote: > HI, > A customer has the data cleaning requirement base on the datetime column, so > they want to fragment some tables, but some tables has detached index; they > want to know after they detach one partition, Do those detached index will be > dropped and recreate ? or julstly delete some index item that belong to the > partition being detached? if IDS drop and recreate the detached index, the > customer afraid those transaction that use the detached index will be failed . > > thanks for your time. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Those indexes that are not fragmented or are fragmented with a different scheme than the table itself will be dropped and recreated. Queries and transactions that have been referencing that index will not fail but may run slow using different query plans until it is rebuilt. If there are open transactions or open cursors using that table the ALTER fragment to drop the old partitions will hang or get an error though and not complete. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 Thu, Jul 14, 2016 at 11:31 AM, CHUAN LU <luchuan@cn.ibm.com> wrote: > HI, > A customer has the data cleaning requirement base on the datetime column, > so > they want to fragment some tables, but some tables has detached index; they > want to know after they detach one partition, Do those detached index will > be > dropped and recreate ? or julstly delete some index item that belong to the > partition being detached? if IDS drop and recreate the detached index, the > customer afraid those transaction that use the detached index will be > failed . > > thanks for your time. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113f8aaed42e3f05379bd726