RE: select count(*)

Sarma,  

    Before you run the statement in each instance try setting "set autotrace on". That should give you your answer.     

Dick Goulet, Senior Oracle DBA

45 Bartlett St Marlborough, Ma 01752, USA Tel.: 508.573.1978 |Fax: 508.229.2019 | Cell:508.742.5795

RGoulet_at_kanbay.com
: POWERING TRANSFORMATION  

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
S5UVAD_at_powls01 <mailto:S5UVAD_at_powls01> > set timin on S5UVAD_at_powls01 > select count(*) from wlcbs_master.rpt_08_vew where 0=1;  

  COUNT(*)

         0  

Elapsed: 00:00:00.07
S5UVAD_at_powls01 > sho user  

In ACPT:  

N7OTHA_at_AOWLS01 > set timin on
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

• http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l