Ak,
I think what you're seeing is the result of the parse not necessarily
what ends up happening.
I ran a similar circumstance with a 10046 trace on and it reports no
physical reads, no logical reads, and returns 1 row. The "explain plan"
still shows a full table scan of my table but the trace doesn't report
any rows or blocks read from that table.
It's probably also worth noting that 100% of my waits were sqlnet
message to / from client.
Also, I ran this test because I didn't know the answer and thought it a
very good question.
The table is one I created called crazy_table as select * from
user_objects.
The query is
select count(*) from crazy_table where 1=2;
Here, I paste relevant sections of my trace output, but to see the
whole picture, you might want to run some tests of your own:
SUMMARY OF CALLS BY USER (INTERNAL LAST) AND NON-RECURSIVE/RECURSIVE
====================================================================
OVERALL TOTALS PER CALL FOR ALL NON-RECURSIVE STATEMENTS FOR USER 44
(ARTHUR)
call count cpu elapsed disk query
current rows misses
------- --------- --------- --------- ------------ ------------
------------ ------------ ---------
Parse 2 0.00 0.01 0 0
0 0 1
Execute 3 0.00 0.00 0 0
0 1 0
Fetch 2 0.00 0.00 0 0
0 1 0
------- --------- --------- --------- ------------ ------------
------------ ------------ ---------
total 7 0.00 0.01 0 0
0 2 1
*******************************************************************************************************************
SUMMARY OF CALLS BY COMMAND TYPE, USER (INTERNAL LAST) AND
NON-RECURSIVE/RECURSIVE
==================================================================================
OVERALL TOTALS PER COMMAND TYPE FOR ALL NON-RECURSIVE STATEMENTS FOR
USER 44 (ARTHUR)
command type count cpu elapsed disk
query current rows misses
----------------------- --------- --------- --------- ------------
------------ ------------ ------------ ---------
select................. 4 0.00 0.01 0
0 0 1 1
alter session.......... 1 0.00 0.00 0
0 0 0 0
pl/sql execute......... 2 0.00 0.00 0
0 0 1 0
----------------------- --------- --------- --------- ------------
------------ ------------ ------------ ---------
total.................. 7 0.00 0.01 0
0 0 2 1
SUMMARY OF PHYSICAL READS, LOGICAL READS, ROWS AND MISSES PER CURSOR
cursor user
id id command type disk query
current rows misses
------ ---- ----------------------- ------------ ------------
------------ ------------ ---------
1..... 44.. alter session.......... 0 0
0 0 0
2..... 44.. select................. 0 0
0 1 1
3..... 44.. pl/sql execute......... 0 0
0 1 0
------ ---- ----------------------- ------------ ------------
------------ ------------ ---------
total. .... ....................... 0 0
0 2 1
CURSOR_ID:2 LENGTH:43 ADDRESS:abb65150 HASH_VALUE:427590100
OPTIMIZER_GOAL:CHOOSE USER_ID:44 (ARTHUR)
select count(*) from crazy_table where 1=2
call count cpu elapsed disk query
current rows misses
------- --------- --------- --------- ------------ ------------
------------ ------------ ---------
Parse 1 0.00 0.01 0 0
0 0 1
Execute 1 0.00 0.00 0 0
0 0 0
Fetch 2 0.00 0.00 0 0
0 1 0
------- --------- --------- --------- ------------ ------------
------------ ------------ ---------
total 4 0.00 0.01 0 0
0 1 1
| Rows Row Source Operation
| ------------ ---------------------------------------------------
| 1 SORT AGGREGATE
| 0 .FILTER
| 0 ..TABLE ACCESS FULL CRAZY_TABLE
Explain Plan
---------------------------------------------------------------
...4 SELECT STATEMENT
...3 .SORT (AGGREGATE)
...2 ..FILTER
...1 ...TABLE ACCESS (FULL) OF 'ARTHUR.CRAZY_TABLE'
OWNER.TABLE_NAME
...owner.index_name num rows
blocks sample last analyzed date
----------------------------------------------------- ----------
---------- ---------- -------------------
ARTHUR.CRAZY_TABLE...................................
Event
Times Count Max. Total Blocks
waited on
Waited Zero Time Wait Waited Accessed
-----------------------------------------------------------------
--------- --------- --------- --------- ---------
SQL*Net message from client (idle)...............................
2 2 0.00 0.00
SQL*Net message to client (idle).................................
2 2 0.00 0.00
-----------------------------------------------------------------
--------- --------- --------- --------- ---------
total............................................................
4 4 0.00 0.00 0
non-idle waits...................................................
0 0 0.00 0.00 0
idle waits.......................................................
4 4 0.00 0.00
>>> [EMAIL PROTECTED] 04/03/03 05:58PM >>>
If I execute a query like ,
select count(*) from some_table where 1=2 .
why does oracle bothers to look at tables . Since there is only one
condition which is false .
If I write a sql like
select count(*)
from some_table
where column_a= :a
and a is not null;
it should not even go to look at table , but explain plan suggests that
it does a index /table scan .
-ak
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Darrell Landrum
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
