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.
User, you are posting this in the wrong forum. This forum is not for product related questions as the title clearly tells you.
Please search the forum for your product and ask your question there.
You may want to ask this in the SQL & PL/SQL space if you are using an Oracle DB.
Locking this thread. It will be removed in the near future.