Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 19, 2007

Basic Analytics versus Generic SQL

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 7:13 am

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

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: