関数ベースのインデックスを Oracle から Postgre に移行するSQL

作成者: Veeranjaneyulu Grandhi (AWS) と Navakanth Talluri (AWS）

[概要]

インデックスはデータベースのパフォーマンスを向上させる一般的な方法です。インデックスを使用すると、データベースサーバーはインデックスがない場合よりもずっと速く特定の行を検索して取得できます。ただし、インデックスはデータベースシステム全体にオーバーヘッドも発生させるため、適切に使用する必要があります。関数または式に基づく関数ベースのインデックスには、複数の列や数式が含まれる場合があります。関数ベースのインデックスは、インデックス式を使用するクエリのパフォーマンスを向上させます。 

ネイティブでは、PostgreSQL は、ボラティリティが安定として定義されている関数を使用した関数ベースのインデックスの作成をサポートしていません。ただし、IMMUTABLE としてのボラティリティを持つ関数を作成して、インデックスの作成に使用することはできます。

IMMUTABLE 関数はデータベースを変更することはできず、同じ引数が与えられてもずっと同じ結果を返すことが保証されています。このカテゴリにより、クエリが定数引数で関数を呼び出したときに、オプティマイザーは関数を事前評価できます。 

このパターンは、to_char、、 などの関数とともに使用する場合にto_date、Oracle 関数ベースのインデックスto_numberを PostgreSQL の同等の に移行するのに役立ちます。

前提条件と制限

前提条件

制約事項

製品バージョン

アーキテクチャ

ソーステクノロジースタック

ターゲットテクノロジースタック

ツール

エピック

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

ALTER ROLE <ROLENAME> set search_path=$user, COMMON;

関連リソース

追加情報

ラッパー関数を作成する

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;

ラッパー関数を使用してインデックスを作成する

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

関数のボラティリティをチェックする

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;

インデックスが使用可能であることを検証する

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)