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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.