D
dbeavon
Guest
I was planning to add some indexes to a table with 20 years of data and about 1,000,000 rows per year (a relatively small table, compared to others). I will be dropping an index and adding two others. Here are the two new indexes: @autocommit false; CREATE INDEX "my_tbl7" ON PUB."my_tbl" ("fy" ASC , "period" ASC , "div" ASC , "center" ASC , "acct" ASC) AREA "my_tbl_idx" PRO_DESCRIPTION '' PRO_ACTIVE 'y'; CREATE UNIQUE INDEX "my_tbl8" ON PUB."my_tbl" ("cy" ASC , "cperiod" ASC , "div" ASC , "center" ASC , "acct" ASC, "cdetail" ASC) AREA "my_tbl_idx" PRO_DESCRIPTION '' PRO_ACTIVE 'y'; COMMIT; What surprised me is that this takes many hours longer than an alternative approach whereby I create the indexes as "PRO_ACTIVE 'n' ", then activate via the idxbuild command: proutil lumbertrack -C idxbuild table my_tbl -B 5000 -T \usr\tmp -rusage -thread 1 -threadnum 4 -SG 64 -TB 64 -TM 32 -TMB 1024 -TF 80 -datascanthreads 12 -mergethreads 4 The idxbuild variation only takes a few minutes. It seems odd that there would be such a vast difference in performance. Ideally the two approaches would be done in a similar amount of time. The first approach substantially under-utilizes the disk and CPU. But unfortunately that is our normal procedure for schema changes and we avoid running proutil commands except in fairly unusual circumstances. IMHO Adding a couple of indexes doesn't seem to qualify as an unusual circumstance. Yet the difference in performance seems extreme, so perhaps we should be thinking about this type of schema change differently than we have in the past. Is it a common thing for OE dba's to resort to using "proutil" commands when adding indexes? I see that there is a KB out there about this, and I'm wondering if we have been doing it wrong. (see Progress KB - Best way to add new index to table with large count of records? ) I suppose it may depend on whether the dba can tolerate waiting for many hours to perform a basic schema change.
Continue reading...
Continue reading...