MTL_ITEM_CAT_SN and DIRLOAD_DML

[Linda]

Hi Erman!
I have interesting issue:
patch 23748901 applied to downgrade MTL_ITEM_CATEGRORIES to NOT BE seed data table and the refresh should go fast.
In patch was run :exec AD_ZD_SEED.DOWNGRADE('MTL_ITEM_CATEGORIES');
Now:
select ad_zd_table.is_seed('INV','MTL_ITEM_CATEGORIES') from dual
AD_ZD_TABLE.IS_SEED('INV','MTL_ITEM_CATEGORIES')
--------------------------------------------------------------------------------
N
Recreated those snapshots
MTL_ITEM_CAT_SN should  NOW be  fast_refreshable='DIRLOAD_DML'
the same  as MTL_DEMAND_SN.
select fast_refreshable from  dba_mviews
where mview_name like 'MTL_ITEM_CAT_SN';
FAST_REFRESHABLE
------------------
NO

SO, DB has desided that MTL_ITEM_CAT_SN is NOT fast_refreshable, the setup of objects  after patch looks like similar  MTL_DEMAND_SN.  
I run that analyze  DBMS_MVIEW.explain_mview  on the the MTL_ITEM_CAT_SN and the aswer is "does not meet the requirements of a primary key mv" LOL :)
Any guesses why RDBMS still think that ?
Any hints ? I think its about still that table MTL_ITEM_CATEGORIES.. but why :)
Thanks alot for reading this..
Br,Linda

[Linda]

Sorry its MTL_ITEM_CATS_SN :)

[ErmanArslansOracleBlog]

Hi Linda,

Another interesting and hard problem from you :) But it is good to deal with these kinds of issues..

I m checking it in my 12.2.4 instance and it is fast_refreshable..

SQL> select fast_refreshable from  dba_mviews where mview_name like '%MTL_ITEM_CATS_SN%';    2
FAST_REFRESHABLE
------------------
DIRLOAD_DML

Note that, Patch 23748901 is not applied to this env, but the MTL_ITEM_CATEGORIES is not a seed table.

SQL> select ad_zd_table.is_seed('INV','MTL_ITEM_CATEGORIES') from dual;
AD_ZD_TABLE.IS_SEED('INV','MTL_ITEM_CATEGORIES')
--------------------------------------------------------------------------------
N


I think it is fixed in EBS 12.2.4.

Anyways;


Can you please execute the check documented in the following support note and send me the "exact output":

How to Use DBMS_MVIEW.EXPLAIN_MVIEW to Check for Fast Refresh Options? (Doc ID 730908.1)

[Linda]

Hi Erman!
Thanks for checking your instance .Yes the hard issues make our DBA life interesting :)

