Poor performance due to table scan in stead of using indexes

[Former Member]

Hi,

In our module IS-H (hospital) there's a view N_PFLISTEN which contains 3 tables, NFAL, NBEW and NPAT. The SQL statement of this view is:

CREATE VIEW "N_PFLISTEN"

("MANDT",

"EINRI",

"FALNR",

"LFDNR",

"FALAR",

"PATNR",

"STATUF",

"BEWTY",

"BWART",

"BWIDT",

"BWIZT",

"PLANB",

"BWEDT",

"BWEZT",

"TELNR",

"ORGPF",

"ORGFA",

"ZIMMR",

"BETT",

"PLANR",

"STORN",

"GSCHL",

"NNAME",

"NNAMS",

"VNAME",

"VNAMS",

"GBDAT",

"GBNAM",

"GBNAS",

"KONFE",

"NATIO",

"LAND",

"PSTLZ",

"ORT",

"STRAS",

"VIPKZ",

"STATU",

"VORSW",

"TODKZ",

"PLANE",

"ADRNR",

"ADROB",

"ADRN2",

"ADRO2")

AS SELECT

T2."MANDT",

T1."EINRI",

T1."FALNR",

T1."LFDNR",

T2."FALAR",

T2."PATNR",

T2."STATU",

T1."BEWTY",

T1."BWART",

T1."BWIDT",

T1."BWIZT",

T1."PLANB",

T1."BWEDT",

T1."BWEZT",

T1."TELNR",

T1."ORGPF",

T1."ORGFA",

T1."ZIMMR",

T1."BETT",

T1."PLANR",

T1."STORN",

T3."GSCHL",

T3."NNAME",

T3."NNAMS",

T3."VNAME",

T3."VNAMS",

T3."GBDAT",

T3."GBNAM",

T3."GBNAS",

T3."KONFE",

T3."NATIO",

T3."LAND",

T3."PSTLZ",

T3."ORT",

T3."STRAS",

T3."VIPKZ",

T3."STATU",

T3."VORSW",

T3."TODKZ",

T1."PLANE",

T3."ADRNR",

T3."ADROB",

T3."ADRN2",

T3."ADRO2"

FROM

"NBEW" T1,

"NFAL" T2,

"NPAT" T3

WHERE

T2."MANDT" = T1."MANDT" AND

T2."EINRI" = T1."EINRI" AND

T2."FALNR" = T1."FALNR" AND

T3."MANDT" = T2."MANDT" AND

T3."PATNR" = T2."PATNR"

Now there's the following ABAP-selection:

SELECT (p_fieldlist)

FROM n_pflisten INTO CORRESPONDING FIELDS OF TABLE sel_pflisten

FOR ALL ENTRIES IN p_npfo

WHERE einri = p_npfo-einri

AND falnr = p_npfo-falnr

AND falar IN rfalar

AND storn = off.

In the execution plan of this view there's a table scan for NFAL, but the Key-fields of nfal are EINRI and FALNR. These fields are filled in internal table p_npfo !!

Why is this selection using a Table Scan and doesn't it take the primary index ???

Kind regards,

Gert-Jan

[roland_mallmann]

Hi Gert-Jan,

I take it you're talking about the OSS message 152419 2007. This message is already being analyzed by a colleague of mine (MaxDB Development Support) and therefore I suggest to close this thread, as you'll get an answer in the official call as soon as the cause of the issue has been found.

Kind regards,

Roland