Query Performance Problem SE 5.06.UC1
One of our clients with following environment:
Hardware: Olivetti SNX 400 with RAID
OS: UnixWare 2.01
INFORMIX SE 5.06.UC1
ISQL 4.14.UC1
is facing following problem:
The optimizer doesn't select the appropriate index and the response time
varies from seconds to 30 minutes as also the no of rows found in the same
SQL statement.
The update statistics statement has been run.
The index is checked with bcheck with no problem.
set optimization low, alter index to cluster etc didn't have any effect
at all.
Why does the Optimizer not choose the right index ?
Has someone with this environment faced a similar problem ?
Here is the table & index structure:
create table position
( firmaid char(4),
aufnr integer,
aufpos integer,
ausgabe char(4),
artnr char(15),
artlandkz char(3),
.....
)row length is 1089 and there are 108392 rows in this table
create unique index position_idx1 on position (firmaid,aufnr,aufpos);
create index position_idx2 on position
(firmaid,ausgabe,artnr,artlandkz);
QUERY
select * from position
where firmaid = "0200"
and aufnr = 11586
and aufpos >= 1
order by aufpos asc
Time: 28 min
6 rows retrieved
Estimated Cost: 40
Estimated # of Rows returned: 1
Temporary Files Required for: Order by
1) informix.position: INDEX PATH
Filters: (informix.position.firmaid = '0200' and
informix.position.aufnr = 11586 ...)
(1) Index Keys: firmaid ausgabe artnr artlandkz
Lower Index Filter: infromix.position.aufpos >= 1
QUERY
select * from position
where aufpos >= 1
and aufnr = 11586
and firmaid = "0200"
and aufnr = 11586
order by aufpos asc
Time: 3 sec
0 rows retrieved
Estimated Cost: 40
Estimated # of Rows returned: 1
Temporary Files Required for: Order by
1) informix.position: INDEX PATH
Filters: (informix.position.aufpos >= 1 and informix.position.aufnr =
11586 ...)
(1) Index Keys: firmaid ausgabe artnr artlandkz
Lower Index Filter: informix.position.aufnr = 11586
Thanks
Tolis Varnas
+---------------------------------------------------------------------+
| V+K Relational Solutions email: tvarnas@compulink.gr |
| Deligiorgi 26 tvarnas@orbit.de |
| 546 42 Thessaloniki Voice: (30) 31 820270 |
| Greece Fax: (30) 31 865463 |
+---------------------------------------------------------------------+