-
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.
"What is past is PROLOGUE"
-
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...
Jeff Hunter
-
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
Last edited by tabreaz; 08-28-2006 at 11:50 AM.
-
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
-
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...........
"What is past is PROLOGUE"
-
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".
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|