Unload vs. Insert into

Here is the SQL command I was --trying-- to use insert into B select * from A order by C Too Bad this is invalid (05.0x SE) the "order by" is not supported. Instead, I can use the round-about method: unload to F select * from A order by C load from F insert into B My question, is WHY?

Vince - Yes, logical ordering is accomplished via an index, not via the physical order of the data in the table. In a relational system, the concept of physically ordered data DOES NOT APPLY. Of course, you can quickly *retrieve* the data in the intended order, simply by building an index on column "C". Then, when you select data from this table, and ORDER BY C, your results will come back immediately. Please be aware that, even if you manage to get the data physically in the order you wish, the physical order will NOT necessarily be maintained as you add and delete records from this table. Your approach may succeed ONLY if you can absolutely guarantee that, once loaded, the table will never be maintained. Otherwise, you will encounter unexpected surprises in the future. Rich "Pachiano, Vince" wrote: > Here is the SQL command I was --trying-- to use > > insert into B select * from A order by C> > Too Bad this is invalid (05.0x SE) > the "order by" is not supported. > > Instead, I can use the round-about method: > > unload to F select * from A order by C > load from F insert into B> > My question, is WHY? -- Richard C. Auslander Database Manager AirFlash, Inc. 1733 Woodside Rd., Suite #110 Redwood City, CA 94061 (650) 556-7928 www.airflash.com

Pachiano, Vince wrote: > > Here is the SQL command I was --trying-- to use > > insert into B select * from A order by C> > Too Bad this is invalid (05.0x SE) > the "order by" is not supported. > > Instead, I can use the round-about method: > > unload to F select * from A order by C > load from F insert into B> > My question, is WHY? Vic and Richard make the main point very well. There is not physical ordering in relational databases and Informix feels no pressure to maintain any order you TRY to impose by loading the data ordered. In addition there is no guarantee that loading the rows in order will cause sequentially added rows to reside sequentially on the same or adjacent pages in the database structure. Having said that there are a few benefits to having the data in the table fairly local to related rows. For example index building on the sort key will go faster, table scans and order by sorting on the sort key will also go somewhat faster until enough new rows have been added to reduce the benefit then you will have to resort the table. It occurs to me that this may be your goal here, to resort the table by copying its data, sorted, to an new table which will be renamed to be the new version of the original table. If so you are doing it the hard way. If you have an index on the key you want the rows sorted by then all you need to do is: ALTER INDEX myindex TO CLUSTER; and the table's data will be sorted according to the index's key and placed on disk contiguously just as you seem to require. If you already have that clustered index but the data has become unclustered - as it will through deletes, updates to the sort keys, and inserts - you can restore the clustering thus: ALTER INDEX myindex TO NOT CLUSTER; ALTER INDEX myindex TO CLUSTER; By the way if you still insist on doing this by copying the data look into my dbcopy.ec utility which is MUCH faster than "INSERT INTO ... SELECT..." and will copy the result set of ANY SQL SELECT statement, even with an ORDER BY clause if you insist. Dbcopy.ec is part of the package utils2_ak which I have submitted to the IIUG Software Repository and can be compiled with ESQL/C V7.10+ or C4GL V7.20+. Art S. Kagel

Art, Many thanks for your assistance. Your suggestion worked flawlessly > -----Original Message----- > From: Art S. Kagel [SMTP:kagel@bloomberg.net] > Posted At: Friday, May 07, 1999 10:06 AM > Posted To: informix > Conversation: Unload vs. Insert into > Subject: Re: Unload vs. Insert into > > [Pachiano, Vince] snip [Pachiano, Vince] > It occurs to me that this may be your goal here, to resort the table > by > copying its data, sorted, to an new table which will be renamed to be > the new version of the original table. If so you are doing it the > hard > way. If you have an index on the key you want the rows sorted by then > > all you need to do is: > > ALTER INDEX myindex TO CLUSTER;> > [Pachiano, Vince] snip [Pachiano, Vince]