기계 번역으로 제공되는 번역입니다. 제공된 번역과 원본 영어의 내용이 상충하는 경우에는 영어 버전이 우선합니다.

# 함수 기반 인덱스를 Oracle에서 PostgreSQL로 마이그레이션
<a name="migrate-function-based-indexes-from-oracle-to-postgresql"></a>

*Veeranjaneyulu Grandhi, Navakanth Talluri, Amazon Web Services*

## 요약
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-summary"></a>

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

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

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

이 패턴은 `to_char`, `to_date` 및 `to_number`와 같은 함수와 함께 사용할 경우 Oracle 함수 기반 인덱스를 해당하는 PostgreSQL로 마이그레이션하는 데 도움이 됩니다.

## 사전 조건 및 제한 사항
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-prereqs"></a>

**사전 조건 **
+ 활성 Amazon Web Services(AWS) 계정
+ 리스너 서비스가 설정되어 실행 중인 소스 Oracle 데이터베이스 인스턴스
+ PostgreSQL 데이터베이스에 대한 지식

**제한 사항 **
+ 데이터베이스 크기 제한은 64TB입니다.
+ 인덱스 생성에 사용되는 함수는 변경할 수 없어야 합니다.

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

## 아키텍처
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-architecture"></a>

**소스 기술 스택**
+ Oracle 데이터베이스 온프레미스 또는 Amazon Elastic Compute Cloud(Amazon EC2) 인스턴스 또는 Amazon RDS for Oracle DB 인스턴스

**대상 기술 스택**
+ 모든 PostgreSQL 엔진

## 도구
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-tools"></a>
+ **pgAdmin 4**는 Postgres를 위한 오픈 소스 관리 도구입니다. pgAdmin 4 도구는 데이터베이스 객체를 생성, 유지 관리 및 사용하기 위한 그래픽 인터페이스를 제공합니다.
+ **Oracle SQL Developer**는 기존 배포와 클라우드 배포 모두에서 Oracle Database를 개발하고 관리하기 위한 통합 개발 환경(IDE)입니다.

## 에픽
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-epics"></a>

### 기본 함수를 사용하여 함수 기반 인덱스 생성
<a name="create-a-function-based-index-using-a-default-function"></a>


| 작업 | 설명 | 필요한 기술 | 
| --- | --- | --- | 
| to\_char 함수를 사용하여 열에 함수 기반 인덱스를 생성합니다. | 다음 코드를 사용하여 함수 기반 인덱스를 생성합니다.<pre>postgres=# create table funcindex( col1 timestamp without time zone);<br />CREATE TABLE<br />postgres=# insert into funcindex values (now());<br />INSERT 0 1<br />postgres=# select * from funcindex;<br />            col1<br />----------------------------<br /> 2022-08-09 16:00:57.77414<br />(1 rows)<br /> <br />postgres=# create index funcindex_idx on funcindex(to_char(col1,'DD-MM-YYYY HH24:MI:SS'));<br />ERROR:  functions in index expression must be marked IMMUTABLE</pre><br /> PostgreSQL에서는 절이 없는 함수 기반 인덱스를 생성할 수 없습니다. | DBA, 앱 개발자 | 
| 함수의 변동성을 확인합니다. | 함수 변동성을 확인하려면 *추가 정보* 섹션의 코드를 사용합니다.   | DBA | 

### 래퍼 함수를 사용하여 함수 기반 인덱스 생성
<a name="create-function-based-indexes-using-a-wrapper-function"></a>


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

### 인덱스 생성 확인
<a name="validate-index-creation"></a>


| 작업 | 설명 | 필요한 기술 | 
| --- | --- | --- | 
| 인덱스 생성 확인. | 쿼리 액세스 패턴을 기반으로 인덱스 생성이 필요한지 확인합니다. | DBA | 
| 인덱스를 사용할 수 있는지 확인합니다. | PostgreSQL Optimizer에서 함수 기반 인덱스를 선택했는지 확인하려면 설명 또는 설명 분석을 사용하여 SQL 명령문을 실행합니다. *추가 정보* 섹션의 코드를 사용합니다. 가능하면 테이블 통계도 수집합니다.설명 계획을 보면 알 수 있듯이 PostgreSQL 옵티마이저가 조건자 조건 때문에 함수 기반 인덱스를 선택한 것입니다. | DBA | 

## 관련 리소스
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-resources"></a>
+ [함수 기반 인덱스](https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_indexes.htm#ADFNS00505)(오라클 설명서)
+ [표현식에 대한 인덱스](https://www.postgresql.org/docs/9.4/indexes-expressional.html)(PostgreSQL 설명서)
+ [PostgreSQL 변동성](https://www.postgresql.org/docs/current/xfunc-volatility.html)(PostgreSQL 설명서)
+ [PostgreSQL search\_path(PostgreSQL 설명서)](https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH)
+ [Oracle Database 19c - Amazon Aurora PostgreSQL Migration Playbook](https://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.html) 

## 추가 정보
<a name="migrate-function-based-indexes-from-oracle-to-postgresql-additional"></a>

**래퍼 함수 생성**

```
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)
```