Query the exact partition when table is hash-partitioned

Question

When I want to query a single partition I usually use something like that:

Select * from t (partition p1)

But when you have to query it in your pl/sql code it comes to using execute immediate and hard-parse of the statement.

Okay, for RANGE-partitioned table (let it be SOME_DATE of date type) I can workaround it like

Select * from t where some_date <= :1 and some_date > :2

Assuming :1 and :2 stand for partition bonds.

Well, as for LIST-partitioned table I can easily specify the exact value of my partition key field like

Select * from t where part_key = 'X'

And what about HASH-partitioning? For example, I have a table partitioned by hash(id) in 16 partitions. And I have 16 jobs each handling its own partition. So I have to use it like that

Select * from t (partition p<n>)

Question is: can I do it like this for example

Select * from t where hash(id) = :1

To enforce partition pruning take the whole n-th partition?

It's okay when you have just 16 partitions but in my case I have composite partitioning (date + hash(id)), so every time job handles a partition it's always a new sql_id and it ends up in quick shared pool growth

I'm not clear what the issue is. where part_key = 'X' and subpart_key = 'Pie' will take you to the exact subpartition. Or you could use subpartition for syntax but then you are back to hard parsing. — William Robertson
Keep in mind that partitioning is intended to help queries that process a large percentage of rows from a table, whereas indexes are intended to help queries that process a small percentage of rows. If you're worried about filling up the shared pool with too many queries, that implies you're using partitions to process small amounts of data. You might want to consider an index strategy instead. Or, if the overall process is working on most of the data, try to combine queries and process a lot of data at one time. — Jon Heller

Justin Cave Justin Cave · Accepted Answer · 2019-10-24T21:41:55

It appears Oracle internally uses the ora_hash function (at least since 10g) to assign a value to a partition. So you could use that to read all the data from a single partition. Unfortunately, though, since you'd be running a query like

select *
  from t
 where ora_hash( id, 9 ) = 6

to get all the data in the 6th of 8 hash partitions, I'd expect Oracle to have to read every partition in the table (and compute the hash on every id) because the optimizer isn't going to be smart enough to recognize that your expression happens to map exactly to its internal partitioning strategy. So I don't think you'd want to do this to split data up to be processed by different threads.

Depending on what those threads are doing, would it be possible to use Oracle's built-in parallelism instead (potentially incorporating things like parallelizable pipelined table functions if you're doing ETL processing). If you tell Oracle to use 16 parallel threads and your table has 16 partitions, Oracle will internally almost certainly do the right thing.

Query the exact partition when table is hash-partitioned

1 Answers