Physics of the FILTER operation within SQL

From: <J.Velikanovs_at_alise.lv>
Date: Mon, 21 Jun 2004 15:45:48 +0300
Message-ID: <OF39271D0A.EF0D6DD9-ONC2256EBA.00460D7E-C2256EBA.0046C407@alise.lv>

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

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';

         2
         3

2 rows selected.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Mon Jun 21 2004 - 07:50:10 CDT

Physics of the FILTER operation within SQL_PLANE.