| 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: why ?????????
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
>>> oramagic_at_hotmail.com 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: DLANDRUM_at_zalecorp.com 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: ListGuru_at_fatcity.com (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).Received on Thu Apr 03 2003 - 19:40:49 CST
 |
 |