CODEWITHSUNDEEP
sqloracle-databasedatabase-partitioning
UltraCommit
UltraCommit

Reputation: 2276

Oracle PL/SQL: SELECT DISTINCT FIELD1, FIELD2 FROM TWO ADIACENT PARTITIONS OF A PARTITIONED TABLE

I have a partitioned table with a field MY_DATE which is always and only the first day of EVERY month from year 1999 to year 2017.

In example, it contains records with 01/01/2015, 01/02/2015, ..... 01/12/2015, such as 01/01/1999, 01/02/1999, and so on.

The field MY_DATE is the partitioning field.

I would like to copy, IN THE MOST EFFICIENT WAY, the distinct values of the field2 and the field3 of two adjacent partitions (month M and month M-1), to another table, in order to find the distinct couple of (field2, field3) of the date overall.

Exchange Partition works only if destination table is not partitioned, but when copying the data of the second, adjacent partition, I receive the error,

"ORA-14099: all rows in table do not qualify for specified partition".

I am using the statement:

ALTER TABLE MY_USER.MY_PARTITIONED_TABLE EXCHANGE PARTITION PART_P201502 WITH TABLE MY_USER.MY_TABLE

Of course MY_PARTITIONED_TABLE and MY_TABLE have the same fields, but the first is partitioned as described above.

Please suppose that MY_PARTITIONED_TABLE is a huge table with about 500 million records.

The goal is to find the different couples of (field2, field3) values of the two adjacent partitions.

My approach was: copy the data of the partition M, copy the data of the partition M-1, and then SELECT DISTINCT FIELD2, FIELD3 from DESTINATION_TABLE.

Thank you very much for considering my request.

Upvotes: 0

Views: 332

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21063

I would like to copy, ...

Please note that EXCHANGE PARTITION performs no copy, but EXCHANGE. I.e. the content of the partition of the big table and the temporary table are switched. If you performs this twice for two different partitions and the same temp table you get exactly the error you received.

To copy (extract the data without changing the big table) you may use

create table tab1 as 
select * from bigtable partition (partition_name1)

create table tab2 as 
select * from bigtable partition (partition_name2)

Your source table is unchanged, after you are ready simple drop the two temp tables. You need only additional space for the two partitions.

Maybe you can event perform your query without copying the data 

with tmp as (
select * from bigtable partition (partition_name1)
union all
select * from bigtable partition (partition_name2)
)
select ....
from tmp;

Good luck!

Upvotes: 1

Related Questions