Re: How to do a Top-N analysis, while avoiding subqueries

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Wed, 7 Jun 2006 14:15:00 +0300
Message-ID: <6e49b6d00606070415w23cc8d56w@mail.gmail.com>

Cannot see any problem with subquery i.e. why the second query is bad? BTW both statistics are copied from the second run of each select to avoid first time parsing overhead. Anyway to get the maximum count(*) for each city you need to scan all table or probably all index if the column is not null.

SQL> set arraysize 100
SQL> set autotrace on
SQL> create table temp as select * from all_objects;

Table created.

Elapsed: 00:00:03.05
SQL> select count(*) cnt, owner
  2 from temp group by owner
  3 order by cnt desc;

       CNT OWNER

---------- ------------------------------
     13893 SYS
     12287 PUBLIC

...
51 rows selected.

Elapsed: 00:00:00.03
Statistics

---

          0  recursive calls
          0  db block gets
        805  consistent gets
          0  physical reads
          0  redo size
       2350  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         51  rows processed

SQL> ed
Wrote file afiedt.buf

  1 select * from (
  2 select count(*) cnt, owner
  3 from temp group by owner
  4 order by cnt desc
  5 )
  6* where rownum <=1
SQL> /        CNT OWNER

---------- ------------------------------
     13893 SYS

Elapsed: 00:00:00.00
Statistics

---

          0  recursive calls
          0  db block gets
        805  consistent gets
          0  physical reads
          0  redo size
        388  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 2006/6/7, tristan.van.essen_at_accenture.com <tristan.van.essen_at_accenture.com>:
>
>
>
> Hi Muhammed
>
>
>
> It's just a case study. Is it possible to do without the sub query?
>
>
>
> Actually, the original question is not from me, but from a friend of mine. I
> was just thinking about a good solution all morning long and couldn't figure
> out a good answer to the question.
>
>
>
> I find it quite reasonable to try a simple thing like a Top-N analysis
> without the use of a (possibly costly) sub query.
>
>
>
> But maybe I'm just spoiled by the proprietary MySQL feature of LIMIT BY…
>
>
>
> Regards,
>
> Tristan
>
>
>
>
> ------------------------------------------------------------------------------------
>
> Tristan van Essen
>
> Accenture Technology Solutions AG
>
> Baslerstrasse 60
>
> CH-8048 Zürich
>
> Office: +41 44 405 3245
>
> Mobile: +41 79 654 0592
>
> Email: tristan.van.essen_at_accenture.com
> ________________________________
>
>
> From: Muhammed Soyer [mailto:msoyer_at_gmail.com]
> Sent: Mittwoch, 7. Juni 2006 12:02
> To: van Essen, Tristan
> Cc: oracle-l_at_freelists.org
> Subject: Re: How to do a Top-N analysis, while avoiding subqueries
>
>
>
>
> Why dont you want to use sub queries ? Any solid reason or just for
> curiosity ..
>
>
>
>
>
> 2006/6/7, tristan.van.essen_at_accenture.com <tristan.van.essen_at_accenture.com>:
>
>
>
> Hi folks, this is my first question in this mailing list.
>
>
>
> How do you do a top-n analysis while avoiding subqueries, but still use
> group functions on other columns?
>
>
>
> I have following Query:
>
>
>
> SELECT COUNT(city) quantity, city
>
> FROM customer
>
> GROUP BY city
>
> ORDER BY quantity DESC;
>
> This works fine so far with the exception that I only want the first record
> returned, instead of all. I tried to solve the problem with the ROWNUM
> function, but it won't work well. The problem here is that I must include
> ROWNUM in the GROUP BY clause, else the query fails. But then the result
> isn't right anymore. I wish there was any sort of LIMIT BY clause, like
> there is in MySQL.
>
>
>
> How can I solve this, while not using any subqueries?
>
>
>
> Regards,
>
> Tristan
>
>
>
>
>
> ------------------------------------------------------------------------------------
>
> Tristan van Essen
>
> Accenture Technology Solutions AG
>
> Baslerstrasse 60
>
> CH-8048 Zürich
>
> Office: +41 44 405 3245
>
> Mobile : +41 79 654 0592
>
> Email: tristan.van.essen_at_accenture.com
>
>
>
>
> This message is for the designated recipient only and may contain
> privileged, proprietary, or otherwise private information. If you have
> received it in error, please notify the sender immediately and delete the
> original. Any other use of the email by you is prohibited.
>
>
>
>
>
>
> This message is for the designated recipient only and may contain
> privileged, proprietary, or otherwise private information. If you have
> received it in error, please notify the sender immediately and delete the
> original. Any other use of the email by you is prohibited.

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 07 2006 - 06:15:00 CDT