| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Physics of the FILTER operation within SQL_PLANE.
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_at_Sun.COM>
Sent by: oracle-l-bounce_at_freelists.org
21.06.2004 16:57
Please respond to oracle-l
To: oracle-l_at_freelists.org
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_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
0 0
> Execute 1 0.00 0.00 0 0 =
0 0
> Fetch 2 3.53 4.53 2551 205405 =
0 1
-- 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 - 09:05:04 CDT
 |
 |