Re: dbexport/dbimport problems

For small tables, it is safe to assume that index pages are cached. For large tables, you'd be surprised how low the caching rate is. I came across this problem by accident when reloading a large table only to find that a batch job that used to take one hour took 5 hours instead. At the time, we used an in-house written dbexport/dbimport which kept all the dbspace, lock and extent information. Basically, the table had over 50M rows, and the primary index had 5 levels. Only the first 2-3 levels were cached because the 4th level already took up more than 50,000 pages. Therefore the lower 2-3 levels never had the chance of being cached so these pages had to be read in each time before the data page was read from one of the other disks. I can't remember how many disks held the data pages but if it was 4, the index disk would be used 8-12 times more. I think the create-load-index took about half a day and the create-index-load took 3-4 days! The update stats took half a day. We also reduced the number of buffers from 30000 to 10000 without any loss in performance. Big databases are a nightmare. David W Chan Objective Research Ltd dwc@cix.compulink.co.uk