Basic Analytics versus Generic SQL

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