RE: DBIMPORT and indexes
OK,
Thought i'd add my 2 peneth on this subject. It seems to me that since
dbimport is generally used to load data created by a dbexport, that it is
entirely reasonable for the program to *always* create the table, load the
data and then create *all* the indexes.
The reason I say this is because the data that was exported should surely be
clean enough for the load to be 100% successful everytime, provided that NO
attempt is made to massage the data/schema prior to the load. After all the
data would not have got into the db without passing all the
constraints/unique etc requirements.
If people want to frig the data/schema then they should be prepared to
suffer the consequences!
I look forward to being corrected.
Mark Denham
BBC
London, UK
Mark.Denham@bbc.co.uk
}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