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








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


Other related posts: