0 Replies Latest reply: Oct 13, 2013 7:20 PM by d5f30d95-e159-4a8c-be22-08210a84ec73 RSS

    Processing 15 billions in Oracle database daily

    d5f30d95-e159-4a8c-be22-08210a84ec73

      Hi,

       

      Thank you all exprets for helping people with their questions. This is my first question on this forum. I'm

       

      working on building warehouse/datamart for one of my client. I've a below situation. Can someone assist me on my below requirement.

       

      I've a below table FULF_HIST with 15 billion records (which very large table).

       

      CREATE TABLE FULF_HIST
      (
      EXTRACT_DT             DATE NOT NULL ,
      HIST_ID                NUMBER(19) NOT NULL ,
      TT_ID                  NUMBER(10) NOT NULL ,
      CUST_ID                NUMBER(20) NULL
      INSERT_DT              DATE NOT NULL,
      UPDATE_DT              DATE NOT NULL
      )
          PARTITION BY RANGE (EXTRACT_DT)
          INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
          (PARTITION DT_20110101 VALUES LESS THAN (TO_DATE('20110101','YYYYMMDD')))
      ;

      ALTER TABLE FULF_HIST
          ADD CONSTRAINT  FULF_HIST_PK PRIMARY KEY
           (EXTRACT_DT,HIST_ID,TT_ID)  
           USING INDEX FULF_HIST_PK  ENABLE;

       

      CREATE TABLE MAP_CUST

      (CUST_ID NUMBER(20) NOT NULL,

      NEW_CUST_ID NUMBER(20)

      )

      PARTITION BY HASH(CUST_ID) PARTITIONS 64;

       

      Every day we populate FULF_HIST table with approximately 2 MM+ records.

       

      We insert/update MAP_CUST table every day. Mostly CUST_ID and NEW_CUST_ID remains same. Often b

       

      ased on some rules we update CUST_ID to NEW_CUST_ID, in this case CUST_ID and NEW_CUST_ID will be different.

      For example

       

      MAP_CUST table initially have an entry with CUST_ID 123 and NEW_CUST_ID 123. If CUST_ID 123 is updated to 345 then we update NEW_CUST_ID to 345.

       

      Now i need to refresh

       

      table "FCT_FULF_M" every day in datamart. Here is the table structure.

       

      CREATE TABLE FCT_FULF_M

      (

      EXTRACT_DT             DATE NOT NULL ,

      HIST_ID                NUMBER(19) NOT NULL ,

      TT_ID                  NUMBER(10) NOT NULL ,

      CUST_ID                NUMBER(20) NULL

      )

          PARTITION BY RANGE (EXTRACT_DT)

          INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

          (PARTITION DT_20110101 VALUES LESS THAN (TO_DATE('20110101','YYYYMMDD')))

       

      For now i'm

       

      truncating the above table and inserting data every day, i know it takes time.

       

      FULF_HIST table had CUST_ID field. based on above example (which i've given for MAP_CUST). If FULF_HIST table has entry for CUST_123 then i need to update it to 345 in this table "FCT_FULF_M

       

      " as well as the new data inserted into FULF_HIST need to be moved to the table "FCT_FULF_M" (kind of incremental + update CUST_ID to NEW_CUST_ID if there is any entry for CUST_ID in the table).

       

      Is there any better approach you can think of just doing incremental data refresh on the table FCT_FULF_M

       

      (i.e. updating the CUST_ID whatever changed in MAP_CUST and new data inserted into FULF_HIST).

       

      I'm using below SQL to insert data into FCT_FULF_M table

       

      INSERT /*+ APPEND */ INTO FCT_FULF_M

      (EXTRACT_DT, HIST_ID, TT_ID, CUST_ID)

      SELECT  a.extract_dt,

                     a.hist_id,

                     a.tt_id,

                     coalesce(b.new_cust_id, a.cust_id) as cust_id

          FROM FULF_HIST a,

                     MAP_CUST b

        WHERE a.cid = b.cid(+);

       

      Appreciate your help!