Explaination of OPTCOMPIND

Hi Folks, We are running Online7.1 UC1 on RS/6000, AIX 3.2.5 and are trying to optimize a process that builds a table with 6-10 million rows from other large data tables and currently runs over 1-2 days. We have done all the indexing stuff, but was wondering if setting the OPTCOMPIND configuration variable (directs the optimizer) to "2" would help (it is currently set to "0"). The following definition of OPTCOMPIND is contains the following: 0 ==> Nested loop joins will be preferred (where possible) over sortmerge joins and hash joins 1 ==> If the transaction isolation mode is not "repeatable read", optimizer behaves as in (2) 2 ==> Use costs regardless of the transaction isolation mode. Nested loop joins are not necessarily preferred. Optimizer bases its decision purely on costs. My problem is that it don't really understand the above, for example, what is the Informix under-the-covers implications of doing a sortmerge join vs. a nest loop join?. Any general information/definitions would be most appreciated. Thanks in advance, the info from this forum is super. BTW, the 2 queries which are run repeatedly follow. They are relatively straightforward (I think): "INSERT INTO dbi_lnd_aoi_assoc " "SELECT raphic_location418.geo_loc_id, " "raphic_location418.geo_loc_type, reason_flag " "FROM raphic_location418, dbi_lnd_aoi_assoc1 " "WHERE raphic_location418.geo_loc_id = geoloc"; "INSERT INTO dbi_lnd_ind_asc1 (geoloc, reason_flag) " "SELECT UNIQUE geo_assoc_id, 6 " "FROM geo_association, dbi_lnd_aoi_assoc1 " "WHERE dbi_lnd_aoi_assoc1.reason_flag = 4 " "AND geo_association.geo_loc_id = " "dbi_lnd_aoi_assoc1.geoloc " "AND geo_association.geo_assoc_type != 3"; -- Carol Davies cdavies@csc.com