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: select count(*)

RE: select count(*)

From: Pande, Rajendra <rajendra.pande_at_ubs.com>
Date: Fri, 6 Oct 2006 11:41:45 -0400
Message-ID: <125BE2CBD9387F458D424DCF96DEBEA80151FC1D@psle19.xchg.pwj.com>


Have you also checked the HWM on the table.

Check the blocks allocated to the table.  

Rajendra Pande

1000 Lincoln harbor

ITI Infrastructure

Ph - 201 352 1415 (19 422 1415)

Pager - 1 201 718 0176


From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sarma Aryasomayajula
Sent: Friday, October 06, 2006 11:10 AM
To: oracle-l_at_freelists.org
Subject: select count(*)  

Hi!  

I'm running the below query in PROD(10g R1) and ACPT(10g R2). the query returns in fraction of a second in PROD and, If I run the same query in ACPT, it's taking 30+ mins.  

In Prod

 <mailto:S5UVAD_at_powls01> S5UVAD_at_powls01 > set timin on S5UVAD_at_powls01 <mailto:S5UVAD_at_powls01> > select count(*) from wlcbs_master.rpt_08_vew where 0=1;  

  COUNT(*)


         0  

Elapsed: 00:00:00.07
S5UVAD_at_powls01 <mailto:S5UVAD_at_powls01> > sho user  

In ACPT:  

N7OTHA_at_AOWLS01 <mailto:N7OTHA_at_AOWLS01> > set timin on N7OTHA_at_AOWLS01 <mailto:N7OTHA_at_AOWLS01> > select count(*) from rpt_08_vew where 0=1;  

  COUNT(*)


         0  

Elapsed: 00:31:02.27

  1. If I apply condition "where 0 = 1" on a single table, does oracle reads entire table then applies this condition or otherwise since this is negative

condition does it apply without reading the entire table?

2. If the same condition is applied on a view which is join of 3 big tables(paritioned)? How does oracle executes the query?

Can any one explain the above scenarious.

Regards,

Sarma

Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you.

UBS Financial Services Inc.
UBS International Inc.

--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 06 2006 - 10:41:45 CDT

Original text of this message

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