OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » plz help me

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

plz help me [message #182823]

Tue, 18 July 2006 05:51

bagulia_pilla
Messages: 25
Registered: July 2006

Junior Member

I need to optimise this query.
so I need some advice.. thanks in advance.

SELECT notificationid, a.status, notificationrefno,
(SELECT COUNT (*)
FROM company com
WHERE a.forcompanyid = com.parentcompanyid) AS parentindicator,
c.companyname AS companyname, transactionid, refamount, refcurrency,
notificationdate, foruserid, forgroupid, forcompanyid,
notificationheader, notificationmessage, bankaccountid,
b.notificationtypeid, notificationtype, description, replyreq,
trxtype, CATEGORY,
DECODE (CATEGORY, 'System', '1', CATEGORY) catorder
FROM notification a,
notificationtype b,
company c
WHERE (formatcode NOT LIKE 'Z%' OR formatcode IS NULL)
AND (bankaccountid IN (NULL, 90030327000475))
AND a.deleted IS NULL
AND UPPER (a.forcompanyid) = c.companyid
AND c.parentcompanyid = 20030408000466
AND a.notificationtypeid = b.notificationtypeid
ORDER BY parentindicator DESC,
companyname ASC,
catorder ASC,
notificationdate DESC

Report message to a moderator

Re: plz help me [message #182825 is a reply to message #182823]

Tue, 18 July 2006 05:58

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Here's some advice:
- use a significant topic title. plz cannot help you since he's no member here.
- use code tags to preserve formatting so others can read your code more easily.

MHE

Report message to a moderator

Re: plz help me [message #182826 is a reply to message #182823]

Tue, 18 July 2006 05:59

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

"AND UPPER (a.forcompanyid) = c.companyid" -> Is the UPPER function necessary? If so, check whether there's a function based index declared on this column.

This ("AND (bankaccountid IN (NULL, 90030327000475))" could be rewritten as

"AND (bankaccountid = 90030327000475 OR bankaccountid IS NULL)"

but I'm not sure would it improve anything or not.

Did you analyze tables and indexes recently?

Also, query will be faster if you omit the ORDER BY clause if possible (for example, you use this query to insert records into another table).

Report message to a moderator

Previous Topic:	Slow insertions
Next Topic:	Performance issue problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 15:08:39 CDT 2025