OraFAQ Forum: SQL & PL/SQL » fine tuning SQL

Home » SQL & PL/SQL » SQL & PL/SQL » fine tuning SQL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

fine tuning SQL [message #225655]

Tue, 20 March 2007 14:10

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 494 times)

Report message to a moderator

Re: fine tuning SQL [message #225656 is a reply to message #225655]

Tue, 20 March 2007 14:31

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

Report message to a moderator

Re: fine tuning SQL [message #225778 is a reply to message #225656]

Wed, 21 March 2007 07:36

michael_bialik
Messages: 621
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

Report message to a moderator

Previous Topic:	Saving XMLTYPE data to server
Next Topic:	How do I total 2 total columns in this SQL??

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 12:41:39 CST 2024