요약 사전 조건 및 제한 사항 아키텍처 도구 에픽 관련 리소스 추가 정보

Oracle에서 Postgre로 함수 기반 인덱스 마이그레이션SQL

작성자: Veeranjaneyulu Grandhi(AWS) 및 Navakanth Talluri(AWS)

환경: 프로덕션	‬소스:: Oracle	대상: PostgreSQL
R 유형: 리아키텍트	워크로드: Oracle	기술: 마이그레이션, 데이터베이스

요약

인덱스는 데이터베이스 성능을 향상시키는 일반적인 방법입니다. 인덱스를 사용하면 데이터베이스 서버가 인덱스가 없을 때보다 훨씬 빠르게 특정 열을 찾고 검색할 수 있습니다. 그러나 인덱스는 데이터베이스 시스템 전체에 오버헤드를 가중시키기도 하므로 현명하게 사용해야 합니다. 함수나 표현식을 기반으로 하는 함수 기반 인덱스에는 여러 열과 수학 표현식이 포함될 수 있습니다. 함수 기반 인덱스는 인덱스 표현식을 사용하는 쿼리의 성능을 개선합니다.

기본적으로 PostgreSQL는 변동성이 안정적인 것으로 정의된 함수를 사용하여 함수 기반 인덱스를 생성하는 것을 지원하지 않습니다. 하지만 IMMUTABLE처럼 변동성이 있는 유사한 함수를 만들어 인덱스 생성에 사용할 수 있습니다.

IMMUTABLE 함수는 데이터베이스를 수정할 수 없으며 동일한 인수가 주어지면 영원히 동일한 결과를 반환할 수 있습니다. 이 범주를 사용하면 쿼리가 상수 인수를 사용하여 함수를 직접적으로 호출할 때 최적화 프로그램이 함수를 미리 평가할 수 있습니다.

이 패턴은 to_char, to_date및 와 같은 함수와 함께 사용할 때 Oracle 함수 기반 인덱스를 PostgreSQL와 동등한 것으로 마이그레이션to_number하는 데 도움이 됩니다.

사전 조건 및 제한 사항

사전 조건

활성 Amazon Web Services(AWS) 계정
리스너 서비스가 설정되어 실행 중인 소스 Oracle 데이터베이스 인스턴스
PostgreSQL 데이터베이스 숙지

제한 사항

데이터베이스 크기 제한은 64TB입니다.
인덱스 생성에 사용되는 함수는 여야 합니다IMMUTABLE.

제품 버전

버전 11g(버전 11.2.0.3.v1 이상) 및 12.2 이하, 18c의 모든 Oracle 데이터베이스 에디션 포함
PostgreSQL 버전 9.6 이상

아키텍처

소스 기술 스택

온프레미스 또는 Amazon Elastic Compute Cloud(AmazonEC2) 인스턴스의 Oracle 데이터베이스 또는 Amazon RDS for Oracle DB 인스턴스

대상 기술 스택

모든 PostgreSQL 엔진

도구

pgAdmin 4는 Postgres용 오픈 소스 관리 도구입니다. pgAdmin 4 도구는 데이터베이스 객체를 생성, 유지 관리 및 사용하기 위한 그래픽 인터페이스를 제공합니다.
Oracle SQL Developer는 기존 배포와 클라우드 배포 모두에서 Oracle Database를 개발하고 관리하기 위한 통합 개발 환경(IDE)입니다.

에픽

작업 설명 필요한 기술

to_char 함수를 사용하여 열에 함수 기반 인덱스를 생성합니다.

작업	설명	필요한 기술
to_char 함수를 사용하여 열에 함수 기반 인덱스를 생성합니다.	다음 코드를 사용하여 함수 기반 인덱스를 생성합니다. `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` 참고: PostgreSQL는 `IMMUTABLE` 절 없이 함수 기반 인덱스를 생성할 수 없습니다.	DBA, 앱 개발자
함수의 변동성을 확인합니다.	함수 변동성을 확인하려면 추가 정보 섹션의 코드를 사용합니다.	DBA

