Re: Tricky SQL question

From: Jurij Modic <jmodic_at_src.si>
Date: Tue, 05 Jan 1999 21:28:15 GMT
Message-ID: <36926ed4.3402277@news.siol.net>

On 3 Jan 1999 22:41:25 GMT, "Canberra" <ctcanb_at_mail.zip.com.au> wrote:

>Consider the following table:
>
>SQL> desc keyword;
> Name Null? Type
> ------------------------------- -------- ----
> ID_OBJECT NOT NULL VARCHAR2(16)
> KEYWORD NOT NULL VARCHAR2(100)
>
>...[SNIP]...
>How can I only show the 10 most frequently occuring keywords ?
>Is a PL/SQL procedure (or function) the answer ?
>
>Any help gratefully accepted.

If you use oracle7.2 or higher, the following query will show you 10 most frequently occuring keywords from your table KEYWORDS:

SELECT v1.keyword FROM
  (SELECT max(v2.keyword) data_type, v2.cnt FROM

     (SELECT keyword, -1*COUNT(*) cnt FROM keyword
      GROUP by keyword
     ) v2

   GROUP BY cnt
  ) v1
WHERE rownum <= 10;

How large is the KEYWORD table? As far as performance is concerned, the above query should not bahave so bad. As you are searching for the count of keywords, the single full table scan is unavoidable, no indexes can help you here (because of the number of different keywords bitmap indexes are out of the question here, I guess). In addition to this table scan, the query will perform two sorts (two GROUP BY clauses), so I don't think any PL/SQL sollution will outperform this SQL query - or am I wrong?

BTW, the "-1*" stuff in the seccond inline view is used because you are looking for the TOP 10 occuring keyword. If you want to search for BOTTOM 10 keywords, simpy omit this "-1*".

>Thanks in advance,
>
>-Richard Barrott
>ctcanb_at_mail.dynamite.com.au
>u952297_at_student.canberra.edu.au

HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Jan 05 1999 - 15:28:15 CST