Hi all.
I have a complex view that does some joins and transformations in several subqueries. It reads employee data and groups everything by employee.
Now i want to query the view result for certain employees in this manner:
select * from myvew where emp_id in (select eid from empListTab);
The explain plan for this query shows that it firstly runs the view and then joins result to empListTab (hash join between it and the view). It takes way too long to do this since it has to join 10mil rows from the view with 5 rows in the empListTab and the result will have around 20 rows.
Instead when running it like this with list of values:
select * from myvew where emp_id in ( 1,2,3,10,15 );
It manages to foward the emp_id to lowest subquery of the view and filter the source tables directly (nested loop between source tables and empListTab) and the whole view will work with 20 rows from the start and not milions.
Is there a good solution for solving this? I'm guessing there should be a way Oracle can also forward the subquery to filter the source tables of the view directly?
Thank you.