RE: how to write this sql?
- From: "Guang Mei" <gmei@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 19 Jul 2004 16:38:36 -0400
Looping using pl/sql is too slow. The current sql query is relatively fast
and it usually returns 20 ~ 30 rows. I just thought there might be a way to
make it a bit faster by not counting the extra rows I don't need.
Well right now I basically have to do
if cnt > 100 then cnt :=100 end if;
for every rows in the result set. This line of code is not a performance hit
though.
Guang
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Anthony Molinaro
Sent: Monday, July 19, 2004 4:28 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: how to write this sql?
Guang,
you can try this:
select x, decode(sign(count(*)-100),1,100,0,100,count(*)) from ABC group
by x;
But it stills does the count. The only way for you to count and stop at
100 is to loop (in plsql)
and check the value one by one, but that would be pretty inneficient
compared to the above.
Hope that helps,
- ant
-----Original Message-----
From: Guang Mei [mailto:gmei@xxxxxxxxxx]=20
Sent: Monday, July 19, 2004 3: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=3D1 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@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
