INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10

Costs for hash join are slightly less  but
execution time for nested loop join is better.

Query is:

SELECT   .....
FROM  tab1 , tab2
WHERE tab1.c1 = tab2.c1
and ...

Both table have a primary key constraint on c1.

"slow" plan:

  1) informix.tab1: SEQUENTIAL SCAN
  2) informix.tab2: SEQUENTIAL SCAN
DYNAMIC HASH JOIN
    Dynamic Hash Filters: informix.tab1.c1 = informix.tab2.c2

"fast" plan:

  1) informix.tab1: SEQUENTIAL SCAN
  2) informix.tab2: INDEX PATH

    (1) Index Name: informix. 100_1
        Index Keys: c1   (Serial, fragments: ALL)
        Lower Index Filter: informix.tab1.c1 = informix.tab2.c1
NESTED LOOP JOIN

not known / see Local fix

The value of (DS_TOTAL_MEMORY/DS_MAX_QUERIES) can affect both
hash join and index scan cost calculation.  Based on testing
(with the case key length and fullness listed below) set
DS_TOTAL_MEMORY/DS_MAX_QUERIES more than

Pagesize    Keys per node (Len = 4, fullness=0.75)
DS_TOTAL_MEMARY/DS_MAX_QUERIES
16K          16K*0.75/(8+4)=1024                            32 *
1024
8K            8K*0.75/(8+4)  = 512                             8
* 1024
4K            4K*0.75/(8+4) = 256
n/a (internal default minimum can handle correctly)
2K            2K*0.75/(8+4) = 128
n/a (internal default minimum can handle correctly)

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

13.11.2018
04.12.2019
04.12.2019