We have a query which looks something like this select /*+ MAPJOIN(a) */ * from tableA a left outer join tableB b on (a.id = b.id and a.dt = b.dt )
TableB is partitioned on dt We found that the query is always doing full table scan on tableB no matter what dates the records in tableA have. Table A had only one record so we expected that only one partition of tableB will be scanned. When we change the query to below (where we somehow figure out the dates in tableA) select /*+ MAPJOIN(a) */ * from tableA a left outer join tableB b on (a.id = b.id and b.dt ='2011-05-04' ) Then it scans only one partition on the tableB Can someone suggest how can we gate the first query scan only the necessary partitions without having to explicitly specify the partitions in the query. Thanks and Regards, Shantian
