Skip to Content
0
Former Member
Dec 09, 2013 at 03:28 AM

DB VIEW VS ABAP JOIN

24 Views

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?

Attachments

DBVIEW.jpg (248.5 kB)
ABAP JOIN.jpg (247.2 kB)
DBVIEW_JOIN.jpg (116.7 kB)