PolarDB:TRUNCATE TABLE

Sintaksis

TRUNCATE TABLE table_name;

Parameter

Prasyarat

Untuk memangkas sebuah tabel, Anda harus memenuhi salah satu kondisi berikut:

• Pemilik root partisi

• Anggota kelompok yang memiliki tabel tersebut

• Pemilik skema

• Seorang superuser database

Contoh: mengosongkan tabel partisi

Contoh ini membuat tabel sales yang dipartisi berdasarkan daftar (list-partitioned), memasukkan 17 baris data, memverifikasi datanya, lalu memangkas tabel tersebut.

Create the table:

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST(country)
(
  PARTITION europe   VALUES('FRANCE', 'ITALY'),
  PARTITION asia     VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);

Insert rows:

INSERT INTO sales VALUES
(10, '4519b', 'FRANCE',   '17-Jan-2012', '45000'),
(20, '3788a', 'INDIA',    '01-Mar-2012', '75000'),
(40, '9519b', 'US',       '12-Apr-2012', '145000'),
(20, '3788a', 'PAKISTAN', '04-Jun-2012', '37500'),
(40, '4577b', 'US',       '11-Nov-2012', '25000'),
(30, '7588b', 'CANADA',   '14-Dec-2012', '50000'),
(30, '9519b', 'CANADA',   '01-Feb-2012', '75000'),
(30, '4519b', 'CANADA',   '08-Apr-2012', '120000'),
(40, '3788a', 'US',       '12-May-2012', '4950'),
(10, '9519b', 'ITALY',    '07-Jul-2012', '15000'),
(10, '9519a', 'FRANCE',   '18-Aug-2012', '650000'),
(10, '9519b', 'FRANCE',   '18-Aug-2012', '650000'),
(20, '3788b', 'INDIA',    '21-Sept-2012', '5090'),
(40, '4788a', 'US',       '23-Sept-2012', '4950'),
(40, '4788b', 'US',       '09-Oct-2012', '15000'),
(20, '4519a', 'INDIA',    '18-Oct-2012', '650000'),
(20, '4519b', 'INDIA',    '2-Dec-2012',  '5090');

Verify data is present (ekspresi tableoid::regclass menunjukkan partisi tempat setiap baris berada):

acctg=# SELECT tableoid::regclass, * FROM sales;
   tableoid    | dept_no | part_no | country  |        date        | amount
---------------+---------+---------+----------+--------------------+--------
 sales_europe  |      10 | 4519b   | FRANCE   | 17-JAN-12 00:00:00 |  45000
 sales_europe  |      10 | 9519b   | ITALY    | 07-JUL-12 00:00:00 |  15000
 sales_europe  |      10 | 9519a   | FRANCE   | 18-AUG-12 00:00:00 | 650000
 sales_europe  |      10 | 9519b   | FRANCE   | 18-AUG-12 00:00:00 | 650000
 sales_asia    |      20 | 3788a   | INDIA    | 01-MAR-12 00:00:00 |  75000
 sales_asia    |      20 | 3788a   | PAKISTAN | 04-JUN-12 00:00:00 |  37500
 sales_asia    |      20 | 3788b   | INDIA    | 21-SEP-12 00:00:00 |   5090
 sales_asia    |      20 | 4519a   | INDIA    | 18-OCT-12 00:00:00 | 650000
 sales_asia    |      20 | 4519b   | INDIA    | 02-DEC-12 00:00:00 |   5090
 sales_americas|      40 | 9519b   | US       | 12-APR-12 00:00:00 | 145000
 sales_americas|      40 | 4577b   | US       | 11-NOV-12 00:00:00 |  25000
 sales_americas|      30 | 7588b   | CANADA   | 14-DEC-12 00:00:00 |  50000
 sales_americas|      30 | 9519b   | CANADA   | 01-FEB-12 00:00:00 |  75000
 sales_americas|      30 | 4519b   | CANADA   | 08-APR-12 00:00:00 | 120000
 sales_americas|      40 | 3788a   | US       | 12-MAY-12 00:00:00 |   4950
 sales_americas|      40 | 4788a   | US       | 23-SEP-12 00:00:00 |   4950
 sales_americas|      40 | 4788b   | US       | 09-OCT-12 00:00:00 |  15000
(17 rows)

Truncate the table:

TRUNCATE TABLE sales;

Verify the table is empty (skema tetap utuh, semua baris telah dihapus):

acctg=# SELECT tableoid::regclass, * FROM sales;
 tableoid | dept_no | part_no | country | date | amount
----------+---------+---------+---------+------+--------
(0 rows)