incremental update scripts

[Roshan]

Oracle DB 12.1.0.2
Solaris 11.4

Hello Erman,

On our BI platform - on which Oracle is running, we have some scripts which will update some custom tables. Can you please advise how we can update the table by taking only the incremental changes from the base tables and applying them.

By dropping and creating those tables (as shown below), it makes the database slow,especially for scripts having complex joins.

Can you please advise on a solution for that?

create table  BIREPORT.TB_MT_TT_PEND_HIST_NEW tablespace CBSID  nologging as
SELECT DISTINCT "TT_TICKETINFO"."DOCKET_NUMBER_N" AS "TICKET_NO",
  "TT_TICKETINFO"."TICKET_REGN_DT_D" AS "TICKET_REGN_DT_D",
  "TT_TICKETINFO"."ENTITY_TYPE_V" AS "ENTITY_TYPE_V",
  TO_CHAR("TT_TICKETINFO"."ENTITY_ID_N") AS "ENTITY_ID_N",
  "TT_TICKETINFO"."STATUS_V" AS "TICKET_STATUS",
  "TT_TICKETINFO"."RESPONSE_MEDIUM_V" AS "RESPONSE_MEDIUM_V",
  "TT_TICKETINFO"."RESP_MEDIUM_ADDR_V" AS "RESP_MEDIUM_ADDR_V",
  "TT_TICKETINFO"."TICKET_TYPE_V" AS "TICKET_TYPE_V",
  "TT_TICKETINFO"."REF_DOCKET_NUMBER_V" AS "REF_DOCKET_NUMBER_V",
  "TT_TICKETINFO"."LOGGED_BY_N" AS "LOGGED_BY_N",
  "TT_TICKETINFO"."TT_SOURCE_V" AS "TT_SOURCE_V",
  "TT_TICKETINFO"."RESPONDED_BY_N" AS "RESPONDED_BY_N",
  "TT_TICKETINFO"."TICKET_RESP_DT_D" AS "TICKET_RESP_DT_D",
  "TT_TICKETINFO"."LOCATION_V" AS "LOCATION_V",
  "TT_TICKETINFO"."AREA_V" AS "AREA_V",
  "TT_TICKETINFO"."REOPEN_COUNT_N" AS "REOPEN_COUNT_N",
  "TT_TICKETINFO"."QUESTION_V" AS "QUESTION_V",
  "TT_TICKETINFO"."ANSWER_V" AS "ANSWER_V",
  "TT_TICKETINFO"."REMEDY_REF_NO_V" AS "REMEDY_REF_NO_V",
  "TT_TICKETINFO"."FILE_NAME_V" AS "FILE_NAME_V",
  "TT_TICKETINFO"."SEGMENT_V" AS "SEGMENT_V",
  "TT_TICKETINFO"."CHANNEL_V" AS "CHANNEL_V",
  "TT_TICKETINFO"."RELEVE" AS "RELEVE",
  "TT_TICKETINFO"."CAUSE" AS "CAUSE",
  "TT_TICKETINFO"."COMMENTS" AS "COMMENTS",
  "TT_TICKETINFO"."REASON" AS "REASON",
  "COMPLAINT TYPE"."COMPLAINT_TYPE" AS "COMPLAINT_TYPE",
  "COMPLAINT TYPE"."SUBCOMPLAINT_TYPE" AS "SUBCOMPLAINT_TYPE",
  "COMPLAINT TYPE"."CATEGORY_DESC" AS "CATEGORY_DESC",
  "COMPLAINT TYPE"."SERVICE_NAME" AS "SERVICE_NAME",
  "WORKFLOW"."EVENT_TYPE_N" AS "WF_EVENT_TYPE_N",
  "WORKFLOW"."EVENT_REFNO_V" AS "WF_EVENT_REFNO_V",
  "WORKFLOW"."WRKFLW_ID_N" AS "WRKFLW_ID_N",
  "WORKFLOW"."START_DT_D" AS "WF_START",
  "WORKFLOW"."END_DT_D" AS "WF_END",
  "WORKFLOW"."STATUS_V" AS "WF_STATUS",
  "WORKFLOW"."CREATED_BY_N" AS "WF_CREATED_BY_N",
  "WORKFLOW"."TERMINATED_BY_N" AS "WF_TERMINATED_BY_N",
  "TODOITEM/HISTORY"."WORK_ID_N" AS "TD_WORK_ID_N",
  "TODOITEM/HISTORY"."STATUS_V" AS "TD_STATUS_V",
  "TODOITEM/HISTORY"."USER_ID_N" AS "TD_USER_ID_N",
  "TODOITEM/HISTORY"."USER_ID_N" AS "TD_USER_ID_N_1",
  "TODOITEM/HISTORY"."ACTIVITY_NAME_V" AS "TD_ACTIVITY_NAME_V",
  "TODOITEM/HISTORY"."WORK_COMPLETION_DT_D" AS "TD_WORK_COMPLETION_DT_D",
  "TODOITEM/HISTORY"."ASSIGNMENT_DT_D" AS "TD_ASSIGNMENT_DT_D",
  "TODOITEM/HISTORY"."EXPIRY_TIME_D" AS "TD_EXPIRY_TIME_D",
  "Customer Service Details"."ACCOUNT_LINK_CODE_N" AS "ACCOUNT_LINK_CODE_N",
  "Customer Service Details"."SUB_SERVICE_CODE_V" AS "SUB_SERVICE_CODE_V",
  "Customer Service Details"."SERVICE_INFO_V" AS "SERVICE_INFO_V",
  "Customer Service Details"."ACCOUNT_CODE_N" AS "ACCOUNT_CODE_N",
  "Customer Service Details"."CUSTOMER_NAME" AS "CUSTOMER_NAME",
  "Customer Service Details"."SUBSCRIBER_CATEGORY_V" AS "SUBSCRIBER_CATEGORY_V",
  "Customer Service Details"."SUBSCRIBER_SUB_CATEGORY_V" AS "SUBSCRIBER_SUB_CATEGORY_V",
  "Customer Service Details"."SERVICE_CODE_V" AS "SERVICE_CODE_V",
  "Customer Service Details"."FDP" AS "FDP",
  "Customer Service Details"."LOC_ZONE" AS "LOC_ZONE",
  "Customer Service Details"."CABINET" AS "CABINET",
  "Customer Service Details"."PACKAGE" AS "PACKAGE",
  "Customer Service Details"."FROM_DATE_D" AS "ACTIVATION_DATE",
  "TICKET INCIDENT MOBI"."NOTES_V" AS "NOTES_V",
  "TICKET INCIDENT MOBI"."INCIDENT_TIMESTAMP_D" AS "INCIDENT_TIMESTAMP_D",
  "SUB SERVICE"."SUBSIDIARY_CODE_V" AS "SUBSIDIARY_CODE_V",
  "SUB SERVICE"."SUB_SERVICE_DESC_V" AS "SUB_SERVICE_DESC_V",
  "SUB SERVICE"."IDENTIFIED_BY_V" AS "IDENTIFIED_BY_V",
  "SUB SERVICE"."START_DATE_D" AS "START_DATE_D",
  "INSTALLATION ADDRESS"."INS_ADDRESS" AS "INS_ADDRESS",
  "INSTALLATION ADDRESS"."ADDRESS_TYPE_N" AS "ADDRESS_TYPE_N",
  "AREA DETAILS"."AREA" AS "AREA",
  "AREA DETAILS"."EXCHANGE" AS "EXCHANGE",
  "AREA DETAILS"."MDF" AS "MDF",
  "AREA DETAILS"."AREA_ENGINEER" AS "AREA_ENGINEER",
  "AREA DETAILS"."AREA_ENGINEER_CONN" AS "AREA_ENGINEER_CONN"
