RE:Re: DBIMPORT and indexes

>From: goldberg@sccsi.com (Steve Goldberg) >Date: 30 Jul 1995 15:12:18 GMT >X-Informix-List-Id: <news.15856> > >>During dbimport the table is created, data is loaded, and then the indexes for >>that table are created, it is all done sequentially per table. > >All, >We have differing ideas on how this works. Informix support (Jonathan?) >can you shed some light on this? Well, since there didn't seem to be an answer available to you by inspecting the behaviour, I went about trying to export and import a database to see what happened. By inflating a stores database customer table to 3000 rows or so, it was fairly clear that the load occurred, then the indexes were created -- it is absolutely true that the table has to be created first, of course. Since such a simple test seemed to resolve it but there also seemed to be some confusion about whether this is what happens or not, I also went to inspect the source code. It is fairly clear that once the CREATE TABLE statement is executed, the data is loaded, and then any supplementary SQL statements are executed. This includes specifically the CREATE INDEX statements. The only possible cause of confusion is what happens if there are PRIMARY KEY, FOREIGN KEY or UNIQUE constraints on the table. To test this, I created a table in a stores database: CREATE TABLE xref ( customer_num INTEGER NOT NULL REFERENCES customer CONSTRAINT f1_xref, manufacturer CHAR(3) NOT NULL REFERENCES manufact CONSTRAINT f2_xref, order_num INTEGER NOT NULL REFERENCES orders CONSTRAINT f3_xref, PRIMARY KEY (customer_num, manufacturer, order_num) CONSTRAINT pk_xref, unique_key SERIAL NOT NULL UNIQUE CONSTRAINT ak_xref ); DBEXPORT generates the following CREATE TABLE statement and ALTER TABLE statements for this table: create table "johnl".xref ( customer_num integer not null, manufacturer char(3) not null, order_num integer not null, unique_key serial not null, unique (unique_key) constraint "johnl".ak_xref, primary key (customer_num,manufacturer,order_num) constraint "johnl".pk_xref ); [...] alter table "johnl".xref add constraint (foreign key (customer_num) references "johnl".customer constraint "johnl".f1_xref); alter table "johnl".xref add constraint (foreign key (manufacturer) references "johnl".manufact constraint "johnl".f2_xref); alter table "johnl".xref add constraint (foreign key (order_num) references "johnl".orders constraint "johnl".f3_xref); This will create two indexes as the table is created (for the UNIQUE and PRIMARY KEY constraints), and will then load the table with these two indexes in situ, and will then create the auxilliary indexes. 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>