DB VIEW VS ABAP JOIN

[Former Member]

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?

[Former Member]

This message was moderated.

[Former Member]

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.

[Former Member]

This message was moderated.