Page 1 of 1
How to DROP more than one PARTITIONS at a time
Posted: Fri Oct 14, 2016 6:51 pm
by sasikari
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
Re: How to DROP more than one PARTITIONS at a time
Posted: Mon Oct 17, 2016 3:12 pm
by JimKnicely
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:
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)
But this could potentially be a lot slower than just dropping the partitions one at a time...
Re: How to DROP more than one PARTITIONS at a time
Posted: Tue Oct 18, 2016 2:47 pm
by sasikari
Thank You Jim Knicely!
Re: How to DROP more than one PARTITIONS at a time
Posted: Fri Feb 09, 2018 2:08 pm
by kenankule
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