sql optimization question

From: Guang Mei <GMei_at_ph.com>
Date: Tue, 22 Feb 2005 10:53:17 -0500
Message-ID: <7E62D965D357694C993D4F0E13B3C39B04D37EF2@phexchange.ph.com>

Hi,

I have a following sql in a pl/sql function against oracle 8i and 9i. However I am only interested to see if count(*) >0 or not for a particular USERID-COMPANYRID pair. This means, after the sql bulk collect into arrays, I will loop through the arrays to do something like

if array_count (i) = 0 Then

do something End if;

Right now this sql will get all the counts (say 165), but I only need one to make decision. So is there a way to optimize the sql (say using rownum=1 somewhere) so that I would get this:

USERID COMPANYRID COUNT(*)
---------- ---------- ----------

        15          7          1
        35          7          1
        90          7          1
       293          7          1
       320          7        1
       434          7        1
       535          7          1

here is the actual result from the query:

SQL> select A.UserId, A.CompanyRid, Count(*) 2 from usercompanyapplications A, (select distinct COMPANYRID,UserId

  3                                                 from
usercompanyapplications
  4                                                 where  Application = 1
  5                                                 and    UserId in (select

RID
6
from Users
7
where Customer = 1)

  8                                                 ) B
  9  Where A.UserId          = B.UserId 
 10  and     A.CompanyRid = B.CompanyRid

11 and A.Application != 1
12 Group By A.UserId, A.CompanyRid;

USERID COMPANYRID COUNT(*)
---------- ---------- ----------

        15          7          7
        35          7          5
        90          7          5
       293          7          7
       320          7        165
       434          7        165
       535          7          9

7 rows selected.

Guang

PRIVILEGED AND CONFIDENTIAL:
This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies.

--

http://www.freelists.org/webpage/oracle-l Received on Tue Feb 22 2005 - 10:54:44 CST