Jump to content

p_sony_2000@yahoo.com

Members
  • Content count

    4
  • Joined

  • Last visited

Community Reputation

0 Neutral

About p_sony_2000@yahoo.com

  • Rank
    Newbie
  1. This is on Oracle version 11.2.0.4
  2. Hi, I have a materialized view created along start with and next clause for a complete refresh. The complete refresh takes 1+ hour for 5million rows. I want to change the refresh to non-atomic with the atomic-refresh = False. Can this be achieved in the create MV statement or do we need a separate job. Also tried creating the MV as BUILD DEFERRED REFRESH COMPLETE ON DEMAND WITH PRIMARY KEY and then refresh using DBMS_MVIEW.REFRESH (MV_NAME, METHOD => 'C', ATOMIC_REFRESH => FALSE); But here also the refresh takes 1 hour.
  3. Max of a value among rows

    found the solution on my own
  4. Max of a value among rows

    Hi, I have the below data in one of our Materialized View R879_PH1_SEQ_NUM R879_PH1_WSLR_CO_ID R879_PH1_RTLR_CO_ID R879_PD1_SEQ_NUM R879_PD1_DATE_QUAL R879_PD1_DATE_CALC R879_PD1_UPC_CASE R879_PD1_UPCEAN_PC R879_PD4_SEQ_NUM ORIG_R879_PD4_MAC_QUAL R879_PD4_MAC_ID CONSUMER_NUM CASE_NUM 199714 1000001887 1000000558 32938719 07' 12/28/2015 18200960468 18200001741 48583194 5 02N03 18200001741 18200960468 193703 1000001887 1000000558 32373409 07' 9/26/2016 18200960468 18200001741 47911465 5 02N03 18200001741 18200960468 200335 1000001887 1000000558 33049290 07' 9/26/2016 18200960468 18200001741 48732282 5 02N03 18200001741 18200960468 I want to get the max of R879_PH1_SEQ_NUM along with other columns of the corresponding max r879_PH1_seq_num row. I used the below query which uses co-related subqueries, but the performance is bad for all data. SELECT mvup.r879_ph1_seq_num AS pl_r879_ph1_seq_num, mvup.r879_pd1_seq_num AS pl_r879_pd1_seq_num, mvup.r879_ph1_wslr_co_id AS pl_r879_ph1_wslr_co_id, mvup.r879_ph1_rtlr_co_id AS pl_r879_ph1_rtlr_co_id, mvup.r879_pd1_upcean_pc AS pl_r879_pd1_upcean_pc, mvup.r879_pd1_upc_case AS pl_r879_pd1_upc_case, mvup.orig_r879_pd4_mac_qual AS pl_r879_pd4_mac_qual, mvup.r879_pd4_mac_id AS pl_r879_pd4_mac_id, mvup.r879_pd1_date_calc AS pl_r879_pd1_start_date, ADD_MONTHS (mvup.r879_pd1_date_calc, 24) AS pl_r879_pd1_end_date FROM unauthorized_products mvup WHERE mvup.r879_ph1_rtlr_co_id = 1000000558 AND EXISTS (SELECT 1 FROM r879_ph1 ph1 WHERE ph1.r879_ph1_seq_num = mvup.r879_ph1_seq_num AND ph1.r879_ph1_doc_type = 'PRC') AND mvup.R879_PD1_DATE_QUAL = '07' AND TRUNC (mvup.r879_pd1_date_calc) < TRUNC (SYSDATE + 1) AND mvup.R879_PH1_SEQ_NUM = (SELECT MAX (b.R879_PH1_SEQ_NUM) FROM unauthorized_products b WHERE mvup.r879_ph1_rtlr_co_id = b.r879_ph1_rtlr_co_id AND mvup.r879_ph1_wslr_co_id = b.r879_ph1_wslr_co_id AND mvup.consumer_num = b.consumer_num AND mvup.case_num = b.case_num AND mvup.ORIG_R879_PD4_MAC_QUAL = b.ORIG_R879_PD4_MAC_QUAL AND NVL (mvup.R879_PD4_MAC_ID, ' ') = NVL (b.R879_PD4_MAC_ID, ' ') AND b.R879_PD1_DATE_QUAL = '07' AND TRUNC (mvup.r879_pd1_date_calc) < TRUNC (SYSDATE + 1) ) I need to tune this query or use analytical function which is faster in performance. Can you please help me in modifying the query in using analytical function
×