How does Oracle estimate cardinality on a partitioned table if the query gets the list of partitions from a subquery or a join?

For example, if I have a query like this

select * from partitioned_table 
   join list_of_partitions using (part_key)

Assume list_of_partitions is a small table (single row per partition key)

What estimate gets used for partitioned_table?

Since Oracle doesn't know exactly what partitions are accessed when the query is parsed, where would the estimate come from?

  • Your question makes no sense. Everything depends on how the table is partitioned and whether the indexes are local are global.
    – Philᵀᴹ
    Oct 10 '14 at 21:37
  • Agreed with Phil - the optimizer has a lot of tricks to choose from for this, including magical bloom-filter based partition join filters... and a lot of stats to choose from too if you have partition-level and global stats for the table and indexes.
    – Mat
    Oct 11 '14 at 6:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.