Informix - Problembeschreibung
Problem IT29031 | Status: Geschlossen |
QUERYING MAXIMUM VALUE OUT OF A GIVEN RANGE IN A DATE COLUMN WILL SCAN INDEX FROM END WITHOUT YIELDING VP | |
Produkt: | |
INFORMIX SERVER / 5725A3900 / B70 - IDS 11.70 | |
Problembeschreibung: | |
Consider following query, finding the last existing date value in a given year: select max(date_column) from my_table where year(date_column) = 2018; If an index exists having 'date_column' as only or leading field, you'll see a reverse aggregate key-only scan on such index scanning exactly as many index entries (not keys) as there are above the 'index key filter'. 1) owner.my_table: INDEX PATH (1) Index Name: owner.date_ix Index Keys: dt c i (Key-Only) (Reverse) (Aggregate) (Serial, fragments: ALL) Index Key Filters: (YEAR (owner.my_table.date_column ) = 2018 ) Query statistics will show as many rows scanned ('rows_scan'), as there exist rows (not key values) for years greater than 2018, duration can be considerable. This was found to be the cause behind recurring "listener-thread: err = -25582: oserr = 0" connection failures (and succeeding connections being delayed simultaneously), as a consequence of such scan potentially extensively blocking an entire vp. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * Users of IDS version prior to 11.70.xC10. * **************************************************************** * PROBLEM DESCRIPTION: * * QUERYING MAXIMUM VALUE OUT OF A GIVEN RANGE IN A DATE COLUMN * * WILL SCAN INDEX FROM END WITHOUT YIELDING VP * **************************************************************** * RECOMMENDATION: * **************************************************************** | |
Local-Fix: | |
Semantically identical query 'select max(date_column) from my_table where date_column | |
Lösung | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 06.05.2019 26.09.2019 31.08.2020 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version |