sql optimization question

From: Guang Mei <GMei@xxxxxx>
To: Oracle-L@xxxxxxxxxxxxx
Date: Tue, 22 Feb 2005 10:53:17 -0500
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.  
*************************************************************************
--
//www.freelists.org/webpage/oracle-l
sql optimization question

Other related posts:
