Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: select count(*)
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:
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
> 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-lReceived on Fri Oct 06 2006 - 10:55:44 CDT
![]() |
![]() |