ROW_NUMBER performance on large datasets

Database
Enthusiast

ROW_NUMBER performance on large datasets

Anyone have a better way to assign sequential numbers to a data set than ROW_NUMBER? It has some miserable performance when you are partioning it over a couple of columns with a large (>100Million rows) data set.

SELECT
ROW_NUMBER() OVER (PARTITION BY PRI_INDEX_COL, TYPE_CD_COL ORDER BY DATE1, DATE2)
FROM ...

The primary index is a NUPI and I am using this query to assisgn an ordered sequence number per PRI_INDEX_COL and TYPE_CD_COL.

R
1 REPLY
Senior Apprentice

Re: ROW_NUMBER performance on large datasets

There's no faster way to achieve this.

Is there a large number of additional columns, i.e. a large row size? OLAP functions keep all the data twice in spool.
What's the datatype of the PRI_INDEX_COL and TYPE_CD_COL?

Dieter