insert into <new-table> select * from <old-table> ;

is the proper SQL syntax and the only one Informix recognizes for
insert into permanent tables.

HPL will probably be faster, but if you make the destination table raw
it will be pretty fast and it might be fast enough.

Depending on how many "millions of rows" you have, you might want to
also investigate fragmentation strategies and spread the table across
several dbspaces.

_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
============================================================
The information contained in this message may be privileged
and confidential and protected from disclosure. If the reader
of this message is not the intended recipient, or an employee
or agent responsible for delivering this message to the
intended recipient, you are hereby notified that any reproduction,
dissemination or distribution of this communication is strictly
prohibited. If you have received this communication in error,
please notify us immediately by replying to the message and
deleting it from your computer. Thank you. Tellabs
============================================================

HPL in express mode is the fastest. test it you'll see.

when you create the target tables make sure the first/next extent size
are properly configured.
and a checkpoint has been done before loading.

Superboer.

Tam OShanter schreef:

If you use named pipes to unload and load the data in parallel (one
onpload writing to a fifo and another onpload loading from the same
fifo) you can move a large table pretty quickly.  This strategy has the
added benefit of eliminating the intermediate step (and bottleneck) of
writing an unload file to disk (much like the insert into select *
does).  You can use multiple named pipes in a device to take advantage
of multiple CPUs, modify the onpload.std config parameters and configure
a No Conversion job to boost performance.

I did this recently with the following results on a 4 CPU machine with
disk attached to a SAN:

Number of rows:        45 million
Row size:        357 bytes (includes a varchar (250))
Unload/reload time:    25 minutes

I doubt you can get insert into select * from to outperform this.

Andrew Ford


-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org] On Behalf Of Superboer
Sent: Tuesday, October 03, 2006 3:12 AM
To: informix-list@iiug.org
Subject: Re: HPL vs. Select Into

HPL in express mode is the fastest. test it you'll see.

when you create the target tables make sure the first/next extent size
are properly configured.
and a checkpoint has been done before loading.

Superboer.

Tam OShanter schreef:

_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list