Regarding Join Index usage when reporting SQLs use Views


Regarding Join Index usage when reporting SQLs use Views

Hi All,

I'm trying creating a Join index to tune a reporting SQL.

Reporting SQL accesses only the views and say i have 3 views V1, V2, V3

Here V1 corresponds to the largest table and is a simple "Select * from Table"
and V2 and V3 are sqls which have some logic (involves joining 3-4 Tables)

But the volume that comes out of V2 and V3 is very small.

I'm not able to create JIs(on the base tables) to cover the items in V1,V2,V3 which are used in the Reporting SQLs.

The reason being the view is being Text substituted and the complete Table List/columns used is not present in JI and the JI is therefore not used.

On top of this some of the views used derived table and so will not be able to replicate this logic in JI DDL.

If i physicalize these small views and have a simple Select * kind of view then the JI is used by the report.
(Which is obvious)

I would like to know if any of you have worked on creating JIs for reports which access data through Views(Which also have logic(joins) built in it) and your experience on the same.

Annal T