FROM "CBSOGG"."TT_TICKETINFO" "TT_TICKETINFO"
  INNER JOIN (
  select tt.docket_number_n,
  (SELECT A3.category_description_v
                   FROM   tt_category_master A3
                   WHERE  A3.category_code_v = (SELECT A2.parent_category_code_v
                                                FROM
                          tt_category_master A2
                                                WHERE  A2.category_code_v =
                                                       (SELECT t1.subcategory_id_v
                                                        FROM
                                                       tt_ticketinfo t1
                                                                             WHERE
                          t1.docket_number_n = tt.docket_number_n))
                          AND A3.parent_category_code_v <> '0')
                  Complaint_type,
                  (SELECT DISTINCT A1.category_description_v
                   FROM   tt_category_master A1
                   WHERE  A1.category_code_v = tt.subcategory_id_v)
                  SubComplaint_type,
                  (SELECT DISTINCT cm.category_description_v
                   FROM   tt_category_master cm
                   WHERE  cm.category_code_v = tt.category_id_v
                          AND cm.category_type_v = tt.ticket_type_v
                          AND cm.parent_category_code_v = '0')
                  Category_desc,
                 (SELECT DISTINCT cm.Service_code_v
                   FROM   tt_category_master cm
                   WHERE  cm.category_code_v = tt.category_id_v
                          )
                  SERVICE_NAME
  from tt_ticketinfo tt
) "COMPLAINT TYPE" ON ("TT_TICKETINFO"."DOCKET_NUMBER_N" = "COMPLAINT TYPE"."DOCKET_NUMBER_N")
  LEFT JOIN (
  select * from wf_workflow
) "WORKFLOW" ON ("TT_TICKETINFO"."DOCKET_NUMBER_N" = "WORKFLOW"."EVENT_REFNO_V")
  LEFT JOIN (
  SELECT WORK_ID_N,STATUS_V,EVENT_REFNO_V,USER_ID_N,ACTIVITY_NAME_V,WORK_COMPLETION_DT_D,ASSIGNMENT_DT_D,EXPIRY_TIME_D FROM WF_TODOITEM
  WHERE ACTIVITY_NAME_V IS NOT NULL
  UNION
  SELECT WORK_ID_N,STATUS_V,EVENT_REFNO_V,USER_ID_N,ACTIVITY_NAME_V,WORK_COMPLETION_DT_D,ASSIGNMENT_DT_D,EXPIRY_TIME_D FROM WF_TODOITEM_HISTORY
  WHERE ACTIVITY_NAME_V IS NOT NULL
  ORDER BY EVENT_REFNO_V
) "TODOITEM/HISTORY" ON ("WORKFLOW"."EVENT_REFNO_V" = "TODOITEM/HISTORY"."EVENT_REFNO_V")
  LEFT JOIN (
  SELECT distinct a.ACCOUNT_LINK_CODE_N, a.SUB_SERVICE_CODE_V, a.SERVICE_INFO_V,
  a.ACCOUNT_CODE_N, a.CUSTOMER_NAME, a.SUBSCRIBER_CATEGORY_V,
  a.SUBSCRIBER_SUB_CATEGORY_V, a.SERVICE_CODE_V, a.FDP, a.LOC_ZONE, a.CABINET, a.PACKAGE,
  b.from_date_d
  FROM TB_MT_TT_SERV_DET a, cb_account_service_list b
  where a.service_info_v = b.service_info_v
  and b.from_date_d = (select max(from_date_d) from cb_account_service_list asl where b.service_info_v = asl.service_info_v)
) "Customer Service Details" ON (to_char("TT_TICKETINFO"."ENTITY_ID_N") = "Customer Service Details"."SERVICE_INFO_V")
  LEFT JOIN (
  SELECT *
  FROM TB_TT_INC_MOBI a
  where
  a.INCIDENT_TIMESTAMP_D = (select max(INCIDENT_TIMESTAMP_D)
  from TB_TT_INC_MOBI b
  where a.DOCKET_NUMBER_N = b.DOCKET_NUMBER_N)
) "TICKET INCIDENT MOBI" ON ("TT_TICKETINFO"."DOCKET_NUMBER_N" = "TICKET INCIDENT MOBI"."DOCKET_NUMBER_N")
  LEFT JOIN (
  select * from cbsogg.cb_sub_services
) "SUB SERVICE" ON (("Customer Service Details"."SERVICE_CODE_V" = "SUB SERVICE"."SERVICE_CODE_V") AND ("Customer Service Details"."SUB_SERVICE_CODE_V" = "SUB SERVICE"."SUB_SERVICE_CODE_V"))
  LEFT JOIN (
  select EXTRACTVALUE(CAX.address_x,'//@STREET_DESC')||' '||EXTRACTVALUE(CAX.address_x,'//@CITY_DESC')||' '||EXTRACTVALUE(CAX.address_x,'//@SUB_LOCALITY_DESC')
  INS_ADDRESS,
  CAX.account_link_code_n ,CAX.ADDRESS_TYPE_N
  from cbsogg.CB_ADDRESS_XML CAX
  where CAX.ADDRESS_TYPE_N ='4'
) "INSTALLATION ADDRESS" ON ("Customer Service Details"."ACCOUNT_LINK_CODE_N" = "INSTALLATION ADDRESS"."ACCOUNT_LINK_CODE_N")
  LEFT JOIN (
  select * from area_details
) "AREA DETAILS" ON ("Customer Service Details"."LOC_ZONE" = "AREA DETAILS"."MDF")
;

