Full 2 million rows Table Scan but cero disk reads

From: LS Cheng <exriscer_at_gmail.com>
Date: Wed, 22 Sep 2010 02:14:32 +0200
Message-ID: <AANLkTikPNNskwDez_8UratXpq56tH2tW9P=rEfgqT-RM_at_mail.gmail.com>

Hi all

I have a database running in 11.1.0.7 in Linux, while I was doing some query testing I found a query as following with the execution plan and execution statistics:

select a.*
from impacts a
where (start_visit, id_visit) IN (SELECT start_visit, id_visit FROM VISITS WHERE ID_USER =
(SELECT

   COD_ID_USER FROM VISIT_USERS WHERE IDENT= :V1 )) call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.00          0          0
0           0
Execute      1      0.00       0.00          0         27
0           0
Fetch        1      0.00       0.00          0          3
0           0

------- ------ -------- ---------- ---------- ---------- ----------

---

total        3      0.00       0.00          0         30
0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 39

Rows Row Source Operation

-------  ---------------------------------------------------
      0  HASH JOIN  (cr=30 pr=0 pw=0 time=0 us cost=8686 size=1783188
card=15642)
      0   TABLE ACCESS BY INDEX ROWID VISITS (cr=30 pr=0 pw=0 time=0 us
cost=724 size=63504 card=3528)
      0    INDEX RANGE SCAN LSC$I1 (cr=30 pr=0 pw=0 time=0 us cost=11 size=0
card=3528)(object id 1131243)
      1     TABLE ACCESS FULL VISIT_USERS (cr=27 pr=0 pw=0 time=0 us cost=7
size=15 card=1)
      0   TABLE ACCESS FULL IMPACTS (cr=0 pr=0 pw=0 time=0 us cost=7920

size=237716448 card=2476213)

This is pretty good because looks like the hash join is between cero rows join result (join between VISITS and VISIT_USERS) and a 2 million rows table
(IMPACTS)and that yields 30 buffer gets. So it actually says TABLE ACCESS
FULL IMPACTS but actually didnt read any rows.

I guess this is a CBO optimization, does anyone know what is this optimization called... :-?

Thanks

--
LSC

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 21 2010 - 19:14:32 CDT