SYMPTOM:
Create primary index and collect statistics on index is not matching in Strategy v9.x SQL when VLDB setting to insert post statement 1 is defined as as 'COLLECT STATISTICS ??? INDEX (!a)' . Refer KB18564.
Primary index SQL generated:
create volatile table ZZOL07, no fallback, no log(
ProgramYear_Nb INTEGER,
ProgramAgent_Id CHAR(9),
ProgramAgentDisplay_Nm VARCHAR(60),
ProgramAgentStatus_Ds CHAR(15),
AgencyLocation_Nm VARCHAR(50),
CustCol_70 CHAR(15),
DistrictDisbursement_Nb INTEGER,
District_Nm VARCHAR(60),
ProductionStart_dt DATE,
PlanType_Cd VARCHAR(50),
PlanType_Ds VARCHAR(100),
PlanSuspension_In CHAR(1),
Subtype_In CHAR(1),
OnPlan_In CHAR(2),
PlanPeriod_Nb SMALLINT,
EndMonth_Id4 INTEGER,
CustCol_79 VARCHAR(50),
AutoExtension_In CHAR(1),
AccountingMonth_Id SMALLINT,
AccountingMonthShort_Ds CHAR(3),
AccountingMonth_Ds CHAR(12))
primary index (ProgramYear_Nb, ProgramAgent_Id, ProgramAgentDisplay_Nm, ProgramAgentStatus_Ds, AgencyLocation_Nm, CustCol_70,
DistrictDisbursement_Nb, District_Nm, ProductionStart_dt, PlanType_Cd, PlanType_Ds, PlanSuspension_In, Subtype_In, OnPlan_In, PlanPeriod_Nb,
EndMonth_Id4) on commit preserve rows
Collect Statistics index generated:
insert into ZZOL07
select a111.ProgramYear_Nb ProgramYear_Nb,
a19.ProgramAgent_Id ProgramAgent_Id,
max(a110.ProgramAgentDisplay_Nm) ProgramAgentDisplay_Nm,
max(a110.ProgramAgentStatus_Ds) ProgramAgentStatus_Ds,
max(a110.AgencyLocation_Nm) AgencyLocation_Nm,
max(SUBSTR(a110.ProgramAgentStatus_Ds, 1, 3)) CustCol_70,
max(a110.DistrictDisbursement_Nb) DistrictDisbursement_Nb,
max(a110.District_Nm) District_Nm,
a19.ProductionStart_dt ProductionStart_dt,
a19.PlanType_Cd PlanType_Cd,
max(a19.PlanType_Ds) PlanType_Ds,
a111.PlanSuspension_In PlanSuspension_In,
a19.Subtype_In Subtype_In,
a111.OnPlan_In OnPlan_In,
a111.PlanPeriod_Nb PlanPeriod_Nb,
a13.EndMonth_Id EndMonth_Id4,
null CustCol_79,
a19.AutoExtension_In AutoExtension_In,
a11.AccountingMonth_Id AccountingMonth_Id,
max(a11.AccountingMonthShort_Ds) AccountingMonthShort_Ds,
max(SUBSTRING(a11.AccountingMonth_Ds FROM 3 FOR 2)) AccountingMonth_Ds
from RPT_AGSL_APPS.A3_LU_CalendarMonth a11
cross join RPT_AGSL_APPS.F00_ProgramAgentPlan a19
cross join (select distinct endmonth_id from rpt_agsl_apps.a00_lu_producer) a13
join RPT_AGSL_APPS.F00_PrgrmAgentPlanMonthActual a111
on (a11.AccountingMonth_Id = a111.AccountingMonth_Id and
a13.EndMonth_Id = a111.EndMonth_Id and
a19.PlanType_Cd = a111.PlanType_Cd and
a19.ProgramAgent_Id = a111.ProgramAgent_Id)
join RPT_AGSL_APPS.A00_LU_ProgramAgent a110
on (a111.StartMonth_Id = a110.StartMonth_Id and
a13.EndMonth_Id = a110.EndMonth_Id and
a19.ProgramAgent_Id = a110.ProgramAgent_Id)
where (a19.ProgramAgent_Id in ('010030318')
and a11.AccountingMonth_Id in (24136)
and a13.EndMonth_Id = 32767)
group by a111.ProgramYear_Nb,
a19.ProgramAgent_Id,
a19.ProductionStart_dt,
a19.PlanType_Cd,
a111.PlanSuspension_In,
a19.Subtype_In,
a111.OnPlan_In,
a111.PlanPeriod_Nb,
a13.EndMonth_Id,
a19.AutoExtension_In,
a11.AccountingMonth_Id
COLLECT STATISTICS ZZOL07 INDEX (ProgramYear_Nb, ProgramAgent_Id, ProgramAgentDisplay_Nm, ProgramAgentStatus_Ds, AgencyLocation_Nm, CustCol_70,
DistrictDisbursement_Nb, District_Nm, ProductionStart_dt, PlanType_Cd, PlanType_Ds, PlanSuspension_In, Subtype_In, OnPlan_In, PlanPeriod_Nb, EndMonth_Id4, CustCol_79, AutoExtension_In, AccountingMonth_Id, AccountingMonthShort_Ds, AccountingMonth_Ds).
From the above SQLs, it is seen that 5 additional columns (CustCol_79, AutoExtension_In, AccountingMonth_Id, AccountingMonthShort_Ds, AccountingMonth_Ds) are being referenced in the Collect Statistics SQL which are not being referenced in the Primary index SQL.
CAUSE
From the above SQLs it is seen that 16 columns are being referenced in the primary index while there are actually 21 attribute ID columns in the SELECT clause. This limit of 16 is coming from the ‘Max Columns in Index’ VLDB setting (located in the ‘Indexing’ folder) which is set to 16 for Teradata.
ACTION
Increase the ‘Max Columns in Index’ VLDB setting to 21. VLDB change can be made at the report level rather than at the project/DB Instance level so that the setting change will apply to just that report if required.
