partitioning a database into multiple dbspaces

I'm attempting to partition an existing database into multiple dbspaces so that I can assign the dbspaces to different physical disks in hopes of improving I/O performance. Unfortunately, I'm doing something wrong. My strategy is to perform a dbexport, modify the sql schema file generated by the dbexport command, reinitialize the database (create the new dbspaces, etc.), and then perform a dbimport. I think modifying the schema file is the critical step. I'm simply inserting an "in dbspace" clause in every "create table" and "create index" statement. I'm trying to create four dbspaces: 2 for the data in our "open" and "closed" tables, and 2 for the indexes for the open and closed tables. At first glance, the process seems to work. However, upon closer inspection I notice that I'm consuming significantly more disk space after the dbimport than I was using before. To figure out what was going on, I ran oncheck -pe on two systems - one containing the database before partitioning, and the other containing the database after the dbexport-import. What I found was that afer the dbimport, exactly the same amount of space was allocated to each table as before PLUS I now had additional space allocated for the indexes. (I expected the space allocated for the tables to be less than before, since the indexes were in a different dbspace.) What am I missing? BTW, I'm also dynamically generating HPL jobs to unload and load data for several large tables before and after running the dbexport and dbimport commands.

JOHN SPURGEON > I'm attempting to partition an existing database into > multiple dbspaces so that I can assign the dbspaces to > different physical disks in hopes of improving I/O performance. > > Unfortunately, I'm doing something wrong. > > My strategy is to perform a dbexport, modify the sql schema > file generated by the dbexport command, reinitialize the > database (create the new dbspaces, etc.), and then perform a dbimport. > > I think modifying the schema file is the critical step. I'm > simply inserting an "in dbspace" clause in every "create > table" and "create index" statement. I'm trying to create > four dbspaces: 2 for the data in our "open" and "closed" > tables, and 2 for the indexes for the open and closed tables. > > At first glance, the process seems to work. However, upon > closer inspection I notice that I'm consuming significantly > more disk space after the dbimport than I was using before. > For best disk usage you have many small extents with little waste. For performance you have fewer, larger extents with more waste. For even better performance you separate your indexes and your data and have more large areas of waste. This is a trade off. It is nor in fact waste, because as your data grows this 'waste' will be used. It is a question of granularity. from The Bullshit guide to Databases. ie I make it up as I go along. Colin Bull c.bull@videonetworks.com