Hi ,
Is there any way to DROP more than one PARTITIONS at a time.
I know that we can drop the partition using DROP_PARTITION function, but we can drop one partition at a time and not able to drop more than one partition at a time.
Or is there any way that we can DROP PARTITONS which is greater than any existing PARTITION?
Regards,
SasiKari
How to DROP more than one PARTITIONS at a time
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: How to DROP more than one PARTITIONS at a time
Hi,
The DROP_PARTITION function "Removes ONE partition from a partitioned table. Each partition contains a related subset of data in the table. Partitioned data can be dropped efficiently, and provides query performance benefits."
Documentation:
https://my.vertica.com/docs/8.0.x/HTML/ ... ngData.htm
So you will have to drop them one at a time.
Although, if you plan to drop partitions in a range, you could move those partitions to another table and then drop that table.
Example:
But this could potentially be a lot slower than just dropping the partitions one at a time...
The DROP_PARTITION function "Removes ONE partition from a partitioned table. Each partition contains a related subset of data in the table. Partitioned data can be dropped efficiently, and provides query performance benefits."
Documentation:
https://my.vertica.com/docs/8.0.x/HTML/ ... ngData.htm
So you will have to drop them one at a time.
Although, if you plan to drop partitions in a range, you could move those partitions to another table and then drop that table.
Example:
Code: Select all
dbadmin=> create table test (c1 int not null) partition by c1;
CREATE TABLE
dbadmin=> insert into test values (1);
OUTPUT
--------
1
(1 row)
dbadmin=> insert into test values (2);
OUTPUT
--------
1
(1 row)
dbadmin=> insert into test values (3);
OUTPUT
--------
1
(1 row)
dbadmin=> insert into test values (4);
OUTPUT
--------
1
(1 row)
dbadmin=> commit;
COMMIT
dbadmin=> select * from test;
c1
----
1
2
3
4
(4 rows)
dbadmin=> create table test2 like test including projections;
CREATE TABLE
dbadmin=> select move_partitions_to_table('test', 2, 3, 'test2');
move_partitions_to_table
--------------------------------------------------
2 distinct partition values moved at epoch 222.
(1 row)
dbadmin=> select * from test2;
c1
----
3
2
(2 rows)
dbadmin=> drop table test2;
DROP TABLE
dbadmin=> select * from test;
c1
----
4
1
(2 rows)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: How to DROP more than one PARTITIONS at a time
Thank You Jim Knicely!
Re: How to DROP more than one PARTITIONS at a time
DROP_PARTITIONS function, which allows you to drop mor than one partition at a time, is introduced in Vertica 9.0. Check : https://my.vertica.com/docs/9.0.x/HTML/ ... nality.htm