Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: how to write this sql?

RE: how to write this sql?

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Mon, 19 Jul 2004 15:32:49 -0500
Message-ID: <0186754BC82DD511B5C600B0D0AAC4D607B00A3C@EXCHMN3>


Guang

   I think you improved method must eliminate the GROUP BY. Do an explain plan and see what the GROUP BY is causing Oracle to do. It is probably doing a full table scan followed by a sort. Perhaps the right index might improve the situation.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
I said it "looked" clear - Riddick

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Guang Mei Sent: Monday, July 19, 2004 2:50 PM
To: Oracle-L-freelists
Subject: how to write this sql?

Oracle 8173.

I have a query like this:

select x, count(1) cnt from ABC group by x;

which shows:

         X CNT
---------- ----------

         1         25
         2         18
         6        156
         7        529
         8         43
         9        355


What I want is

         X CNT
---------- ----------

         1         25
         2         18
         6        100
         7        100
         8         43
         9        100

This means any count > 100 will be replaced with value 100. I have no problem of displaying this. What I would like to have is to let oracle know that when the count reaches 100, stop counting for that x. This way oracle does not need to keep scan after cnt reaches 100, hopefully saving some time. (somehow it is similar to adding rownum=1 to let oracle stop when certain condition is met).

Is it possible to add something to the query to accomplish this?

TIA. Guang



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Mon Jul 19 2004 - 15:34:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US