Re: DBIMPORT and indexes
OK,
I omitted an option for "it depends" because, as has been pointed out, the
source code I was scanning was for version 7.x. The dbimport I tested was
6.0x. I have not got the energy left to worry about 5.0x or earlier
versions of dbimport.
If there are '*** load table ***' comments around, it seems altogether
reasonable to suppose that the load operation occurs at that point, and
therefore if the comment appears after the create index statements by
default, then the load will occur after the indexes are created, hence
slowing down the load at the expense of removing possible integrity
checking. If you edit the file and move the '*** load table ***' comments,
it may or may not work -- you can find out easily enough on a stores
database.
And if you try to confuse dbimport by including '*** load table ***'
comments in a file that didn't have them in the first place, it appears
that you get what you deserve -- double-loaded data, probably with index
problems.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
}Date: Mon, 31 Jul 1995 21:20:55 +0200
}From: Nils.Myklebust@CCMAIL.telemax.no
}Subject: Re: DBIMPORT and indexes
}X-Informix-List-Id: <list.7105>
}
}> johnl@informix.com (Jonathan Leffler) writes:
}>
}> So, the answer is "it depends".
}>
}> * If the table has no UNIQUE or PRIMARY KEY constraints on it, then the
}> table is created, then loaded, then indexed for FOREIGN KEY and
}> general indexes, in that sequence.
}>
}> * If there are UNIQUE or PRIMARY KEY constraints on the table, then the
}> table is created with the indexes for the UNIQUE or PRIMARY KEY
}> constraints, then loaded, then indexed for the FOREIGN KEY and general
}> indexes.
}>
}> Yours,
}> Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
}>
}>>>>
}
}Well, Jonathan, this is the case at our 7.10 OnLine on SCO also.
}But when we used dbexport from a 5.0x SE it generated the following
}in the .sql file:
}
}*** load table ***
}
}which was placed *after* the create index statements.
}This seemed to lead the 7.10 OnLine dbimport utility to create the
}indexes firts, and then load the table.
}It is some time since I did this, and I don't have the time to test it
}again now, so I *may* be wrong, but don't think so. At the time I did
}remove the create index statements from the .sql file and executed
}them afterwards and am quite sure I got a significantly faster load
}this way (including the time to create the indexes).
}
}When I now put *** load table *** manually into the .sql file it
}still loads the table when it sees the create table statement, but
}it tries to load it again when it sees this load table stuff. This
}of course immediately leads to a duplicate unique index error. It
}seems to understand somehow what version did the unload.
}I never tried to move the *** load table *** before the create
}index statements in that old .sql file to see if that would have
}worked.
}
}Nils.Myklebust@ccmail.telemax.no
}NM-data, Dalsbergstien 7, N-0170 Oslo, Norway
}My opinions are those of my company