How Many Parameters can Rank() Take?Posted: July 24, 2008
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:
4 ) within group (order by
6 ) x
7 from dual
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.