SQL QUESTIONS WITH EXPLAIN INFO

sushant dash

SQL QUESTIONS WITH EXPLAIN INFO
Hi Group here is attachement I m sending for verification of performance of the query. It is a dynamic sql. For tuning the application I rewrote the query and collected the explain information for both. Both the queries as well explain information are given. As per my knowledge I could see I was able to remove only one sort and a merge scan join. But I could not restrict the query blocks. Only I see the plan no has been reduced from from the first explain information. There is also twice reference to same table . Would it be benefiacial if I take case structure to have only one scan for that table. If that is the case how much benefit I would get. Any suggestion as well comments would be highly appreciated.

Dash