Thanks , That's what I guessed . But was feeling lazy to do 10046 tracing . N E way thanks for the info . -ak
----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, April 03, 2003 5:40 PM > 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). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: AK 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).
