How to DROP more than one PARTITIONS at a time

Moderator: NorbertKrupa

Post Reply
sasikari
Newbie
Newbie
Posts: 4
Joined: Tue Oct 23, 2012 10:28 am

How to DROP more than one PARTITIONS at a time

Post by sasikari » Fri Oct 14, 2016 6:51 pm

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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1804
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to DROP more than one PARTITIONS at a time

Post by JimKnicely » Mon Oct 17, 2016 3:12 pm

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...
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

sasikari
Newbie
Newbie
Posts: 4
Joined: Tue Oct 23, 2012 10:28 am

Re: How to DROP more than one PARTITIONS at a time

Post by sasikari » Tue Oct 18, 2016 2:47 pm

Thank You Jim Knicely!

kenankule
Newbie
Newbie
Posts: 1
Joined: Fri Feb 09, 2018 2:03 pm

Re: How to DROP more than one PARTITIONS at a time

Post by kenankule » Fri Feb 09, 2018 2:08 pm

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

Post Reply

Return to “Vertica SQL”