It needs basically to add the join condition on the wonum column of the outer table (workder.wonum=woancestor.wonum) and then replace the IN with EXISTS.
Then repeat the same approach with the outer table wftransaction adding the join condition on the colum ownerid:
Finally this is the rewritten part of the query:
Each change should be validated checking with the tool to check the cost of the access plan. So do not trust of this best practice, this is only an example, please also check with your database and data.
Sometime the subselect is faster than the rewritten one. This happens when the subselect returns only a few data (like in my case the subselect of the "transtype in" condition returns a few rows).
Another point I want to show is about the indexes. Let's use as example the last subselect of the original query. This subselects does not need to be rewritten but I can talk about the index that should be defined:
The index should be made by all the columns specified on the where condition, starting from with the columns with the equal condition (the most efficient) then with those with like condition, finally with those that have condition with functions (the less efficient). So in this case the index should be have this order column: "nodeid, processname, processrev". But since in the select there is only a column to retrieve I suggest to add to the index also with the "title" as final column on the index in order to retrieve it faster in the select because it is available with the index scan itself. Then the index on the table wfnode will be in this case:
"nodeid, processname, processrev,title"
When the select is "select * from " it is not possible to leverage the index scan to retrieve the all the columns it would be not convenient, so a table scan is performed.
Nessun commento:
Posta un commento