Physics of the FILTER operation within SQL_PLANE.
- From: J.Velikanovs@xxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 21 Jun 2004 15:45:48 +0300
Please excuse me for the basic question, but I can?t find any information
source which can give me significant answer to my question (if you know
one, please just point me).
I have 2 similar (by returned result) SQL-s (See below).
I wonder, How Oracle executing FILTER operation? And why there is so huge
difference of LIO (SQL1 = 2591; SQL2 = 205405)? I am intended to
understand how physically work FILTER operation.
Please excuse if it is basic question.
I am ready to get information by myself if you point me to source.
Than you in advance,
Jurijs
============================================================
1. SQL (from tkprof output)
==============================
SELECT count(*) from dkm_outbill_receipts dor
WHERE EXISTS (SELECT 1 FROM MNS_PHARMACIES WHERE unify = 'N' and id =
dor.phs_id)
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 0 0 0
Fetch 2 0.58 1.37 2551 2591 0 1
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 0.58 1.38 2551 2591 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=2591 r=2551 w=0 time=1378304 us)
164319 FILTER (cr=2591 r=2551 w=0 time=1341440 us)
202818 TABLE ACCESS FULL DKM_OUTBILL_RECEIPTS (cr=2585 r=2551 w=0
time=1158144 us)
2 TABLE ACCESS BY INDEX ROWID MNS_PHARMACIES (cr=6 r=0 w=0 time=0
us)
3 INDEX UNIQUE SCAN MNS_PHS_PK (cr=3 r=0 w=0 time=0 us)(object id
6893)
2. SQL
==============================
SELECT /*+ RULE */ count(*) from dkm_outbill_receipts dor
WHERE dor.phs_id IN (SELECT ID FROM MNS_PHARMACIES WHERE unify = 'N')
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 0 0 0
Fetch 2 3.53 4.53 2551 205405 0 1
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 4 3.53 4.54 2551 205405 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=205405 r=2551 w=0 time=4537344 us)
164319 NESTED LOOPS (cr=205405 r=2551 w=0 time=4501504 us)
202818 TABLE ACCESS FULL DKM_OUTBILL_RECEIPTS (cr=2585 r=2551 w=0
time=1281024 us)
164319 TABLE ACCESS BY INDEX ROWID MNS_PHARMACIES (cr=202820 r=0 w=0
time=2269184 us)
202818 INDEX UNIQUE SCAN MNS_PHS_PK (cr=2 r=0 w=0 time=705536
us)(object id 6893)
============================================================
SYS:MNS01> SELECT ID FROM MNS_PHARMACIES WHERE unify = 'N';
ID
----------
2
3
2 rows selected.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
