Re: select count(*)

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <avnsarma@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • 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


Jonathan Lewis

The Co-operative Oracle Users' FAQ

Cost Based Oracle: Fundamentals

----- Original Message ----- From: "Sarma Aryasomayajula" <avnsarma@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, October 06, 2006 4:10 PM
Subject: select count(*)


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;


Elapsed: 00:00:00.07
S5UVAD@powls01 > sho user


N7OTHA@AOWLS01 > set timin on
N7OTHA@AOWLS01 > select count(*) from rpt_08_vew where 0=1;


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.





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


Other related posts: