Some days ago we found out a new comparison study on psoug and send an mail to OracleTURK to discuss on these differences especially with people who have also MS SQL knowledge.
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;
– No Bitmap Index,
– No Materialized Views,
– No Model Functions,
– No Hierarchical Operators,
– Limited Analytic Functions
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;
Code listing 60 : Basic Analytics versus Generic Sql
| Rows in table | CPU Analytics | CPU Generic | Elapsed Analtics | Elapsed Generic | Consist. Gets Analytics | Consist. Gets Generic |
| 1,000 | 95 | 206 | 00:00.06 | 00:00.12 | 4 | 16 |
| 10,000 | 142 | 791 | 00:00.53 | 00:05.96 | 17 | 68 |
| 100,000 | 624 | 64311 | 00:05.34 | 10:41.09 | 171 | 122192 |
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
