Home » RDBMS Server » Performance Tuning » sql tuning
sql tuning [message #198009] Fri, 13 October 2006 15:10 Go to next message
srinivas4u2
Messages: 66
Registered: June 2005
Member
Hi,

Is there any better way to do this?
select distinct olf.invoice_date, TO_CHAR(olf.invoice_date,'DAY'), trunc(create_date), 
count(*)
from order_line_fact olf
where invoice_date in
       (-- invoice date loaded on more than 1 day
         select distinct invoice_date
         from order_line_fact
         where invoice_date between to_date('01-JAN'||to_char(sysdate-2, '-rr')) and sysdate-2
         having count(distinct trunc(create_date)) > 2
         group by invoice_date
         union
         -- invoice date not loaded next day
         select distinct invoice_date
         from order_line_fact
         where invoice_date between to_date('01-JAN'||to_char(sysdate-2, '-rr')) and sysdate-2
         and ((invoice_date > trunc(create_date))
           or (invoice_date < trunc(create_date) - 1))
         union
         -- invoice date record count outside of normal range (Mon-Fri)
         select distinct invoice_date
         from order_line_fact 
         where invoice_date between to_date('01-JAN'||to_char(sysdate-2, '-rr')) and sysdate-2
         and to_number(to_char(invoice_date, 'D')) <> 1 
         and to_number(to_char(invoice_date, 'D')) <> 7
         having count(*) < 10000 or count(*) > 17000
         group by invoice_date
         union
         -- invoice date record count outside of normal range (Sat)
         select distinct invoice_date
         from order_line_fact 
         where invoice_date between to_date('01-JAN'||to_char(sysdate-2, '-rr')) and sysdate-2
         and to_number(to_char(invoice_date, 'D')) = 7
         having count(*) <= 0 or count(*) > 1700
         group by invoice_date
         union
         -- invoice date record count outside of normal range (Sun)
         select distinct invoice_date
         from order_line_fact 
         where invoice_date between to_date('01-JAN'||to_char(sysdate-2, '-rr')) and sysdate-2
         and to_number(to_char(invoice_date, 'D')) = 1
         having count(*) < 0 or count(*) > 50
         group by invoice_date
) 
group by olf.invoice_date, TO_CHAR(olf.invoice_date,'DAY'), trunc(create_date);

I replaced the unions with union all, tried exists instead of in, removed all distincts in select clause except the main select, but not any improvement.

Thanks,
Srinivas

[Updated on: Fri, 13 October 2006 15:39] by Moderator

Report message to a moderator

Re: sql tuning [message #198029 is a reply to message #198009] Fri, 13 October 2006 21:54 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You're definitely committed to parsing all rows from 1/Jan through to the current date at least once, but try not to do it more than once.

I'm not going to test this, but it might give you some ideas.

  SELECT invoice_date
  FROM order_line_fact
  WHERE invoice_date BETWEEN
        to_date('01-JAN'||to_char(sysdate-2, '-rr')) 
        AND sysdate-2
  HAVING count(distinct trunc(create_date)) - 1  -- two_or_more_days
  +      MAX(
           CASE invoice_date
           WHEN trunc(create_date) THEN 0
           WHEN trunc(create_date) + 1 THEN 0
           ELSE 1
           END
         )                                  -- not_next_day
  +      COUNT(
           CASE to_char(invoice_date, 'D')
           WHEN 1 THEN NULL
           WHEN 7 THEN NULL
           ELSE 1
         )                                  -- num_weekdays
  +      COUNT(
           CASE to_char(invoice_date, 'D')
           WHEN 1 THEN NULL
           WHEN 7 THEN 1
           ELSE NULL
         )                                  -- num_saturdays
  +      COUNT(
           CASE to_char(invoice_date, 'D')
           WHEN 1 THEN 1
           WHEN 7 THEN NULL
           ELSE NULL
         )                                  -- num_sundays
  > 0

If you had an index on INVOICE_DATE, CREATE_DATE, it might help as well (providing you don't use any other table columns in the SQL.

Ross Leishman

[Updated on: Sun, 15 October 2006 22:07]

Report message to a moderator

Re: sql tuning [message #198157 is a reply to message #198009] Sun, 15 October 2006 21:34 Go to previous messageGo to next message
srinivas4u2
Messages: 66
Registered: June 2005
Member
Thanks much for the response Ross!

I have not used anytime "+" in having clause before, I searched on the net and could not find any article about the same, could you explain the usage of it or show any link? Is it equivalent to "union" or "union all" or "and"?

Also, there is some condition in the having clause of the original subqueries but there is only 1 condition of being >1 for all cases in the suggested approach, didn't quiet get that.
Apologize for such questions.

Thanks,
Srinivas
Re: sql tuning [message #198161 is a reply to message #198157] Sun, 15 October 2006 22:07 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
+.... as in "addition".

Notice that all of the constructs in the HAVING clause are NUMBERS: each of these components is 0 if the condition is to be ignored, and >0 if the condition is of interest. We add them up and keep any invoice date with a total > 0 (ie. at least one of the conditions was met).

count(distinct trunc(create_date)) - 1
This one will return the number of different CREATE_DATEs and subtract 1. So if there is only 1 date, it will return 0. If there are 2 or more, ite returns >1.

  +      MAX(
           CASE invoice_date
           WHEN trunc(create_date) THEN 0
           WHEN trunc(create_date) + 1 THEN 0
           ELSE 1
           END
         )                                  -- not_next_day
This one compares the CREATE_DATE to the INVOICE_DATE for each row and returns 0 if the INVOICE_DATE is either the CREATE_DATE or the day after, and 1 otherwise. The MAX() then takes the maximum of all rows. So, if there is just one row with an INVOICE_DATE outside the two-day period, it returns 1.

You get the idea.

Ross Leishman
Re: sql tuning [message #198364 is a reply to message #198009] Mon, 16 October 2006 16:44 Go to previous messageGo to next message
srinivas4u2
Messages: 66
Registered: June 2005
Member
Ross,

Thank you very much for your inputs!

Greatly appreciate your help.

I have used the same idea in a different way as follows to get what I wanted -

select distinct invoice_date
from order_line_fact
where invoice_date between to_date('01-JAN'||to_char(sysdate-2, '-rr')) and sysdate-2
having count(distinct trunc(create_date)) > 2
or sum(case when invoice_date NOT IN (trunc(create_date),trunc(create_date)-1) then 1 end) > 0
or sum(case when to_char(invoice_date, 'D') NOT IN ('1','7') then 1 end) NOT BETWEEN 10000 and 17000
or sum( case when to_char(invoice_date) = '7' then 1 end ) > 1700
or sum( case when to_char(invoice_date) = '1' then 1 end ) > 50
group by invoice_date


Thanks again!

-Srinivas
Re: sql tuning [message #198380 is a reply to message #198364] Mon, 16 October 2006 21:56 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yep, that'll do it.

You don't need the DISTINCT - Oracle will just ignore it.

Ross Leishman
Previous Topic: Differences in query execution between two similar environments
Next Topic: Very low network times when retriving data from a query.
Goto Forum:
  


Current Time: Tue Dec 03 05:35:10 CST 2024