I have two queries from which I expected to get same result.
First one:
select d.serialno, d.location, d.ticket, d.vehicle, o.name, d.parrivaltime, e.etype
from deliveries d left outer join events e
on d.serialno=e.eserialno and d.client=e.eclient and d.carrier=e.ecarrier and d.location=e.elocation
left outer join operators o
on d.client=o.client AND d.driver=o.code AND d.carrier=o.carrier AND d.location=o.location
where d.statusmessage='CURRENT' and d.scheduleddate BETWEEN '08/02/2017' AND '08/03/2017' and d.dropno!=0 and d.location in ('MIAMID') AND (e.etype=107 or e.etype=108)
order by d.location, d.dropno, e.etype;
Second one:
select d.serialno, d.location, d.ticket, d.vehicle, o.name, d.parrivaltime, e.etype
from deliveries d, outer events e, outer operators o
where d.serialno=e.eserialno and d.client=e.eclient and d.carrier=e.ecarrier and d.location=e.elocation
AND d.client=o.client and d.driver=o.code and d.carrier=o.carrier and d.location=o.location
AND d.statusmessage='CURRENT' and d.scheduleddate BETWEEN '08/02/2017' AND '08/03/2017' and d.dropno!=0 and d.location in ('MIAMID') AND (e.etype=107 or e.etype=108)
order by d.location, d.dropno, e.etype;
However, from the first query, I got 1044 records. But from the second query, I got 876 records.
I also checked the explain.out file which is as below. But I still cannot figure out why the output record is different.
QUERY: (OPTIMIZATION TIMESTAMP: 09-06-2017 11:27:22)
------
select d.serialno, d.location, d.ticket, d.vehicle, o.name, d.parrivaltime, e.etype from deliveries d, outer events e, outer operators o where d.serialno=e.eserialno and d.client=e.eclient and d.carrier=e.ecarrier and d.location=e.elocation AND d.client=o.client AND d.driver=o.code AND d.carrier=o.carrier AND d.location=o.location AND d.statusmessage='CURRENT' and d.scheduleddate BETWEEN '08/02/2017' AND '08/03/2017' and d.dropno!=0 and d.location in ('MIAMID') AND (e.etype=107 or e.etype=108) order by d.location, d.dropno, e.etype
Estimated Cost: 304
Estimated # of Rows Returned: 71
Temporary Files Required For: Order By
1) jlong.d: INDEX PATH
Filters: (jlong.d.statusmessage = 'CURRENT' AND jlong.d.dropno != 0 )
(1) Index Name: informix.delivcapac1idx
Index Keys: scheduleddate location client customername (Serial, fragments: ALL)
Index Self Join Keys (scheduleddate )
Lower bound: jlong.d.scheduleddate >= 08/02/2017
Upper bound: jlong.d.scheduleddate <= 08/03/2017
Lower Index Filter: jlong.d.scheduleddate = jlong.d.scheduleddate AND jlong.d.location = 'MIAMID'
2) jlong.o: INDEX PATH
(1) Index Name: informix. 126_300
Index Keys: client carrier location code (Serial, fragments: ALL)
Lower Index Filter: (((jlong.d.driver = jlong.o.code AND jlong.d.location = jlong.o.location ) AND jlong.d.carrier = jlong.o.carrier ) AND jlong.d.client = jlong.o.client )
NESTED LOOP JOIN
3) jlong.e: INDEX PATH
Filters: ((((jlong.d.carrier = jlong.e.ecarrier AND (jlong.e.etype = 107 OR jlong.e.etype = 108 ) ) AND jlong.d.location = jlong.e.elocation ) AND jlong.d.client = jlong.e.eclient ) AND jlong.e.elocation = 'MIAMID' )
(1) Index Name: informix.ix154_17
Index Keys: eserialno (Serial, fragments: ALL)
Lower Index Filter: jlong.d.serialno = jlong.e.eserialno
NESTED LOOP JOIN
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 d
t2 o
t3 e
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 603 71 742 00:00.00 122
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 603 741 603 00:00.00 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 603 71 00:00.00 158
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t3 876 1597 1729 00:00.00 2
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 1044 71 00:00.01 291
type rows_sort est_rows rows_cons time est_cost
------------------------------------------------------------
sort 1044 71 1044 00:00.01 14
QUERY: (OPTIMIZATION TIMESTAMP: 09-06-2017 11:27:29)
------
select d.serialno, d.location, d.ticket, d.vehicle, o.name, d.parrivaltime, e.etype from deliveries d left outer join events e on d.serialno=e.eserialno and d.client=e.eclient and d.carrier=e.ecarrier and d.location=e.elocation left outer join operators o on d.client=o.client AND d.driver=o.code AND d.carrier=o.carrier AND d.location=o.location where d.statusmessage='CURRENT' and d.scheduleddate BETWEEN '08/02/2017' AND '08/03/2017' and d.dropno!=0 and d.location in ('MIAMID') AND (e.etype=107 or e.etype=108) order by d.location, d.dropno, e.etype
Estimated Cost: 254
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By
1) jlong.d: INDEX PATH
Filters: (jlong.d.statusmessage = 'CURRENT' AND jlong.d.dropno != 0 )
(1) Index Name: informix.delivcapac1idx
Index Keys: scheduleddate location client customername (Serial, fragments: ALL)
Index Self Join Keys (scheduleddate )
Lower bound: jlong.d.scheduleddate >= 08/02/2017
Upper bound: jlong.d.scheduleddate <= 08/03/2017
Lower Index Filter: jlong.d.scheduleddate = jlong.d.scheduleddate AND jlong.d.location = 'MIAMID'
2) jlong.e: INDEX PATH
Filters: ((jlong.e.etype = 107 OR jlong.e.etype = 108 ) AND jlong.e.elocation = 'MIAMID' )
(1) Index Name: informix.ix154_17
Index Keys: eserialno (Serial, fragments: ALL)
Lower Index Filter: jlong.d.serialno = jlong.e.eserialno
ON-Filters:(((jlong.d.serialno = jlong.e.eserialno AND jlong.d.client = jlong.e.eclient ) AND jlong.d.carrier = jlong.e.ecarrier ) AND jlong.d.location = jlong.e.elocation )
NESTED LOOP JOIN
3) jlong.o: INDEX PATH
(1) Index Name: informix. 126_300
Index Keys: client carrier location code (Serial, fragments: ALL)
Lower Index Filter: (((jlong.d.client = jlong.o.client AND jlong.d.driver = jlong.o.code ) AND jlong.d.carrier = jlong.o.carrier ) AND jlong.d.location = jlong.o.location )
ON-Filters:(((jlong.d.client = jlong.o.client AND jlong.d.driver = jlong.o.code ) AND jlong.d.carrier = jlong.o.carrier ) AND jlong.d.location = jlong.o.location )
NESTED LOOP JOIN(LEFT OUTER JOIN)
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 d
t2 e
t3 o
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 603 71 742 00:00.00 122
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 1752 1597 1729 00:00.00 2
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 876 1 00:00.00 254
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t3 1752 9862 876 00:00.00 1
type rows_prod est_rows time est_cost
-------------------------------------------------
nljoin 876 1 00:00.01 254
type rows_sort est_rows rows_cons time est_cost
------------------------------------------------------------
sort 876 1 876 00:00.01 0
Can anybody help to analyze the explain file and give the reason of different output?
Thanks