Normally when we replicate base tables using Striim, we add an op_update_date field which will hold the last updated date of that table. Then on Zeplin we create in incremental cube which will update the script if the op_update_date is greater than the current time.

For example:

       ,now() op_update_date
   FROM oracle_financial.FND_FLEX_VALUES_TL FLEX_TL, oracle_financial.FND_FLEX_VALUES FLEX
   WHERE FLEX_TL.FLEX_VALUE_ID = FLEX.FLEX_VALUE_ID
   AND LANGUAGE = 'US'
   AND FLEX_VALUE_SET_ID =1015691
   AND (FLEX_TL.op_update_date>date_sub(to_timestamp('${last_successful_execution}', 'yyyy-MM-dd HH:mm:ss.SSS'), interval 2 minutes) or
   FLEX.op_update_date>date_sub(to_timestamp('${last_successful_execution}', 'yyyy-MM-dd HH:mm:ss.SSS'), interval 2 minutes))

How can we achieve same using Oracle if the base tables are replicated from Oracle to Oracle without op_update_date?

Regards,
Roshan

[Roshan]

How can I add the op_update_date using goldengate parameter for each base tables?
reploggt.prm

[ErmanArslansOracleBlog]

I just couldn't get your question perfectly Roshan.

What do you need to do at the moment? Please write what you need currently?
Do you have some query on Goldengate? Can't you just use MAP and FILTER arguments to do this task? I couldn't understand what is the problem here? Or are you asking some things about ANSI or Oracle SQL?