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
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@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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
-- //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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
