Informix - Problembeschreibung
Problem IT26946 | Status: Geschlossen |
HASH JOIN IS USED EVEN WHEN NESTED LOOP JOIN IS FASTER | |
Produkt: | |
INFORMIX SERVER / 5725A3900 / C10 - IDS 12.10 | |
Problembeschreibung: | |
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 | |
Problem-Zusammenfassung: | |
Local-Fix: | |
Lösung | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Kommentar | |
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) | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 13.11.2018 04.12.2019 04.12.2019 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version |