To register for an Internet.com membership to receive newsletters and white papers, use the Register button ABOVE.
To participate in the message forums BELOW, click here

HOME 11g Central 10g Central 9i Central 8i Central Oracle News Scripts FAQ OCP Zone Resources Database Journal

Go Back   DBAsupport.com Forums > Oracle Forums > Oracle Database Administration
Reload this Page ORA-01793: maximum number of index columns is 32
Register FAQ Members List Calendar Mark Forums Read

Reply Post New Thread
Page 1 of 2 1 2 >
 
Thread Tools Rate Thread Display Modes
  #1  
Old 08-28-2006, 11:06 AM
pranavgovind pranavgovind is offline
Member
  
Join Date: Dec 2005
Posts: 195
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.
Reply With Quote
  #2  
Old 08-28-2006, 11:38 AM
dbasan's Avatar
dbasan dbasan is offline
Advisor
  
Join Date: Mar 2004
Location: DC,USA
Posts: 650
Post your Mview create script.
__________________
"What is past is PROLOGUE"
Reply With Quote
  #3  
Old 08-28-2006, 11:51 AM
pranavgovind pranavgovind is offline
Member
  
Join Date: Dec 2005
Posts: 195
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"
Reply With Quote
  #4  
Old 08-28-2006, 12:27 PM
marist89's Avatar
marist89 marist89 is offline
Super-Genius
  
Join Date: Nov 2000
Location: greenwich.ct.us
Posts: 9,094
I'd say it has something to do with your groupby being 43 columns...
__________________
Jeff Hunter
marist89@yahoo.com
http://marist89.blogspot.com/
Get Firefox!
"I pledge to stop eating sharks fin soup and will not do so under any circumstances."
Reply With Quote
  #5  
Old 08-28-2006, 12:35 PM
tabreaz tabreaz is offline
Senior Member
  
Join Date: Sep 2005
Posts: 277
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.
Reply With Quote
  #6  
Old 08-28-2006, 12:47 PM
tabreaz tabreaz is offline
Senior Member
  
Join Date: Sep 2005
Posts: 277
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 12:50 PM.
Reply With Quote
  #7  
Old 08-28-2006, 12:51 PM
pranavgovind pranavgovind is offline
Member
  
Join Date: Dec 2005
Posts: 195
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
Reply With Quote
  #8  
Old 08-28-2006, 12:59 PM
dbasan's Avatar
dbasan dbasan is offline
Advisor
  
Join Date: Mar 2004
Location: DC,USA
Posts: 650
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...........
__________________
"What is past is PROLOGUE"
Reply With Quote
  #9  
Old 08-28-2006, 02:12 PM
pranavgovind pranavgovind is offline
Member
  
Join Date: Dec 2005
Posts: 195
Thank you all. I appreciate every one on this thread. USING NO INDEX resolved this problem. thank you all.
Reply With Quote
  #10  
Old 08-28-2006, 02:14 PM
slimdave slimdave is offline
Foreign Script Kiddie
  
Join Date: Aug 2002
Location: Colorado Springs
Posts: 5,253
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.
__________________
David Aldridge,
"The Oracle Sponge"

Senior Manager, Business Intelligence Development
XM Satellite Radio
Washington, DC

Oracle ACE
Reply With Quote
Reply Post New Thread
Page 1 of 2 1 2 >

Bookmarks

« Previous Thread | Next Thread »
Thread Tools
Display Modes Rate This Thread
Linear Mode Linear Mode
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Forum Jump


All times are GMT -4. The time now is 12:20 PM.

DBAsupport.com Recent Articles

 » Configuring Oracle as a Data Source for SQL Server

 » When tuning Oracle is not an option

 » Leveraging Logical Standby Databases in Data Guard

 » Building an Oracle Server Environment

 » Oracle 11g Security – Guidelines for Auditing

Search DBAsupport:
 


Click Here to Expand Forum to Full Width








Contact Us - DBAsupport.com Home Page - Archive - Top



Acceptable Use Policy


The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers


Powered by vBulletin® Version 3.7.4
Copyright ©2000 - 2010, Jelsoft Enterprises Ltd.