Re: Generated Keys in a DataWarehouse

Yes. If you do it within Informix then any linking objects that get loaded have to 'do a lookup' to return the proper key which they might not have to do outside of the DB. Your mileage may vary. If there is no such 'lookup' then the issue is not there. There is also a technique to doing this 'lookup' which can run at acceptable warehouse speeds: e.g. a lookup table with a descriptive column 'desc' and a key column 'key' where you want to replace the passed in 'desc' with the proper 'key': foo is the data file in question and has col1, col2, col3, desc you want to build col1, col2, col3, key load foo into a temp table insert into destination table select a.col1, a.col2, a.col3, b.key from temp a, lookup b where a.desc = b.desc This will run faster than a row by row lookup of 'key'. Primarily because you are taqking advantage of the engine to do the work rather than reading each row into a client and then pushing it back. You can also get parallel performance this way. But it may be a big join - make sure you set aside enough memory to handle it. In your case the 'key' is that serial value. cheers j. "Viegas, John" <ViegasJP@panasonic.com> on 05/21/98 11:22:37 AM To: "'informix-list@iiug.org'" <informix-list@iiug.org> cc: (bcc: Jack Parker/Boston50/Epsilon) Subject: Generated Keys in a DataWarehouse We are going to be implementing a new datawarehouse using generated keys. The extraction program we are using is Prism. We are running Informix 7.21 on AIX 4.1.5. Is there any advantage of using serial datatype where informix generates the keys over a numeric datatype and generating the keys through prism?? Thanks in advance, JP