I have to fetch large amount of data from Table A. From which i have to fetch large amount of data from Table B and Table C (in millions).
For ex, my query will be like this:
With tbl_A as (select emp_id from Table_A where emp_id between 1 and 999999999)
select * from Table_B b, tbl_A a where b.emp_id=a.emp_id
select * from Table_C c, tbl_A a where c.emp_id=a.emp_id
emp_id is primary key in Table_A and it is indexed column in Table_B and Table_C.
Let us consider that there is only one record in Table_A with emp_id as '100' and Table_B & Table_C has large set of data but only 4 or 5 records with emp_id '100'.
While executing the above query it takes a lot of time (no output even after 30 mins or so).
But if the query is re-structured as given below, it ran only 200 msec and got the output.
With tbl_A1 as (select emp_id from Table_A),
tbl_A2 as (select emp_id from Table_A)
select * from Table_B b, tbl_A1 a where b.emp_id=a.emp_id
select * from Table_C c, tbl_A2 a where c.emp_id=a.emp_id
As for real scenario, I have to fetch large amount of emp_id from Table_A. So, I dont want to use the Table_A twice as it may have performance issue or using of large buffer cache.
Please provide your valuable suggestions to help me out.
Note: From the result set, I have to perform some more operations as well.