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

Thread: error in creating materialized view

  1. #1
    Join Date
    Jan 2007
    Posts
    231

    error in creating materialized view

    Hi all,
    I want to create a materialized view,which uses
    Having count(*)>1 it shows error in this line as

    HAVING COUNT(*) > 1
    *
    ERROR at line 18:
    ORA-30353: expression not supported for query rewrite


    thanks in advance.

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Jan 2007
    Posts
    231
    thanks for ur response,

    but that link doesn't helps me..,

  4. #4
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Try to change query_rewrite_enabled to FALSE and then check. Are u using Oracle 10g version? If so this note says it's a bug Note:359826.1
    http://www.perf-engg.com
    A performance engineering forum

  5. #5
    Join Date
    Jan 2007
    Posts
    231
    iam using Oracle9.2
    i tried with setting query_rewrite_enables=false
    now it shows error as

    ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

  6. #6
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Can you post your entire sql. Are the tables being used IOT or normal table.
    http://www.perf-engg.com
    A performance engineering forum

  7. #7
    Join Date
    Mar 2002
    Posts
    534
    As far as I know you can't use a ON COMMIT refersh for a MV containing a GROUP BY

  8. #8
    Join Date
    Jan 2007
    Posts
    231
    here is the code:
    Code:
    create materialized view snack1
      build immediate
      refresh on commit
      enable query rewrite
    as
    SELECT COUNT(*), MENU_MEAL.person_nr , MENU_MEAL.consume_Date,MENU_MEAL.meal_type_code 
    	FROM MENU_MEAL,PERSON p
    		WHERE p.person_nr=MENU_MEAL.person_nr 
    			AND MENU_MEAL.is_prescribed='Y' 
    			AND MENU_MEAL.is_deleted='N' 
    			AND is_modified='N'
    			AND p.status_code IN('ACTV','CHNG')
    			AND MENU_MEAL.meal_type_code = 'S1'
    			AND TRUNC(MENU_MEAL.consume_date) >= TRUNC(SYSDATE)
    			AND MENU_MEAL. person_nr > 0
    			AND p.email not like '%test%'
    	GROUP BY MENU_MEAL.person_nr,consume_date,meal_type_code
    	HAVING COUNT(*) > 1;
    we are using normal tables

  9. #9
    Join Date
    Jan 2007
    Posts
    231
    ok, i removed refresh on commit now
    it works ..
    without query_rewrite_enable true how it will consider optimal query.

    Thanks

  10. #10
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    i think you can now set TRUE for query_rewrite_enable and this should work.
    http://www.perf-engg.com
    A performance engineering forum

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