Issue with Rank/row_number in table

Database
Enthusiast

Issue with Rank/row_number in table

Hi 

I have a table with 19 M records and we are using row_number to get ranks on 10/11 cols. Something like:

row_number () over (partition by cola,colb,colc,cold  order by col1 desc) rank1,

row_number () over (partition by cola,colb,colc,cold  order by col2 desc) rank2,

row_number () over (partition by cola,colb,colc,cold  order by col3 desc) rank3,

row_number () over (partition by cola,colb,colc,cold  order by col4 desc) rank4,

row_number () over (partition by cola,colb,colc,cold  order by col5 desc) rank5,

.......

row_number () over (partition by cola,colb,colc,cold  order by col10 desc) rank10

Along with this we are also selecting 110/15 other columns. 

This query gets aborted either due to spool space issue or due to high CPU utilization.

Could any one let me know how to fine tune it?

Note: We are using TD13.

Thanks.

1 REPLY
Senior Apprentice

Re: Issue with Rank/row_number in table

Each RANK will be a seperate step in explain and each step will spool all the rows twice.

What datatypes are the PARTITION/ORDER BY columns? VARCHARs are expanded to CHARs in that case, this might result in high spool usage.

Depending on the size of the "other columns" a solution might split the query in two Derived Tables, the data needed for RANKing and the "other columns" and then join them back.

Dieter