DB VIEW VS ABAP JOIN

Former Member — Mon, 09 Dec 2013 03:28:08 GMT

hey,guys,I wanna know what's the difference between DB VIEW AND ABAP JOIN,the explain plan,we use the Oracle as DBS.

HERE IS THE CASE:THE WHERE CONDITIONS' JUST CONTAIN THE ON CONDITIONS' FIELDS,SO WHICH TABLE SHOULD I DISPATCH THE SAME VALUE TO THE 'SAME FIELDS'.MY SQL STATEMENT IS :

DATA v_mcnum TYPE zmcnum.

DATA: t1 TYPE f,t2 LIKE t1.

CLEAR: t1,t2.

GET RUN TIME FIELD t1.

SELECT SINGLE mcnum FROM zmcvzorg INTO v_mcnum

WHERE parno EQ '10181051'

AND sfrgr EQ 'X002'.

GET RUN TIME FIELD t2.

t2 = t2 - t1.

WRITE: 'ACCESSING TABLS VIA DB VIEW RUNTIME:',t2.

WAIT UP TO 10 SECONDS.

CLEAR: t1,t2.

GET RUN TIME FIELD t1.

SELECT SINGLE a~mcnum FROM zmch AS a

INNER JOIN lfa1 AS b

ON a~parno = b~lifnr

AND a~mandt = b~mandt

INNER JOIN zmdt096 AS c

ON b~sfrgr = c~sfrgr

AND b~mandt = c~mandt

INNER JOIN zmchv AS d

ON a~mcnum = d~mcnum

AND a~mandt = d~mandt

INTO v_mcnum

WHERE

a~parno EQ '10181051'

AND b~lifnr EQ '10181051'

AND b~sfrgr EQ 'X002'

AND c~sfrgr EQ 'X002'.

GET RUN TIME FIELD t2.

t2 = t2 - t1.

WRITE: 'ACCESSING TABLS VIA ABAP JOIN RUNTIME:',t2.

AS you can c,i even give the value to every table as ican and i change the sequence of all the table,but the explain plan display different access path and the costs was different,I DID THAT COZ I LEARNED FROM BC490,AS IT SAID:

In the selection of the fields in the ON and WHERE conditions, use as

many fields as possible from this table, if the same fields occur in several

tables. This might also have a positive effect on the optimizer response.

THERE IS NO efficiency..

THE ACCESS PATH WITH 4 NESTED LOOPS WAS THE DBVIEW'S,THE OTHER IS 1 HASHED JOIN AND 3 NESTED LOOPS.ANYBODY KNOWS THE REASON?

Re: DB VIEW VS ABAP JOIN

Former Member — Mon, 09 Dec 2013 06:40:23 GMT

This message was moderated.

Re: DB VIEW VS ABAP JOIN

Former Member — Mon, 09 Dec 2013 07:45:30 GMT

I'm sorry,Prisesh,it dont do me any favor,my confusion was how can i set value to the corresponding fields in the WHERE conditions(also appeared in the ON conditions),ie the where conditions belong to which table so that there will be the same access path as the DBVIEW does.

Re: DB VIEW VS ABAP JOIN

Former Member — Wed, 11 Dec 2013 03:13:43 GMT

This message was moderated.

topic Re: DB VIEW VS ABAP JOIN in Application Development and Automation Discussions

DB VIEW VS ABAP JOIN

Re: DB VIEW VS ABAP JOIN

Re: DB VIEW VS ABAP JOIN

Re: DB VIEW VS ABAP JOIN