Partition Row number usage

Database
Enthusiast

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 

Tags (1)
2 REPLIES
Enthusiast

Re: Partition Row number usage

Saranya,

DDL for both tables would help.

Rglass

Enthusiast

Re: Partition Row number usage

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.