Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Strange behaviour of CDS view with parameter

al_siv
Discoverer
0 Kudos

Hi all.

I found one strange thing with ABAP CDS view.

I mage a simple CDS view on QMEL table, without joins and associations.

@AbapCatalog.sqlViewName: 'ZTI_NOT_LCL_V'
@AbapCatalog.compiler.CompareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS view for notification data'
define view zti_not_lcl
as select from qmel
{ key qmel.qmnum,
    qmel.qmart,
    qmel.objnr,
    qmel.qmtxt,
    qmel.priok,
    qmel.qmdat,
    qmel.mzeit,
    qmel.ltrmn,
    qmel.ltrur,
    qmel.qmcod
}

This is query to my view:

SELECT * FROM zti_not_lcl_v INTO TABLE @DATA(lt_table)
WHERE
  qmnum = '000200000046'.

The query written above gives this execution plan:

--------------------------------------------------------------------------------------
| Id  |Operation                   | Name   | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECTSTATEMENT            |        |    1 |    92 |     1 (100)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| QMEL  |     1 |    92 |    0   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         |QMEL~0 |     1 |      |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Looks fine.

OK, then I add a parameter P_LANGU to my view:

@AbapCatalog.sqlViewName: 'ZTI_NOT_LCL_V'
@AbapCatalog.compiler.CompareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'CDS view for notification data'
define view zti_not_lcl
with parameters p_langu : abap.lang
as select from qmel
{ key qmel.qmnum,
    qmel.qmart,
    qmel.objnr,
    qmel.qmtxt,
    qmel.priok,
    qmel.qmdat,
    qmel.mzeit,
    qmel.ltrmn,
    qmel.ltrur,
    qmel.qmcod
}

Do query:

SELECT * FROM zti_not_lcl_v( p_langu = 'E' ) INTO TABLE @DATA(lt_table)
WHERE
  qmnum = '000200000046'.

Loot at execution plan in ST05. I see strange thing that query is separated into two parts and I see two execution steps:

Step1

---------------------------------------------------------------------------------------------------
| Id  |Operation                         |Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECTSTATEMENT                  |               |     1 |    2 |    17   (6)| 00:00:01 |
|*  1 |  COLLECTION ITERATOR PICKLER FETCH| ZTI_NOT_LCL_V |     1 |    2 |    16   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

After clicking on EXPLAIN I get one more plan

Step2

--------------------------------------------------------------------------
| Id  |Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECTSTATEMENT  |      |   998| 91816 |     8  (13)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| QMEL |  998 | 91816 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

What is confusing me - TABLE ACCESS FULL, even if I do query by primary key.

Do you have any ideas of such behavior?

Database server ORACLE 12.1.0.2.0

SAP_BASIS 740 0012SAPKB74012 SAP Basis Component

SAP_ABA 740 0012 SAPKA74012 Cross-Application Component

SAP_GWFND 740 0013 SAPK-74013INSAPGWFND SAP Gateway Foundation 7.40

2 REPLIES 2

srikanthnalluri
Active Participant
0 Kudos

The Parameter p_LANGU is just hanging out without where condition in the CDS view, can you try with below code and see (sorry i don't have system to try it out)

define view zti_not_lcl
with parameters notification_no : qmnum
as select from qmel
{ key qmel.qmnum,
    qmel.qmart,
    qmel.objnr,
    qmel.qmtxt,
    qmel.priok,
    qmel.qmdat,
    qmel.mzeit,
    qmel.ltrmn,
    qmel.ltrur,
    qmel.qmcod
} where qmnum = notification_no;

al_siv
Discoverer
0 Kudos

Hello Nalluri.

I want to build the view like this:

define view zti_not_lcl
with parameters p_langu : abap.lang
as select from qmel left outer join t356_t
on  t356_t.artpr = qmel.artpr
and t356_t.priok = qmel.priok
and t356_t.spras = $parameters.p_langu

{ key  qmel.qmnum,
    qmel.qmart,
    qmel.objnr,
    qmel.qmtxt,
    qmel.priok,
    t356_t.priokx,
    qmel.qmdat,
    qmel.mzeit,
    qmel.ltrmn,
    qmel.ltrur,
    qmel.qmcod
} 

But when I add any parameter to the view, full scan of QMEL is always performed.