2 Replies Latest reply: Feb 27, 2014 12:19 PM by rp0428 RSS

    PCT not possible on MV

    1754709

          Hi,

      using Oracle 11.02

      I've created two tables - partitioned exactly the same way , same columns, partitioning definition . After running DBMS_MVIEW.EXPLAIN_MVIEW:

      begin

        DBMS_MVIEW.EXPLAIN_MVIEW( 'SELECT DATE_CREATED, COUNTRY,EVENT_TYPE_KEY, EVENT_STATUS_KEY, COUNT(*)

        FROM DWH_CENTRAL.C_DIARY_EVENTS_TEST5

        group by DATE_CREATED, COUNTRY,EVENT_TYPE_KEY, EVENT_STATUS_KEY' );

      END;

       

       

      begin

        DBMS_MVIEW.EXPLAIN_MVIEW( 'SELECT DATE_CREATED, COUNTRY,EVENT_TYPE_KEY, EVENT_STATUS_KEY, COUNT(*)

        FROM DWH_CENTRAL.C_DIARY_EVENTS

        group by DATE_CREATED, COUNTRY,EVENT_TYPE_KEY, EVENT_STATUS_KEY' );

      END;

       

      MV_CAPABILITIES_TABLE is showing PCT is possible for top query, but not for second query - with message: no partition key or PMARKER or join dependent expression in select list.

      Both tables use same partitioning - so how come ?

       

      here's deifnition of two tables:

       

      CREATE TABLE C_DIARY_EVENTS_TEST5

      (

        COUNTRY VARCHAR2(2) NOT NULL

      , EVENT_NO NUMBER(9,0) NOT NULL

      , EVENT_TYPE_KEY NUMBER(5,0) NOT NULL

      , EVENT_STATUS_KEY NUMBER(3,0) NOT NULL

      , DATE_CREATED DATE NOT NULL

      , TIME_CREATED NUMBER(6,0) NULL

      )

      PARTITION BY RANGE (DATE_CREATED)

        INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'))

        SUBPARTITION BY LIST(COUNTRY)

           SUBPARTITION TEMPLATE

               ( SUBPARTITION P_AT VALUES ('AT')

                , SUBPARTITION P_BE VALUES ('BE', 'FI', 'SE', 'IE', 'CZ', 'NO')

                , SUBPARTITION P_CH VALUES ('CH', 'DK')

                , SUBPARTITION P_DE VALUES ('DE')

                , SUBPARTITION P_ES VALUES ('ES')

                , SUBPARTITION P_FR VALUES ('FR')

                , SUBPARTITION P_HU VALUES ('HU')

                , SUBPARTITION P_IT VALUES ('IT')

                , SUBPARTITION P_NL VALUES ('GB', 'NL')

                , SUBPARTITION P_PL VALUES ('PL', 'PT')

               )

      (

         PARTITION P_2010 VALUES LESS THAN (TO_DATE('2010-01-01', 'YYYY-MM-DD'))

      , PARTITION P_2011 VALUES LESS THAN (TO_DATE('2011-01-01', 'YYYY-MM-DD'))

      , PARTITION P_2012 VALUES LESS THAN (TO_DATE('2012-01-01', 'YYYY-MM-DD'))

      -- , PARTITION P_2012_02 VALUES LESS THAN (TO_DATE('2012-02-01', 'YYYY-MM-DD'))

      )

      ;

       

       

       

      CREATE TABLE C_DIARY_EVENTS

      (

        COUNTRY VARCHAR2(2 BYTE) NOT NULL

      , EVENT_NO NUMBER(9,0) NOT NULL

      , ACCOUNT_NO NUMBER(8,0) NOT NULL

      , SUBSCRIPTION_NO NUMBER(8,0) NOT NULL

      , ADDRESS_NO NUMBER(8,0) NOT NULL

      , AGREEMENT_NO NUMBER(8,0) NOT NULL

      , INVOICE_NO NUMBER(8,0) NOT NULL

      , CUSTOMER_FXD_KEY NUMBER(9,0) NOT NULL

      , CUSTOMER_CHG_KEY NUMBER(9,0) NOT NULL

      , SUBSCRIPTION_FXD_KEY NUMBER(10,0) NOT NULL

      , SUBSCRIPTION_CHG_KEY NUMBER(10,0) NOT NULL

      , EVENT_TYPE_KEY NUMBER(5,0) NOT NULL

      , EVENT_STATUS_KEY NUMBER(3,0) NOT NULL

      , EVENT_REASON_KEY NUMBER(5,0) NOT NULL

      , EVENT_CONTACT_KEY NUMBER(8,0) NOT NULL

      , DAYS_OPEN NUMBER(5,0) GENERATED ALWAYS AS (NVL(RESOLVED_DATE, TO_DATE('2014-01-01', 'YYYY-MM-DD')) - DATE_CREATED)-- NOT NULL

      , DAYS_OPEN_BIN VARCHAR2(20 BYTE) GENERATED ALWAYS AS (CASE

          WHEN NVL(RESOLVED_DATE, TO_DATE('2014-01-01', 'YYYY-MM-DD')) - DATE_CREATED = 0 THEN 'Raised today'

          WHEN NVL(RESOLVED_DATE, TO_DATE('2014-01-01', 'YYYY-MM-DD')) - DATE_CREATED < 3 then '1-2 days'

          WHEN NVL(RESOLVED_DATE, TO_DATE('2014-01-01', 'YYYY-MM-DD')) - DATE_CREATED < 6 THEN '3-5 days'

          WHEN NVL(RESOLVED_DATE, TO_DATE('2014-01-01', 'YYYY-MM-DD')) - DATE_CREATED < 11 then '6-10 days'

          WHEN NVL(RESOLVED_DATE, TO_DATE('2014-01-01', 'YYYY-MM-DD')) - DATE_CREATED < 21 then '11-20 days'

          WHEN NVL(RESOLVED_DATE, TO_DATE('2014-01-01', 'YYYY-MM-DD')) - DATE_CREATED < 31 then '21-30 days'

          WHEN NVL(RESOLVED_DATE, TO_DATE('2014-01-01', 'YYYY-MM-DD')) - DATE_CREATED < 51 then '31-50 days'

          WHEN NVL(RESOLVED_DATE, TO_DATE('2014-01-01', 'YYYY-MM-DD')) - DATE_CREATED < 101 then '51-100 days'

          ELSE 'Over 100 days' END)

      , CURRENT_DISE_USER_KEY NUMBER(5,0) NOT NULL

      , RESOLVED_BY_DISE_USER_KEY NUMBER(5,0) NOT NULL

      , CREATED_BY_DISE_USER_KEY NUMBER(5,0) NOT NULL

      , CHANGED_BY_DISE_USER_KEY NUMBER(5,0) NOT NULL

      , DATE_CREATED DATE NOT NULL

      , TIME_CREATED NUMBER(6,0) NOT NULL

      , DATE_CHANGED DATE

      , TIME_CHANGED NUMBER(6,0)

      , RESOLVED_DATE DATE

      , RESOLUTION_REQ_BY_DATE DATE

      , PROCESSED_DATE DATE

      , INVOICE_TAX_DATE DATE

      , OVERRIDE_ACTION_DATE DATE

      , TOTAL_SL_ADJUSTMENTS NUMBER(11, 2)

      )

      PARTITION BY RANGE (DATE_CREATED)

        INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'))

        SUBPARTITION BY LIST(COUNTRY)

           SUBPARTITION TEMPLATE

               ( SUBPARTITION P_AT VALUES ('AT')

                , SUBPARTITION P_BE VALUES ('BE', 'FI', 'SE', 'IE', 'CZ', 'NO')

                , SUBPARTITION P_CH VALUES ('CH', 'DK')

                , SUBPARTITION P_DE VALUES ('DE')

                , SUBPARTITION P_ES VALUES ('ES')

                , SUBPARTITION P_FR VALUES ('FR')

                , SUBPARTITION P_HU VALUES ('HU')

                , SUBPARTITION P_IT VALUES ('IT')

                , SUBPARTITION P_NL VALUES ('GB', 'NL')

                , SUBPARTITION P_PL VALUES ('PL', 'PT')

               )

      (

         PARTITION P_2010 VALUES LESS THAN (TO_DATE('2010-01-01', 'YYYY-MM-DD'))

      , PARTITION P_2011 VALUES LESS THAN (TO_DATE('2011-01-01', 'YYYY-MM-DD'))

      , PARTITION P_2012 VALUES LESS THAN (TO_DATE('2012-01-01', 'YYYY-MM-DD'))

      )

      ;

       

      what's the reason for one query being eligible for PCT , and second not ?

       

      Pawel

        • 1. Re: PCT not possible on MV
          1754709

          I've checked out that if you remove virtual columns from the table - then PCT is enabled. I thought it might have to do with those virtual columns - both based on partitioned column - are not part of  MV definition - so I added them to MV definition - but then again PCT is not possible - so I don't understand why - any thoughts ?

           

          Pawel

          • 2. Re: PCT not possible on MV
            rp0428
            I've checked out that if you remove virtual columns from the table - then PCT is enabled. I thought it might have to do with those virtual columns - both based on partitioned column - are not part of  MV definition - so I added them to MV definition - but then again PCT is not possible - so I don't understand why - any thoughts ?

            It appears to be due to the virtual columns and have NOTHING to do with their use of the partitioning key.

             

            This simple table is also not PCT enabled:

            CREATE TABLE test_pct
            (EVENT_NO NUMBER(9,0) NOT NULL
            , DAYS_OPEN NUMBER(5,0) GENERATED ALWAYS AS (event_no + 10000)
            , DATE_CREATED DATE NOT NULL
            )
            PARTITION BY RANGE (DATE_CREATED)
              INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'))
            (
               PARTITION P_2010 VALUES LESS THAN (TO_DATE('2010-01-01', 'YYYY-MM-DD'))
            , PARTITION P_2011 VALUES LESS THAN (TO_DATE('2011-01-01', 'YYYY-MM-DD'))
            , PARTITION P_2012 VALUES LESS THAN (TO_DATE('2012-01-01', 'YYYY-MM-DD'))
            )

            You might want to search MOS to see if there are any notes about PCT and virtual columns.