Re: Explaination of OPTCOMPIND

In article <51c1gc$ng2@cssun.mathcs.emory.edu>, Carol Davies <cdavies@csc.com> writes >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. > No problemo. Gosh some thanks - don't often seen many of those!! >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"; > Dpends on what percent of each table you are reading. A nested loop join ------------------ foreach matching row in one table for each matching row in the other table create 'row' in 'result table'. end roreach end foreach A sort merge join is -------------------- read all of both tables, sort them by the join column(s) read both resulting sorted tables in order and pick lowest one from each if match then put into results I'm not explaining this very well so here an example of sorted tables with one integer key. E.g. table A table B 1 1 2 3 3 5 6 6 read 1 from A read 1 from B match! and discard both values read 2 from A read 3 from B 2<3 so discard value from A read 3 from A match! and discard both values read 6 from A read 5 from B 6>5 so read 5 from B match! and discard both values matching values are 1,3,6. Since values are sorted read never have to step back but can keep on reading forwards. Hash joins are -------------- read all of smaller table into memory and build a hash table from it. This is a table where given a key you can quickly work out whether or not it exists in the table. It is fast but you need to be able to fit all of the table in memory without swapping/paging. Read each row in the larger table and check if it is in the hash table. Sort merge joins and hash joins involve reading all of one or both tables into memory to do the join and so are faster than nested loop joins when a) you have the memory b) you are going to read most of the table anyway. i.e. cost of reading all rows < costs of read matching rows + required part of the associated index for the matching rows. -- David Williams