Oracle MAX() bug with NULLs CBO index fix leads to index limitation
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.htmOriginal 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, ACCOUNT开发者_开发知识库NBRC, 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 32First MAX() does not work with large column sets and second, the index+CBO patch
leads to index limitation.Any suggestions?
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.
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.
SELECT
matchKey as matchKey ,
NVL(contributionP ,0 ) as contributionP ,
NVL(stimulusContributionP ,0 ) as stimulusContributionP ,
NVL(contributionC ,0 ) as contributionC ,
NVL(ageMultiplier ,0 ) as ageMultiplier ,
NVL(rateTableIdP ,0 ) as rateTableIdP ,
NVL(rateTableIdC ,0 ) as rateTableIdC ,
NVL(accountNbrP ,0 ) as accountNbrP ,
NVL(accountNbrC ,0 ) as accountNbrC ,
NVL(commissionExpenseAccount ,0 ) as commissionExpenseAccount ,
NVL(commissionReceivableAccount ,0 ) as commissionReceivableAccount ,
NVL(commissionType ,0 ) as commissionType ,
NVL(commission ,0 ) as commission ,
NVL(pmPm ,0 ) as pmPm ,
NVL(fee ,0 ) as fee ,
NVL(planAgeGroupIdP ,0 ) as planAgeGroupIdP ,
NVL(planAgeGroupIdC ,0 ) as planAgeGroupIdC ,
NVL(rafP ,0 ) as rafP ,
NVL(rafC ,0 ) as rafC ,
NVL(nbrEmployeesRafP ,0 ) as nbrEmployeesRafP ,
NVL(nbrEmployeesRafC ,0 ) as nbrEmployeesRafC ,
CASE
WHEN maxContractId = 'No Contract No' THEN minContractId
ELSE maxContractId
END as contractId ,
NVL(basePlanId ,0 ) as basePlanId ,
CASE
WHEN maxGroupOrPolicyNumber = 'No Contract No' THEN minGroupOrPolicyNumber
ELSE maxGroupOrPolicyNumber
END as groupOrPolicyNumber ,
CASE
WHEN maxPlanCoverageDescription = 'No Contract No' THEN minPlanCoverageDescription
ELSE maxPlanCoverageDescription
END as planCoverageDescription ,
CASE
WHEN maxCobraGopn = 'No Contract No' THEN minCobraGopn
ELSE maxCobraGopn
END as cobraGopn ,
CASE
WHEN maxCobraPcd = 'No Contract No' THEN minCobraPcd
ELSE maxCobraPcd
END as cobraPcd ,
CASE
WHEN maxCobraCid = 'No Contract No' THEN minCobraCid
ELSE maxCobraCid
END as cobraCid ,
benefitId as benefitId ,
NULL as insuranceStart ,
NULL as insuranceEnd ,
NULL as categoryId
FROM (
SELECT
matchKey as matchKey ,
MAX(contributionP ) as contributionP ,
MAX(stimulusContributionP ) as stimulusContributionP ,
MAX(contributionC ) as contributionC ,
MAX(ageMultiplier ) as ageMultiplier ,
MAX(rateTableIdP ) as rateTableIdP ,
MAX(rateTableIdC ) as rateTableIdC ,
MAX(accountNbrP ) as accountNbrP ,
MAX(accountNbrC ) as accountNbrC ,
MAX(commissionExpenseAccount ) as commissionExpenseAccount ,
MAX(commissionReceivableAccount ) as commissionReceivableAccount ,
MAX(commissionType ) as commissionType ,
MAX(commission ) as commission ,
MAX(pmPm ) as pmPm ,
MAX(fee ) as fee ,
MAX(planAgeGroupIdP ) as planAgeGroupIdP ,
MAX(planAgeGroupIdC ) as planAgeGroupIdC ,
MAX(rafP ) as rafP ,
MAX(rafC ) as rafC ,
MAX(nbrEmployeesRafP ) as nbrEmployeesRafP ,
MAX(nbrEmployeesRafC ) as nbrEmployeesRafC ,
MIN(contractId ) as minContractId ,
MAX(contractId ) as maxContractId ,
MAX(basePlanId ) as basePlanId ,
MIN(groupOrPolicyNumber ) as minGroupOrPolicyNumber ,
MAX(groupOrPolicyNumber ) as maxGroupOrPolicyNumber ,
MIN(planCoverageDescription ) as minPlanCoverageDescription ,
MAX(planCoverageDescription ) as maxPlanCoverageDescription ,
MIN(cobraGopn ) as minCobraGopn ,
MAX(cobraGopn ) as maxCobraGopn ,
MIN(cobraPcd ) as mincobraPcd ,
MAX(cobraPcd ) as maxcobraPcd ,
MIN(cobraCid ) as minCobraCid ,
MAX(cobraCid ) as maxCobraCid ,
MAX(benefitId ) as benefitId
FROM meCostingXPrePrePre
GROUP BY matchKey
);
OK, splitting the work in half will get me what I need:
INSERT /*+ APPEND */ INTO meCostingXPrePreFirstHalf(
matchKey ,
contributionP ,
stimulusContributionP ,
contributionC ,
ageMultiplier ,
rateTableIdP ,
rateTableIdC ,
accountNbrP ,
accountNbrC ,
commissionExpenseAccount ,
commissionReceivableAccount ,
commissionType ,
commission ,
pmPm ,
fee ,
planAgeGroupIdP ,
planAgeGroupIdC )
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
FROM meCostingXPrePrePre
GROUP BY matchKey;
/* No commit yet. */
INSERT /*+ APPEND */ INTO meCostingXPrePreOtherHalf(
matchKey ,
rafP ,
rafC ,
nbrEmployeesRafP ,
nbrEmployeesRafC ,
contractId ,
basePlanId ,
groupOrPolicyNumber ,
planCoverageDescription ,
cobraGopn ,
cobraPcd ,
cobraCid ,
benefitId ,
insuranceStart ,
insuranceEnd ,
categoryId )
SELECT
matchKey as matchKey ,
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
FROM meCostingXPrePrePre
GROUP BY matchKey;
/* No commit yet. */
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
f.matchKey as matchKey ,
f.contributionP as contributionP ,
f.stimulusContributionP as stimulusContributionP ,
f.contributionC as contributionC ,
f.ageMultiplier as ageMultiplier ,
f.rateTableIdP as rateTableIdP ,
f.rateTableIdC as rateTableIdC ,
f.accountNbrP as accountNbrP ,
f.accountNbrC as accountNbrC ,
f.commissionExpenseAccount as commissionExpenseAccount ,
f.commissionReceivableAccount as commissionReceivableAccount ,
f.commissionType as commissionType ,
f.commission as commission ,
f.pmPm as pmPm ,
f.fee as fee ,
f.planAgeGroupIdP as planAgeGroupIdP ,
f.planAgeGroupIdC as planAgeGroupIdC ,
o.rafP as rafP ,
o.rafC as rafC ,
o.nbrEmployeesRafP as nbrEmployeesRafP ,
o.nbrEmployeesRafC as nbrEmployeesRafC ,
o.contractId as contractId ,
o.basePlanId as basePlanId ,
o.groupOrPolicyNumber as groupOrPolicyNumber ,
o.planCoverageDescription as planCoverageDescription ,
o.cobraGopn as cobraGopn ,
o.cobraPcd as cobraPcd ,
o.cobraCid as cobraCid ,
o.benefitId as benefitId ,
o.insuranceStart as insuranceStart ,
o.insuranceEnd as insuranceEnd ,
o.categoryId as categoryId ,
NULL as insuranceStart ,
NULL as insuranceEnd ,
NULL as categoryId
FROM
meCostingXPrePreFirstHalf f
INNER JOIN meCostingXPrePreOtherHalf o
ON f.matchKey = o.matchKey;
/* Now it is safe to commit. */
COMMIT;
加载中,请稍侯......
精彩评论