I am trying to optimize a query in my prod env. The queries are generated by COGNOS and the trend I am seeing in that is the same table is used n number of times in the query in various joins and before and after somn UNION clauses.
I just need to understand if we can use any kind of a derived table in the start of the query and we can use the same spool file within the query.
Volatile table is not an option and unfortunately I'll not be able to post the SQL here as its a secured env.
Any kind of a help is appreciated.