Re: select count(*)
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 6 Oct 2006 18:06:47 +0100
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
-----
- dynamic sampling used for this statement
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;
-- gather table stats on t1
alter tablespace {of your choice} offline
select * from t1 where 1 = 0;
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:48 PM
Subject: RE: select count(*)
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
Other related posts:
