Performance problem for the below query
Hi,
Can anybody help me on the below query while improving the performance.
I am using TOAD 8.6.1 version and executing the query in maximo schema . And please let me know the other possibility ways of writing the below query.
select * from maximo.doclinks
where (ownertable='WORKORDER' and ownerid=:workorderid)
or (ownertable='WORKORDER' and ownerid in (select workorderid from workorder where parent=:wonum and istask='1' and siteid=:siteid))
or (ownertable='ASSET' and ownerid in (select assetuid from asset where assetnum=:assetnum and siteid=:siteid))
or (ownertable='LOCATIONS' and ownerid in (select locationsid from locations where location=:location and siteid=:siteid))
or (ownertable='JOBPLAN' and ownerid in (select jobplanid from jobplan where jpnum=:jpnum and (siteid=null or siteid=:siteid)))
or (ownertable='PM' and ownerid in (select pmuid from pm where pmnum=:pmnum and siteid=:siteid))
or (ownertable='SAFETYPLAN' and ownerid in (select safetyplanuid from safetyplan,wosafetyplan where safetyplan.safetyplanid=wosafetyplan.safetyplanid and wosafetyplan.wonum=:wonum and wosafetyplan.siteid=:siteid))
or (ownertable in ('SR','INCIDENT','PROBLEM') and ownerid in (select ticketuid from ticket,relatedrecord where ticketid=recordkey and ticket.class = relatedrecord.class and relatedrecclass='WORKORDER' and relatedreckey=:wonum and relatedrecsiteid=:siteid))
or (ownertable in ('WOCHANGE','WORELEASE','WOACTIVITY') and ownerid in (select workorderid from workorder,relatedrecord where wonum=recordkey and workorder.woclass = relatedrecord.class and relatedrecclass='WORKORDER' and relatedreckey=:wonum and relatedrecsiteid=:siteid))
or (ownertable='COMMLOG' and ownerid in (select commloguid from commlog where ownerid=:workorderid and ownertable='WORKORDER'))
or (ownertable='SLA' and ownerid in (select slaid from sla,slarecords,workorder where sla.slanum=slarecords.slanum and slarecords.ownerid=workorder.workorderid and sla.objectname='WORKORDER'and slarecords.ownertable='WORKORDER' and workorder.wonum=:wonum))
Regards,
Arjun