Oracle Retail Knowledge Base

DMF Index.sql

2 min read

Source: DMF/PuC/AIF/DMF Index.sql

-- ITEM
CREATE INDEX sap_slt_prod_pv1.PV1_MARA_IDX1 ON sap_slt_prod_pv1.mara ('MATNR');
CREATE INDEX sap_slt_prod_pv1.PV1_MARA_IDX2 ON sap_slt_prod_pv1.mara ('MANDT','MATNR');
CREATE INDEX sap_slt_prod_pv1.PV1_MARA_IDX3 ON sap_slt_prod_pv1.mara ('MANDT','MATNR','ZZWGR_WAWI');
CREATE INDEX sap_slt_prod_pv1.PV1_MARA_IDX4 ON sap_slt_prod_pv1.mara ('MATNR', 'MTART');
CREATE INDEX sap_slt_prod_pv1.PV1_MARA_IDX5 ON sap_slt_prod_pv1.mara ('SATNR');
 
 
CREATE INDEX sap_slt_prod_pr1.PR1_MARA_IDX1 ON sap_slt_prod_pr1.mara ('MATNR');
CREATE INDEX sap_slt_prod_pr1.PR1_MARA_IDX2 ON sap_slt_prod_pr1.mara ('MANDT','MATNR');
CREATE INDEX sap_slt_prod_pr1.PR1_MARA_IDX3 ON sap_slt_prod_pr1.mara ('MANDT','MATNR','ZZWGR_WAWI');
CREATE INDEX sap_slt_prod_pr1.PR1_MARA_IDX4 ON sap_slt_prod_pr1.mara ('MATNR', 'MTART');
 
-- SAP CONDITION COST
 
CREATE INDEX SAP_SLT_PROD_PV1.PV1_A018_IDX1 ON SAP_SLT_PROD_PV1.A018 ('MATNR','KSCHL','LIFNR','DATBI','EKORG');
CREATE INDEX SAP_SLT_PROD_PR1.PR1_A018_IDX1 ON SAP_SLT_PROD_PR1.A018 ('MATNR','KSCHL','LIFNR','DATBI','EKORG');
 
CREATE INDEX SAP_SLT_PROD_PV1.PV1_KONP_IDX1 ON SAP_SLT_PROD_PV1.KONP ('MATNR','KNUMH','MANDT');
CREATE INDEX SAP_SLT_PROD_PR1.PR1_KONP_IDX1 ON SAP_SLT_PROD_PR1.KONP ('MATNR','KNUMH','MANDT');
 
-- SUPPLIER INVOICES
 
CREATE INDEX SAP_SLT_PROD_PV1.PV1_RBKP_IDX1 ON SAP_SLT_PROD_PV1.RBKP ('BELNR','GJAHR');
CREATE INDEX SAP_SLT_PROD_PR1.PR1_RBKP_IDX1 ON SAP_SLT_PROD_PR1.RBKP ('BELNR','GJAHR');
 
CREATE INDEX SAP_SLT_PROD_PV1.PV1_RSEG_IDX1 ON SAP_SLT_PROD_PV1.RSEG ('BELNR','GJAHR');
CREATE INDEX SAP_SLT_PROD_PR1.PR1_RSEG_IDX1 ON SAP_SLT_PROD_PR1.RSEG ('BELNR','GJAHR');
 
-- ORDERS
CREATE INDEX SAP_SLT_PROD_PV1.PV1_EKKO_IDX1 ON SAP_SLT_PROD_PV1.EKKO ('EBELN','BSART','BEDAT');
CREATE INDEX SAP_SLT_PROD_PR1.PR1_EKKO_IDX1 ON SAP_SLT_PROD_PR1.EKKO ('EBELN','BSART','BEDAT');  
 
CREATE INDEX SAP_SLT_PROD_PV1.PV1_EKKO_IDX2 ON SAP_SLT_PROD_PV1.EKKO ('EBELN','LIFNR','BEDAT');
CREATE INDEX SAP_SLT_PROD_PR1.PR1_EKKO_IDX2 ON SAP_SLT_PROD_PR1.EKKO ('EBELN','LIFNR','BEDAT'); 
 
CREATE INDEX SAP_SLT_PROD_PV1.PV1_EKPO_IDX1 ON SAP_SLT_PROD_PV1.EKPO ('EBELN','UEBPO');
CREATE INDEX SAP_SLT_PROD_PR1.PR1_EKPO_IDX1 ON SAP_SLT_PROD_PR1.EKPO ('EBELN','UEBPO');
 
