Hash join with partition limit from third table

My current problem is in 11g, but I'm also interested in how to solve this more intelligently in future releases.

I want to join two tables. Table A has 10 million rows, and Table B is huge, with a billion records on about a thousand partitions. A partition has about 10 million records. I didn't join the partition key. For most rows in table A, one or more rows will be found in table B. example:

select * from table_a a
inner join table_b b on a.ref = b.ref

The above will return about 50 million rows, and the results are from about 30 partitions of table b. I'm assuming the hash join is the correct join, hash table a and FTSing/index scan table b.

So 970 partitions were scanned for no reason. Also, I have a third query that tells oracle which 30 partitions to check for a join. Third query example:

select partition_id from table_c

This query gives exactly 30 partitions for the above query.

my question:

In PL/SQL, this problem can be solved by

1. select 30 partition_ids into a variable (it's just aselect listagg(partition_id,',') ... into v_partitions from table_c

2. Execute my query like this:

   execute immediate 'select * from table_a a 
   inner join table_b b on a.ref = b.ref 
   where b.partition_id in ('||v_partitions||')' into ...
   

Let's say this is done in 10 minutes.

Now, how can this be done in the same amount of time using pure SQL ?

just simply write

select * from table_a a
inner join table_b b on a.ref = b.ref 
where b.partition_id in (select partition_id from table_c)

It doesn't seem to do this, or maybe I'm aiming for the wrong plan.

I think the plan I want is

hash join
    table a
    nested loop
       table c
       partition pruning here
           table b

However, this will not recover within 10 minutes.

So, how to do this in SQL and for what execution plan? A variant that I haven't tried that might be a solution is

nested loop
   table c
   hash join
       table a
       partition pruning here (pushed predicate from the join to c)
            table b

My other feeling is that the solution might lie in joining table a to table c (but not sure what) and then joining this result to table b.

I'm not asking you to type everything out for me. Just a general idea of ​​how to do this in SQL (get the partition limit from the query) - what should I plan against?

thank you very much! Peter