Re: select count(*)

Hi Jonathan,

Thank you very much for answer and you are right,  in predicate section it says null is not null.

I did the explain plan but I am not sure wether I can attach here or not?

one other thing I observed is, in 10gR1  I see sort group by in the plan whereas in 10gR2 it is hash group by.

When I checked metalink for hash group by I got a link which says it is a bug.

Regards,

Sarma

---

From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
To: <avnsarma@hotmail.com>,<oracle-l@freelists.org>
Subject: Re: select count(*)
Date: Fri, 6 Oct 2006 16:36:33 +0100


Do the following:
explain plan for
select count(*) from wlcbs_master.rpt_08_vew where 0=1;

set linesize 180
set pagesize 50

select * from table(dbms_xplan.display);

This will produce a fairly complete execution plan
for the query - including the predicate information.

I would expect to see something that starts like:

----------------------------------------
| Id | Operation |
----------------------------------------
| 0 | SELECT STATEMENT |
| 1 | SORT AGGREGATE |
|* 2 | FILTER

And the FILTER predicate from the predicates section will
either say "1=0", or "null is not null" depending on version.

The indication is that you will get a plan, but the filter
line will ensure that the portion of the plan that is the
child to the filter will do no work.

It would be a little surprising if 10gR2 managed to find
a transformation that bypassed this optimization - but
all things are possible in the optiimizer.

The direct answers to your questions are:
a) Oracle will not read the table - the filter acts to short-circuit the read
b) Oracle SHOULD NOT read any data from the view for the same reason


Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


----- Original Message ----- From: "Sarma Aryasomayajula" <avnsarma@hotmail.com>
To: <oracle-l@freelists.org>
Sent: Friday, October 06, 2006 4:10 PM
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@powls01 > set timin on
>S5UVAD@powls01 > select count(*) from wlcbs_master.rpt_08_vew where
>0=1;
>
> COUNT(*)
>----------
> 0
>
>Elapsed: 00:00:00.07
>S5UVAD@powls01 > sho user
>
>In ACPT:
>
>N7OTHA@AOWLS01 > set timin on
>N7OTHA@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


--------------------------------------------------------------------------------


No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.407 / Virus Database: 268.13.0/464 - Release Date: 05/10/2006