Re: Physics of the FILTER operation within SQL_PLANE.
- From: J.Velikanovs@xxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 21 Jun 2004 17:00:48 +0300
Daniel, thanks for the answer.
It is seams I miss some think.
Take a look on the plan bellow.
How FILTER operation can join to tables?
From plane it is seems that FILTER operation join tow tables
DKM_OUTBILL_RECEIPTS and MNS_PHARMACIES.
----------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | FILTER | |
| 3 | TABLE ACCESS FULL | DKM_OUTBILL_RECEIPTS |
|* 4 | TABLE ACCESS BY INDEX ROWID| MNS_PHARMACIES |
|* 5 | INDEX UNIQUE SCAN | MNS_PHS_PK |
----------------------------------------------------------------
Jurijs
Daniel Fink <Daniel.Fink@xxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
21.06.2004 16:57
Please respond to oracle-l
To: oracle-l@xxxxxxxxxxxxx
cc:
Subject: Re: Physics of the FILTER operation within
SQL_PLANE.
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@xxxxxxxx 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@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
> -----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
