Exists clause
hi,
I have a table v_station_info which has 1759 rows. I have a exists clause on
the table as
SELECT count(*) from v_station_info a where exists
(SELECT 1 from v_station_info b
where a.station_code = b.station_code ANDa.station_text = b.station_text AND
a.daylight_saving_st = b.daylight_saving_st AND
a.daylight_saving_ed = b.daylight_saving_ed AND
a.gmt_offset = b.gmt_offset AND
a.stn_shut_status = b.stn_shut_status AND
a.trce_dept_fl = b.trce_dept_fl AND
a.trce_fclty_tx = b.trce_fclty_tx)
Estimated Cost: 3
Estimated # of Rows Returned: 1
1) informix.a: SEQUENTIAL SCAN
2) informix.b: INDEX PATH
Filters: ((((((informix.a.station_text = informix.b.station_text AND
informix.a.daylight_saving_st = informix.b.daylig
ht_saving_st ) AND informix.a.daylight_saving_ed =
informix.b.daylight_saving_ed ) AND informix.a.gmt_offset = informix.b.gmt_
offset ) AND informix.a.stn_shut_status = informix.b.stn_shut_status ) AND
informix.a.trce_dept_fl = informix.b.trce_dept_fl )
Press Return to continue _tx = informix.b.trce_fclty_tx )
(1) Index Keys: station_code (Serial, fragments: ALL)
Lower Index Filter: informix.a.station_code = informix.b.station_code
NESTED LOOP JOIN
I think this query should return 1759 rows, but its returning count as 17.
What could be the reason.
Bye.
Do any of the rows contain NULLs in one or more of the columns being compared?
That would eliminate them from the EXISTS clause filter since no 'equals'
comparison containing a NULL will evaluate to TRUE, you'd have to include an IS
NULL filter also so, if gmt_offset could contain NULLS then the correct filter
for the sub-query (or a join) would be:
(a.gmt_offset = b.gmt_offset OR (a.gmt_offset IS NULL AND b.gmt_offset IS
NULL))
AND
Art S. Kagel
----- Original Message -----
From: Parameshwar.... <pcdudyala@yahoo.com>
At: 4/26 8:36
> hi,
> I have a table v_station_info which has 1759 rows. I have a exists clause on
the
> table as
>
> SELECT count(*) from v_station_info a where exists
> (SELECT 1 from v_station_info b
> where a.station_code = b.station_code AND> a.station_text = b.station_text AND
> a.daylight_saving_st = b.daylight_saving_st AND
> a.daylight_saving_ed = b.daylight_saving_ed AND
> a.gmt_offset = b.gmt_offset AND
> a.stn_shut_status = b.stn_shut_status AND
> a.trce_dept_fl = b.trce_dept_fl AND
> a.trce_fclty_tx = b.trce_fclty_tx)
>
> Estimated Cost: 3
> Estimated # of Rows Returned: 1
>
> 1) informix.a: SEQUENTIAL SCAN
>
> 2) informix.b: INDEX PATH
>
> Filters: ((((((informix.a.station_text = informix.b.station_text AND
> informix.a.daylight_saving_st = informix.b.daylig
> ht_saving_st ) AND informix.a.daylight_saving_ed =
informix.b.daylight_saving_ed
> ) AND informix.a.gmt_offset = informix.b.gmt_
> offset ) AND informix.a.stn_shut_status = informix.b.stn_shut_status ) AND
> informix.a.trce_dept_fl = informix.b.trce_dept_fl )
> Press Return to continue _tx = informix.b.trce_fclty_tx )
>
> (1) Index Keys: station_code (Serial, fragments: ALL)
> Lower Index Filter: informix.a.station_code = informix.b.station_code
> NESTED LOOP JOIN
>
> I think this query should return 1759 rows, but its returning count as 17.
What
> could be the reason.
>
>
> Bye.
I agree with Art's analysis - nulls are the likely culprits.
Did you consider:
SELECT count(*) from v_station_info
GROUP BY station_code, station_text, daylight_saving_st,
daylight_saving_ed, gmt_offset, stn_shut_status,
trce_dept_fl, trce_fclty_tx
HAVING COUNT(*) > 1;
You can adapt this to list the duplicated values rather easily - list the
grouping columns in the select list.
--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Information Management Division
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"
forum.subscriber@iiug.org wrote on 04/26/2005 05:52:44 AM:
>
> Do any of the rows contain NULLs in one or more of the columns
beingcompared?
> That would eliminate them from the EXISTS clause filter since no
'equals'
> comparison containing a NULL will evaluate to TRUE, you'd have to
> include an IS
> NULL filter also so, if gmt_offset could contain NULLS then the correct
filter
> for the sub-query (or a join) would be:
>
> (a.gmt_offset = b.gmt_offset OR (a.gmt_offset IS NULL AND b.
> gmt_offset IS NULL))
> AND
>
> Art S. Kagel
>
> ----- Original Message -----
> From: Parameshwar.... <pcdudyala@yahoo.com>
> At: 4/26 8:36
>
> > hi,
> > I have a table v_station_info which has 1759 rows. I have a
existsclause on
> the
> > table as
> >
> > SELECT count(*) from v_station_info a where exists
> > (SELECT 1 from v_station_info b
> > where a.station_code = b.station_code AND> > a.station_text = b.station_text AND
> > a.daylight_saving_st = b.daylight_saving_st AND
> > a.daylight_saving_ed = b.daylight_saving_ed AND
> > a.gmt_offset = b.gmt_offset AND
> > a.stn_shut_status = b.stn_shut_status AND
> > a.trce_dept_fl = b.trce_dept_fl AND
> > a.trce_fclty_tx = b.trce_fclty_tx)
> >
> > Estimated Cost: 3
> > Estimated # of Rows Returned: 1
> >
> > 1) informix.a: SEQUENTIAL SCAN
> >
> > 2) informix.b: INDEX PATH
> >
> > Filters: ((((((informix.a.station_text =
informix.b.station_text AND
> > informix.a.daylight_saving_st = informix.b.daylig
> > ht_saving_st ) AND informix.a.daylight_saving_ed =
> informix.b.daylight_saving_ed
> > ) AND informix.a.gmt_offset = informix.b.gmt_
> > offset ) AND informix.a.stn_shut_status = informix.b.stn_shut_status )
AND
> > informix.a.trce_dept_fl = informix.b.trce_dept_fl )
> > Press Return to continue _tx = informix.b.trce_fclty_tx )
> >
> > (1) Index Keys: station_code (Serial, fragments: ALL)
> > Lower Index Filter: informix.a.station_code = informix.b.
> station_code
> > NESTED LOOP JOIN
> >
> > I think this query should return 1759 rows, but its returning count as
17.
> What
> > could be the reason.
> >
> >
> > Bye.
>
>
>
>