Ошибка Oracle MAX() с исправлением индекса CBO NULL приводит к ограничению индекса
Обновление 31.01.2011
Я думаю, что я бегу против лимита БД. Выражение GROUP BY
и все нечеткие агрегатные функции, возможно, превысили одну
блок базы данных.
См. http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/limits003.htm
Исходное сообщение:
Это в Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod.
Следующая вставка не возвращает истинные значения MAX()
если у меня есть две записи, где совпадает matchKey, одна
со значением NULL contribP и другим с ненулевым значением.
Иногда значение, возвращаемое 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
приводит к ограничению индекса.
Какие-либо предложения?
3 ответа
Решение
Хорошо, разделив работу пополам, я получу то, что мне нужно:
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;
Если вы получаете неправильные результаты, это, очевидно, ошибка Oracle. Зарегистрировали ли вы запрос в службу поддержки в Oracle Support и / или определили ошибку, с которой сталкиваетесь (я предполагаю, что это обходной путь)?
Если вы используете 10.2.0.1, пробовали ли вы применить последний набор патчей (скорее всего, 10.2.0.4)? Существует несколько исправлений ошибок между первоначальной версией 10.2 и версией терминала 10.2. Если это ошибка, исправленная Oracle, возможно, имеется одноразовое исправление, хотя для этого исправления, вероятно, требуется установить последний набор исправлений.
Вы пытались написать SELECT часть запроса, как это. Он должен вернуть тот же результат.
Он работает в два этапа: сначала вычисляются необходимые минимальные и максимальные значения, а затем выполняется замена некоторых значений NULL. (Ваши операторы CASE кажутся слишком сложными, поскольку вы не считали, что пустая строка и NULL одинаковы в Oracle.)
Я был бы очень заинтересован, чтобы знать, имеет ли внутренний SELECT уже неправильный максимальный результат.
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
);