Re: select count(*)

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 6 Oct 2006 16:36:33 +0100
Message-ID: <056c01c6e95d$33ca3cc0$0200a8c0@Primary>

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:

Oracle will not read the table - the filter acts to short-circuit the read
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_at_hotmail.com> To: <oracle-l_at_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_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

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

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

Received on Fri Oct 06 2006 - 10:36:33 CDT