| 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: 9.2.0.5, views, queries, and pushing predicates.....
Riyaj,
I don't think it's a cardinality issue or optimizer choice. The reason is, I tried using a global hint to specify which index should be used inside of the view, and the optimizer recognized the hint, and switched from FTS to index access, but it did an INDEX FULL SCAN, rather than an INDEX RANGE SCAN. If it could "see" the predicates inside the view, it should have chosen an INDEX RANGE SCAN. Do you agree?
Here's the three SQLs and the differing execution plans:
select * from xan_view where doc_id =3D 38943105; Execution Plan
0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D14 Card=3D2 = Bytes=3D290)
1 0 VIEW OF 'XAN_VIEW' (Cost=3D14 Card=3D2 Bytes=3D290)
2 1 SORT (UNIQUE) (Cost=3D14 Card=3D2 Bytes=3D28) 3 2 UNION-ALL 4 3 WINDOW (SORT) (Cost=3D7 Card=3D1 Bytes=3D14) 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'COMP_AGREEMENTS' (Cost=3D2 Card=3D1 Bytes=3D14) 6 5 INDEX (RANGE SCAN) OF 'CPAG_CNST_UK01' (UNIQUE) (Cost=3D1 Card=3D1) 7 3 WINDOW (SORT) (Cost=3D7 Card=3D1 Bytes=3D14) 8 7 TABLE ACCESS (BY INDEX ROWID) OF 'XAN_COMP_AGREEMENTS' (Cost=3D2 Card=3D1 Bytes=3D14) 9 8 INDEX (RANGE SCAN) OF 'XAN_CPAG_INDX_FK01'(NON-UNIQUE) (Cost=3D1 Card=3D1)
variable b1 number;
exec :b1:=3D38943105;
PL/SQL procedure successfully completed.
select * from xan_view where doc_id =3D :b1;
Execution Plan
0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D14 Card=3D2 = Bytes=3D290)
1 0 VIEW OF 'XAN_VIEW' (Cost=3D14 Card=3D2 Bytes=3D290)
2 1 SORT (UNIQUE) (Cost=3D14 Card=3D2 Bytes=3D28) 3 2 UNION-ALL 4 3 WINDOW (SORT) (Cost=3D7 Card=3D1 Bytes=3D14) 5 4 TABLE ACCESS (FULL) OF 'COMP_AGREEMENTS' (Cost=3D2 Card=3D1 Bytes=3D14) 6 3 WINDOW (SORT) (Cost=3D7 Card=3D1 Bytes=3D14) 7 6 TABLE ACCESS (FULL) OF 'XAN_COMP_AGREEMENTS' =(Cost=3D2
select /*+ INDEX(xv.cpag cpag_indx_pr02) index(xv.xcpag xan_cpag_indx_pr02) */ * from xan_view xv where doc_id =3D :b1;
Execution Plan
0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D14 Card=3D2 = Bytes=3D290)
1 0 VIEW OF 'XAN_VIEW' (Cost=3D14 Card=3D2 Bytes=3D290)
2 1 SORT (UNIQUE) (Cost=3D14 Card=3D2 Bytes=3D28) 3 2 UNION-ALL 4 3 WINDOW (SORT) (Cost=3D7 Card=3D1 Bytes=3D14) 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'COMP_AGREEMENTS' (Cost=3D2 Card=3D1 Bytes=3D14) 6 5 INDEX (FULL SCAN) OF 'CPAG_INDX_PR02' (NON-UNIQUE) (Cost=3D1 Card=3D1) 7 3 WINDOW (SORT) (Cost=3D7 Card=3D1 Bytes=3D14) 8 7 TABLE ACCESS (BY INDEX ROWID) OF 'XAN_COMP_AGREEMENTS' (Cost=3D2 Card=3D1 Bytes=3D14) 9 8 INDEX (FULL SCAN) OF 'XAN_CPAG_INDX_PR02'(NON-UNIQUE) (Cost=3D1 Card=3D1)
If you STILL suspect a CBO decision problem, let me know, and I'll do 10053 traces. (I hate doing them, cause they're so painful to read.....;-))
Thanks,
-Mark
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Riyaj Shamsudeen
Sent: Friday, July 16, 2004 4:00 PM
To: oracle-l_at_freelists.org
Subject: RE: 9.2.0.5, views, queries, and pushing predicates.....
I confess, I haven't read the to_number thread completely. But I
thought, to_number thread was(is?) about view merging, not pushing the
predicates to the view. View doesn't need to be merged, but the
predicates can be pushed, right ? Or Are you mocking something( which I
didn't get )?
Looking at the Mark's case, I think, it is more of a CBO decision not to
push the predicates due to bind variables. Usage of bind variables
introduces few assumptions in the cardinality calculations and that
might disallow CBO from pushing the predicates. Mark, can you please
look at the explain plan and see whether the view is being merged in any
of this case ? Further, you might want to review the 10053 trace output
for these two conditions and my guess is that cardinality calculations
are causing this issue.
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling
Sent: Friday, July 16, 2004 2:34 PM
To: oracle-l_at_freelists.org
Subject: Re: 9.2.0.5, views, queries, and pushing predicates.....
If we follow the argument that was brought forward in the to_number
thread,=20
then the predicate must never be pushed into the view because the writer
of=20
the sql obviously wanted the view to be "materialized" before applying
the=20
outer predicate.
At 12:33 PM 7/16/2004, you wrote:
>My test case had the following SQL to demonstrate the problem: select *
>from xan_view where doc_id =3D 38943105; and with the patch applied, =
this
>did the right thing. However, in the real world, we use little things=20
>called BIND VARIABLES.
>
>If you re-write the above query and execute:
>
>variable b1 number;
>exec :b1:=3D38943105;
>select * from xan_view where doc_id =3D :b1;
>
>IT FAILS!!! ARGH! I mean, I never considered that the fix would only=20
>address the case where a literal is specified! So, it took two weeks=20
>to get the patch, and I'm right back where I started from!!!
Regards
Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com=20
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- -- Attached file included as plaintext by Ecartis -- -- Desc: Signature The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. ---------------------------------------------------------------- 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 Fri Jul 16 2004 - 15:25:04 CDT
 |
 |