Home » SQL & PL/SQL » SQL & PL/SQL » Query Tuning
Query Tuning [message #209649] Sat, 16 December 2006 06:14 Go to next message
sauami
Messages: 54
Registered: October 2005
Location: india
Member
Dear Sir,

I have a below query which is part of my view and that query
retrieve 6000 records in 1 hours 50 minute due to function due
to substr and to_char function used in query Pls. help me to tune this query to make it fast
i have one more request pls. suggest oracle tuning document or book through which i can study the
tuning techniques.

---query
SELECT
Customer_category_code,
trx_date,
TO_CHAR(trx_date,'YYYYMM') month_year,
BRANCH_CODE,
'N' CMN_Flag,
PRODUCT_CODE,
GRADE,
INVENTORY_ITEM_ID,
WH_NAME,
SUM(QUANTITY_INVOICED) qty,
SUM(BASIC_AMOUNT) * 1000 basic,
SUM(DISCOUNT_AMOUNT) * 1000 discount,
MODEL_SUFFIX
FROM APPS.ESO_SALES_DETAIL_V WHERE
SUBSTR(wh_name,5,4) = 'FG-2'
and TO_CHAR(TRX_DATE,'YYYYMM')=p_yyyymm -- i.e. '200611'
AND order_category_code <> 'RETURN'
GROUP BY Customer_category_code,
trx_date,
TO_CHAR(trx_date,'YYYYMM') ,
BRANCH_CODE,
PRODUCT_CODE,
GRADE,
INVENTORY_ITEM_ID,
WH_NAME,
MODEL_SUFFIX
Thanks,
Saurabh
Re: Query Tuning [message #209676 is a reply to message #209649] Sat, 16 December 2006 11:27 Go to previous messageGo to next message
dba_blr
Messages: 43
Registered: December 2006
Member
sauami wrote on Sat, 16 December 2006 17:44
Dear Sir,

I have a below query which is part of my view and that query
retrieve 6000 records in 1 hours 50 minute due to function due
to substr and to_char function used in query





How do you know that the query is taking long time because of the functions? Have you generated the EXPLAIN PLAN for the query?

If you have then can you please post it...
Re: Query Tuning [message #210176 is a reply to message #209649] Tue, 19 December 2006 15:52 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Do NOT PM your question to people!!

David
Re: Query Tuning [message #210199 is a reply to message #209649] Tue, 19 December 2006 17:26 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
You select from the table APPS.ESO_SALES_DETAIL_V (or is it a view?). So if you do not want to full scan this table, you should use an index. As I do not know your DDL and data (number of rows, the most selective column), I will only provide you with some hints:
If you want to index scan by SUBSTR(wh_name,5,4), you should build the functional index on this (or add it to the existing index).
TO_CHAR(TRX_DATE,'YYYYMM')=p_yyyymm will never use the index on TRX_DATE; rather than creating functional index on this expression, I would use index on TRX_DATE and rewrite this condition as
(TRX_DATE >= TO_DATE(p_yyyymm,'YYYYMM') and TRX_DATE < add_months(TO_DATE(p_yyyymm,'YYYYMM'),1)
Previous Topic: Racle FAO,s
Next Topic: DATE help (adding 7 hours to a date)
Goto Forum:
  


Current Time: Wed Dec 07 12:41:32 CST 2016

Total time taken to generate the page: 0.08361 seconds