Jonathan, Now that's an interesting statement. May I ask how one can say that when the stats section indicates that 63 consistent blocks were retrieved from the database? My way of reading an explain plan has always been right to left, namely the statement with the most right offset is executed before the one with the next most right offset, which would mean a full table scan is done before the filter is applied to each row. Hence the 63 block gets for 49,250 rows. BTW: here is an autotrace from after I truncated the table and dropped the storage: Execution Plan ---------------------------------------------------------- Plan hash value: 134683083 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 0 (0)| | | 1 | SORT AGGREGATE | | 1 | | | |* 2 | FILTER | | | | | | 3 | TABLE ACCESS FULL| TEST1 | 1 | 2 (0)| 00:00:01 | --------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(NULL IS NOT NULL) Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 1 recursive calls 2 db block gets 6 consistent gets 0 physical reads 148 redo size 218 bytes sent via SQL*Net to client 238 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed Note the rows processed = 1 and the consistent gets is down to 6. Makes sense to me. Dick Goulet, Senior Oracle DBA 45 Bartlett St Marlborough, Ma 01752, USA Tel.: 508.573.1978 |Fax: 508.229.2019 | Cell:508.742.5795 RGoulet@xxxxxxxxxx : POWERING TRANSFORMATION -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis Sent: Friday, October 06, 2006 12:22 PM To: oracle-l@xxxxxxxxxxxxx Subject: Re: select count(*) Dick, The point about the 1=0 / null is not null filter is that line 2 of your plan (the FILTER) says: if null is not null then execute line 3 Line 3 (the full tablescan does not happen). 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: "Richard J. Goulet" <rgoulet@xxxxxxxxxx> To: <jonathan@xxxxxxxxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx> Sent: Friday, October 06, 2006 5:19 PM Subject: RE: select count(*) Sarma, I did the autotrace & got back the following: Execution Plan ---------------------------------------------------------- Plan hash value: 134683083 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 0 (0)| | | 1 | SORT AGGREGATE | | 1 | | | |* 2 | FILTER | | | | | | 3 | TABLE ACCESS FULL| TEST1 | 49250 | 59 (2)| 00:00:01 | --------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(NULL IS NOT NULL) Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 28 recursive calls 0 db block gets 63 consistent gets 188 physical reads 0 redo size 204 bytes sent via SQL*Net to client 238 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed I also did plans for with an index on the table and stats. In neither case did I see a group by occur, but the predicate was always there. Now someone did mention the high water mark of the table which I've seen before cause all sorts of pains in the backside. There's a note out on metalink which states that any full table scan HAS to scan all data blocks up the high water mark, even if they are empty just to be sure.there's nothing hanging out there all alone. In this case ythe presence of a lot of empty space below the HWM would be the explanation of your problem. Have seen it many times in PeopleSoft with their "temp" tables. Dick Goulet, Senior Oracle DBA 45 Bartlett St Marlborough, Ma 01752, USA Tel.: 508.573.1978 |Fax: 508.229.2019 | Cell:508.742.5795 RGoulet@xxxxxxxxxx : POWERING TRANSFORMATION ________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sarma Aryasomayajula Sent: Friday, October 06, 2006 12:06 PM To: jonathan@xxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx Subject: Re: select count(*) Hi Jonathan, Thank you very much for answer and you are right, in predicate section it says null is not null. I did the explain plan but I am not sure wether I can attach here or not? one other thing I observed is, in 10gR1 I see sort group by in the plan whereas in 10gR2 it is hash group by. When I checked metalink for hash group by I got a link which says it is a bug. Subject: Wrong Results Possible on 10.2 When New "HASH GROUP BY" Feature is Used Doc ID <https://metalink.oracle.com/help/usaeng/Search/search.html#file> : Note:387958.1 Regards, Sarma ________________________________ From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx> To: <avnsarma@xxxxxxxxxxx>,<oracle-l@xxxxxxxxxxxxx> Subject: Re: select count(*) 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 ------------------------------------------------------------------------ -------- 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 -- //www.freelists.org/webpage/oracle-l