Skip to main content
Question

Is there a WHERE predicate to filter exactly all rows with the same PARTITION KEY ?

  • February 14, 2022
  • 0 replies
  • 3 views

marcothesane
Forum|alt.badge.img+1

To perform some clever DML involving SWAP_PARTITIONS_BETWEEN_TABLES(), I'd like to be able to filter out all rows with a given partition key from a partitioned table, starting from an input date.

With PARTITION BY YEAR(sls_date) * 100 + MONTH(sls_date), and the partition affected by the date of today, it's easy:
WHERE sls_date BETWEEN TRUNC(CURRENT_DATE,'MONTH')::DATE AND ADD_MONTHS(TRUNC(CURRENT_DATE,'MONTH'),1)-1.

But what do I do with PARTITION BY YEAR(sls_date) * 100 + WEEK(sls_date)?

Or other fancy partitioning expressions based on the element of time?

Is there some way of saying:
WHERE partition_key = YEAR(sls_date) * 100 + MONTH(sls_date) to get all rows in the same partition as the date I supply?