Suggestion Required regarding derived table usage

Database
Enthusiast

Suggestion Required regarding derived table usage

Hi,

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.

Thanks

Manik

1 REPLY
Enthusiast

Re: Suggestion Required regarding derived table usage

You can create JI (Join INDEX) on the repeating tables/joins and make COGNOS go towards JI instead of tables directly, that would definately improve the performance.