1 Reply Latest reply on Dec 13, 2004 9:36 PM by 358102

    ORA-30353 and Materialized View with rewrite

    153933
      Greetings,

      We have a number of fact tables that maintain a history of changes by using an END_EFFECTIVE_DATE column. When a fact changes we don't delete the old record, but set the end effective date to SYSDATE, and insert a new record with an end effective date of '31-DEC-4444'.

      I'd like to create an MV that only contains the currently effective records (ie END_EFFECTIVE_DATE='31-DEC-4444').

      When I try to do this with QUERY REWRITE enabled, it gives me
      "ORA-30353 expression not supported for query rewrite"

      But as far as I can tell, I'm not performing any forbidden operations. What gives? Is there a way around this? I've tried adding an explicit 'TO_DATE', but that didn't help.

      CREATE MATERIALIZED VIEW mv_ticket_fact
      BUILD IMMEDIATE
      REFRESH COMPLETE
      ENABLE QUERY REWRITE
      AS
      SELECT
      TICKET_FACT.TF_TICKET_ID,
      TICKET_FACT.TF_CLOSE_DATE,
      TICKET_FACT.TF_CREATE_DATE,
      NVL(TICKET_FACT.TF_X_NW_SYM_TYPE,'NOT Applicable'),
      NVL(TICKET_FACT.TF_X_NTSG_CUST_TKT_TYPE,'NOT Applicable')
      FROM
      TICKET_FACT
      WHERE
      ( TICKET_FACT.TF_END_EFFECTIVE_DATE_TRUNC='31-DEC-4444' )
        • 1. Re: ORA-30353 and Materialized View with rewrite
          358102
          This example may be of help.
          SQL> CREATE TABLE ticket_fact(
            2  tf_ticket_id number,
            3  tf_close_date date,
            4  tf_create_date date,
            5  tf_x_nw_sym_type varchar2(30),
            6  tf_x_ntsg_cust_tkt_type varchar2(30),
            7  tf_end_effective_date_trunc date);
          
          Table created.
          
          SQL> CREATE MATERIALIZED VIEW mv_ticket_fact
            2  BUILD IMMEDIATE
            3  REFRESH COMPLETE
            4  ENABLE QUERY REWRITE
            5  AS
            6  SELECT
            7  TICKET_FACT.TF_TICKET_ID,
            8  TICKET_FACT.TF_CLOSE_DATE,
            9  TICKET_FACT.TF_CREATE_DATE,
           10  NVL(TICKET_FACT.TF_X_NW_SYM_TYPE,'NOT Applicable'),
           11  NVL(TICKET_FACT.TF_X_NTSG_CUST_TKT_TYPE,'NOT Applicable')
           12  FROM
           13  TICKET_FACT
           14  WHERE
           15  ( TICKET_FACT.TF_END_EFFECTIVE_DATE_TRUNC='31-DEC-4444' )
           16  ;
          ( TICKET_FACT.TF_END_EFFECTIVE_DATE_TRUNC='31-DEC-4444' )
                                                    *
          ERROR at line 15:
          ORA-30353: expression not supported for query rewrite
          
          
          SQL> ed
          Wrote file afiedt.buf
          
            1  CREATE MATERIALIZED VIEW mv_ticket_fact
            2  BUILD IMMEDIATE
            3  REFRESH COMPLETE
            4  ENABLE QUERY REWRITE
            5  AS
            6  SELECT
            7  TICKET_FACT.TF_TICKET_ID,
            8  TICKET_FACT.TF_CLOSE_DATE,
            9  TICKET_FACT.TF_CREATE_DATE,
           10  NVL(TICKET_FACT.TF_X_NW_SYM_TYPE,'NOT Applicable'),
           11  NVL(TICKET_FACT.TF_X_NTSG_CUST_TKT_TYPE,'NOT Applicable')
           12  FROM
           13  TICKET_FACT
           14  WHERE
           15* ( TICKET_FACT.TF_END_EFFECTIVE_DATE_TRUNC=TO_DATE('31-DEC-4444','DD-MON-YYYY') )
           16  /
          TICKET_FACT
          *
          ERROR at line 13:
          ORA-12014: table 'TICKET_FACT' does not contain a primary key constraint
          
          
          SQL> ALTER TABLE TICKET_FACT ADD CONSTRAINT TF_PK PRIMARY KEY(TF_TICKET_ID);
          
          Table altered.
          
          SQL>  CREATE MATERIALIZED VIEW mv_ticket_fact
            2   BUILD IMMEDIATE
            3   REFRESH COMPLETE
            4   ENABLE QUERY REWRITE
            5   AS
            6   SELECT
            7   TICKET_FACT.TF_TICKET_ID,
            8   TICKET_FACT.TF_CLOSE_DATE,
            9   TICKET_FACT.TF_CREATE_DATE,
           10   NVL(TICKET_FACT.TF_X_NW_SYM_TYPE,'NOT Applicable'),
           11   NVL(TICKET_FACT.TF_X_NTSG_CUST_TKT_TYPE,'NOT Applicable')
           12   FROM
           13   TICKET_FACT
           14   WHERE
           15   ( TICKET_FACT.TF_END_EFFECTIVE_DATE_TRUNC=TO_DATE('31-DEC-4444','DD-MON-YYYY') );
          
          Materialized view created.
          
          SQL> DISCONNECT
          Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
          With the Partitioning option
          JServer Release 9.2.0.3.0 - Production