This query:
1 SELECT COUNT(*) FROM
2 (SELECT WATER_DOW,NAME,COUNTY FROM LKDBMAIN
3 UNION SELECT ID_NO,NAME,COUNTY FROM STREAMS
4* )
takes 10 -12 seconds to return about 25,000 records
this query:
SELECT COUNT(*) FROM
(SELECT WATER_DOW,NAME,COUNTY,ALT_NAME,'' FROM LKDBMAIN
UNION SELECT ID_NO,NAME,COUNTY,ALT_NAME,AREA_NO FROM STREAMS
)
takes 10 – 12 seconds.
But This Query:
and this query:
1 SELECT COUNT(*) FROM
2 (SELECT WATER_DOW,NAME FROM LKDBMAIN
3 UNION SELECT ID_NO,NAME FROM STREAMS
4* )
Takes less than one second.
Why do I pay so big a price for the third column in a UNION and then subsequent columns seem to be free of charge?
Because UNION is doing a full table scan for each column you request to avoid duplicates.
If I am certain that LAKES and STREAMS tables don't share any records, I should use UNION ALL instead of UNION because UNION does a full join to avoid returning duplicated records. UNION ALL just returns one table appended to the other and so is much faster.
2:27:14 PM 
|
