Home » SQL & PL/SQL » SQL & PL/SQL » fine tuning SQL
fine tuning SQL [message #225655] Tue, 20 March 2007 14:10 Go to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
I have been given a task to fine tune the SELCT query in the attached doc. I currently have the following indexes that have been created and they exist for:-


idx_fxrate_leg_id
ON fxrate (
leg_id
)



idx_fxtrade_trade_header_id
ON fxtrade (
trade_header_id
)


Would it be helpful to create indexes for Trade_id and leg_number as well.

Secondly would changing the order of the where clause improve the performance? Any advice is welcome.
  • Attachment: SELECT.doc
    (Size: 24.00KB, Downloaded 152 times)
Re: fine tuning SQL [message #225656 is a reply to message #225655] Tue, 20 March 2007 14:31 Go to previous messageGo to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
for those of you who are reluctant to download files here is the query:-

SELECT ………
FROM PTEDB.FXRATE a, PTEDB.FXTRADE b, PTEDB.FXTRADEHEADER c, PTEDB.GBS_MONITOR d
WHERE (b.TRADE_HEADER_ID = c.PTE_TRADE_ID AND a.LEG_ID = b.LEG_ID
AND (d.PTE_TRADE_ID = c.PTE_TRADE_ID and d.LEG_NUM=b.LEG_NUMBER)) and
b.LEG_NUMBER =:1 and
d.TRADE_SENT_STATUS = :"SYS_B_0"
ORDER BY b.VALUE_DATE
Re: fine tuning SQL [message #225778 is a reply to message #225656] Wed, 21 March 2007 07:36 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
IMHO for the query to achieve the better performance you need following indexes:

1. For table FXTRADE index on LEG_NUMBER column.
2. For table FXRATE index on LEG_ID column (you already have it).
3. For table GBS_MONITOR index on PTE_TRADE_ID, TRADE_SENT_STATUS columns (together).
4. For table FXTRADEHEADER index on PTE_TRADE_ID column.

Check if these indexes exists. If not - define them.
Post EXPLAIN of TKPROF file.

HTH.
Michael
Previous Topic: Saving XMLTYPE data to server
Next Topic: How do I total 2 total columns in this SQL??
Goto Forum:
  


Current Time: Sat Dec 03 01:22:22 CST 2016

Total time taken to generate the page: 0.10925 seconds