PolarDB：ALTER TABLE...ADD PARTITION

Cara kerjanya

ALTER TABLE ... ADD PARTITION adalah operasi metadata, tetapi dampak performa dan konkurensinya terutama ditentukan oleh konfigurasi indeks tabel.

• Perilaku Kunci: Saat menjalankan ADD PARTITION, database memperoleh kunci eksklusif pada tabel target. Kunci ini memblokir semua operasi baca dan tulis bersamaan (SELECT, INSERT, UPDATE, dan DELETE) pada tabel. Durasi kunci tergantung pada waktu yang diperlukan untuk membuat indeks pada partisi baru.

• Pemeliharaan Indeks

  • Indeks Lokal: Perintah ini secara otomatis membuat partisi indeks yang sesuai pada partisi baru. Jika tabel memiliki beberapa indeks lokal, sistem akan membuat partisi baru untuk masing-masing. Inilah alasan utama durasi operasi.

  • Indeks Global: Struktur tetap tidak berubah karena mencakup data yang dimasukkan ke dalam partisi baru tanpa pemeliharaan tambahan.

• Durasi Operasi

  • Tabel Tanpa Indeks: Operasi hampir instan karena hanya memperbarui metadata tabel.

  • Tabel Dengan Indeks: Durasi operasi sebanding dengan waktu yang diperlukan untuk membuat indeks lokal pada partisi baru yang kosong.

Keterbatasan

• Aturan Definisi Dasar

  1. Konsistensi Tipe Partisi: Partisi baru harus sesuai dengan tipe partisi yang ada pada tabel (LIST atau RANGE).

  2. Konsistensi Kunci Partisi: Aturan partisi untuk partisi baru harus merujuk pada kolom kunci partisi yang sama seperti yang didefinisikan untuk tabel.

  3. Nama Partisi Unik: Nama partisi baru harus unik di antara semua partisi dan subpartisi tabel.

• Pembatasan Nilai Partisi

  • MAXVALUE dan DEFAULT Partisi

    • Batasan: Anda tidak dapat menambahkan partisi baru ke tabel partisi rentang yang memiliki partisi MAXVALUE atau ke tabel partisi daftar yang memiliki partisi DEFAULT. Hal ini karena partisi MAXVALUE dan DEFAULT secara logis mencakup semua nilai yang tidak ditentukan, sehingga tidak ada ruang untuk partisi baru.

    • Solusi: Gunakan ALTER TABLE ... SPLIT PARTITION untuk membagi partisi MAXVALUE atau DEFAULT. Ini menciptakan batas baru, memungkinkan Anda menambahkan partisi baru.

    • Catatan

      Operasi ini dapat memindahkan data, yang dapat menghasilkan overhead I/O dan kunci yang signifikan. Lakukan operasi ini selama jam non-puncak atau dalam jendela pemeliharaan.

      -- Contoh: Pisahkan partisi MAXVALUE untuk menambahkan partisi untuk tahun 2024
      ALTER TABLE sales SPLIT PARTITION max_partition AT (TO_DATE('2025-01-01', 'YYYY-MM-DD')) INTO (PARTITION p_2024, PARTITION max_partition);

  • RANGE Persyaratan Urutan Partisi Untuk partisi RANGE baru, nilai VALUES LESS THAN harus lebih besar dari batas atas partisi tertinggi yang ada. Jika tidak, operasi gagal.

  • LIST Nilai Unik Partisi

    Nilai partisi LIST baru tidak boleh tumpang tindih dengan yang ada di partisi lainnya. Jika tidak, operasi gagal dengan kesalahan seperti ERROR: partition "xx" would overlap partition "xxx".

• Persyaratan Hak Istimewa

  Untuk menjalankan ALTER TABLE ... ADD PARTITION, Anda harus menjadi pemilik tabel atau menggunakan akun istimewa.

Praktik terbaik

• Jalankan dalam Jendela Pemeliharaan: Karena ADD PARTITION memperoleh kunci eksklusif pada tabel, hal ini memblokir operasi DML dan DDL. Untuk tabel besar dengan indeks lokal, proses pembuatan indeks bisa memakan waktu lama, menyebabkan gangguan layanan yang berkepanjangan. Kami merekomendasikan melakukan operasi ini selama jam non-puncak atau jendela pemeliharaan terjadwal untuk menghindari pemblokiran layanan online Anda.

• Monitor Tunggu Kunci: Selama operasi, pantau database untuk tunggu kunci. Jika waktu tunggu kunci berlebihan, Anda mungkin perlu menghentikan operasi dan menjadwalkannya ulang.

