How Many Parameters can Rank() Take?
Posted: July 24, 2008 Filed under: nerdism 5 Comments »Laurent Schneider and I were discussing the analytics chapter of his nearly finished book, when he casualy mentioned that Rank() can take unlimited number of parameters. “Wow!” I said, “Thats cool. But how many parameters can it take before something crashes?”
I checked on my test 11g system, running on a puny windows xp.
Starting with 80 parameters, everything works fine:
SQL> select
2 rank(
3 1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8
,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8
,9,0
4 ) within group (order by
5 1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8
,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8
,9,0
6 ) x
7 from dual
8 /
X
———-
1
160 parameters also worked fine, 240 worked fine, returns results within two seconds.
So I tried with 320. It has been running for almost 15 minutes when I decided to check with 310 and 300. At that point Oracle took 100% cpu and my mouse could barely move. I had to crash Oracle. You could say that three processes each with slightly over 300 parameters crashes Oracle 
I continued donating my CPU to scientific discovery and reached interesting results:
At 255 parameters Rank still works fine. At 256, Oracle seems to go into an infinite loop – it never returns an answer and CPU climbs to 100%. Suspicious numbers, I’d say.
A logically sound system should’ve climbed to 0×100% cpu, i believe.
Yes, the aggregate function RANK takes an unlimited number of arguments
but the analytic function takes exactly zero arguments.
Thanks for your research DDL
So. Freakin. Awesome.
999
With more than 999 you will get ORA-962
(echo "set timi on";echo "select rank(";seq 1000|sed s/$/,/;echo "0) within group (order by ";seq 1000|sed s/$/,/;echo "0) from dual;")|sqlplus -s scott/tiger
0) from dual
*
ERROR at line 2003:
ORA-00962: too many group-by / order-by expressions
Elapsed: 00:00:00.01
oops, I mean 1000