Re: dbexport/dbimport problems
> Subject: Re: dbexport/dbimport problems
> Date: Thu, 2 Feb 1995 22:27:22 GMT
> Reply-To: dwc@cix.compulink.co.uk ("David Chan")
> Organization: Objective Research Limited
>
> You're right. I just checked on Online 6.00UE1. dbimport creates the
> index after loading the table. In which case I suggest that if you are
> loading a large table that will span multiple disks, you do them manually
> using LOAD.
>
> I've received a few e-mails asking why so here is the reply:
>
> Let's say that you have a table that spans 5 disks and the index accounts
> for 20% of that. If you load the data with no index, the table will fill
> the first four disks. When you index the table, this will fill up the
> last disk.
>
> When users start using this table, each indexed access will read 5 (say)
> index pages from the last disk and one page from one of the other four.
> Therefore the last disk will be accessed 20 times more than the other
> four causing a bottleneck
>
> David W Chan
> Objective Research Ltd
> dwc@cix.compulink.co.uk
Someone has borrowed my OnLine admin manuals, so I can't RTFM.
1. Is there a way to place a table's index in a dbspace other than the
dbspace containing the table, presumably on a different disk?
Offhand I would think this is a good idea. On long queries sorted by the
index (a recommended strategy), one disk would read the index while the
other read the data, reducing head contention. In the example you give,
if the LOAD mixes the data and index pages then you get most of the same
effect, but there might be a bit of thrashing during disk transitions. If
you can put the index into a different dbspace, this would make the
performance more uniform over dbimport vs. load.
2. Do Informix's indexes tend to be "deep" or "wide"?
Your 5 index reads per data read seems high to me, unless the table is
REALLY big, thus making even "wide" indexes into "deep" ones as well.
Even with deep indexes, wouldn't the first few levels stay in the cache,
reducing the physical reads to only the lower levels for subsequent index
searches?
Regards,
Alan
+---------------------------+-----------------------------------------------+
| R. Alan Popiel | Internet: alan@den.mmc.com |
| Martin Marietta, SLS | Voice: 303-977-9998 |
| P.O. Box 179, M/S 3810 | Standard disclaimers apply. Cutesy ones, too. |
| Denver, CO 80201-0179 USA | Your mileage may vary. Void where prohibited. |
+---------------------------+-----------------------------------------------+