Oracle MAX()错误与NULLs CBO索引修复导致索引限制

Question

更新1/31/2011

我想我遇到了数据库限制。按表达分组

而且所有不明确的聚合函数都可能超过了一个

数据库块。

请参阅http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/limits003.htm

原始帖子：

这是在Oracle数据库10g企业版10.2.0.1.0-prod上发布的。

下面的insert不返回真正的MAX()值

如果我有两个matchKey相同的记录，一个

有一个contributionP空值，另一个非空值。

有时，MAX()返回的值是空值。

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()不适用于大型列集；其次，使用index+CBO修补程序

导致指数限制。

有什么建议吗？

Answer 1

好吧，把工作一分为二，我就能得到我所需要的：

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;

Answer 2

如果得到了不正确的结果，这显然是Oracle的错误。您是否用Oracle支持记录了一个支持请求，并且/或识别了您所碰到的bug (我猜这就是解决方法的来源)？

如果您使用的是10.2.0.1，您是否尝试过应用最新的修补程序集(很可能是10.2.0.4)？在最初10.2版本和终端10.2版本之间有许多bug修复。如果这是Oracle修复的错误，也可能有一次性修补程序，尽管该修补程序可能需要安装最新的修补程序集。

Answer 3

您是否尝试过像这样编写查询的SELECT部分。它应该返回相同的结果。

它分为两个步骤:首先计算所需的最小值和最大值，然后进行一些空值替换。(您的CASE语句似乎过于复杂，因为您还没有考虑到在Oracle中空字符串和NULL是相同的。)

我很高兴知道内部选择是否已经有错误的最大结果。

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
);