Alternative for Join.

[Former Member]

Hi friends,

I am facing problem in breaking up a select statement which contains join between 10 tables.

can you people suggest me different options for removing join in a select statements.

[RichHeilman]

Sure, instead of joins, use FOR ALL ENTRIES

REgards,

Rich Heilman

[Former Member]

For all entries.

santhosh

[Former Member]

Let me take a example for this problem

Lets say there are 8 table

tab1

tab2 and so on..

now my select statement has a join condition as

select (fields) from

tab1

innerjoin tab2 on tab1_{field1 = tab2}field1

innerjoin tab3 on tab3~field3 = tab1-field3

innerjoin tab4 on tab4_{field4 = tab2}field4

innerjoin tab5 on tab5_{field1 = tab1}field1 and

tab5~field5 = tab1-field5

innerjoin tab6 on tab6~field5 = tab5-field5

innerjoin tab7 on tab7~field1 = tab1-field1 and

tab7_{field3 = tab3}field3 and

tab7_{field5 = tab5}field5 and

innerjoin tab8 on tab8_{field8 = tab7}field8

This creaets a huge table from which fields are fetched.

Its a bit difficult to use For all entries in this case.

Can creating views be of any help in this case.

[Former Member]

What exactly is the problem? IE, why are you doing this? Is there a performance issue here?

Rob

[Former Member]

Because of these joins this statement is taking a long time to execute. That is why i want it to be done in some other way.

[Former Member]

It's possible to split up the select, but difficult to say exactly how without seeing the exact code. Could you please post it?

In general, a large join shouldn't cause a performance problem by itself, but if it's not well constructed (IE not using indexes) that can cause a problem.

Rob

[Former Member]

Hi Rob

here is the actual select statement.

have a look at it and see what can be done with this.

I tried out to break it in two parts, but was unsucessful.

SELECT AFKO~AUFNR "Order Number

AFKO~GSTRP "Order Basic start date

AFKO~GSTRS "Order Scheduled start

AFIH~PRIOK "Maint order Priority

AFIH~ILART "Maint activity type

AFIH~WARPL "Maint plan

AFIH~ABNUM "Package num

AFIH~WAPOS "Maint item

ILOA~TPLNR "Functional location

ILOA~ABCKZ "ABC indicator for technical object

AUFK~KTEXT "Order Short text

AUFK~AUART "Order type

AUFK~OBJNR "object num of order

AFVC~OBJNR "Object num of operation

AFVC~ANLZU "Task list oper's sys condition

CRHD~ARBPL "Work center

AFVV~ARBEI "Plan hours for the activity

AFVV~ISMNW "Actual hours

MPOS~WSTRA "Strategy

AFKO~PLNNR

AFKO~PLNAL

MHIS~ZAEHL "Maint package

FROM ( AFKO "afko: Order header

INNER JOIN AUFK "aufk: Order master data.

ON AUFK_{AUFNR = AFKO}AUFNR

INNER JOIN AFIH "afih: Maint order header

ON AFIH_{AUFNR = AUFK}AUFNR

INNER JOIN ILOA "iloa: PM Obj Loc & Acct Assgmt

ON ILOA_{ILOAN = AFIH}ILOAN

INNER JOIN caufv "

ON caufv_{AUFNR = AFKO}AUFNR

INNER JOIN AFVC "afvc: Operation within an order

ON AFVC_{AUFPL = CAUFV}AUFPL

INNER JOIN AFVV "afvv: qty/dates in operation

ON AFVV_{AUFPL = AFKO}AUFPL

INNER JOIN CRHD "crhd: Work Center Header

ON CRHD_{OBJID = AFVC}ARBID

INNER JOIN PLAS

ON PLAS_{PLNTY = AFVC}PLNTY

AND PLAS_{PLNNR = AFVC}PLNNR

AND PLAS_{PLNAL = AFVC}PLNAL

AND PLAS_{ZAEHL = AFVC}ZAEHL

INNER JOIN PLWP

ON PLWP_{PLNTY = AFVC}PLNTY

AND PLWP_{PLNNR = PLAS}PLNNR

AND PLWP_{PLNAL = PLAS}PLNAL

INNER JOIN MHIS "mhis: Maint plan history

ON MHIS_{ZAEHL = PLWP}PAKET

LEFT OUTER JOIN MPOS "mpos: Maint item

ON MPOS_{WAPOS = AFIH}WAPOS

)

INTO TABLE I_CO_MH3

WHERE AFKO~GSTRS IN S_DATES "Order scheduled start date

AND AFKO~GSTRS IN R_ONE_YEAR "Restriction by year

AND ILOA~TPLNR IN S_TPLNR "func location input

AND CRHD~OBJTY = 'A' "Object types of CIM resource

[Former Member]

It looks to me that your problem has more to do with the use of indexes rather than the join. Although there is an index on TPLNR in ILOA and OBJTY in CRHD, I don't think they will be restrictive enough to help much.

Rob

[Former Member]

Hi Rob thanks for the advice.

Can you please elaborate a bit more on this, and how how can we make this work properly.

Thnaks in advance.

[sridhar_k1]

YOu've used CAUFV in the join, it's is a view for tables AFKO and AUFK. Take it out from join and use

ON AFVC_{AUFPL = AFKO}AUFPL in the next join condition with AFVC.

Regards

Sridhar

[Former Member]

Well, I'm not sure I can. I was just trying to get you to find the source of the problem rather than just assuming the join was the sole source. I think you need to examine your logic to determine why you are selecting these fields from these tables using your selection criteria.

Rob