Re: Tom Kyte's book obsolescence

From: Mladen Gogala <mgogala_at_yahoo.com>
Date: 02 Feb 2008 14:11:33 GMT
Message-ID: <47a47a15$0$1344$834e42db@reader.greatnowhere.com>

On Fri, 01 Feb 2008 11:02:01 -0800, Charles Hooper wrote:

> Mladen, can you post one of analytical and non-analytical SQL statements
> that use the bigtab table. I have the books "Expert One-on- One" and
> "Expert Oracle Database Architecture", but neither includes SQL
> statements that reference the bigtab table.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Here are both queries:

Analytic version:

---

select owner,last_ddl_time,object_name,object_type from (select t1.*,

             max(last_ddl_time) over (partition by owner) max_time
      from bigtab t1
      )

where last_ddl_time=max_time:

Non-analytic version:

---

select owner,last_ddl_time,object_name,object_type from bigtab t1
where last_ddl_time=(select max(last_ddl_time) from bigtab t2

                     where t2.owner=t1.owner);

The queries in the book reference table BIG_TABLE but I typed it over and abbreviated the original name for the reason of being lazy.

-- 
http://mgogala.freehostia.com

Received on Sat Feb 02 2008 - 08:11:33 CST