DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Tuning SQL

  1. #1
    Join Date
    Dec 1999
    Posts
    217

    Tuning SQL

    I have another SQL. I am wondering if any of you'll can tune the code any more so that it is run in a more efficient manner.

    The SQL code is:
    select
    release_date,
    count(order_no) as ord_cnt,
    count(package_no) as pkg_cnt,
    sum(package_qty) as units,
    sum(pkg_merch_retail) as retail,
    sum(shipment_charge) as shp_chrg
    from cust_package
    where release_date > '13-APR-05'
    group by
    release_date;

    Thank You in advance,
    Chintzs

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    Is it running bad ? If so, how bad ?
    svk

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Sure, I'd love to. Maybe post a query plan to start.
    Jeff Hunter

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Tuning SQL

    Originally posted by Chintz ... release_date > '13-APR-05' ...[/B]
    Is that ... an implicit datatype conversion I see there? *shriek*

    Code:
    ...release_date > to_date('13-Apr-2005','DD-Mon-YYYY')...
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Dec 1999
    Posts
    217
    Apparently this query is taking 52 seconds to run in Oracle whereas the user is comparing it to a redbrick database where it took 5 seconds. That is the reason I want to see if I can tune the query.

    Thanks,
    Chintzs

  6. #6
    Join Date
    Dec 1999
    Posts
    217
    Here is the explain plan:

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50030 Card=1
    Bytes=16)
    1 0 SORT (GROUP BY) (Cost=50030 Card=1 Bytes=16)
    2 1 TABLE ACCESS (FULL) OF 'CUST_PACKAGE' (Cost=49993
    Card=8205 Bytes=131280)

    Chintzs

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Well there you go, if there is an index on cust_package.release_date, it's not being used. This could be because of an implicit type conversion or something else.
    Jeff Hunter

  8. #8
    Join Date
    Dec 1999
    Posts
    217
    Marist, there is a foreign key defined on the RELEASE_DATE column on that table.

    Chintzs

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by Chintz
    Marist, there is a foreign key defined on the RELEASE_DATE column on that table.

    Chintzs
    Even more reason why it should have an index, and don't forget to analyze it either.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Jul 2000
    Posts
    521
    The question was : Is there an index on RELEASE_DATE column in CUST_PACKAGE table ?

    Is there or is there not ?
    If it is there, what is data type of that column in the table ?
    Is the table and index stats available ?
    How many rows this query returns ?
    How big is the table ?
    svk

Posting Permissions

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


Click Here to Expand Forum to Full Width