How Many Parameters can Rank() Take?
Posted: July 24, 2008 Filed under: nerdism 5 CommentsLaurent 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.
Just a simple Hadoop DBA 
A logically sound system should’ve climbed to 0x100% 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 🙂