DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: ORA-01793: maximum number of index columns is 32

  1. #1
    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.

  2. #2
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Post your Mview create script.
    "What is past is PROLOGUE"

  3. #3
    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"

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I'd say it has something to do with your groupby being 43 columns...
    Jeff Hunter

  5. #5
    Join Date
    Sep 2005
    Posts
    278
    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.

  6. #6
    Join Date
    Sep 2005
    Posts
    278
    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.

  7. #7
    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

  8. #8
    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"

  9. #9
    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.

  10. #10
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width