Bear in mind though that the original query will only count rows where
b.award_number is not null whereas this new query will count all rows in
the result set.
Regards,
Dave
erickingus_at_hotmail.com wrote:
> Can you change it to this query:
>
> SELECT count(1) FROM RF_BALANCE_T b, rf_security_by_dceid s
> WHERE
> (s.award_number = 'ALL')
> OR (b.award_number = s.award_number AND s.project_number = 'ALL')
> OR (b.award_number = s.award_number AND b.project_number=
> s.project_number AND s.task_number = 'ALL')
> OR (b.award_number = s.award_number AND b.project_number=
> s.project_number AND b.task_number = s.task_number)
>
>
> eric
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Monday, January 12, 2004 3:04 PM
>
>
>
>>David - Can you post the EXPLAIN PLAN for both?
>>
>>Dennis Williams
>>DBA
>>Lifetouch, Inc.
>>dwilliams_at_lifetouch.com
>>
>>-----Original Message-----
>>Sent: Monday, January 12, 2004 1:14 PM
>>To: Multiple recipients of list ORACLE-L
>>
>>
>>Hi List,
>>
>>I have following sql that runs in 1 sec:
>>
>>SELECT b.* FROM RF_BALANCE_T b, rf_security_by_dceid s
>>WHERE
>>(s.award_number = 'ALL')
>>OR (b.award_number = s.award_number AND s.project_number = 'ALL')
>>OR (b.award_number = s.award_number AND b.project_number=
>>s.project_number AND s.task_number = 'ALL')
>>OR (b.award_number = s.award_number AND b.project_number=
>>s.project_number AND b.task_number = s.task_number)
>>
>>However, when I try to count above query as following, it hangs. Does
>>someone have any ideas?
>>
>>SELECT count(b.award_number) FROM RF_BALANCE_T b, rf_security_by_dceid s
>>WHERE
>>(s.award_number = 'ALL')
>>OR (b.award_number = s.award_number AND s.project_number = 'ALL')
>>OR (b.award_number = s.award_number AND b.project_number=
>>s.project_number AND s.task_number = 'ALL')
>>OR (b.award_number = s.award_number AND b.project_number=
>>s.project_number AND b.task_number = s.task_number)
>>
>>_________________________________________________________________
>>High-speed users-be more efficient online with the new MSN Premium
>
> Internet
>
>>Software. http://join.msn.com/?pgmarket=en-us&page=byoa/prem&ST=1
>>
>>--
>>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>>--
>>Author: David Boyd
>> INET: davidb158_at_hotmail.com
>>
>>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>>San Diego, California -- Mailing list and web hosting services
>>---------------------------------------------------------------------
>>To REMOVE yourself from this mailing list, send an E-Mail message
>>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>>the message BODY, include a line containing: UNSUB ORACLE-L
>>(or the name of mailing list you want to be removed from). You may
>>also send the HELP command for other information (like subscribing).
>>--
>>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>>--
>>Author: DENNIS WILLIAMS
>> INET: DWILLIAMS_at_LIFETOUCH.COM
>>
>>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>>San Diego, California -- Mailing list and web hosting services
>>---------------------------------------------------------------------
>>To REMOVE yourself from this mailing list, send an E-Mail message
>>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>>the message BODY, include a line containing: UNSUB ORACLE-L
>>(or the name of mailing list you want to be removed from). You may
>>also send the HELP command for other information (like subscribing).
>>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Hau
INET: davehau123_at_netscape.net
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Jan 13 2004 - 01:34:26 CST