Hi Paul,
Analyze the table and see if you have any chained rows. If there are chained
rows and if the STATE_CODE field is not always in the last row piece, then a
extra consistent gets will be needed to get the column values from the trailing
row pieces of chained rows that are not excluded by the where clause predicates.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-----Original Message-----
Sent: Tuesday, 13 February 2001 3:31
To: Multiple recipients of list ORACLE-L
Hi all,
Could someone attempt to explain the difference
in the no. of "consistent gets" reported for
these 2 queries?
I have a table (TEST1) made up of 11,333 blocks.
No indexes on this table. I run two queries,
both reported to do full table scans (as
expected), one returning all the rows from the
table and one with a bogus condition resulting in
no rows returned. I expected, that since both
queries did full table scans, that the amount of
IO would be the same. Yet the query which
returned data did 3 times as much IO as the one
which did not. Output follows :
12:08:16 T10-SERVCBO-CH> @p2
12:08:22 T10-SERVCBO-CH> set autotrace traceonly
exp stat
12:08:22 T10-SERVCBO-CH> select
12:08:22 2 *
12:08:22 3 from
12:08:22 4 test1
12:08:22 5 where
12:08:22 6 pay_dealer_date >= '01/01/2000'
12:08:22 7 -- and state_code = 'AB' ----
BOGUS CONDITION
12:08:22 8 ;
375043 rows selected.
Elapsed: 00:00:55.46
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST1'
Statistics
----------------------------------------------------------
0 recursive calls
15 db block gets
35581 consistent gets
10575 physical reads
0 redo size
66817080 bytes sent via SQL*Net to client
2775646 bytes received via SQL*Net from
client
25004 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
375043 rows processed
12:09:18 T10-SERVCBO-CH> ed p2
12:09:33 T10-SERVCBO-CH> @p2
12:09:35 T10-SERVCBO-CH> set autotrace traceonly
exp stat
12:09:35 T10-SERVCBO-CH> select
12:09:35 2 *
12:09:35 3 from
12:09:35 4 test1
12:09:35 5 where
12:09:35 6 pay_dealer_date >= '01/01/2000'
12:09:35 7 and state_code = 'AB' ---- BOGUS
CONDITION
12:09:35 8 ;
no rows selected
Elapsed: 00:00:03.43
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST1'
Statistics
----------------------------------------------------------
0 recursive calls
15 db block gets
11337 consistent gets
10573 physical reads
0 redo size
1860 bytes sent via SQL*Net to client
313 bytes received via SQL*Net from
client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
12:09:38 T10-SERVCBO-CH> spool off
What am I missing here? Any help appreciated.
Thanx
Paul
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve Adams
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
