$(function () { pageContext.i18n.modTalk = 'moderation talk'; pageContext.i18n.replyToComment = 'Reply'; pageContext.i18n.modTalkEmpty = 'moderation talk is empty'; pageContext.url.getModTalk = "/comments/%25ID%25/listModTalk.json"; pageContext.url.possibleCommentRecipients = "/comments/%ID%/possibleRecipients.json"; pageContext.url.commentEdit = '/comments/%25ID%25/edit.html'; pageContext.url.commentView = '/comments/%ID%/view.html'; pageContext.i18n.commentVisibility = { 'full': 'Viewable by all users', 'op': 'Viewable by the original poster', 'mod': 'Viewable by moderators', 'opAndMod': 'Viewable by moderators and the original poster', 'other': 'Advanced visibility', 'dialogTitle': 'Comment visibility', 'selectGroups': 'Visible to groups', 'selectOther': 'Other recipients', 'selectOriginalPoster': 'Original poster', 'selectModerators': 'Moderators', 'selectAssignees': 'Asked to answer users' }; pageContext.i18n.commentMenuLabels = { 'comment-edit': 'comments.menu.edit', 'comment-delete': 'comments.menu.delete', 'comment-convert': 'comments.menu.convert' };pageContext.i18n.answer= { bestAnswer: 'Best Answer', controlBar : { accept: 'Accept', unaccept: 'Unaccept', acceptCommand: 'Accept this answer as correct', cancelAcceptedCommand: 'Remove this answers accepted status' } }; window.croles = { u: false, op: false, m: false, og: false, as: false, ag: false, dc: false, doc: false, eo: false, ea: false }; tools.init({ q: { e: false, ew: false, eo: false, r: false, ro: false, d: false, dow: false, fv: false, c: false, co: false, p: false, tm: false , ms: false, mos: false }, n: { f: false, vf: false, vfo: false, vr: true, vro: true, c: false, co: false, vu: false, vd: false, w: false, wo: false, l: false }, c: { e: false, eo: false, d: false, dow: false, ta: false, tao: false, l: false }, a: { e: false, ew: false, eo: false, d: false, dow: false, a: false, aoq: false, ao: false, tc: false, tco: false, p: false, tm: false }, pc: croles }, { tc: true, nsc: true }); commandUtils.initializeLabels(); }); Skip to Content
avatar image
Former Member

HANA SQL join behaviour

Hello,

Hello, We have a simple SQL statements:

SELECT

Table__24."ZCONTRACT_NO",

Table__111."ACCOUNTING_PRODUCT_GRP_CODE1",

SUM(Table__24."KF_VALUE")

FROM

"_SYS_BIC"."MD.Groupings/AT_GRP_ACCOUNTING_PRODUCT"  Table__111

INNER JOIN "_SYS_BIC"."BNHP-UTILS.NewDrillDown/CV_DAILY_BALANCES"

('PLACEHOLDER' = ('$$IP_BAL_DATE1$$','20150331' ),'PLACEHOLDER' = ('$$IP_MODULE$$', '0')) Table__24

ON (Table__24."_BIC_CACCPRODG" = Table__111."_BIC_CACCPRODG"  AND  Table__24."ACCOUNTING_PRODUCT_GRP_CODE1" = Table__111."ACCOUNTING_PRODUCT_GRP_CODE1"  AND  Table__24."ACCOUNTING_PRODUCT_GRP_CODE2" = Table__111."ACCOUNTING_PRODUCT_GRP_CODE2"  AND  Table__24."ACCOUNTING_PRODUCT_GRP_CODE3" = Table__111."ACCOUNTING_PRODUCT_GRP_CODE3"  AND  Table__24."ACCOUNTING_PRODUCT_GRP_CODE4" = Table__111."ACCOUNTING_PRODUCT_GRP_CODE4"  AND  Table__24."ACCOUNTING_PRODUCT_GRP_CODE5" = Table__111."ACCOUNTING_PRODUCT_GRP_CODE5")

WHERE

( Table__24."CURRENCY_TYPE" IN ('G')  )

AND    (    Table__111."ACCOUNTING_PRODUCT_GRP_CODE1"  IN  ( '11'  ) 

AND  Table__24."ZRDL_TS_DATE" <= '20150401' 

     )

GROUP BY

Table__24."ZCONTRACT_NO",

Table__111."ACCOUNTING_PRODUCT_GRP_CODE1"

We run this SQL twice: first time with the restriction:

   AND  Table__24."ZRDL_TS_DATE" 

And second time without it.

The first viso plan:

The second viso plan:

As you can see with the restriction of "ZRDL_TS_DATE" the query returns 1,077,662 records and without it the query returns 101,875 records (in both queries we select the same fields…).

Why does it behaves this way?

Isn't it supposed to penetrate our restriction to the lowest level of the calculation view and only return the selected fields?

Thanks,

Amir 

viso1.jpg (54.3 kB)
viso2.jpg (69.9 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Aug 12, 2015 at 06:38 AM

    Hi Amir,

    No this Where clause restriction is NOT passed to lowest level and restrict the data set. So this is showing as in the explain. You are only passing the IP in one view so it wont restrict for the whole view. Hope you got it.

    Cheers!

    Chandan

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Amir,

      About bring more data than needed I explain with an issue I faced on SP7 on break of join optimization.

      In a calc view joinning BKPF and BSEG, which the main filter is BUDAT (date) on BKPF (header) table... and BSEG is acessed after from BKPF date filtering with good performance.

      After add an filter on subsequente projection BSEG-KOART=' ', the behavior changed to run selections in parallel for BSEG with filter KOART=' ' and BKPF with same filter BUDAT to join later. As many rows was acessed from BSEG the performance went down and memory usage increased.

      So, what should helping to reduce number of rows (KOART) was in fact breaking the join optimization....

      Solution applied at that time was adjuste the expression to (KOART = ' ' or KOART <> '1').. The OR broke the push down and preserved the join optimization.

      Just to Remember: It happened ON that revision, ON that scenario... HANA DEV are enhancing such optimizers with a lot of inteliggence, so this sample is not needed anymore.

      Anyhow, your case remind me this one and I guess could be happening similar issue. In this case if I have data since 2010, I'll read basically 5 years:

      ZRDL_TS_DATE" <= '20150401'


      Give a try. Perform a test with an expression that broke the push down optimization:

      (ZRDL_TS_DATE" <= '20150401' OR ZRDL_TS_DATE = ZRDL_TS_DATE)

      Regards, Fernando Da Rós