同事测试的时候，执行一个SELECT语句的时候出错了，如图所示：

感觉上像是个Oracle的bug。
数据库版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

将 FULL JOIN改成LEFT JOIN没有出错, SQL语句

点击(此处)折叠或打开

SELECT ac.checkno,
ac.compid,
ac.busno,
ac.wareid,
ac.makeno,
ac.stallno,
ac.batid,
(ac.invalidate) AS invalidate,
ac.accountqty,
CASE
WHEN tr.checkqty IS NULL THEN
0
ELSE
tr.checkqty
END AS checkqty,
((CASE
WHEN tr.checkqty IS NULL THEN
0
ELSE
tr.checkqty
END) - ac.accountqty) AS plqty,
ac.purtax,
ac.avgpurprice AS accavgpurprice,
ac.avgpurprice,
(ac.purprice) AS accpurprice,
CASE
WHEN (tr.purprice) IS NULL THEN
0
ELSE
tr.purprice
END AS chepurprice,
(ac.saleprice) AS accsaleprice,
CASE
WHEN (tr.saleprice) IS NULL THEN
0
ELSE
tr.saleprice
END AS chesaleprice
FROM t_check_account ac
FULL JOIN (SELECT (h.checkno) AS checkno,
wareid,
MAX(makeno) makeno,
batid,
stallno,
MAX(nvl(invalidate, '')) invalidate,
SUM(nvl(checkqty, 0)) checkqty,
MAX(purtax) purtax,
MAX(purprice) purprice,
MAX(saleprice) saleprice
FROM t_check_track_h h, t_check_track_d d
WHERE h.checkno = '150811001'
AND h.checkno = d.checkno
AND h.status = 1
AND h.pageno = d.pageno
GROUP BY h.checkno, wareid, batid, stallno) tr
ON (ac.checkno = tr.checkno AND ac.wareid = tr.wareid AND
ac.batid = tr.batid AND ac.stallno = tr.stallno)
WHERE ac.checkno = '150811001'

解决方法：
修改隐含参数：_optimizer_cost_based_transformation
alter system set "_optimizer_cost_based_transformation"=OFF;

参考：http://blog.csdn.net/zzvnzz/article/details/7728329
http://www.itpub.net/thread-1742242-2-1.html

ITPub博客

ORA-00600: arguments: kkqcscpopn_Int:0

最新文章