Hi All,
I need a help, I have a query based a 2 tables which has millions of records, and 2 major filter conditions. Table A based on employeeID and Table B on date range. This is a OLTP query so I need it respond as quick as possible.
select id from tableA A, tableB B where A.pkid=B.fkid and A.empid= 123 and b.createDate between sysdate-30 and sysdate;
I always look for last 30 days data. so if i keep tableA as driving data I will have about 40-60k Records for each employee for last 10 years of data on the other hand tableB will have about 1-1.5Million records for last 30 days of data. both filters applied will fetch me around 1k-2k records, where I'm suppose to show only the latest records (order by desc on B.createdate).
And this query keeps running thru out the day on business hours.
My question is should I consider TableA as driving table or TableB as driving table?
Considering tableA as driving table, I see number of records processed for each query is low but increase on the physical read for the different employees queried.
Considering tableB as driving table, I see the number of records processed are high but the last 30 days data will be in buffer reducing the physical IO.
Please help!