I would also like to know this! I haven't tried this sort of query yet, but I know I will run this exact formula of query very often.
On Fri, May 27, 2011 at 2:32 PM, Shantian Purkad <[email protected]>wrote: > 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 > -- Tim Riot Games
