CODEWITHSUNDEEP
sqloracle-databasepartition
Llama
Llama

Reputation: 71

Select proper partition

I would like to join two according partitions, but they have different names. Both are partitioned by date. For example I have partition 'PARTITION_20161231' in table A (with rows with date between 1.12.2016 and 31.12.2016) and interval partition SYS_XXXXXX in table B. How can I get proper partition name from table B to join it with partition from table A (i mean partition with same date range as partition from table A has).

FOR part IN (SELECT partition_name FROM dba_tab_partitions WHERE table_name = 'TABLE_A')
  LOOP
    SELECT /*+ parallel(8) */
           a.rowid
           b.attribute
      FROM table_a PARTITION(part.partition_name) 
      LEFT JOIN table_b PARTITION(#PARTITION#) b
        ON b.id = a.id
  END LOOP;

It should be merge over partitions, here is only simplified example. The question is, how do I get #PARTITION#?

Upvotes: 1

Views: 117

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

To query a particular partition its not necessary to know its name. partition for () clause allows you to reference a partition by partition key. For example a query similar to the below one

select * 
  from table_a  --or table_b
 partition for (date '2016-12-01')

will return all data from a partition the date '2016-12-01' partition key belongs to.

Here is another example:

create table t2(
 c1 date
)
partition by range (c1)
interval (interval '1' month) (
  partition part_1 values less than (date '2017-01-01')
);

insert into t2 values (date '2016-12-01');
insert into t2 values (date '2016-12-31');

commit;

select *
  from t2 partition for (date '2016-12-01');

C1       
---------
01-DEC-16
31-DEC-16

Upvotes: 3

Related Questions