Yep I run that check.. and  its not helping- me at least :(
SQL> exec dbms_mview.explain_mview('APPS.MTL_ITEM_CATS_SN');
PL/SQL procedure successfully completed.
 SELECT capability_name,
           possible,
              substr(msgtxt,1,60) AS msgtxt
         FROM mv_capabilities_table
       WHERE capability_name like '%FAST%'
REFRESH_FAST                   N
REFRESH_FAST_AFTER_INSERT      N does not meet the requirements of a primary key mv
REFRESH_FAST_AFTER_ONETAB_DML  N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML     N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT               N PCT is not possible on any of the detail tables in the mater


Its the setup of the related objects of that MTL_ITEM_CATEGORIES. But what exactly... and the table ofcourse dont have the primary key

Do you have  this table looking like this :
CREATE TABLE MLOG$_MTL_ITEM_CATEGORIES
(
  M_ROW$$          VARCHAR2(255 BYTE),
  SNAPTIME$$       DATE,
  DMLTYPE$$        VARCHAR2(1 BYTE),
  OLD_NEW$$        VARCHAR2(1 BYTE),
  CHANGE_VECTOR$$  RAW(255),
  XID$$            NUMBER
)

CREATE MATERIALIZED VIEW apps.MTL_ITEM_CATS_SN (INVENTORY_ITEM_ID,ORGANIZATION_ID,CATEGORY_SET_ID,CATEGORY_ID,RN)
TABLESPACE APPS_TS_TX_DATA
PCTUSED    0
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOCACHE
NOLOGGING
NOCOMPRESS
PARALLEL ( DEGREE 4 INSTANCES 1 )
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
/* Formatted on 1/10/2017 6:03:20 PM (QP5 v5.256.13226.35538) */
SELECT "A1"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID",
       "A1"."ORGANIZATION_ID" "ORGANIZATION_ID",
       "A1"."CATEGORY_SET_ID" "CATEGORY_SET_ID",
       "A1"."CATEGORY_ID" "CATEGORY_ID",
       0 "RN"
  FROM "INV"."MTL_ITEM_CATEGORIES" "A1";

Thanks,Linda

[ErmanArslansOracleBlog]

Yes I have ->

CREATE TABLE INV.MLOG$_MTL_ITEM_CATEGORIES
(
  M_ROW$$          VARCHAR2(255 BYTE),
  SNAPTIME$$       DATE,
  DMLTYPE$$        VARCHAR2(1 BYTE),
  OLD_NEW$$        VARCHAR2(1 BYTE),
  CHANGE_VECTOR$$  RAW(255),
  XID$$            NUMBER
)
TABLESPACE APPS_TS_TX_DATA
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

Here the MV decription:


CREATE MATERIALIZED VIEW APPS.MTL_ITEM_CATS_SN (INVENTORY_ITEM_ID,ORGANIZATION_ID,CATEGORY_SET_ID,CATEGORY_ID,RN)
TABLESPACE APPS_TS_NOLOGGING
PCTUSED    0
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOCACHE
NOLOGGING
NOCOMPRESS
PARALLEL ( DEGREE 4 INSTANCES 1 )
BUILD IMMEDIATE
USING INDEX
            TABLESPACE APPS_TS_NOLOGGING
            PCTFREE    10
            INITRANS   2
            MAXTRANS   255
            STORAGE    (
                        INITIAL          128K
                        NEXT             128K
                        MINEXTENTS       1
                        MAXEXTENTS       UNLIMITED
                        PCTINCREASE      0
                        BUFFER_POOL      DEFAULT
                        FLASH_CACHE      DEFAULT
                        CELL_FLASH_CACHE DEFAULT
                       )
REFRESH FORCE ON DEMAND
WITH ROWID
AS
/* Formatted on 1/11/2017 3:02:29 PM (QP5 v5.185.11230.41888) */
SELECT "A1"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID",
       "A1"."ORGANIZATION_ID" "ORGANIZATION_ID",
       "A1"."CATEGORY_SET_ID" "CATEGORY_SET_ID",
       "A1"."CATEGORY_ID" "CATEGORY_ID",
       0 "RN"
  FROM "INV"."MTL_ITEM_CATEGORIES" "A1";

[ErmanArslansOracleBlog]

Youı see the MV in my env has "WITH ROWID"

[ErmanArslansOracleBlog]

So the problem is obvious.

The table MTL_ITEM_CATEGORIES has no primary key.
that's why the "Materialized View log" should be created on it is "with rowid", and it is created "with rowid", I checked it on my 12.2.4 system.

So, in this situation the "Materialized view" can be created without specificy the rowid clause, no error will be presented, but in that case Fast refresh will not be possible.

However, if the" Materialized view" is created with rowid caluse, then Fast refresh will be possible.

So, you can recrete APPS.MTL_ITEM_CATS_SN with rowid clause to make it fast refreshable.

[Linda]

Hi Erman!
Yes... Its obvius.  Thanks for sharing your version of the view. It showed HOW it SHOULD BE! :)
But the solution is not that simple as it seems to be.
Next collections can recreate that and I tried it ->recreated AGAIN  with the wrong version( primary key).. Arghh.

We will cleanup  the planning setup and re-setup again  and lets see how that goes :)


Have a nice evening! :)
Linda

[ErmanArslansOracleBlog]

Hmmm....
So, you say, you will Refresh Collection Snapshot program.
That will run Crete INV Snapshots program and it will recreate MTL_ITEM_CATS_SN and snapshot logs on MTL_ITEM_CATEGORIES.
and after that you will check the MTL_ITEMS_CATS_SN and see if it is created with rowid and see if it is fast freshable right?


Ref:

- On the Source instance set the profile MSC: Source setup required to Y
- On the Source instance - Run Refresh Collections Snapshots parameter Mode = Fast
This will launch the Refresh Collections Snapshot program which will in turn launch a series of requests that will check APS objects and recreate them as required.  For example: Create INV Snapshots program which will recreate MTL_ITEM_CATS_SN and the snapshot log (MLOG) on MTL_ITEM_CATEGORIES


Okay.
Keep me updated on this...