SQL & PL/SQL

Hi All,

We have recently upgraded from 11i to R12 and the DB is also upgraded to 11g.

In 11i, I had a query which was selecting the data from a view and if i see the explain plan of the query, I can see that it is using the indexes on the base tables and so, it is not going for a full table scan on the base tables to which the view refers.

But in R12, the explain plan of the same query shows that the indexes of the base tables are not used and it is going for a full table scan on all the tables (used in the view)

I tried forcing the index by using 'Select /*+ INDEX(empv B_IDX_NAME) */ from xx_emp_vw empv' (where xx_emp_vw is the view name and B_IDX_NAME is the index on the base table which the view refers), but the index is not getting used causing a full table scan on the base tables

Is there a different way to force the index on the views. Any other pointers to solve this issue is welcome.

Regards,
Mohan