I compared cost of some queries on DB2 9.7 for Windows.
Because, I have no access to DB2 for iSeries.
Although, sometimes, it is meaningless to compare costs of different queries,
you might get some hints for effectiveness of queries by comparing costs.
But, please note that these tests were done on very poor(memory, processor and disk configuration) environment and with litte data.
Costs of queries are.....
a) EXISTS > b) IN > c) NOT EXISTS
But, the differences are small.
a) EXISTS
Code:
SELECT *
FROM table_name a
WHERE EXISTS
(SELECT 0
FROM table_name b
WHERE b.date_col = a.date_col
HAVING COUNT(*) = 1
)
;
b) IN
Code:
SELECT *
FROM table_name
WHERE date_col IN
(SELECT date_col
FROM table_name
GROUP BY date_col
HAVING COUNT(*) = 1
)
;
c) NOT EXISTS
Code:
SELECT *
FROM table_name t1
WHERE NOT EXISTS
(SELECT *
FROM table_name t2
WHERE t2.date_col = t1.date_col
AND NOT
(t2.a = t1.a AND t2.b = t1.b)
)
;
Followings are much less costs than previous three queries.
Costs are....
d) GROUP BY and HAVING < f) COUNT(*) OLAP specification < e) ROW_NUMBER() OLAP specifications
But, the differences of the three queries are small.
Note: f) COUNT(*) OLAP specification may not work on DB2 for iSeries.
d) GROUP BY and HAVING
Code:
SELECT MAX(a) AS a
, MAX(b) AS b
, MAX(user_nme) AS user_nme
, date_col
FROM table_name
GROUP BY
date_col
HAVING COUNT(*) = 1
;
e) ROW_NUMBER() OLAP specifications
Code:
SELECT a , b , user_nme , date_col
FROM (SELECT t.*
, ROW_NUMBER()
OVER(PARTITION BY date_col
ORDER BY a ASC , b ASC ) AS rn_asc
, ROW_NUMBER()
OVER(PARTITION BY date_col
ORDER BY a DESC , b DESC) AS rn_desc
FROM table_name t
) s
WHERE rn_asc = 1
AND rn_desc = 1
;
f) COUNT(*) OLAP specification
(This may not work on DB2 for iSeries.)
Code:
SELECT a , b , user_nme , date_col
FROM (SELECT t.*
, COUNT(*)
OVER(PARTITION BY date_col ) AS cnt
FROM table_name t
) s
WHERE cnt = 1
;
dBforums
>
Selecting records that appear only once in the table
12-17-09, 01:33
Linear Mode
