Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Max of a value among rows
Poonam
post Feb 23 2017, 08:55 AM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 23-September 16
Member No.: 51,569



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
Go to the top of the page
 
+Quote Post
Poonam
post Feb 24 2017, 12:23 PM
Post #2


Newbie
*

Group: Members
Posts: 2
Joined: 23-September 16
Member No.: 51,569



found the solution on my own
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd October 2017 - 01:36 PM