For me a database which runs on just a spesific plathform is already out of the game. Oracle runs on;
– AIX 5L Based Systems (64-Bit)
– hp OpenVMS, hp Tru64 UNIX, hp-ux Itanium, hp-ux PA-RISC (64-Bit)
– IBM z/OS (OS/390), IBM zSeries Based Linux
– Linux Itanium, Linux on POWER, Linux x86, Linux x86-64
– Microsoft Windows, Microsoft Windows (64-Bit) on Intel Itanium, Microsoft Windows (x64)
– Solaris Operating System (SPARC 64-Bit), Solaris Operating System (x86), Solaris Operating System (x86-64)
But since MS SQL is one of the largest in the market I was very suprised to see these difference;
I always thought database vendors in time got very close to each other. I know there are fundamental differences like locking mechanism and NULL behavior, but especially this differences in the features vendors supply really surprised me.
So I wanted to experience how my daily life without same basic Oracle analytic functions could be, here are some results of my following test, let the numbers speak;
|Rows in table||CPU Analytics||CPU Generic||Elapsed Analtics||Elapsed Generic||Consist. Gets Analytics||Consist. Gets Generic|
Ok, nothing unexpected here. Someone may suggest these functionalities may to be written in T-SQL etc. but be careful above results are showing some proofs of the Universal mantra on data intensive processing; “You should do it in a single SQL statement if at all possible” No function implemented by pl/sql or t-sql etc. outside the SQL engine could beat them in terms of resource consumption, so as a result responce timings.
Just after my conclusions, a good news from Mr.Thomas Kyte; “..been thinking about writing a book just about analytics (but wait’ll you see the SQL Model clause in 10g)..” I am sure that book will be great :)
Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Refences Used :
Effective Oracle by Design by Thomas Kyte Chapter 1-The right approach to building applications, Page 15