Any better way to write this Query for performance

Hi,
Is there any better way to write the attached Query for performance??
thx Bill
select case
 when nbr = 1 and QTR1_CNT > 0 then 'Q1'
 when nbr = 2 and QTR2_CNT > 0 then 'Q2'
 when nbr = 3 and QTR3_CNT > 0 then 'Q3'
 when nbr = 4 and QTR4_CNT > 0 then 'Q4' end display, nbr id_nbr
from
(select
 COUNT(CASE WHEN first_qtr_begin  <= d and d <= second_qtr_begin -1   then 1  end) QTR1_CNT,
 COUNT(CASE WHEN second_qtr_begin <= d and d <= third_qtr_begin -1    then 1  end) QTR2_CNT,
 COUNT(CASE WHEN third_qtr_begin  <= d and d <= fourth_qtr_begin -1   then 1  end) QTR3_CNT,
 COUNT(CASE WHEN fourth_qtr_begin <= d and d <= first_of_next_year -1 then 1  end) QTR4_CNT
FROM ( SELECT PAGE_ID, MODULE_ID, EBS_USER_TYPE,
 TRUNC(view_date) d,
 TRUNC(view_date, 'yyyy') y,
 to_date(:p_SELECTED_YEAR) first_qtr_begin,
 ADD_MONTHS(to_date(:p_SELECTED_YEAR),3) second_qtr_begin,
 ADD_MONTHS(to_date(:p_SELECTED_YEAR),6) third_qtr_begin,
 ADD_MONTHS(to_date(:p_SELECTED_YEAR),9) fourth_qtr_begin,
 ADD_MONTHS(to_date(:p_SELECTED_YEAR),12) first_of_next_year
FROM WURS_ACTIVITY_LOG_EXT_VIEW
WHERE TRUNC(view_date, 'yyyy') = to_date(:p_SELECTED_YEAR)
) MyTable
group by y) x,
(select level nbr from dual connect by level <= 4) qnum
where case when nbr = 1 and QTR1_CNT > 0 then 1
 when nbr = 2 and QTR2_CNT > 0 then 1
 when nbr = 3 and QTR3_CNT > 0 then 1
when nbr = 4 and QTR4_CNT > 0 then 1 end = 1

Open in new window

LVL 2
BILL CarlisleAPEX DeveloperAsked:
Who is Participating?
 
sdstuberCommented:
if you indexes on view_date,  try this...


WHERE view_date >= trunc(to_date(:p_SELECTED_YEAR),'yyyy') and view_date <add_months( trunc(to_date(:p_SELECTED_YEAR),'yyyy') ,12)
0
 
lwadwellCommented:
If have understood the SQL correctly - this should be an equivalent simplification and may perform better:
SELECT 'Q' || TO_CHAR(view_date, 'Q')  as display,
       TO_CHAR(view_date, 'Q')         as id_nbr
  FROM WURS_ACTIVITY_LOG_EXT_VIEW
 WHERE TRUNC(view_date, 'yyyy') = to_date(:p_SELECTED_YEAR)
 GROUP BY TO_CHAR(view_date, 'Q')
HAVING COUNT(*) > 0

Open in new window


And if I have really understood it ... it seems that it would return Q1, 1 if data exists in the selected year first quarter, Q2, 2 if ... etc.
Then, like sdstuber has already replied - with an index on view_date - this should be better:
 
SELECT 'Q1' as display, 1 as id_nbr
  FROM DUAL
 WHERE EXISTS (SELECT 1 
                 FROM WURS_ACTIVITY_LOG_EXT_VIEW
                WHERE view_date >= trunc(to_date(:p_SELECTED_YEAR),'yyyy') 
                  AND view_date <add_months( trunc(to_date(:p_SELECTED_YEAR),'yyyy') ,12)
                  AND TO_CHAR(view_date, 'Q') = 1)
UNION ALL
SELECT 'Q2' as display, 2 as id_nbr
  FROM DUAL
 WHERE EXISTS (SELECT 1 
                 FROM WURS_ACTIVITY_LOG_EXT_VIEW
                WHERE view_date >= trunc(to_date(:p_SELECTED_YEAR),'yyyy') 
                  AND view_date <add_months( trunc(to_date(:p_SELECTED_YEAR),'yyyy') ,12)
                  AND TO_CHAR(view_date, 'Q') = 2)
UNION ALL
SELECT 'Q3' as display, 3 as id_nbr
  FROM DUAL
 WHERE EXISTS (SELECT 1 
                 FROM WURS_ACTIVITY_LOG_EXT_VIEW
                WHERE view_date >= trunc(to_date(:p_SELECTED_YEAR),'yyyy') 
                  AND view_date <add_months( trunc(to_date(:p_SELECTED_YEAR),'yyyy') ,12)
                  AND TO_CHAR(view_date, 'Q') = 3)
UNION ALL
SELECT 'Q4' as display, 4 as id_nbr
  FROM DUAL
 WHERE EXISTS (SELECT 1 
                 FROM WURS_ACTIVITY_LOG_EXT_VIEW
                WHERE view_date >= trunc(to_date(:p_SELECTED_YEAR),'yyyy') 
                  AND view_date <add_months( trunc(to_date(:p_SELECTED_YEAR),'yyyy') ,12)
                  AND TO_CHAR(view_date, 'Q') = 4)

Open in new window

0
 
Christoffer SwanströmPartnerCommented:
Could you post an explain plan as well as some information regarding what the current performance is and how much you would like to improve it?

Is WURS_ACTIVITY_LOG_EXT_VIEW a table of a view? Based on the name I would guess it's a view, in which case the potential for optimization depends a lot on how the view is defined and whether you have any possibility to change the view definition.

If WURS_ACTIVITY_LOG_EXT_VIEW is a table then the main question becomes how you can get the data you need out of the table as efficiently as possible. Your query retrieves data for one year. How many years are stored in the table and how many rows are the per year? As sdstuber pointed out one possibility would be to add an index on view_date and include a restriction on view_date in the WHERE clause.

I think even more efficient would be to partition the table. You could for example add a column which contains just the year (could also be a virtual column if you run 11g) and partition the table on the year. That way your query would access a single partition of the table, which would most likely be far more efficient than using an index.
0
 
BILL CarlisleAPEX DeveloperAuthor Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.