Skip to Content
0
Former Member
Mar 01, 2007 at 08:59 PM

Poor performance due to table scan in stead of using indexes

22 Views

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