All Forums Database
28 Jan 2015
Partition Row number usage

SELECT a.col1
,CASE
WHEN b.cust_id IS NULL
THEN 0
ELSE 1
END AS is_true
FROM DB1.table1 AS a
LEFT JOIN DB2.table2 AS b
ON (
a.col3 = b.cust_id
AND a.start_date BETWEEN '2015-01-01' AND '2015-01-02'
) QUALIFY 1 = ROW_NUMBER() OVER (
PARTITION BY a.col1 ORDER BY b.col4
,b.col5
)
WHERE a.start_date BETWEEN '2015-01-01' AND '2015-01-02'
The records in both tables are in millions.
Is there any way to optimize the above query. Its taking so much CPU time 

Saranya
Glass 225 posts Joined 04/10
29 Jan 2015

Saranya,
DDL for both tables would help.
 
Rglass

ravimans 54 posts Joined 02/14
03 Feb 2015

Saranya,
1) I am seeing the below peice used twice. Might be you are doing double check but using in anyone place is advisable.
AND a.start_date BETWEEN '2015-01-01' AND '2015-01-02'
WHERE a.start_date BETWEEN '2015-01-01' AND '2015-01-02'
2) Below given JOIN columns are PI columns or not? Also for start_date is there any partition created bcz you are trying to filter most of the data using date range.
a.col3 = b.cust_id
AND a.start_date BETWEEN '2015-01-01' AND '2015-01-02'
3) Required stats should be collected for all the above columns/join columns to make it perform better.

You must sign in to leave a comment.