index creation

[ids2000]

Hi there,
my customer is asking me about the time consuming, the space consuming and the lock time used to create an index.
I can't use oncheck, as it locks the table permanently.

I tried to use some SQL-scripts, but nothing shows me exactly this kind of information.

Anything would help me.

Thanks,


FFO
"The only way of knowing is to understand"

 

[vpshriyan]

Hi,

The time, disk space and other overhead involved in index pages creation varies according to the prevalent conditions. The factors like load in the server box, quality of hardware, number of physical processors present, number of rows to process, specification back-end configurable parameters, number of disks available for sorting work etc, etc. has a direct bearing for the inclined / declined performance. You may at the best can only quote / measure it tentatively.

The SQL below shows some information on existing indexes:

select b.tabname, idxname, ti_npused pages_used
from sysmaster:systabinfo a, sysmaster:systabnames b,
systables c, sysindexes d
where ti_partnum = b.partnum and ti_partnum=c.partnum
and d.tabid=c.tabid and c.tabid > 99
order by 1

For calculating the object size and related issues, you may browse a thread quoted below:

Sizing an index: thread179-707185

If you have bulk index creation job coming up, you better revise the current set Read Ahead pages parameters in the $ONCONFIG file. Increase the values to an appropriate value and reset to original value once you have completed the indexing job.

RA_PAGES
RA_THRESHOLD

The functioning of the sorting mechanism can be deviated and manipulated for advantage by setting the 2 environment variables. You can set the PSORT_DBTEMP to multiple paths, so that the sorting threads can work concurrently on different paths at different disks (parallel soring operation). Set the PSORT_NPROCS to the number of physical processors that you have in your box.

PSORT_DBTEMP=/diskpath1:/dispkpath2:/diskpath3
PSORT_NPROCS=2

Regards,
Shriyan