I have a view V2 that selects from another view V1 and adds a couple predicate filters.
V2 IS SELECT * FROM V1
WHERE ACTIVE='Y'
AND TYPE = '1';
When I do a join between V2 and a table FOO on the column USER_ID (indexed in FOO) I find that it first calculates the entire results for V2 before joining with FOO on the indexed column (indexed also in the source table of V1).
SELECT * FROM FOO
INNER JOIN V2
ON FOO.USER_ID = V2.USER_ID
WHERE FOO.SCHOOL = '3'
But when I do a join between V1 and FOO it finds the record in FOO first and then uses the index to quickly retrieve the row from V1 using predicate pushing & indexes.
I have narrowed down the difference to the fact that V2 is built on top of V1 with predicates. I changed V2 to be an exact copy of V1 but put the two additional predicate filters in it directly and found that it behaved the same way when joined with FOO that V1 did but with the proper restrictions in place now.
Are nested views unable to push predicates all the way down to the tables? Also, how consistent is predicate pushing? I have several views where if predicates are not pushed it would ruin performance. They work fine now but what guarantee is there they will continue to do so?
I'd like to nest/inherit views rather than duplicating the exact same queries with minor differences to keep things DRY.
What version of Oracle are you using? What is the parameter OPTIMIZER_SECURE_VIEW_MERGING set to? You may try setting that to FALSE or granting the user the MERGE ANY VIEW privilege.
If that doesn't help, can you post the two query plans?