Physics of the FILTER operation within SQL_PLANE.

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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: