Force partition pruning on Oracle

Question

I have a query similar to this

select *
from small_table A
inner join huge_table B on A.DATE =B.DATE

The huge_table is partitioned by DATE, and the PK is DATE, some_id and some_other_id (so the join not is done by pk index). small_table just contains a few dates.

The total cost of the SQL is 48 minutes

By some reason the explain plan give me a "PARTITION RANGE (ALL)" with a high numbers on cardinality. Looks like access to the full table, not just the partitions indicated by small_table.DATE

If I put the SQL inside a loop and do

for o in (select date from small_table)
loop
    select *
    from small_table A
    inner join huge_table B on A.DATE =B.DATE
    where B.DATE=O.DATE
end loop;

Only takes 2 minutes 40 seconds (the full loop). There is any way to force the partition pruning on Oracle 12c?

Additional info:

small_table has 37 records for 13 different dates. huge_table has 8,000 million of records with 179 dates/partitions. The SQL needs one field from small_table, but I can tweak the SQL to not use it

Update:

With the use_nl hint, now the cardinality show in the execution plan is more accurate and the execution time downs from 48 minutes to 4 minutes.

select /* use_nl(B) */*
from small_table A
inner join huge_table B on A.DATE =B.DATE

Answer 1

This seems like the problem:

"small_table have 37 registries for 13 different dates. huge_table has 8.000 millions of registries with 179 dates/partitions.... The SQL need one field from small_table, but I can tweak the SQL to not use it "

According to the SQL you posted you're joining the two tables on just their DATE columns with no additional conditions. If that's really the case you are generating a cross join in which each partition of huge_table is joined to small_table 2-3 times. So your result set may be much large than you're expecting, which means more database effort, which means more time.

The other thing to notice is that the cardinality of small_table to huge_table partitions is about 1:4; the optimizer doesn't know that there are really only thirteen distinct huge_table partitions in play.

Optimization ought to be a science and this is more guesswork than anything but try this:

select B.*
from ( select /*+ cardinality(t 13) */
             distinct t.date
        from small_table t ) A
inner join huge_table B 
    on A.DATE =B.DATE

This should communicate to the optimizer that only a small percentage of the huge_table partitions are required, which may make it choose partition pruning. Also it removes that Cartesian product, which should improve performance too. Obviously you will need to apply that tweak you mentioned, to remove the need to query anything else from small_table.