다음 코드를 사용하여 함수 기반 인덱스를 생성합니다.


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

참고: PostgreSQL는 IMMUTABLE 절 없이 함수 기반 인덱스를 생성할 수 없습니다.

DBA, 앱 개발자

함수의 변동성을 확인합니다.

함수 변동성을 확인하려면 추가 정보 섹션의 코드를 사용합니다.

DBA

작업 설명 필요한 기술

래퍼 함수를 생성합니다.

작업	설명	필요한 기술
래퍼 함수를 생성합니다.	래퍼 함수를 생성하려면 추가 정보 섹션의 코드를 사용합니다.	PostgreSQL 개발자
래퍼 함수를 사용하여 인덱스를 생성합니다.	추가 정보 섹션의 코드를 사용하여 애플리케이션과 동일한 스키마에서 키워드 `IMMUTABLE`이 포함된 사용자 정의 함수를 만들고 인덱스 생성 스크립트에서 해당 함수를 참조할 수 있습니다. 이전 예제의 공통 스키마에서 사용자 정의 함수를 만든 경우 `search_path`를 그림과 같이 업데이트합니다. `ALTER ROLE <ROLENAME> set search_path=$user, COMMON;`	DBA, PostgreSQL 개발자

래퍼 함수를 생성하려면 추가 정보 섹션의 코드를 사용합니다.

PostgreSQL 개발자

래퍼 함수를 사용하여 인덱스를 생성합니다.

추가 정보 섹션의 코드를 사용하여 애플리케이션과 동일한 스키마에서 키워드 IMMUTABLE이 포함된 사용자 정의 함수를 만들고 인덱스 생성 스크립트에서 해당 함수를 참조할 수 있습니다.

이전 예제의 공통 스키마에서 사용자 정의 함수를 만든 경우 search_path를 그림과 같이 업데이트합니다.


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

DBA, PostgreSQL 개발자

작업	설명	필요한 기술
인덱스 생성 확인.	쿼리 액세스 패턴을 기반으로 인덱스 생성이 필요한지 확인합니다.	DBA
인덱스를 사용할 수 있는지 확인합니다.	PostgreSQL Optimizer에서 함수 기반 인덱스를 픽업하는지 확인하려면 설명 또는 설명 분석을 사용하여 SQL 문을 실행합니다. 추가 정보 섹션의 코드를 사용합니다. 가능하면 테이블 통계도 수집합니다. 참고: 설명 계획을 발견하면 PostgreSQL 최적화 프로그램이 조건부로 인해 함수 기반 인덱스를 선택했습니다.	DBA

작업

설명

필요한 기술

인덱스 생성 확인.

쿼리 액세스 패턴을 기반으로 인덱스 생성이 필요한지 확인합니다.

DBA

인덱스를 사용할 수 있는지 확인합니다.

PostgreSQL Optimizer에서 함수 기반 인덱스를 픽업하는지 확인하려면 설명 또는 설명 분석을 사용하여 SQL 문을 실행합니다. 추가 정보 섹션의 코드를 사용합니다. 가능하면 테이블 통계도 수집합니다.

참고: 설명 계획을 발견하면 PostgreSQL 최적화 프로그램이 조건부로 인해 함수 기반 인덱스를 선택했습니다.

DBA

추가 정보

래퍼 함수 생성


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)

javascript가 브라우저에서 비활성화되거나 사용이 불가합니다.

AWS 설명서를 사용하려면 Javascript가 활성화되어야 합니다. 지침을 보려면 브라우저의 도움말 페이지를 참조하십시오.

문서 규칙

Oracle 외부 테이블을 Amazon Aurora로 마이그레이션

Oracle 네이티브 함수를 Postgre로 마이그레이션SQL