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
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