Oracle MAX() 错误与 NULL CBO 索引修复导致索引限制
更新 1/31/2011
我认为我的运行达到了数据库限制。 GROUP BY 表达式
并且所有非不同的聚合函数可能已经超过了单个
数据库块。
请参阅http://download.oracle.com/docs/ cd/B19306_01/server.102/b14237/limits003.htm
原始帖子：
这是 Oracle Database 10g 企业版版本 10.2.0.1.0 - 产品。
以下插入不会返回真正的 MAX() 值
如果我有两条 matchKey 相同的记录，则一条
一个贡献 P 为 NULL 值，另一个贡献为非 NULL 值。
有时，MAX() 返回的值是 NULL 值。
INSERT /*+ APPEND */ INTO meCostingXPrePre(
matchKey ,
contributionP ,
stimulusContributionP ,
contributionC ,
ageMultiplier ,
rateTableIdP ,
rateTableIdC ,
accountNbrP ,
accountNbrC ,
commissionExpenseAccount ,
commissionReceivableAccount ,
commissionType ,
commission ,
pmPm ,
fee ,
planAgeGroupIdP ,
planAgeGroupIdC ,
rafP ,
rafC ,
nbrEmployeesRafP ,
nbrEmployeesRafC ,
contractId ,
basePlanId ,
groupOrPolicyNumber ,
planCoverageDescription ,
cobraGopn ,
cobraPcd ,
cobraCid ,
benefitId ,
insuranceStart ,
insuranceEnd ,
categoryId )
SELECT
matchKey as matchKey ,
MAX(NVL(contributionP ,0 )) as contributionP ,
MAX(NVL(stimulusContributionP ,0 )) as stimulusContributionP ,
MAX(NVL(contributionC ,0 )) as contributionC ,
MAX(NVL(ageMultiplier ,0 )) as ageMultiplier ,
MAX(NVL(rateTableIdP ,0 )) as rateTableIdP ,
MAX(NVL(rateTableIdC ,0 )) as rateTableIdC ,
MAX(NVL(accountNbrP ,0 )) as accountNbrP ,
MAX(NVL(accountNbrC ,0 )) as accountNbrC ,
MAX(NVL(commissionExpenseAccount ,0 )) as commissionExpenseAccount ,
MAX(NVL(commissionReceivableAccount ,0 )) as commissionReceivableAccount ,
MAX(NVL(commissionType ,0 )) as commissionType ,
MAX(NVL(commission ,0 )) as commission ,
MAX(NVL(pmPm ,0 )) as pmPm ,
MAX(NVL(fee ,0 )) as fee ,
MAX(NVL(planAgeGroupIdP ,0 )) as planAgeGroupIdP ,
MAX(NVL(planAgeGroupIdC ,0 )) as planAgeGroupIdC ,
MAX(NVL(rafP ,0 )) as rafP ,
MAX(NVL(rafC ,0 )) as rafC ,
MAX(NVL(nbrEmployeesRafP ,0 )) as nbrEmployeesRafP ,
MAX(NVL(nbrEmployeesRafC ,0 )) as nbrEmployeesRafC ,
CASE WHEN MAX(contractId) IS NOT NULL AND
MIN(contractId) IS NOT NULL AND
MAX(contractId) != MIN(contractId) THEN
CASE WHEN MAX(contractId) = 'No Contract No' THEN
MIN(contractId)
WHEN MIN(contractId) = 'No Contract No' THEN
MAX(contractId)
ELSE
MAX(contractId)
END
ELSE
MAX(contractId)
END as contractId ,
MAX(NVL(basePlanId ,0 )) as basePlanId ,
CASE WHEN MAX(groupOrPolicyNumber) IS NOT NULL AND
MIN(groupOrPolicyNumber) IS NOT NULL AND
MAX(groupOrPolicyNumber) != MIN(groupOrPolicyNumber) THEN
CASE WHEN MAX(groupOrPolicyNumber) = 'No Contract No' THEN
MIN(groupOrPolicyNumber)
WHEN MIN(groupOrPolicyNumber) = 'No Contract No' THEN
MAX(groupOrPolicyNumber)
ELSE
MAX(groupOrPolicyNumber)
END
ELSE
MAX(groupOrPolicyNumber)
END as groupOrPolicyNumber ,
CASE WHEN MAX(planCoverageDescription) IS NOT NULL AND
MIN(planCoverageDescription) IS NOT NULL AND
MAX(planCoverageDescription) != MIN(planCoverageDescription) THEN
CASE WHEN MAX(planCoverageDescription) = 'No Contract No' THEN
MIN(planCoverageDescription)
WHEN MIN(planCoverageDescription) = 'No Contract No' THEN
MAX(planCoverageDescription)
ELSE
MAX(planCoverageDescription)
END
ELSE
MAX(planCoverageDescription)
END as planCoverageDescription ,
CASE WHEN MAX(cobraGopn) IS NOT NULL AND
MIN(cobraGopn) IS NOT NULL AND
MAX(cobraGopn) != MIN(cobraGopn) THEN
CASE WHEN MAX(cobraGopn) = 'No Contract No' THEN
MIN(cobraGopn)
WHEN MIN(cobraGopn) = 'No Contract No' THEN
MAX(cobraGopn)
ELSE
MAX(cobraGopn)
END
ELSE
MAX(cobraGopn)
END as cobraGopn ,
CASE WHEN MAX(cobraPcd) IS NOT NULL AND
MIN(cobraPcd) IS NOT NULL AND
MAX(cobraPcd) != MIN(cobraPcd) THEN
CASE WHEN MAX(cobraPcd) = 'No Contract No' THEN
MIN(cobraPcd)
WHEN MIN(cobraPcd) = 'No Contract No' THEN
MAX(cobraPcd)
ELSE
MAX(cobraPcd)
END
ELSE
MAX(cobraPcd)
END as cobraPcd ,
CASE WHEN MAX(cobraCid) IS NOT NULL AND
MIN(cobraCid) IS NOT NULL AND
MAX(cobraCid) != MIN(cobraCid) THEN
CASE WHEN MAX(cobraCid) = 'No Contract No' THEN
MIN(cobraCid)
WHEN MIN(cobraCid) = 'No Contract No' THEN
MAX(cobraCid)
ELSE
MAX(cobraCid)
END
ELSE
MAX(cobraCid)
END as cobraCid ,
MAX(benefitId ) as benefitId ,
NULL as insuranceStart ,
NULL as insuranceEnd ,
NULL as categoryId
FROM meCostingXPrePrePre
GROUP BY matchKey;
如果我按如下方式构建索引并给出 CBO 提示，
CREATE INDEX C$MECOSTINGXPREPREPRE$MULTI0 ON MECOSTINGXPREPREPRE
(MATCHKEY, CONTRIBUTIONP, STIMULUSCONTRIBUTIONP, CONTRIBUTIONC, AGEMULTIPLIER,
RATETABLEIDP, RATETABLEIDC, ACCOUNTNBRP, ACCOUNTNBRC, COMMISSIONEXPENSEACCOUNT,
COMMISSIONRECEIVABLEACCOUNT, COMMISSIONTYPE, COMMISSION, PMPM, FEE,
PLANAGEGROUPIDP, PLANAGEGROUPIDC, RAFP, RAFC,
NBREMPLOYEESRAFP, NBREMPLOYEESRAFC, CONTRACTID, BASEPLANID, GROUPORPOLICYNUMBER,
PLANCOVERAGEDESCRIPTION, COBRAGOPN, COBRAPCD, COBRACID, BENEFITID)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;
INSERT /*+ APPEND */ INTO meCostingXPrePre(
...)
SELECT /*+ INDEX(meCostingXPrePrePre c$mecostingxpreprepre$multi0) */
...
FROM meCostingXPrePrePre
GROUP BY matchKey;
这将使 MAX() 正常运行。
我需要添加 15 个额外列并更改索引
小问题
CREATE INDEX C$MECOSTINGXPREPREPRE$MULTI0 ON MECOSTINGXPREPREPRE
(MATCHKEY, CONTRIBUTIONP, STIMULUSCONTRIBUTIONP, CONTRIBUTIONC, AGEMULTIPLIER,
RATETABLEIDP, RATETABLEIDC, ACCOUNTNBRP, ACCOUNTNBRC, COMMISSIONEXPENSEACCOUNT,
COMMISSIONRECEIVABLEACCOUNT, COMMISSIONTYPE, COMMISSION, PMPM, FEE,
PLANAGEGROUPIDP, PLANAGEGROUPIDC, ADDRESSONEP, ADDRESSONEC, ADDRESSTWOP,
ADDRESSTWOC, CITYP, CITYC, STATEP, STATEC, ZIPFULLP, ZIPFULLC, RAFP, RAFC,
NBREMPLOYEESRAFP, NBREMPLOYEESRAFC, CONTRACTID, BASEPLANID, GROUPORPOLICYNUMBER,
PLANCOVERAGEDESCRIPTION, COBRAGOPN, COBRAPCD, COBRACID, BENEFITID)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;
给出
ORA-01793: 索引列的最大数量为 32
首先 MAX() 不适用于大型列集，其次是索引+CBO 补丁
导致索引限制。
有什么建议吗？
Update 1/31/2011
I think I'm running against a DB limit. The GROUP BY expression
and all nondistinct aggregate functions may have exceeded a single
database block.
See http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/limits003.htm
Original post:
This is on Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod.
The following insert does not return the true MAX() values
if I have two records where the matchKey is the same, one
with a contributionP NULL value and another with a non-NULL value.
Sometimes, the value returned by MAX() is the NULL value.
INSERT /*+ APPEND */ INTO meCostingXPrePre(
matchKey ,
contributionP ,
stimulusContributionP ,
contributionC ,
ageMultiplier ,
rateTableIdP ,
rateTableIdC ,
accountNbrP ,
accountNbrC ,
commissionExpenseAccount ,
commissionReceivableAccount ,
commissionType ,
commission ,
pmPm ,
fee ,
planAgeGroupIdP ,
planAgeGroupIdC ,
rafP ,
rafC ,
nbrEmployeesRafP ,
nbrEmployeesRafC ,
contractId ,
basePlanId ,
groupOrPolicyNumber ,
planCoverageDescription ,
cobraGopn ,
cobraPcd ,
cobraCid ,
benefitId ,
insuranceStart ,
insuranceEnd ,
categoryId )
SELECT
matchKey as matchKey ,
MAX(NVL(contributionP ,0 )) as contributionP ,
MAX(NVL(stimulusContributionP ,0 )) as stimulusContributionP ,
MAX(NVL(contributionC ,0 )) as contributionC ,
MAX(NVL(ageMultiplier ,0 )) as ageMultiplier ,
MAX(NVL(rateTableIdP ,0 )) as rateTableIdP ,
MAX(NVL(rateTableIdC ,0 )) as rateTableIdC ,
MAX(NVL(accountNbrP ,0 )) as accountNbrP ,
MAX(NVL(accountNbrC ,0 )) as accountNbrC ,
MAX(NVL(commissionExpenseAccount ,0 )) as commissionExpenseAccount ,
MAX(NVL(commissionReceivableAccount ,0 )) as commissionReceivableAccount ,
MAX(NVL(commissionType ,0 )) as commissionType ,
MAX(NVL(commission ,0 )) as commission ,
MAX(NVL(pmPm ,0 )) as pmPm ,
MAX(NVL(fee ,0 )) as fee ,
MAX(NVL(planAgeGroupIdP ,0 )) as planAgeGroupIdP ,
MAX(NVL(planAgeGroupIdC ,0 )) as planAgeGroupIdC ,
MAX(NVL(rafP ,0 )) as rafP ,
MAX(NVL(rafC ,0 )) as rafC ,
MAX(NVL(nbrEmployeesRafP ,0 )) as nbrEmployeesRafP ,
MAX(NVL(nbrEmployeesRafC ,0 )) as nbrEmployeesRafC ,
CASE WHEN MAX(contractId) IS NOT NULL AND
MIN(contractId) IS NOT NULL AND
MAX(contractId) != MIN(contractId) THEN
CASE WHEN MAX(contractId) = 'No Contract No' THEN
MIN(contractId)
WHEN MIN(contractId) = 'No Contract No' THEN
MAX(contractId)
ELSE
MAX(contractId)
END
ELSE
MAX(contractId)
END as contractId ,
MAX(NVL(basePlanId ,0 )) as basePlanId ,
CASE WHEN MAX(groupOrPolicyNumber) IS NOT NULL AND
MIN(groupOrPolicyNumber) IS NOT NULL AND
MAX(groupOrPolicyNumber) != MIN(groupOrPolicyNumber) THEN
CASE WHEN MAX(groupOrPolicyNumber) = 'No Contract No' THEN
MIN(groupOrPolicyNumber)
WHEN MIN(groupOrPolicyNumber) = 'No Contract No' THEN
MAX(groupOrPolicyNumber)
ELSE
MAX(groupOrPolicyNumber)
END
ELSE
MAX(groupOrPolicyNumber)
END as groupOrPolicyNumber ,
CASE WHEN MAX(planCoverageDescription) IS NOT NULL AND
MIN(planCoverageDescription) IS NOT NULL AND
MAX(planCoverageDescription) != MIN(planCoverageDescription) THEN
CASE WHEN MAX(planCoverageDescription) = 'No Contract No' THEN
MIN(planCoverageDescription)
WHEN MIN(planCoverageDescription) = 'No Contract No' THEN
MAX(planCoverageDescription)
ELSE
MAX(planCoverageDescription)
END
ELSE
MAX(planCoverageDescription)
END as planCoverageDescription ,
CASE WHEN MAX(cobraGopn) IS NOT NULL AND
MIN(cobraGopn) IS NOT NULL AND
MAX(cobraGopn) != MIN(cobraGopn) THEN
CASE WHEN MAX(cobraGopn) = 'No Contract No' THEN
MIN(cobraGopn)
WHEN MIN(cobraGopn) = 'No Contract No' THEN
MAX(cobraGopn)
ELSE
MAX(cobraGopn)
END
ELSE
MAX(cobraGopn)
END as cobraGopn ,
CASE WHEN MAX(cobraPcd) IS NOT NULL AND
MIN(cobraPcd) IS NOT NULL AND
MAX(cobraPcd) != MIN(cobraPcd) THEN
CASE WHEN MAX(cobraPcd) = 'No Contract No' THEN
MIN(cobraPcd)
WHEN MIN(cobraPcd) = 'No Contract No' THEN
MAX(cobraPcd)
ELSE
MAX(cobraPcd)
END
ELSE
MAX(cobraPcd)
END as cobraPcd ,
CASE WHEN MAX(cobraCid) IS NOT NULL AND
MIN(cobraCid) IS NOT NULL AND
MAX(cobraCid) != MIN(cobraCid) THEN
CASE WHEN MAX(cobraCid) = 'No Contract No' THEN
MIN(cobraCid)
WHEN MIN(cobraCid) = 'No Contract No' THEN
MAX(cobraCid)
ELSE
MAX(cobraCid)
END
ELSE
MAX(cobraCid)
END as cobraCid ,
MAX(benefitId ) as benefitId ,
NULL as insuranceStart ,
NULL as insuranceEnd ,
NULL as categoryId
FROM meCostingXPrePrePre
GROUP BY matchKey;
If I build an index as follows and give a CBO hint
CREATE INDEX C$MECOSTINGXPREPREPRE$MULTI0 ON MECOSTINGXPREPREPRE
(MATCHKEY, CONTRIBUTIONP, STIMULUSCONTRIBUTIONP, CONTRIBUTIONC, AGEMULTIPLIER,
RATETABLEIDP, RATETABLEIDC, ACCOUNTNBRP, ACCOUNTNBRC, COMMISSIONEXPENSEACCOUNT,
COMMISSIONRECEIVABLEACCOUNT, COMMISSIONTYPE, COMMISSION, PMPM, FEE,
PLANAGEGROUPIDP, PLANAGEGROUPIDC, RAFP, RAFC,
NBREMPLOYEESRAFP, NBREMPLOYEESRAFC, CONTRACTID, BASEPLANID, GROUPORPOLICYNUMBER,
PLANCOVERAGEDESCRIPTION, COBRAGOPN, COBRAPCD, COBRACID, BENEFITID)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;
INSERT /*+ APPEND */ INTO meCostingXPrePre(
...)
SELECT /*+ INDEX(meCostingXPrePrePre c$mecostingxpreprepre$multi0) */
...
FROM meCostingXPrePrePre
GROUP BY matchKey;
This gets MAX() to behave correctly.
I need to add 15 extra columns and change the index
Small problem
CREATE INDEX C$MECOSTINGXPREPREPRE$MULTI0 ON MECOSTINGXPREPREPRE
(MATCHKEY, CONTRIBUTIONP, STIMULUSCONTRIBUTIONP, CONTRIBUTIONC, AGEMULTIPLIER,
RATETABLEIDP, RATETABLEIDC, ACCOUNTNBRP, ACCOUNTNBRC, COMMISSIONEXPENSEACCOUNT,
COMMISSIONRECEIVABLEACCOUNT, COMMISSIONTYPE, COMMISSION, PMPM, FEE,
PLANAGEGROUPIDP, PLANAGEGROUPIDC, ADDRESSONEP, ADDRESSONEC, ADDRESSTWOP,
ADDRESSTWOC, CITYP, CITYC, STATEP, STATEC, ZIPFULLP, ZIPFULLC, RAFP, RAFC,
NBREMPLOYEESRAFP, NBREMPLOYEESRAFC, CONTRACTID, BASEPLANID, GROUPORPOLICYNUMBER,
PLANCOVERAGEDESCRIPTION, COBRAGOPN, COBRAPCD, COBRACID, BENEFITID)
NOLOGGING
TABLESPACE INDX
NOPARALLEL;
gives
ORA-01793: maximum number of index columns is 32
First MAX() does not work with large column sets and second, the index+CBO patch
leads to index limitation.
Any suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您得到不正确的结果，这显然是 Oracle 的错误。您是否向 Oracle 支持记录了支持请求和/或识别了您遇到的错误（我猜这就是解决方法的来源）？
如果您使用的是 10.2.0.1，您是否尝试过应用最新的补丁集（很可能是 10.2.0.4）？初始 10.2 版本和最终 10.2 版本之间修复了许多错误。如果这是 Oracle 已修复的错误，则可能还会有一个一次性补丁，尽管该补丁可能需要安装最新的补丁集。
If you're getting incorrect results, that's obviously an Oracle bug. Have you logged a support request with Oracle Support and/or identified the bug you're hitting (which I'm guessing is where the workaround comes from)?
If you are using 10.2.0.1, have you tried applying the latest patchset (most likely 10.2.0.4)? There are a number of bug fixes between the initial 10.2 release and the terminal 10.2 release. If this is a bug that Oracle has fixed, there may also be a one-off patch, though that patch likely requires the latest patchset to be installed.
您是否尝试过像这样编写查询的 SELECT 部分？它应该返回相同的结果。
它分两步工作：首先计算所需的最小值和最大值，然后进行一些 NULL 值替换。 （你的 CASE 语句似乎过于复杂，因为你没有考虑到 Oracle 中的空字符串和 NULL 是相同的。）
我很想知道内部 SELECT 是否已经有错误的最大结果。
Have you tried writing the SELECT part of the query like this. It should return the same result.
It works in two steps: First it calculates the required minimum and maximum values and then it does some NULL value replacement. (Your CASE statements seem to be overly complex since you haven't considered that an empty string and NULL are the same in Oracle.)
I'd be very intersted to know if the inner SELECT has already wrong maximum result.
好的，将工作分成两半就能得到我需要的东西：
OK, splitting the work in half will get me what I need: