ORA-01793: maximum number of index columns is 32

Dear all,

I am creating materialized view. The base table does not have any index at all. But it is giving this error.

SQL> start c:/hr_baseline_mv.sql
124 /
FROM hruser.HR_BASELINE) "Workforce_Reporting"
*
ERROR at line 80:
ORA-01793: maximum number of index columns is 32

SQL>

Here is the oracle version i am using.

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

I would appreciate if any one can help me on this.

Post your Mview create script.

Here is the code.

CREATE MATERIALIZED VIEW test_mv
ENABLE QUERY REWRITE AS
SELECT
"Workforce_Reporting"."COUNTRY" "Country",
"Workforce_Reporting"."OFFICE_CITY" "Personal_Subarea",
"Workforce_Reporting"."GROUP_NAME" "Group_Financial_",
"Workforce_Reporting"."DIV" "Division",
"Workforce_Reporting"."JOB_FAMILY" "Job_Family",
"Workforce_Reporting"."JOB_FUNCTION" "Job_Function",
"Workforce_Reporting"."JOB_TITLE" "Job_Title",
"Workforce_Reporting"."AREA_TOP_LEVEL" "Area_Top_Level",
"Workforce_Reporting"."TEMP_ID" "Temp_ID",
"Workforce_Reporting"."FNAME" "First_Name",
"Workforce_Reporting"."LNAME" "Last_Name",
"Workforce_Reporting"."TEMP_SUPV_ID" "Temp_Supv_ID",
"Workforce_Reporting"."SUPV_NAME" "Supv_Name",
"Workforce_Reporting"."POS_DESC" "Position_Description",
"Workforce_Reporting"."EMPLOYEE_CATEGORY" "Employee_Category",
"Workforce_Reporting"."COST_CENTER" "Cost_Center",
"Workforce_Reporting"."COST_CENTER_DESC" "Cost_Center_Desc",
"Workforce_Reporting"."EMAIL" "Email",
"Workforce_Reporting"."STATUS" "Status",
"Workforce_Reporting"."CTIME" "As_Of_End",
"Workforce_Reporting"."GENERIC_FUNCTION" "Generic_Function",
"Workforce_Reporting"."AVAYA_TENOVIS" "Global_Workforce",
"Workforce_Reporting"."PERF_INFO" "Performance_Info",
"Workforce_Reporting"."PAY_TYPE" "Pay_Scale_Type",
"Workforce_Reporting"."PSGROUP_GRADE" "PS_Group_Grade",
"Workforce_Reporting"."POSITION_LEVEL" "Level1",
"Workforce_Reporting"."LAST_STATUS_CHANGE" "Last_Status_Change",
"Workforce_Reporting"."FINANCIALCCHIER10" "Financial_Hierarchy_10",
"Workforce_Reporting"."FINANCIALCCHIER9" "Financial_Hierarchy_9",
"Workforce_Reporting"."FINANCIALCCHIER8" "Financial_Hierarchy_8",
"Workforce_Reporting"."FINANCIALCCHIER7" "Financial_Hierarchy_7",
"Workforce_Reporting"."FINANCIALCCHIER6" "Financial_Hierarchy_6",
"Workforce_Reporting"."FINANCIALCCHIER5" "Financial_Hierarchy_5",
"Workforce_Reporting"."FINANCIALCCHIER4" "Financial_Hierarchy_4",
"Workforce_Reporting"."EC" "EC",
"Workforce_Reporting"."EC_PLUS1" "EC_Plus1",
"Workforce_Reporting"."EC_PLUS2" "EC_Plus2",
"Workforce_Reporting"."EC_PLUS3" "EC_Plus3",
"Workforce_Reporting"."EC_PLUS4" "EC_Plus4",
"Workforce_Reporting"."EC_PLUS5" "EC_Plus5",
"Workforce_Reporting"."EC_PLUS6" "EC_Plus6",
"Workforce_Reporting"."EC_PLUS7" "EC_Plus7",
"Workforce_Reporting"."AREA_SL" "Area_SL",
SUM ("Workforce_Reporting"."YEARS_OF_SERVICE") "Years_Of_Service",
SUM ("Workforce_Reporting"."PCT_WORKED") "FTE",
SUM ("Workforce_Reporting"."REPR_SPOC") "Representative_Spoc",
SUM ("Workforce_Reporting"."MGMT_SPOC") "Management_Spoc",
SUM ("Workforce_Reporting"."LAYER") "Layer",
SUM ("Workforce_Reporting"."CORP_EQUIVALENT") "Corp_Equivalent"
FROM (SELECT HR_BASELINE.status, HR_BASELINE.area_top_level,
HR_BASELINE.country, HR_BASELINE.office_city,
HR_BASELINE.group_name, HR_BASELINE.div,
HR_BASELINE.job_family, HR_BASELINE.job_title,
HR_BASELINE.avaya_tenovis, HR_BASELINE.temp_id,
HR_BASELINE.employee_category, HR_BASELINE.pct_worked,
HR_BASELINE.perf_info, HR_BASELINE.ctime,
HR_BASELINE.data_type, HR_BASELINE.last_status_change,
HR_BASELINE.generic_function, HR_BASELINE.global_regional,
HR_BASELINE.area_sl, HR_BASELINE.job_function,
HR_BASELINE.ec, HR_BASELINE.ec_plus1, HR_BASELINE.ec_plus2,
HR_BASELINE.ec_plus3, HR_BASELINE.ec_plus4,
HR_BASELINE.ec_plus5, HR_BASELINE.ec_plus6,
HR_BASELINE.ec_plus7, HR_BASELINE.financialcchier4,
HR_BASELINE.financialcchier5, HR_BASELINE.financialcchier6,
HR_BASELINE.financialcchier7, HR_BASELINE.financialcchier8,
HR_BASELINE.financialcchier9, HR_BASELINE.financialcchier10,
HR_BASELINE.mgmt_spoc, HR_BASELINE.repr_spoc,
HR_BASELINE.position_level, HR_BASELINE.corp_equivalent,
HR_BASELINE.psgroup_grade, HR_BASELINE.pay_type,
HR_BASELINE.years_of_service, HR_BASELINE.LAYER,
HR_BASELINE.fname, HR_BASELINE.lname,
HR_BASELINE.temp_supv_id, HR_BASELINE.supv_name,
HR_BASELINE.pos_desc, HR_BASELINE.cost_center,
HR_BASELINE.cost_center_desc,
HR_BASELINE.monthly_base_salary, HR_BASELINE.salary_months,
HR_BASELINE.email
FROM hruser.HR_BASELINE) "Workforce_Reporting"
GROUP BY "Workforce_Reporting"."COUNTRY",
"Workforce_Reporting"."OFFICE_CITY",
"Workforce_Reporting"."GROUP_NAME",
"Workforce_Reporting"."DIV",
"Workforce_Reporting"."JOB_FAMILY",
"Workforce_Reporting"."JOB_FUNCTION",
"Workforce_Reporting"."JOB_TITLE",
"Workforce_Reporting"."AREA_TOP_LEVEL",
"Workforce_Reporting"."TEMP_ID",
"Workforce_Reporting"."FNAME",
"Workforce_Reporting"."LNAME",
"Workforce_Reporting"."TEMP_SUPV_ID",
"Workforce_Reporting"."SUPV_NAME",
"Workforce_Reporting"."POS_DESC",
"Workforce_Reporting"."EMPLOYEE_CATEGORY",
"Workforce_Reporting"."COST_CENTER",
"Workforce_Reporting"."COST_CENTER_DESC",
"Workforce_Reporting"."EMAIL",
"Workforce_Reporting"."STATUS",
"Workforce_Reporting"."CTIME",
"Workforce_Reporting"."GENERIC_FUNCTION",
"Workforce_Reporting"."AVAYA_TENOVIS",
"Workforce_Reporting"."PERF_INFO",
"Workforce_Reporting"."PAY_TYPE",
"Workforce_Reporting"."PSGROUP_GRADE",
"Workforce_Reporting"."POSITION_LEVEL",
"Workforce_Reporting"."LAST_STATUS_CHANGE",
"Workforce_Reporting"."FINANCIALCCHIER10",
"Workforce_Reporting"."FINANCIALCCHIER9",
"Workforce_Reporting"."FINANCIALCCHIER8",
"Workforce_Reporting"."FINANCIALCCHIER7",
"Workforce_Reporting"."FINANCIALCCHIER6",
"Workforce_Reporting"."FINANCIALCCHIER5",
"Workforce_Reporting"."FINANCIALCCHIER4",
"Workforce_Reporting"."EC",
"Workforce_Reporting"."EC_PLUS1",
"Workforce_Reporting"."EC_PLUS2",
"Workforce_Reporting"."EC_PLUS3",
"Workforce_Reporting"."EC_PLUS4",
"Workforce_Reporting"."EC_PLUS5",
"Workforce_Reporting"."EC_PLUS6",
"Workforce_Reporting"."EC_PLUS7",
"Workforce_Reporting"."AREA_SL"

I'd say it has something to do with your groupby being 43 columns...

Seems all the column values in GROUP BY Clause exceeds DB_BLOCK_SIZE value.

How about Normalizing HR_BASELINE tables, ur using the follwoing columns which are against the rules of normalization..

HTML Code:

Workforce_Reporting"."FINANCIALCCHIER10" "Financial_Hierarchy_10", "Workforce_Reporting"."FINANCIALCCHIER9" "Financial_Hierarchy_9", "Workforce_Reporting"."FINANCIALCCHIER8" "Financial_Hierarchy_8", "Workforce_Reporting"."FINANCIALCCHIER7" "Financial_Hierarchy_7", "Workforce_Reporting"."FINANCIALCCHIER6" "Financial_Hierarchy_6", "Workforce_Reporting"."FINANCIALCCHIER5" "Financial_Hierarchy_5", "Workforce_Reporting"."FINANCIALCCHIER4" "Financial_Hierarchy_4", "Workforce_Reporting"."EC" "EC", "Workforce_Reporting"."EC_PLUS1" "EC_Plus1", "Workforce_Reporting"."EC_PLUS2" "EC_Plus2", "Workforce_Reporting"."EC_PLUS3" "EC_Plus3", "Workforce_Reporting"."EC_PLUS4" "EC_Plus4", "Workforce_Reporting"."EC_PLUS5" "EC_Plus5", "Workforce_Reporting"."EC_PLUS6" "EC_Plus6", "Workforce_Reporting"."EC_PLUS7" "EC_Plus7",

Or use multiple block size's and assign the temporary tablspace to hruser with higest block size available with your operating system.

Hope you can have Multiple block size for temporary tablespaces, if its possible then you can solve the problem by assigning hruser a temporary tablespace with highest possible block size.

The below link confirms that you cant have multiple block sizes for temporary tablespace.

http://www.oracle-base.com/articles/...BlockSizes.php

You have two options either to Normalize the table. Or to change the DB_BLOCK_SIZE to highest possible block size run the query, and bring back the DB_BLOCk_SIZE to its original value.

For the second option u need to create the seperate database

Hi, Thanks for the inputs.

I used only 32 columns in the group by clause. It works. If it exceeds more then 32, it is giving error.

I will take a look at the Normalization part and see how i can assign multiple block size tablespace for this table. Can please confirm that this problem is due to "Column values in the group by clause exceeds DB_BLOCK_SIZE". I just want to confirm this for my understanding... Thank you so much for the info. This site is very helpful. Once again thank you all

Quote:

Originally Posted by pranavgovind

Hi, Thanks for the inputs.

I used only 32 columns in the group by clause. It works. If it exceeds more then 32, it is giving error.

Its a known bug, You can over come by "using no index" clause in MV creation if its more than 32 cols.Try...........

Thank you all. I appreciate every one on this thread. USING NO INDEX resolved this problem. thank you all.

You're hitting this problem because to support fast refresh Oracle will create a multicolumn index on the GROUP BY columns of the MV table, and as the error message says, 32 columns is your limit on that -- I wouldn't categorise it as a bug, but more of an "undocumented implied restriction". :D

If you're intending on using fast refresh then you may still need a supporting index for the MV -- not neccasarily, but most likely. The index that Oracle creates to so this is based on the undocumented Sys_Op_Map_NonNull() function, to avoid problems with trying to join (actually, MERGE) on nullable columns.

Anyway, you might have a combination of columns in the MV that forms a unique key without having to include the entire set of GROUP BY columns, particularly if some of the columns are hierarchical parents of others. If so then you can just create your own index ... something like ...

Code:

create index my_index on my_table ( Sys_Op_Map_NonNull(col1), Sys_Op_Map_NonNull(col2), Sys_Op_Map_NonNull(col4), Sys_Op_Map_NonNull(col5), ... )

You will almost certainly benefit from the index having a compressed structure.

Not a fan of the mixed-case column names, by the way.

Dave, It makes more sense to me now. Let me list out the possible solutions to resove this problem. I just want to have better understanding.

Please let me know if i am wrong.

1. Normalize the table and reduce the number of columns in the
GROUP BY clause.

2. Create a tablespace with highest possible DB_BLOCK_SIZE for this table.

3. Using by "USING NO INDEX" clause.

If there is benefit to your users in including the additional columns then go ahead with them, and use the USING NO INDEX clause to allow you to define your own index using a permitted number of columns.

The GROUP BY limitation is a different matter, though. If you have to use a larger block size to include those additional columns then that might be more hassle than I would be prepared to suffer.

The two restrictions are independent of each other.

Good to know about "using no index" clause.

I did a small test, which is not more relevent now after all the posts but maybe helpful to others in future.

Block size of the database is 8k

Code:

create table test_grp( col1 char(2000), col2 char(2000), col3 char(2000), col4 char(2000), col5 char(2000), COLNUM NUMBER ); / Insert into test_grp values('a','b','c','d','e',1); / 1 SELECT COL1, COL2, COL3, col4, col5, SUM(COLNUM) 2 FROM TEST_GRP 3* GROUP BY COL1, COL2, COL3, col4 ,col5 SQL> / FROM TEST_GRP * ERROR at line 2: ORA-01467: sort key too long 1 SELECT COL1, COL2, COL3, col4, SUM(COLNUM) 2 FROM TEST_GRP 3* GROUP BY COL1, COL2, COL3, col4 It works.

Now I test with using varchar2 column.

Code:

create table test_grpV2( col1 VARchar2(2000), col2 VARchar2(2000), col3 VARchar2(2000), col4 VARchar2(2000), col5 VARchar2(2000), col6 VARchar2(2000), col7 VARchar2(2000), col8 VARchar2(2000), col9 VARchar2(2000), col10 VARchar2(2000), col11 VARchar2(2000), col12 VARchar2(2000), col13 VARchar2(2000), col14 VARchar2(2000), col15 VARchar2(2000), col16 VARchar2(2000), col17 VARchar2(2000), col18 VARchar2(2000), col19 VARchar2(2000), col20 VARchar2(2000), col21 VARchar2(2000), col22 VARchar2(2000), col23 VARchar2(2000), col24 VARchar2(2000), col25 VARchar2(2000), col26 VARchar2(2000), col27 VARchar2(2000), col28 VARchar2(2000), col29 VARchar2(2000), col30 VARchar2(2000), col31 VARchar2(2000), col32 VARchar2(2000), col33 VARchar2(2000), col34 VARchar2(2000), col35 VARchar2(2000), col36 VARchar2(2000), col37 VARchar2(2000), col38 VARchar2(2000), col39 VARchar2(2000), col40 VARchar2(2000), COLNUM NUMBER ); / INSERT INTO TEST_GRPV2 VALUES('A','B','C','D','E','F','G','H','I','J','K','L', 'M','N','O','P','Q','R','S','T', 'A','B','C','D','E','F','G','H','I','J','K','L', 'M','N','O','P','Q','R','S','T',1); / SELECT COL1, COL2, COL3, col4, col5, col6,col7, col8,col9,col10,col11,col12,col13,col14,col15, col16,col17,col18,col19,col20, col21,col22,col23,col24,col25,coL26,coL27, coL28,coL29,col30, COL31, COL32, COL33, coL34, coL35, coL36, col27,col28,col39,col40, SUM(COLNUM) FROM TEST_GRPv2 GROUP BY COL1, COL2, COL3, col4, col5, col6,col7, col8,col9,col10,col11,col12,col13,col14,col15, col16,col17,col18,col19,col20, col21,col22,col23,col24,col25,coL26,coL27, coL28,coL29,col30, COL31, COL32, COL33, coL34, coL35, coL36, col27,col28,col39,col40 /

which works fine.

Here my point its not about the number of columns in group by clause or with materalized view. Oracle creates a Unique Index on group by columns which has a restriction of total length of BLOCK SIZE.

Normalizing advantage

Code:

HR_Financial_Hierarchy ID (HR_BASELINE.PrimaryKey...if any such column for join), FH_ID Number, (to hold 1,2,3,..10) FH_Val Number or Varchar2 (wat ever be) ) HR_EC( ID (HR_BASELINE.PrimaryKey...if any such column for join), EC_ID Number (1..7) EC_Val Number)

then you can query, as below

Code:

SELECT ........ decode(HR_Financial_Hierarchy.FH_ID, 1, FH_Val)FH1, decode(HR_Financial_Hierarchy.FH_ID, 2, FH_Val)FH2, ........ decode(HR_EC.EC_ID, 1, EC_Val) EC1, decode(HR_EC.EC_ID, 2, EC_Val) EC2, decode(HR_EC.EC_ID, 3, EC_Val) EC3, ........ FROM HR_BASELINE, HR_Financial_Hierarchy, HR_EC WHERE HR_BASELINE.ID = HR_Financial_Hierarchy.ID HR_BASELINE.ID = HR_EC.ID ........ GROUP BY ................ HR_Financial_Hierarchy.ID, Financial_Hierarchy_Val, HR_EC.EC_ID, HR_EC.EC_VAL, ...............................

This query will have some performance overhead becoz of joins. But
I think grouping on 10 columns of Financial_Hierarchy_1..10 and &
8 columns of EC1..Ec8 consumes time than grouping 4 columns
HR_Financial_Hierarchy.ID, Financial_Hierarchy_Val,
HR_EC.EC_ID, HR_EC.EC_VAL

And regarding the Block Size, a Single record of HR_BASELINE consumes more than 1 block, that means lot of block overhead with rowchaining. If having a block size which can fit 1 or 2 records in a single block then it will reduce I/O.

If you are accessing 4 rows from HR_BASELINE which means almost
reading 6-8 blocks if u increase the block size you can reduce the
number of blocks need for accessing those same 4 rows.

Hope its a valid point to both normalize and increasing block size of
the tablespace which holds HR_BASELINE table.

Quote:

Originally Posted by tabreaz

This query will have some performance overhead becoz of joins. But I think grouping on 10 columns of Financial_Hierarchy_1..10 and & 8 columns of EC1..Ec8 consumes time than grouping 4 columns HR_Financial_Hierarchy.ID, Financial_Hierarchy_Val,HR_EC.EC_ID, HR_EC.EC_VAL

Whether you want to do this or not depends on the trade-off between time spent on maintaining the MV and time spent on querying the result. If the emphasis is to get results for the end user as fast as possible then it makes sense to include the full hierarchy of values and take the hit on the MV maintenance, especially if the MV is extensively used.

Hi dave,

Quote:

Whether you want to do this or not depends on the trade-off between time spent on maintaining the MV and time spent on querying the result. If the emphasis is to get results for the end user as fast as possible then it makes sense to include the full hierarchy of values and take the hit on the MV maintenance, especially if the MV is extensively used.

I got your point but how about grouping the result set on 18 columns rather than 4 columns + join (with proper index)..may be the cost of both options are equal (what you say)? and through decode statement all required columns can be used.

It's the sort of thing that would be amenable to testing.

My first instinct, coming from a DW background, would be to materialize all the required values myself. There are a great many optimizations that could be applied to the refresh of a summary table like this, and some of them would allow materialization of all values without grouping by them all.

For example:

Code:

with agg_data as (select date_of_month, sum(sales_amt) s_amt from fct_sales group by date_of_month) select ad.date_of_month, d.date_of_quarter, d.date_of_year, ad.s_amt from agg_data ad, dim_date_of_month d where ad.date_of_month = d.date_of_month

You couldn't define your MV like that, but you could certainly use such code as the basis for a faster refresh than the native MV method.

Thanks dave, Its really a nice tip. I know WITH Statement but I never got any idea for using such.

How to improve productivity??????