RE: Explaination of OPTCOMPIND

Carol In general, I would suggest that you: 1. SET EXPLAIN ON for these queries in the real (not development) = database and see what query path the optimizer takes. In case there are = sequential scans maybe you can add some indexes which make the SELECT do = an indexed path retrieval. You would need to experiment a little here. 2. Since these statements are called frequently you would gain a lot by = PREPAREing these statements, so the optimizer does not repeatedly have = to generate a query path. Yes, it does it each and every time. Setting OPTCOMPIND =3D 0 (I think) is a legacy of previous versions of = Informix, so you know that a particular optimized query would run as it = was running on the old optimizer. I prefer OPTCOMPIND =3D 2 so it bases = a search path on the estimated cost. And BTW, my ISOLATION MODE is = COMMITTED READ. Hope this helps Sujit Pal DBA, Computer Sciences Corporation ---------- From: Carol Davies[SMTP:cdavies@csc.com] Sent: Friday, September 13, 1996 9:50 AM To: informix-list@rmy.emory.edu Subject: 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 =3D=3D> Nested loop joins will be preferred (where possible) over sortmerge joins and hash joins 1 =3D=3D> If the transaction isolation mode is not "repeatable read", optimizer behaves as in (2) 2 =3D=3D> 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?. =20 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 =3D 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 =3D 4 " "AND geo_association.geo_loc_id =3D " "dbi_lnd_aoi_assoc1.geoloc " "AND geo_association.geo_assoc_type !=3D 3"; --=20 Carol Davies cdavies@csc.com