Database Reference
In-Depth Information
5 from emp, heap_addresses a
6 where emp.empno = a.empno
7 and emp.empno = x.empno )
8 loop
9 null;
10 end loop;
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed.
Here, we are just emulating a busy period and running the query some 72,000 times, once for each
EMPNO
. If we
run that for the
HEAP_ADRESSES
and
IOT_ADDRESSES
tables,
TKPROF
shows us the following:
SELECT EMP.ENAME, A.STREET, A.CITY, A.STATE, A.ZIP
FROM EMP, HEAP_ADDRESSES A WHERE EMP.EMPNO = A.EMPNO AND EMP.EMPNO = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 72110 1.02 1.01 0 0 0 0
Fetch 72110 2.16 2.11 0 722532 0 288440
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 144221 3.18 3.12 0 722532 0 288440
...
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- -------------------------------------------------------------------
4 4 4 NESTED LOOPS (cr=10 pr=0 pw=0 time=40 us cost=8 size=228 card=4)
1 1 1 TABLE ACCESS BY INDEX ROWID EMP (cr=3 pr=0 pw=0 time=11 us cost=2...
1 1 1 INDEX UNIQUE SCAN EMP_PK (cr=2 pr=0 pw=0 time=7 us cost=1 size=0...
4 4 4 TABLE ACCESS BY INDEX ROWID BATCHED HEAP_ADDRESSES (cr=7...
4 4 4 INDEX RANGE SCAN SYS_C0032863 (cr=3 pr=0 pw=0 time=10 us cost=2...
****************************************************************************************************
SELECT EMP.ENAME, A.STREET, A.CITY, A.STATE, A.ZIP
FROM EMP, IOT_ADDRESSES A WHERE EMP.EMPNO = A.EMPNO AND EMP.EMPNO = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 72110 1.04 1.01 0 0 0 0
Fetch 72110 1.64 1.63 0 437360 0 288440
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 144221 2.69 2.64 0 437360 0 288440
...
