Migrasikan indeks berbasis fungsi dari Oracle ke PostgreSQL - AWS Prescriptive Guidance
RingkasanPrasyarat dan batasanArsitekturAlatEpikSumber daya terkaitInformasi tambahan

Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.

Migrasikan indeks berbasis fungsi dari Oracle ke PostgreSQL

Veeranjaneyulu Grandhi dan Navakanth Talluri, Amazon Web Services

Ringkasan

Indeks adalah cara umum untuk meningkatkan kinerja database. Indeks memungkinkan server database untuk menemukan dan mengambil baris tertentu jauh lebih cepat daripada yang bisa tanpa indeks. Tetapi indeks juga menambahkan overhead ke sistem database secara keseluruhan, sehingga mereka harus digunakan dengan bijaksana. Indeks berbasis fungsi, yang didasarkan pada fungsi atau ekspresi, dapat melibatkan beberapa kolom dan ekspresi matematika. Indeks berbasis fungsi meningkatkan kinerja kueri yang menggunakan ekspresi indeks. 

Secara native, PostgreSQL tidak mendukung pembuatan indeks berbasis fungsi menggunakan fungsi yang memiliki volatilitas yang didefinisikan sebagai stabil. Namun, Anda dapat membuat fungsi serupa dengan volatilitas IMMUTABLE dan menggunakannya dalam pembuatan indeks.

Sebuah IMMUTABLE fungsi tidak dapat memodifikasi database, dan itu dijamin untuk mengembalikan hasil yang sama diberikan argumen yang sama selamanya. Kategori ini memungkinkan pengoptimal untuk melakukan pra-evaluasi fungsi saat kueri memanggilnya dengan argumen konstan. 

Pola ini membantu dalam memigrasikan indeks berbasis fungsi Oracle saat digunakan dengan fungsi seperti,to_char, to_date dan ke PostgreSQL yang setara. to_number

Prasyarat dan batasan

Prasyarat

  • Akun Amazon Web Services (AWS) yang aktif

  • Sebuah instance database Oracle sumber dengan layanan listener yang disiapkan dan dijalankan

  • Keakraban dengan database PostgreSQL

Batasan

  • Batas ukuran database adalah 64 TB.

  • Fungsi yang digunakan dalam pembuatan indeks harus IMMUTABLE.

Versi produk

  • Semua edisi database Oracle untuk versi 11g (versi 11.2.0.3.v1 dan yang lebih baru) dan hingga 12.2, dan 18c

  • PostgreSQL versi 9.6 dan yang lebih baru

Arsitektur

Tumpukan teknologi sumber

  • Database Oracle di tempat atau di instans Amazon Elastic Compute Cloud EC2 (Amazon), atau Amazon RDS for Oracle DB instans

Tumpukan teknologi target

  • Mesin PostgreSQL apa pun

Alat

  • pgAdmin 4 adalah alat manajemen open source untuk Postgres. Alat pgAdmin 4 menyediakan antarmuka grafis untuk membuat, memelihara, dan menggunakan objek database.

  • Oracle SQL Developer adalah lingkungan pengembangan terintegrasi (IDE) untuk mengembangkan dan mengelola Oracle Database baik dalam penerapan tradisional maupun cloud.

Epik

TugasDeskripsiKeterampilan yang dibutuhkan
Buat indeks berbasis fungsi pada kolom menggunakan fungsi to_char.

Gunakan kode berikut untuk membuat indeks berbasis fungsi.

postgres=# create table funcindex( col1 timestamp without time zone);
CREATE TABLE
postgres=# insert into funcindex values (now());
INSERT 0 1
postgres=# select * from funcindex;
            col1
----------------------------
 2022-08-09 16:00:57.77414
(1 rows)
 
postgres=# create index funcindex_idx on funcindex(to_char(col1,'DD-MM-YYYY HH24:MI:SS'));
ERROR:  functions in index expression must be marked IMMUTABLE

 

catatan

PostgreSQL tidak mengizinkan pembuatan indeks berbasis fungsi tanpa klausa. IMMUTABLE

DBA, Pengembang aplikasi
Periksa volatilitas fungsi.

Untuk memeriksa volatilitas fungsi, gunakan kode di bagian Informasi tambahan. 

DBA
TugasDeskripsiKeterampilan yang dibutuhkan
Buat fungsi pembungkus.

Untuk membuat fungsi pembungkus, gunakan kode di bagian Informasi tambahan.

Pengembang PostgreSQL
Buat indeks dengan menggunakan fungsi pembungkus.

Gunakan kode di bagian Informasi tambahan untuk membuat fungsi yang ditentukan pengguna dengan kata kunci IMMUTABLE dalam skema yang sama dengan aplikasi, dan merujuknya dalam skrip pembuatan indeks.

Jika fungsi yang ditentukan pengguna dibuat dalam skema umum (dari contoh sebelumnya), perbarui seperti yang search_path ditunjukkan.

ALTER ROLE <ROLENAME> set search_path=$user, COMMON;
DBA, pengembang PostgreSQL
TugasDeskripsiKeterampilan yang dibutuhkan
Validasi pembuatan indeks.

Validasi bahwa indeks perlu dibuat, berdasarkan pola akses kueri.

DBA
Validasi bahwa indeks dapat digunakan.

Untuk memeriksa apakah indeks berbasis fungsi diambil oleh PostgreSQL Optimizer, jalankan pernyataan SQL menggunakan menjelaskan atau menjelaskan analisis. Gunakan kode di bagian Informasi tambahan. Jika memungkinkan, kumpulkan juga statistik tabel.

catatan

Jika Anda melihat rencana penjelasan, pengoptimal PostgreSQL telah memilih indeks berbasis fungsi karena kondisi predikat.

DBA

Sumber daya terkait

Informasi tambahan

Buat fungsi pembungkus

CREATE OR REPLACE FUNCTION myschema.to_char(var1 timestamp without time zone, var2 varchar) RETURNS varchar AS $BODY$ select to_char(var1, 'YYYYMMDD'); $BODY$ LANGUAGE sql IMMUTABLE;

Buat indeks dengan menggunakan fungsi pembungkus

postgres=# create function common.to_char(var1 timestamp without time zone, var2 varchar) RETURNS varchar AS $BODY$ select to_char(var1, 'YYYYMMDD'); $BODY$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION
postgres=# create index funcindex_idx on funcindex(common.to_char(col1,'DD-MM-YYYY HH24:MI:SS'));
CREATE INDEX

Periksa volatilitas fungsi

SELECT DISTINCT p.proname as "Name",p.provolatile as "volatility" FROM pg_catalog.pg_proc p
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
 LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
 WHERE n.nspname OPERATOR(pg_catalog.~) '^(pg_catalog)$' COLLATE pg_catalog.default AND p.proname='to_char'GROUP BY p.proname,p.provolatile
ORDER BY 1;

Validasi bahwa indeks dapat digunakan

explain analyze <SQL>
 
 
postgres=# explain select col1 from funcindex where common.to_char(col1,'DD-MM-YYYY HH24:MI:SS') = '09-08-2022 16:00:57';
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Index Scan using funcindex_idx on funcindex  (cost=0.42..8.44 rows=1 width=8)
   Index Cond: ((common.to_char(col1, 'DD-MM-YYYY HH24:MI:SS'::character varying))::text = '09-08-2022 16:00:57'::text)
(2 rows)