Prev: ttf2tfm file conversion pcl on Reports 10.1.2.3
Next: Latch / Wait Events
From: lsllcm on 20 Nov 2009 09:24
On 11月18日, 下午8时53分, Charles Hooper <hooperc2...(a)yahoo.com> wrote:
> On Nov 18, 5:26 am, lsllcm <lsl...(a)gmail.com> wrote:
>
>
>
>
>
> > Hi Charles and Randolf,
>
> > There is one more interesting issue.
>
> > When I use A JOIN B and A JOIN C, the optimizer choose index scan on
> > table C.
> > When I use A JOIN B and B JOIN C, the optimizer choose full table scan
> > on table C.
>
> > Below is test case:
>
> > Case 1: When I use A JOIN B and A JOIN C, the optimizer choose index
> > scan on table C.
>
> > SELECT X.SERV_PROV_CODE,
> >        X.B1_PER_ID1,
> >        X.B1_PER_ID2,
> >        X.B1_PER_ID3,
> >        SUM(NVL(X.Fee_Allocation, 0)) GF_FEE
> >   FROM SETDETAILS S, X4PAYMENT_FEEITEM X, F4FEEITEM F
> >  WHERE S.SERV_PROV_CODE = 'SACRAMENTO'
> >    AND S.SET_ID = 'CONNIE'
> >    AND S.REC_STATUS = 'A'
> >    AND S.SERV_PROV_CODE = X.SERV_PROV_CODE
> >    AND F.SERV_PROV_CODE = S.SERV_PROV_CODE
> >    AND S.B1_PER_ID1 = X.B1_PER_ID1
> >    AND S.B1_PER_ID2 = X.B1_PER_ID2
> >    AND S.B1_PER_ID3 = X.B1_PER_ID3
> >    AND F.B1_PER_ID1 = S.B1_PER_ID1
> >    AND F.B1_PER_ID2 = S.B1_PER_ID2
> >    AND F.B1_PER_ID3 = S.B1_PER_ID3
> >    AND F.FEEITEM_SEQ_NBR = X.FEEITEM_SEQ_NBR
> >    AND S.REC_STATUS = X.REC_STATUS
> >    AND F.REC_STATUS = X.REC_STATUS
> >    AND (X.PAYMENT_FEEITEM_STATUS != 'VOIDED' OR
> >        X.PAYMENT_FEEITEM_STATUS IS NULL)
> >  GROUP BY X.SERV_PROV_CODE, X.B1_PER_ID1, X.B1_PER_ID2, X.B1_PER_ID3
>
> Please post the other query also.  Transitive closure should apply to
> the query, and Oracle should be able to derive the additional join
> conditions:
> If A=B and B=C, then A=C
> If A=B and A=C, then B=C
>
> Thus, after transformation the two queries should appear nearly
> identical.
>
> Please generate a 10053 trace at level 1 for each of the two queries.
> Search the 10053 trace for the following line:
> Final query after transformations:******* UNPARSED QUERY IS *******
>
> Below that line in a 11.2.0.1 10053 trace file you should see the
> final version of the query after transformation (although it appears
> that Oracle 11.1.0.6 and above may not show derived "IS NOT NULL"
> predicates in the final query output.)  Please post the transformed
> version of each query which appears below the line "Final query after
> transformations:******* UNPARSED QUERY IS *******" in the trace file.
>
> There must be a significant difference in the two transformed versions
> of the queries as one plan predicts that 40,516 rows will be returned
> while the other predicts that 1 row will be returned.
>
> If you are now experiencing performance problems with a different
> query, you might consider creating a new message thread in this group
> - there is a chance that more people will see the new message thread
> and offer assistance.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- 隐藏被引用文字 -
>
> - 显示引用的文字 -

Thanks Charles,

I have created one new thread

http://groups.google.com.tw/group/comp.databases.oracle.server/browse_thread/thread/6aac0570974c9d62#

I will put related information at that thread.

Thanks
lsllcm
First  |  Prev  | 
Pages: 1 2 3 4 5 6 7
Prev: ttf2tfm file conversion pcl on Reports 10.1.2.3
Next: Latch / Wait Events