Re: Physics of the FILTER operation within SQL

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Mon, 21 Jun 2004 07:57:00 -0600
Message-id: <40D6E92C.6050301@sun.com>

Jurijs,

A FILTER operation simply takes a row source or previous operatons re= sult set and discards rows not meeting the predicate (WHERE).=20 Let's say you have a predicate on a non-indexed column (SELECT * FROM= EMP WHERE SAL > 1000). The only way that Oracle can determine=20 which rows meet this condition is to perform a FTS of the EMP table a= nd FILTER out (discard) the rows where the SAL is not greater=20 than 1000. If there was an index, Oracle *might* use the index to imp= licitly perform this filtering operation.

The issue with LIO is not in the FILTER operation, but in the executi= on plan of the queries. In the second (NESTED_LOOPS), examine=20 the number of CRs for the MNS_PHARAMCIES table. It is 202820 compared= with 6 in the first plan. It is not the FILTER operation that=20 is impacting the LIOs, but the manner in which a NESTED_LOOPS is perf= ormed.

Of course, the second statement has a much better buffer cache hit ra= tio. <ducking and running for cover>

Regards,

Daniel Fink

J.Velikanovs_at_alise.lv wrote:
> Please excuse me for the basic question, but I can=92t find any inf=
ormation=20
> source which can give me significant answer to my question (if you =
know=20
> 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 s=
o huge=20
> difference of LIO (SQL1 =3D 2591; SQL2 =3D 205405)? I am intended t=
o=20
> understand how physically work FILTER operation.

>=20

> Please excuse if it is basic question.
> I am ready to get information by myself if you point me to source.

>=20

> Than you in advance,
> Jurijs

>=20
>=20

> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> 1. SQL (from tkprof output)
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D
> SELECT count(*) from dkm_outbill_receipts dor
> WHERE EXISTS (SELECT 1 FROM MNS_PHARMACIES WHERE unify =3D 'N' and =
id =3D=20
> dor.phs_id)

>=20
> call count cpu elapsed disk query curren=
t rows
> ------- ------ -------- ---------- ---------- ---------- ---------=
-=20
> ----------
> 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

> ------- ------ -------- ---------- ---------- ---------- ---------=
-=20
> ----------
> total 4 0.58 1.38 2551 2591 =
0 1

>=20


> Rows     Row Source Operation

> -------  ---------------------------------------------------

>       1  SORT AGGREGATE (cr=3D2591 r=3D2551 w=3D0 time=3D1378304 us=

)

> 164319 FILTER (cr=3D2591 r=3D2551 w=3D0 time=3D1341440 us)
> 202818 TABLE ACCESS FULL DKM_OUTBILL_RECEIPTS (cr=3D2585 r=3D25=
51 w=3D0=20
> time=3D1158144 us)
> 2 TABLE ACCESS BY INDEX ROWID MNS_PHARMACIES (cr=3D6 r=
=3D0 w=3D0 time=3D0=20
> us)
> 3 INDEX UNIQUE SCAN MNS_PHS_PK (cr=3D3 r=3D0 w=3D0 time=
=3D0 us)(object id=20
> 6893)

>=20
>=20

> 2. SQL
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D
> SELECT /*+ RULE */ count(*) from dkm_outbill_receipts dor
> WHERE dor.phs_id IN (SELECT ID FROM MNS_PHARMACIES WHERE unify =
=3D 'N')
>=20
> call count cpu elapsed disk query curren=
t rows
> ------- ------ -------- ---------- ---------- ---------- ---------=
-=20
> ----------
> 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

> ------- ------ -------- ---------- ---------- ---------- ---------=
-=20
> ----------
> total 4 3.53 4.54 2551 205405 =
0 1

>=20


> Rows     Row Source Operation

> -------  ---------------------------------------------------

>       1  SORT AGGREGATE (cr=3D205405 r=3D2551 w=3D0 time=3D4537344 =

us)

> 164319 NESTED LOOPS (cr=3D205405 r=3D2551 w=3D0 time=3D4501504 =
us)
> 202818 TABLE ACCESS FULL DKM_OUTBILL_RECEIPTS (cr=3D2585 r=3D25=
51 w=3D0=20
> time=3D1281024 us)
> 164319 TABLE ACCESS BY INDEX ROWID MNS_PHARMACIES (cr=3D202820 =
r=3D0 w=3D0=20
> time=3D2269184 us)
> 202818 INDEX UNIQUE SCAN MNS_PHS_PK (cr=3D2 r=3D0 w=3D0 time=
=3D705536=20
> us)(object id 6893)
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

>=20
>=20


> SYS:MNS01> SELECT ID FROM MNS_PHARMACIES WHERE unify =3D 'N';

>         ID

> ----------

>          2

>          3

> 2 rows selected.

>=20


> ----------------------------------------------------------------

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

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 - 08:53:55 CDT

Re: Physics of the FILTER operation within SQL_PLANE.