SELECT notificationid, a.status, notificationrefno,
(SELECT COUNT (*)
FROM company com
WHERE a.forcompanyid = com.parentcompanyid) AS parentindicator,
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 <'Z'OR formatcode IS NULL)
AND ( bankaccountid IS NULL OR bankaccountid = 90050428038137)
AND a.deleted IS NULL
AND a.notificationtypeid = b.notificationtypeid
AND c.companyid = forcompanyid
AND c.parentcompanyid = 20020420000003
ORDER BY parentindicator DESC,
companyname ASC,
catorder ASC,
notificationdate DESC;
I have optimized this statements further, but i still get table access full Notification as the explain plan.
The indexed columns are notificationtypeid,notificationid,transactionid,formatcode,parentcompanyid and companyid.
Can this statement be further optimized?
