| 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
|
|
|
|
|
|
| Re: plz help me [message #182826 is a reply to message #182823] |
Tue, 18 July 2006 05:59  |
 |
Littlefoot
Messages: 21808
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).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
