This content has been marked as final. Show 10 replies
Please find the below query.
SELECT DECODE(TEMP.TENOR_IND, 'L', TEMP.SHORT_NAME, NULL) PRODUCT_NAME,
FROM (SELECT /*+INDEX(LIM BUS_CB_LIMIT_INDX01) INDEX(btpd BUS_TP_PORTFOLIO_DTLS_INDX02)*/
DECODE(LIM.TENOR_IND, 'L', MCBPT.SHORT_NAME, NULL),
(TO_CHAR(LIM.PERIOD_VALUE) || '-' ||
DECODE(LIM.PERIOD_TYPE, 4, 'Y', 3, 'M', 2, 'W', 1, 'D'))) TENOR,
FROM BUS_CB_LIMIT LIM,
WHERE BTPD.ORG_ID = 108503
WHERE ORG_STAT IN (1, 2, 4, 7, 8, 9, 22, 24, 25)
AND BTPD.BOOKING_ENTITY = ORG_ID
START WITH ORG_ID = 894
CONNECT BY PRIOR ORG_ID = PARENT_ORG_ID)
AND NOT EXISTS
(SELECT /*+INDEX(btpd1 BUS_TP_PORTFOLIO_DTLS_INDX02)*/
FROM bus_tp_portfolio_details btpd1
WHERE btpd1.org_id = 108503
AND btpd1.booking_entity = 894
AND BTPD.BA_PRODTYPE_ID = btpd1.ba_prodtype_id
WHERE product_grp_id = 1
AND btpd1.ba_prodtype_id = ba_prodtype_id))
AND MCBPT.BA_PRODTYPE_ID = BTPD.BA_PRODTYPE_ID
AND BTPD.CP_STATUS = 4
AND LIM.OWNER_ID = BTPD.PORTFOLIO_ID
AND LIM.OWNER_TYPE = 4
AND LIM.LIMIT_TYPE_ID = 2) TEMP
WHERE 1 = 1
GROUP BY TEMP.BA_PRODTYPE_ID,
ORDER BY TEMP.BA_PRODTYPE_ID, TEMP.PERIOD_TYPE, TEMP.PERIOD_VALUE
definitely raise an SR. thats not right but its known issue so you will have found on support if you search or open sR.
If you dont have support, try those parameters you mention, cant really be any worse off, fix_control was brought in to bypass bugs but it is reported that it can prevent databse from starting as its hidden param and usually should only be used on request of support. but the database will either start or it wont. so you'll know very quicikly if you put it in.
optimizer_features_enable is safer but if you do use it how do you know the optimizer wont change if you use at system level for prior version. I would suggest set online trigger for online user to do alter session for that.
We have a query which is working in 10g and giving error "ORA-00979: not a GROUP BY expression" in Oracle11g R2 (188.8.131.52). we have found following two > solution to resolve it from the internet.I would try to match the scope of the solution to the scope of the problem.
1)You can Set the hidden parameter “_FIX_CONTROL”=’5520732:OFF’ in the pfile. This parameter disables a given bug number.
2)We can set the parameter optimizer_features_enable to a previous version of Oracle, ’184.108.40.206′
but I am unable to find the side effects of above change, so I need to know:
1)whcih is batter solution
2)what are the possible side effects of both solution.
If he problem is a single query, the solution should ideally impact only that query.
Both the fix control and the optimizer_features_enable solutions could be implemented at the session level using ALTER SESSION in your code or just at the query level using the /*+ OPT_PARAM */ hint.
But I would definitely raise an SR and if patching is viable then consider patching. But bear in mind that patches can introduce other bugs which is exactly what fix control 5520732 seems to have done for you.
We have similar issue over here, in a 220.127.116.11 database on AIX7.1.
Oracle Support first told us that the problem is solved in 18.104.22.168 ( according to bug ID 9824198), so it took me some effort and a lot of test-cases from them to show them the problem still does exist.
Meanwhile Development is working already for more than 2 months on a solution.
Extra info: Our Bug is referred to by Bug number 13622036
Edited by: fjfranken on 17-apr-2012 0:43
To add to above, I would have to wonder about the latest release bug fixes, I have had 2 cases in the last 3 months where I was told bugs in 22.214.171.124.0 were fixed in 126.96.36.199 but werent and like above, had to produce test cases to prove. This is not behaviour Ive seen before from support, ie, usually if they say a fix is out, its out (milage may vary between posters). Only anecdotal story but one that should be watched.