11.6.3 Linux
I have a program that is accumulating GB of data from various database tables. The target Db has a data area, an index area, and the usual schema area.
I need to reset the db before starting my process, and I use "truncate area" to clear the data tables. "Truncate area" disables the indexes for all the tables in the truncated area, so I'm doing a full index rebuild to reactivate them.
As I'm watching the index rebuild process it looks like its scanning the entire data area which is ~170GB in size. Needless to say this takes a fair amount of time for a set of tables that're empty.
This leads me to a couple of questions:
> As I'm watching the index rebuild process it looks like its scanning the entire data area which is ~170GB in size.
Index 15 ( PUB.Customer, Name ): de-activated (8831) TMB value is 8, TM value is 8, SG value is 48, packing factor is 100. Area 10: Index scan (Type II) complete. Elapsed time: 0.001 Processing area 8 : (11463) Area 8: Record scan (Type II) complete. Elapsed time: 0.000
IMHO, it's a bug.
> is there a faster way to reactivate indexes for tables that are in an area that's freshly truncated?
If all indexes of those tables are resided in their own area than you can truncate this area before running idxbuild.
Otherwise idxbuild will do a few operations that could be avoided:
1. Sanity checks of the old index blocks (before step 2!);
2. Converting the old index blocks to the free blocks;
3. Rebuilding free chains.
> As I'm watching the index rebuild process it looks like its scanning the entire data area which is ~170GB in size.
Index 15 ( PUB.Customer, Name ): de-activated (8831) TMB value is 8, TM value is 8, SG value is 48, packing factor is 100. Area 10: Index scan (Type II) complete. Elapsed time: 0.001 Processing area 8 : (11463) Area 8: Record scan (Type II) complete. Elapsed time: 0.000
IMHO, it's a bug.
> is there a faster way to reactivate indexes for tables that are in an area that's freshly truncated?
If all indexes of those tables are resided in their own area than you can truncate this area before running idxbuild.
Otherwise idxbuild will do a few operations that could be avoided:
1. Sanity checks of the old index blocks (before step 2!);
2. Converting the old index blocks to the free blocks;
3. Rebuilding free chains.
George - truncating both the data and index areas solved it - no more scanning the empty table area and the process was complete in ~5% of the time.
If it's the whole database, a different approach could be to maintain a database that contains only the schema without any data and procopy that db over the db you want to clear,
no need to rebuild indexes after that.