ORA-01450: Maximum Key Length (6398) Exceeded When Creating a Materialized View (Doc ID 1072416.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 10.2.0.4 [Release 10.1 to 10.2]
Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.2 [Release 11.1 to 11.2]
Information in this document applies to any platform.

Symptoms

When running the following create materialized view ( mview ) statement we see that the index coming from the master mview is causing the index key limit to be exceeded.

Offending create mview statement:

CREATE MATERIALIZED VIEW ITPF_NPDM10.RPT_HIBS_ACT_VS_PLAN_MV
TABLESPACE CBIS_DATA_LARGE
NOCACHE
NOLOGGING
NOCOMPRESS
NOPARALLEL
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
AS
SELECT regional_billing_function,
customer_region,
customer_name,
ssoref,
hlpcode,
hlpname,
bp_id,
billable_product,
bp_name,
bp_id_and_name,
bp_desc,
bp_is_swc,
bp_product_category_group,
bp_product_category_name,
bp_portfolio_name,
bp_business_name,
bp_organisation_name,
bp_programme_name,
bp_group,
bp_team_name,
swc_bcc,
pdu_name,
pdu_region,
pdu_business_area,
bp_it_owner,
bp_bus_sponsor,
bp_flagship_name,
bp_product_type_name,
customer_business_product,
customer_business_area,
customer_is_overhead,
cost_type,
bp_status,
latest_fcst_bp_status,
billable_service_code,
billable_service_name,
ito_type,
customer_currency,
billable_service_grouping,
billable_service_subgroup,
supplier_name,
bp_priority,
pdu_country,
pdu_cost_centre,
pdu_frm,
cust_rm,
cust_country,
cust_le_code,
cust_le_name,
cdu_name,
cdu_business_area,
cdu_region,
cdu_country,
pdu_group,
cdu_group,
consumption_type,
client,
cu_global_head,
pdu_area,
pdu_global_head,
system_name,
boulder_id,
boulder_description,
boulder_category,
boulder_owner,
boulder_currency,
allocation_percentage,
boulder_spend,
boulder_spend_usd,
supplier_type,
default_cost_centre,
customer_level_1,
customer_level_2,
customer_level_3,
last_billing_month,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 1 THEN local_cost ELSE 0 END) AS jan_act,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 2 THEN local_cost ELSE 0 END) AS feb_act,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 3 THEN local_cost ELSE 0 END) AS mar_act,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 4 THEN local_cost ELSE 0 END) AS apr_act,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 5 THEN local_cost ELSE 0 END) AS may_act,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 6 THEN local_cost ELSE 0 END) AS jun_act,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 7 THEN local_cost ELSE 0 END) AS jul_act,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 8 THEN local_cost ELSE 0 END) AS aug_act,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 9 THEN local_cost ELSE 0 END) AS sep_act,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 10 THEN local_cost ELSE 0 END) AS oct_act,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 11 THEN local_cost ELSE 0 END) AS nov_act,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 12 THEN local_cost ELSE 0 END) AS dec_act,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 1 THEN local_cost ELSE 0 END) AS jan_plan,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 2 THEN local_cost ELSE 0 END) AS feb_plan,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 3 THEN local_cost ELSE 0 END) AS mar_plan,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 4 THEN local_cost ELSE 0 END) AS apr_plan,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 5 THEN local_cost ELSE 0 END) AS may_plan,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 6 THEN local_cost ELSE 0 END) AS jun_plan,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 7 THEN local_cost ELSE 0 END) AS jul_plan,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 8 THEN local_cost ELSE 0 END) AS aug_plan,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 9 THEN local_cost ELSE 0 END) AS sep_plan,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 10 THEN local_cost ELSE 0 END) AS oct_plan,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 11 THEN local_cost ELSE 0 END) AS nov_plan,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 12 THEN local_cost ELSE 0 END) AS dec_plan,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 1 THEN local_cost ELSE 0 END) AS jan_fcst,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 2 THEN local_cost ELSE 0 END) AS feb_fcst,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 3 THEN local_cost ELSE 0 END) AS mar_fcst,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 4 THEN local_cost ELSE 0 END) AS apr_fcst,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 5 THEN local_cost ELSE 0 END) AS may_fcst,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 6 THEN local_cost ELSE 0 END) AS jun_fcst,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 7 THEN local_cost ELSE 0 END) AS jul_fcst,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 8 THEN local_cost ELSE 0 END) AS aug_fcst,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 9 THEN local_cost ELSE 0 END) AS sep_fcst,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 10 THEN local_cost ELSE 0 END) AS oct_fcst,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 11 THEN local_cost ELSE 0 END) AS nov_fcst,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 12 THEN local_cost ELSE 0 END) AS dec_fcst,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 1 THEN units ELSE 0 END) AS jan_act_units,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 2 THEN units ELSE 0 END) AS feb_act_units,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 3 THEN units ELSE 0 END) AS mar_act_units,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 4 THEN units ELSE 0 END) AS apr_act_units,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 5 THEN units ELSE 0 END) AS may_act_units,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 6 THEN units ELSE 0 END) AS jun_act_units,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 7 THEN units ELSE 0 END) AS jul_act_units,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 8 THEN units ELSE 0 END) AS aug_act_units,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 9 THEN units ELSE 0 END) AS sep_act_units,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 10 THEN units ELSE 0 END) AS oct_act_units,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 11 THEN units ELSE 0 END) AS nov_act_units,
SUM (CASE WHEN cycle_name = 'Actual' AND month_of_year = 12 THEN units ELSE 0 END) AS dec_act_units,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 1 THEN units ELSE 0 END) AS jan_plan_units,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 2 THEN units ELSE 0 END) AS feb_plan_units,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 3 THEN units ELSE 0 END) AS mar_plan_units,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 4 THEN units ELSE 0 END) AS apr_plan_units,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 5 THEN units ELSE 0 END) AS may_plan_units,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 6 THEN units ELSE 0 END) AS jun_plan_units,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 7 THEN units ELSE 0 END) AS jul_plan_units,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 8 THEN units ELSE 0 END) AS aug_plan_units,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 9 THEN units ELSE 0 END) AS sep_plan_units,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 10 THEN units ELSE 0 END) AS oct_plan_units,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 11 THEN units ELSE 0 END) AS nov_plan_units,
SUM (CASE WHEN cycle_name = 'Plan' AND month_of_year = 12 THEN units ELSE 0 END) AS dec_plan_units,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 1 THEN units ELSE 0 END) AS jan_fcst_units,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 2 THEN units ELSE 0 END) AS feb_fcst_units,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 3 THEN units ELSE 0 END) AS mar_fcst_units,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 4 THEN units ELSE 0 END) AS apr_fcst_units,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 5 THEN units ELSE 0 END) AS may_fcst_units,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 6 THEN units ELSE 0 END) AS jun_fcst_units,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 7 THEN units ELSE 0 END) AS jul_fcst_units,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 8 THEN units ELSE 0 END) AS aug_fcst_units,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 9 THEN units ELSE 0 END) AS sep_fcst_units,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 10 THEN units ELSE 0 END) AS oct_fcst_units,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 11 THEN units ELSE 0 END) AS nov_fcst_units,
SUM (CASE WHEN cycle_name = 'Forecast' AND month_of_year = 12 THEN units ELSE 0 END) AS dec_fcst_units
FROM hibs_billing_cube_mv              --   a nested mview, structure unknown
WHERE NOT (local_cost = 0 AND units = 0)
GROUP BY regional_billing_function,
customer_region,
customer_name,
ssoref,
hlpcode,
hlpname,
bp_id,
billable_product,
bp_name,
bp_id_and_name,
bp_desc,
bp_is_swc,
bp_product_category_group,
bp_product_category_name,
bp_portfolio_name,
bp_business_name,
bp_organisation_name,
bp_programme_name,
bp_group,
bp_team_name,
swc_bcc,
pdu_name,
pdu_region,
pdu_business_area,
bp_it_owner,
bp_bus_sponsor,
bp_flagship_name,
bp_product_type_name,
customer_business_product,
customer_business_area,
customer_is_overhead,
cost_type,
bp_status,
latest_fcst_bp_status,
billable_service_code,
billable_service_name,
ito_type,
customer_currency,
billable_service_grouping,
billable_service_subgroup,
supplier_name,
bp_priority,
pdu_country,
pdu_cost_centre,
pdu_frm,
cust_rm,
cust_country,
cust_le_code,
cust_le_name,
cdu_name,
cdu_business_area,
cdu_region,
cdu_country,
pdu_group,
cdu_group,
consumption_type,
client,
cu_global_head,
pdu_area,
pdu_global_head,
system_name,
boulder_id,
boulder_description,
boulder_category,
boulder_owner,
boulder_currency,
allocation_percentage,
boulder_spend,
boulder_spend_usd,
supplier_type,
default_cost_centre,
customer_level_1,
customer_level_2,
customer_level_3,
last_billing_month;


This gives the error:

ORA-01450: maximum key length (6398) exceeded while creating a materialized view

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms