-
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
-
Is it running bad ? If so, how bad ?
svk
-
Sure, I'd love to. Maybe post a query plan to start.
Jeff Hunter
-
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')...
-
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
-
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
-
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
-
Marist, there is a foreign key defined on the RELEASE_DATE column on that table.
Chintzs
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|