Results 1 to 13 of 13

Thread: sql tuning

  1. #1
    Join Date
    Oct 2005
    Posts
    61

    Unanswered: sql tuning

    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

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The query seems strange is invoice_date the PK or something?

    Anyway an index on invoice_date should help. Also if you group by a column dont bother putting in distinct. Also try to remove OR clauses and replace them i.e.

    ((invoice_date > trunc(create_date))or (invoice_date < trunc(create_date) - 1))

    can be replaced by

    invoice_date not between trunc(create_date-1) and trunc(create_date)

    You could also use a with clause to select from your fact table between the date range and then just select from it repeatedly. Also try replacing the outer queries IN with a join.

    Alan

  3. #3
    Join Date
    Oct 2005
    Posts
    61
    Thanks for the response Alan!

    No, the invoice_date is not the pk. It is however indexed. Forgot to mention - it is a 8i database.

    I have tried to replace the IN with exists, but did not give any improvement.

    Can you give an example for using with in select or point to some link?

    Thanks,
    Srinivas

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can you give an example for using with in select or point to some link?
    Which of the sources listed in the #1 STICKY post have you previously read?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2005
    Posts
    61
    I always search and try various things before posting anything, you could see the things I have tried before I posted my query here.

    When "with select" was suggested, I searched and did not find any meaningful article in google or asktom, but I am sure there must be plenty out there, maybe I am just not searching correctly. So, if someone already knows a good link or is an expert in explaining it, it was better I thought. Please correct if I am wrong.

    Would appreciate if you could search for the same and not post the links if you could find it easily (but update the thread) else you could post it here.

    Thanks,

  6. #6
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Things that immediately come to my mind:

    1) the syntax for whole statement is not correct. The first UNION selects four columns, all other only select one column (invoice_date). Is that a copy & paste error?

    2) run each select separately to find out if one of them is slower than the others. Do an explain plan on each of them, then do an explain plain on the whole thing.

    3) The group by olf.invoice_date, TO_CHAR(olf.invoice_date,'DAY'), trunc(create_date) might hurt performance. You could try to create an index on TO_CHAR(invoice_date,'DAY') (because the existing one will not be re-used when using a function).
    As an alternative you could try extract(day from invoice_date) instead. Maybe that will re-use the existing index. You will need to run explain plan to find that out. The same goes for trunc(create_date)


    Btw: it would be better if you enlosed you statement with a [code] tag. It will be easier to read.

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    With regards your first point Shammat I think the query is correct as the select with 4 columns is the outer query which then does an IN against the unioned subqueries.

    Anyway with regards replacing an in with a join just do
    Code:
    select ....
    from
    (
         select subquery1
         union all
         select subquery2
         ...
    ) x,
    y
    where x.invoice_date = y.invoice_date
    ...
    The other key point is to get rid of as many OR clauses as they can big performance hits.

    You could also replace

    and to_number(to_char(invoice_date, 'D')) <> 1
    and to_number(to_char(invoice_date, 'D')) <> 7
    with
    and to_number(to_char(invoice_date, 'D')) not in ( 1,7)

    Another thing is you could merge some of the subqueries and use case to filter on the different count criteria.

    Alan

  8. #8
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Quote Originally Posted by AlanP
    With regards your first point Shammat I think the query is correct as the select with 4 columns is the outer query which then does an IN against the unioned subqueries
    Ah, yes. Didn't see that due to the lack of formatting.

    and to_number(to_char(invoice_date, 'D')) not in ( 1,7)
    Would be interesting to test (EXPLAIN PLAN) if
    Code:
    and extract(day from invoice_date) not in (1,7)
    would make any difference in the execution plan.

  9. #9
    Join Date
    Oct 2005
    Posts
    61
    Thanks guys!

    Case (as querying once against vs many times) was one thing I wanted to try on Friday, I will check case statement and also check fbi makes any difference in this case and keep you guys posted.

    Thanks,

  10. #10
    Join Date
    Oct 2005
    Posts
    61
    Shammat,

    Forgot to add in the last post, the explain plan looks good, it was using index for all the subqueries and full table scan for the outer query.

    Thanks,

  11. #11
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If you cant get rid of the subqueries and your outer query is using a full table scan then add this to the outer query:

    invoice_date between to_date('01-JAN'||to_char(sysdate-2, '-rr')) and sysdate-2

    Alan

  12. #12
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    At a quick glance, try the following.

    Code:
    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 invoice_date
     from order_line_fact
     where invoice_date between trunc(sysdate-2,'YEAR') and TRUNC(sysdate-2)
     having count(trunc(create_date)) > 2
     group by invoice_date
     union ALL
    -- invoice date not loaded next day
    select invoice_date
    from order_line_fact
    where invoice_date between trunc(sysdate-2,'YEAR') and TRUNC(sysdate-2)
    AND INVOICE_DATE NOT BETWEEN TRUNC(CREATE_DATE - 1) AND TRUNC(CREATE_DATE)
    GROUP BY INVOICE_DATE
    union ALL
    -- invoice date record count outside of normal range (Mon-Fri)
    select invoice_date
    from order_line_fact
    where invoice_date between trunc(sysdate-2,'YEAR') and TRUNC(sysdate-2)
    AND TO_CHAR(INVOICE_DATE,'DY') NOT IN ('SAT','SUN')
    having count(*) < 10000 or count(*) > 17000
    group by invoice_date
    union ALL
    -- invoice date record count outside of normal range (Sat)
    select invoice_date
    from order_line_fact
    where invoice_date between trunc(sysdate-2,'YEAR') and TRUNC(sysdate-2)
    and to_char(invoice_date, 'DY')) = 'SAT'
    having count(*) <= 0 or count(*) > 1700
    group by invoice_date
    union ALL
    -- invoice date record count outside of normal range (Sun)
    select distinct invoice_date
    from order_line_fact
    where invoice_date between trunc(sysdate-2,'YEAR') and TRUNC(sysdate-2)
    and to_char(invoice_date, 'DY')) = 'SUN'
    having count(*) < 0 or count(*) > 50
    group by invoice_date
    )
    group by olf.invoice_date, TO_CHAR(olf.invoice_date,'DAY'), trunc(create_date)
    ;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  13. #13
    Join Date
    Oct 2005
    Posts
    61
    Thanks guys for your responses!

    beilstwh ,

    I had already tried that and it did not give any improvement, which I have mentioned in my first post.

    I have however got it going as fast it can by combining all the unions into one subquery by introducing a "case statement" in the having clause.

    Thanks,
    Srinivas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •