Re: Tom Kyte's book obsolescence

From: Mladen Gogala <>
Date: 02 Feb 2008 14:22:03 GMT
Message-ID: <47a47c8b$0$1344$>

On Sat, 02 Feb 2008 05:48:37 -0800, Gints Plivna wrote:

> I was really surprised!
> sga_target and sga_max_size both 1.5 G Without any explicit changes
> subselect version run in 1.01 sec while analytic version run 36.96 secs.
> However the hard disk diode was flashing all the time and autotrace
> showed 1 sort on disk. So with

Which is, essentially, the same thing I noticed. What prompted me to start investigating was complaint from one of my DW developers who told me that analytic functions are not as fast as she expected.

> alter session set workarea_size_policy = manual; alter session set
> sort_area_size = 2000000000; (~2G) alter session set hash_area_size =
> 2000000000; (~2G) I was able to minimize the analytic version down to
> 3.46 secs (with one sort in memory according to autotrace) which is 10
> times faster than the first analytic shot but 3 times slower than
> subquery version anyway.
> All the sqls are below.

If you tried compressing the BIG_TABLE and adding index on the LAST_DDL_TIME, the gap would be back.

> Speaking about the Mladen's "implication"

>> 2) Analytic functions are now just a neat trick, no longer a
>> performance
>>    improvement. The cost of the window buffer sort is relatively high
>>    compared to the cost of simple join.

> I don't agree with that. Analytics are just much more than maximum row
> within a group. For example with lag and lead one can look either fixed
> or computed rows backwards and forward (how to do that without
> analytics???), TOP N analysis not always include finding just maximum
> but finding N, where N>1 and generally all that would need more joins
> than one or sometimes even cannot simply be done with simple SQL without
> analytics.

Yes, that is true. Analytics really are a neat trick. Some things really cannot be done without the analytic functions. Unfortunately, it seems that self-join is faster for the queries that can be written in both ways. My post was not meant as dumping the analytic functions altogether, my post was just observing the difference between versions.

I got an email in an accusatory tone about my "complaining about Tom's book". I was not complaining about the book and will not ask Barnes and Noble for a refund. Unfortunately, I am not in a habbit of using RDBMS books as a bible, not even Tom Kyte's books. Even the word of Tom, given through the burning RDBMS on top of Mt. Sinai, can change from version to version.

Received on Sat Feb 02 2008 - 08:22:03 CST

Original text of this message