CREATE INDEX SAP_SLT_PROD_PV1.PV1_EKPO_IDX2 ON SAP_SLT_PROD_PV1.EKPO ('EBELN','UEBPO','EBELP');
CREATE INDEX SAP_SLT_PROD_PR1.PR1_EKPO_IDX2 ON SAP_SLT_PROD_PR1.EKPO ('EBELN','UEBPO','EBELP');
 
CREATE INDEX SAP_SLT_PROD_PV1.PV1_EKPO_IDX3 ON SAP_SLT_PROD_PV1.EKPO ('EBELN','UEBPO','LOEKZ');
CREATE INDEX SAP_SLT_PROD_PR1.PR1_EKPO_IDX3 ON SAP_SLT_PROD_PR1.EKPO ('EBELN','UEBPO','LOEKZ');
 
CREATE INDEX SAP_SLT_PROD_PV1.PV1_EKET_IDX1 ON SAP_SLT_PROD_PV1.EKET ('EBELN','EBELP');
CREATE INDEX SAP_SLT_PROD_PR1.PR1_EKET_IDX1 ON SAP_SLT_PROD_PR1.EKET ('EBELN','EBELP');
 
-- STOCK
 
CREATE INDEX SAP_SLT_PROD_PV1.PV1_MBEW_IDX1 ON SAP_SLT_PROD_PV1.MBEW ('MATNR','BWKEY') NOLOGGING;
CREATE INDEX SAP_SLT_PROD_PR1.PR1_MBEW_IDX1 ON SAP_SLT_PROD_PR1.MBEW ('MATNR','BWKEY') NOLOGGING;
 
CREATE INDEX SAP_SLT_PROD_PV1.PV1_MSEG_IDX2 ON SAP_SLT_PROD_PV1.MSEG ('MATNR','BUDAT_MKPF') NOLOGGING;
CREATE INDEX SAP_SLT_PROD_PR1.PR1_MSEG_IDX2 ON SAP_SLT_PROD_PR1.MSEG ('MATNR','BUDAT_MKPF') NOLOGGING;     
 
CREATE INDEX SAP_SLT_PROD_PV1.PV1_MARD_IDX1 ON SAP_SLT_PROD_PV1.MARD ('MATNR','WERKS') NOLOGGING;
CREATE INDEX SAP_SLT_PROD_PR1.PR1_MARD_IDX1 ON SAP_SLT_PROD_PR1.MARD ('MATNR','WERKS') NOLOGGING;     
 
---- 
 
CREATE INDEX sap_slt_prod_pr1.ZVGH_MAT_DISPO_IDX1 ON sap_slt_prod_pr1.ZVGH_MAT_DISPO ('MATNR');
 
 
-------------
 
DMF
 
CREATE INDEX DMF_INIT_PRICE_IDX1 ON INIT_PRICE_RAW ('ITEM','STORE');
CREATE INDEX DMF_INIT_PRICE_IDX2 ON INIT_PRICE_RAW ('ITEM','STORE','START_DATE');
 
CREATE INDEX DMF_PRICE_CLEARANCE_IDX1 ON ORI_PRICE_CLEARANCE_RAW ('ITEM','STORE');
CREATE INDEX DMF_PRICE_CLEARANCE_IDX2 ON ORI_PRICE_CLEARANCE_RAW ('ITEM','STORE','START_DATE');
 
CREATE INDEX DMF_INIT_PRICE_HIST_IDX1 ON PRICE_HISTORY_RAW ('ITEM','STORE');
CREATE INDEX DMF_INIT_PRICE_HIST_IDX2 ON PRICE_HISTORY_RAW ('ITEM','STORE','START_DATE');
 
CREATE INDEX DMF_XREF_LOCATION_IDX1 ON XREF_LOCATION ('SAP_ID','ORACLE_TYPE');
 
CREATE INDEX DMF_ITEM_SUPP_COUNTRY_CTRL_IDX1 ON ITEM_SUPP_COUNTRY_CTRL ('ITEM','PRIMARY_SUPP_IND','PRIMARY_COUNTRY_ID');
 
CREATE INDEX DMF_XREF_ITEM_IBC_RETAIL_IDX1 ON XREF_ITEM_IBC_RETAIL ('ITEM_PR1','ITEM_PV1');
 
CREATE INDEX ITEM_LOC_SOH_IDX1 ON ITEM_LOC_SOH_CTRL ('CTRL_STATUS');

Graph View