• Jumlah Partisi yang Direkomendasikan: Meskipun tidak ada batasan fisik pada jumlah partisi yang dapat dimiliki sebuah tabel, kami merekomendasikan menjaga total jumlah partisi untuk satu tabel di bawah 1.000 untuk alasan manajemen dan performa. Jumlah partisi yang berlebihan meningkatkan biaya penguraian bagi Pengoptimal Kueri, yang dapat menurunkan performa kueri.

Sintaksis

ALTER TABLE nama_tabel ADD PARTITION spesifikasi_partisi;

Tambahkan partisi ke tabel partisi daftar

Gunakan operasi ini untuk menambahkan kategori data baru ke tabel yang dipartisi berdasarkan daftar nilai diskrit, seperti negara atau kode status.

1. Buat tabel partisi daftar contoh. Tabel sales_list berikut dipartisi berdasarkan kolom country.

   CREATE TABLE sales_list (
       dept_no     NUMBER,
       part_no     VARCHAR2(50),
       country     VARCHAR2(20),
       sale_date   DATE,
       amount      NUMBER
   )
   PARTITION BY LIST(country) (
       PARTITION europe VALUES ('FRANCE', 'ITALY'),
       PARTITION asia VALUES ('INDIA', 'PAKISTAN'),
       PARTITION americas VALUES ('US', 'CANADA')
   );

2. Gunakan perintah ALTER TABLE ... ADD PARTITION untuk menambahkan partisi baru bernama east_asia untuk menyimpan data untuk 'CHINA' dan 'KOREA'.

   ALTER TABLE sales_list ADD PARTITION east_asia VALUES ('CHINA', 'KOREA');

3. (Opsional) Verifikasi bahwa partisi baru berhasil ditambahkan.

   SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS WHERE table_name = 'sales_list';

   Hasil kueri harus mencakup partisi east_asia yang baru ditambahkan.

Tambahkan partisi ke tabel partisi rentang

Gunakan operasi ini untuk menambahkan periode waktu baru atau rentang numerik ke tabel yang dipartisi berdasarkan rentang nilai kontinu, seperti tanggal atau ID.

1. Buat tabel partisi rentang contoh. Tabel sales_range berikut dipartisi berdasarkan kolom sale_date.

   CREATE TABLE sales_range (
       dept_no     NUMBER,
       part_no     VARCHAR2(50),
       country     VARCHAR2(20),
       sale_date   DATE,
       amount      NUMBER
   )
   PARTITION BY RANGE(sale_date) (
       PARTITION q1_2023 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
       PARTITION q2_2023 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
       PARTITION q3_2023 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
       PARTITION q4_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
   );

   Catatan

   Contoh ini menggunakan fungsi TO_DATE untuk memastikan kejelasan format tanggal. Dalam praktiknya, format string harus sesuai dengan pengaturan NLS_DATE_FORMAT database.

2. Gunakan ALTER TABLE ... ADD PARTITION untuk menambahkan partisi baru bernama q1_2024. Rentang partisi baru harus lebih besar dari batas atas semua partisi yang ada.

   ALTER TABLE sales_range ADD PARTITION q1_2024 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD'));

3. (Opsional) Verifikasi bahwa partisi baru berhasil ditambahkan.

   SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS WHERE table_name = 'sales_range';

   Hasil kueri harus mencakup partisi q1_2024 yang baru ditambahkan di akhir daftar partisi.

Tambahkan partisi ke tabel partisi komposit

Untuk tabel partisi komposit, seperti RANGE-LIST, ADD PARTITION memungkinkan Anda mendefinisikan subpartisi pada saat yang sama saat Anda menambahkan partisi utama.

1. Buat tabel partisi komposit RANGE-LIST contoh.

   CREATE TABLE composite_sales (
       sale_id     NUMBER,
       sale_date   DATE,
       region      VARCHAR2(20)
   )
   PARTITION BY RANGE(sale_date)
   SUBPARTITION BY LIST(region) (
       PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) (
           SUBPARTITION p_2023_north VALUES ('NORTH'),
           SUBPARTITION p_2023_south VALUES ('SOUTH')
       )
   );

2. Tambahkan partisi utama baru bernama p_2024 dan definisikan dua subpartisi: north dan south.

   ALTER TABLE composite_sales ADD PARTITION p_2024
       VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) (
           SUBPARTITION p_2024_north VALUES ('NORTH'),
           SUBPARTITION p_2024_south VALUES ('SOUTH')
       );

3. (Opsional) Verifikasi bahwa subpartisi baru berhasil dibuat.

   SELECT nama_partisi, nama_subpartisi, high_value FROM ALL_TAB_SUBPARTITIONS WHERE nama_tabel = 'composite_sales' AND nama_partisi = 'p_2024';

FAQ