Skip to Content
0
Former Member
Mar 26, 2015 at 08:51 AM

strange out of memory query

14 Views

Hello,

We are having weird SQL performance issue in the following SQL:

SELECT

DIM."_BA1_IGL_ACCOUNT", "_BA1_C40FTRAN", "CURRENCY_TYPE",

SUM("KF_VALUE") AS "KF_VALUE_SUM"

FROM

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

( 'PLACEHOLDER' = ('$$IP_MODULE$$', '0'), 'PLACEHOLDER' = ('$$IP_BAL_DATE1$$', '20141231')) FACT

INNER JOIN "_SYS_BIC"."MD.Groupings/AT_GRP_GL_ACC_NUM" DIM

ON (

DIM."_BA1_IGL_ACCOUNT" = FACT."_BA1_IGL_ACCOUNT" AND

DIM."GL_ACCOUNT_GRP_CODE2" = FACT."GL_ACCOUNT_GRP_CODE2" AND

DIM."GL_ACCOUNT_GRP_CODE1" = FACT."GL_ACCOUNT_GRP_CODE1" AND

DIM."GL_ACCOUNT_GRP_CODE3" = FACT."GL_ACCOUNT_GRP_CODE3" AND

DIM."GL_ACCOUNT_GRP_CODE4" = FACT."GL_ACCOUNT_GRP_CODE4" AND

DIM."GL_ACCOUNT_GRP_CODE5" = FACT."GL_ACCOUNT_GRP_CODE5" )

WHERE ("CURRENCY_TYPE" = 'G') and

DIM."_BA1_IGL_ACCOUNT" = '0000099020'

--DIM."GL_ACCOUNT_GRP_CODE1" = '05'

GROUP BY

DIM."_BA1_IGL_ACCOUNT", "CURRENCY_TYPE","_BA1_C40FTRAN"

ORDER BY DIM."_BA1_IGL_ACCOUNT" ASC, "CURRENCY_TYPE" ASC



If we unmark the where sentence to: DIM."GL_ACCOUNT_GRP_CODE1" = '05' and change it with DIM."_BA1_IGL_ACCOUNT" = '0000099020'

although the query should return even more records it ends in 2 seconds!!



When checking the explain plan we discover:

Where DIM."_BA1_IGL_ACCOUNT" = '0000099020'



Where DIM."GL_ACCOUNT_GRP_CODE1" = '05'



When checking the visio plan:

Where DIM."_BA1_IGL_ACCOUNT" = '0000099020':

Where DIM."GL_ACCOUNT_GRP_CODE1" = '05' :



As you can see from the visio plan estimation in the working query the ATT view cost estimation is bigger than the CV cost estimation while in the out of memory query the estimation is exactly the opposite.

Is there a way to control which query will run first?

Please help!!!!

Thanks,

Amir

Attachments

1.jpg (146.6 kB)
2.jpg (142.7 kB)
3.jpg (66.3 kB)
4.jpg (61.7 kB)