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