Let's say we have a view that consists of (in my case) 4 nested subqueries one inside the other. Is it possible to use push_pred hint or some other method to push the predicates all the way down to the lowest subquery. Are there some limitations to this? In my case only lowest subquery fetches data from table and the query would benefit hugely if predicate could be pushed to it.
Other upper queries don't filter or join any data but do analytic functions and minor transformations on numeric and date values. But the IDs of the main table are forwarded all the way from bottom to top of the view.
If needed i can show some lite example of what i have in mind since this view is pretty huge and i'd like to spare you the details if possible.