P
Patrice Perrot
Guest
Hello YMMV , I let you estimate and do your own test on idxactivate , ….. I did some tests on idxactivate (with and without idxfix before) on a sports2000 db and another test DB (each time 2 fields in the new index with more fields => more time). - On a 10 million row table it took around 10 minutes to achieve idxactivate (mean value 614 secunds). - On a 1 million row table it took around 1 minutes to achieve idxactivate (mean value 60 secunds). YMMV , I let you estimate and do your own test on idxactivate , I do not test with 400 million row table ... On idxactivate there are 2 different possibilities - useindex [UNIQ INDEX] - useindex [NOT UNIQ INDEX] Progress write that you can use use IDXACTIVATE “ONLINE” - with use-index “not uniq index” you cannot create update or delete row of the table - with use-index “uniq index” you “can” create update or delete row of the table In the case of using an “Uniq Index” to do the idxactivate, I did some tests with an create / update/ delete activity on this table when I did my idxactivate. I notice some few things - At the end of the wait time (of the usernotifytime) the schema lock to update this index could crash my other session (for example message “6527” “Table is locked. Record create failed.”) -> No activity when you launch it - During the IDXACTIAVTE (create index keys) , I could have some messages “2624” due to the lock of idxactivate on the session that do the update activity -> Test and probability show that decreasing the recs from 5000 to 50 have some benefit on those messages -> But it increase my AI file size of 3 % and the elapse time of 1.5 % I notice too that idxfix before idxactivate could be suppress in case of activating a non-unique Index , The block of the “inactive” index create by the idxfix are dropped at the begining of IDXACTIVATE . So it increases for IDXACTIVATE time ( 0.8 %) and AI file size (3.5 to 4 % depending on the recs value 50 or 5000) Remember YMMV. Patrice
Continue reading...
Continue reading...