|
I can confirm this on FB3
query: ------------------------------------ select * from opt_test where sysid+0 = 1 and clid = 23 and cust_type = 1 and cust_id = 73 order by order_no desc PLAN SORT (OPT_TEST INDEX (OPT_TEST_IDX1)) 22 fetches --------------------- query: ------------------------------------ select * from opt_test where sysid = 1 and clid = 23 and cust_type = 1 and cust_id = 73 order by order_no desc PLAN (OPT_TEST ORDER OPT_TEST_IDX3) 6707 fetches --------------------- Please post query plans for both v2.5.8 and v3.0.4.
Moved code to create and populate table to a file attachment
Code for creating and populating table to reproduce the issue
|
FB2.5.8:
Query
------------------------------------------------
select * from opt_test
where
sysid = 1 and
clid = 23 and
cust_type = 1 and
cust_id = 73
order by order_no desc
Plan
------------------------------------------------
PLAN SORT ((OPT_TEST INDEX (OPT_TEST_IDX1)))
22 fetches
------------------------------------------------
FB3.0.4:
Query
------------------------------------------------
select * from opt_test
where
sysid = 1 and
clid = 23 and
cust_type = 1 and
cust_id = 73
order by order_no desc
Plan
------------------------------------------------
PLAN (OPT_TEST ORDER OPT_TEST_IDX3)
6409 fetches
------------------------------------------------