| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: select count(*)
Dick,
"I've always done X" doesn't mean that X is the right thing to do. As the man says in "Pirates of the Caribbean" - they're not so much rules as guidelines. The "over to the right, near the top" thing is a consequence of the correct recursive descent - but it's only an approximation and doesn't give a full description of the recursive descent of a plan.
The answer to the 63 blocks is here:
Note
It is an interesting anomaly of order of work that the optimizer has sampled the table before deciding an execution plan that says it doesn't visit the table.
Try this:
create table t1 tablespace {of your choice} as select * from all_objects;
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
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
| 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
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_at_kanbay.com
: POWERING TRANSFORMATION
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Friday, October 06, 2006 12:22 PM
To: oracle-l_at_freelists.org
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
Sarma,
I did the autotrace & got back the following:
Execution Plan
| 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
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_at_kanbay.com
: POWERING TRANSFORMATION
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sarma Aryasomayajula
Sent: Friday, October 06, 2006 12:06 PM
To: jonathan_at_jlcomp.demon.co.uk; oracle-l_at_freelists.org
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_at_jlcomp.demon.co.uk>
To: <avnsarma_at_hotmail.com>,<oracle-l_at_freelists.org>
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:
| 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 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_vewwhere
>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, doesoracle
>negative >condition does it apply without reading the entire table? > >2. If the same condition is applied on a view which is join of3 big
>tables(paritioned)? How does oracle executes the query? > >Can any one explain the above scenarious. > >Regards, > >Sarma > >
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 06 2006 - 12:06:47 CDT
 |
 |