Oracle에서 SQL Server로 및 SQL Server에서 Oracle로 마이그레이션 가이드 - Pt. 2
게시 됨: 2022-03-11이 시리즈의 첫 번째 부분에서는 Oracle에서 SQL Server로 또는 그 반대로 마이그레이션하는 동안 발생할 수 있는 함정에 중점을 두고 트랜잭션 구현에서 Oracle Database와 Microsoft SQL Server의 차이점에 대해 설명했습니다. 이 다음 기사에서는 일치 항목이 없거나 Oracle–SQL Server 구분에서 완전히 다른 의미 또는 사용법을 갖는 일반적으로 사용되는 여러 SQL 구문 요소를 다룹니다.
Oracle의 시퀀스 및 SQL Server의 ID 열
데이터베이스 커뮤니티에는 두 진영, 즉 자연 키의 애국자와 인공(또는 "대리") 키 지지자 사이에 오랫동안 분열이 있었습니다.
나 자신은 자연 키를 방어하지만 종종 이런저런 이유로 대리자를 생성하는 자신을 발견합니다. 그러나 이 논쟁의 내용을 제쳐두고 인공 키를 생성하기 위한 표준 메커니즘인 Oracle 시퀀스 및 SQL Server ID 열을 살펴보겠습니다.
Oracle 시퀀스는 일급 데이터베이스 수준 개체입니다. 대조적으로 SQL Server ID 열은 개체가 아니라 열 유형입니다.
Oracle 시퀀스를 사용하여 테이블 키(일반적으로 기본 키)를 생성하면 증가가 보장되므로 고유합니다. 그러나 연속적이라는 보장은 없습니다 . 사실, 잘 설계된 구현에서도 약간의 차이가 있을 가능성이 가장 높습니다. 따라서 어떤 Oracle 구현도 연속적인 시퀀스 생성 값에 의존해서는 안 됩니다.
또한 시퀀스는 Oracle 데이터베이스의 데이터 사전을 통해 관리되므로 각각의 모든 대리 키를 지원하는 전용 시퀀스를 생성하는 것은 리소스를 너무 많이 소모하고 번거롭습니다. 단일 시퀀스 개체는 여러 또는 모든 대리 키를 지원할 수 있습니다.
반면에 여러 프로세스가 시퀀스에서 NEXTVAL (다음 증분 값)에 액세스해야 하는 경우 시퀀스는 중요한 단일 액세스 리소스가 됩니다. 이는 모든 다중 스레드(단일 또는 다중 서버) 구현을 긴 대기 시간과 높은 메모리/낮은 CPU 사용으로 단일 스레드 프로세스로 전환하여 엄격하게 순차적으로 액세스하는 모든 프로세스를 효과적으로 만듭니다.
이러한 구현은 실제로 발생합니다. 이 문제에 대한 해결책은 문제의 시퀀스 개체를 합리적인 캐시 값으로 정의하는 것입니다. 즉, 정의된 범위의 값(100 또는 100,000)이 호출 프로세스의 캐시로 선택되고 사용된 대로 데이터 사전에 기록됩니다. , NEXTVAL 이 호출될 때마다 데이터 사전에 액세스할 필요 없이 이 특정 프로세스에서 사용할 수 있게 됩니다.
그러나 이것이 바로 모든 캐시된 값이 사용되지 않을 가능성이 있으므로 간격이 생성되는 이유입니다. 또한 병렬 세션의 여러 프로세스에서 일부 기록된 시퀀스 값이 시간순으로 반전될 수 있음을 의미합니다. 이 반전은 시퀀스 값이 재설정되거나 백시프트되지 않는 한 단일 프로세스 내에서 발생할 수 없습니다. 그러나 이 마지막 시나리오는 문제를 찾는 것과 같습니다. 불필요해야 하며 잘못 구현하면 중복 값이 생성될 수 있습니다.
따라서 Oracle 시퀀스를 사용하는 유일한 올바른 방법은 대리 키를 생성하는 것입니다. 고유하지만 다른 안정적으로 사용 가능한 정보를 보유하지 않는 것으로 간주되는 키입니다.
SQL Server의 ID 열
SQL Server는 어떻습니까? SQL Server 2012에서 Oracle 대응과 매우 유사한 기능 및 구현을 가진 시퀀스가 도입되었지만, 일류의 필수 기술은 아닙니다. 다른 기능 추가와 마찬가지로 Oracle에서 변환하는 데 적합하지만 SQL Server에서 처음부터 대리 키를 구현할 때 IDENTITY 가 훨씬 더 나은 옵션입니다.
IDENTITY 는 테이블의 "하위" 개체입니다. 테이블 외부의 리소스에는 액세스하지 않으며 의도적으로 조작하지 않는 한 순차적으로 보장됩니다. 그리고 Oracle과의 의미론적 호환성보다는 바로 이 작업을 위해 특별히 설계되었습니다.
Oracle이 버전 12.1에서 IDENTITY 기능을 구현했기 때문에 이전에 IDENTITY 기능이 없었을 때 어떻게 했는지, 왜 지금 구현했는지, SQL Server가 처음부터(Sybase SQL Server 기원부터) 왜 필요했는지 궁금해하는 것은 당연합니다.
그 이유는 Oracle이 항상 ID 키 기능인 ROWID 또는 UROWID 데이터 유형을 갖는 ROWID 의사 열을 가지고 있기 때문입니다. 이 값은 숫자가 아니며( ROWID 및 UROWID 는 Oracle 독점 데이터 유형임) 데이터 레코드를 고유하게 식별합니다.
SQL Server의 IDENTITY 와 달리 Oracle의 ROWID 는 쉽게 조작할 수 없으며(쿼리할 수 있지만 삽입하거나 수정할 수 없음) 모든 Oracle 테이블의 모든 행에 대해 백그라운드에서 생성됩니다. 또한 Oracle 데이터베이스의 모든 데이터 행에 액세스하는 가장 효율적인 방법은 ROWID 를 사용하는 것이므로 성능 최적화 기술로 사용됩니다. 마지막으로 행 데이터의 하위 수준 저장소를 효과적으로 인덱싱하므로 기본 쿼리 출력 정렬 순서를 정의합니다.
Oracle의 ROWID 가 그렇게 중요하다면 SQL Server가 없이 몇 년 동안 어떻게 살아남았습니까? IDENTITY 열을 기본(대리) 키로 사용합니다.
Oracle과 SQL Server 간의 인덱스 구조 구현의 차이점에 주목하는 것이 중요합니다.
SQL Server에서는 첫 번째 인덱스인 기본 키가 클러스터링되는 경우가 많습니다. 이는 가장 일반적으로 기본 데이터 파일의 데이터가 이 키로 정렬된다는 것을 의미합니다. Oracle 측에서 클러스터형 인덱스에 해당하는 것은 인덱스로 구성된 테이블입니다. 이것은 예를 들어 읽기 전용 조회 테이블과 같이 필요할 때만 산발적으로 사용되는 Oracle의 선택적 구성입니다.
ROWID 사용을 기반으로 하는 Oracle의 모든 디자인 패턴(예: 데이터 중복 제거)은 SQL Server로 마이그레이션할 때 IDENTITY 열을 기반으로 구현되어야 합니다.
SQL Server의 IDENTITY 사용에서 Oracle의 IDENTITY 사용으로 마이그레이션하면 기능적으로 올바른 코드를 생성할 수 있지만 Oracle 측에서 ROWID 가 훨씬 더 효율적으로 수행되기 때문에 최적이 아닙니다.
Oracle 시퀀스를 SQL Server로 이동하기 위해 간단한 SQL 구문 변환을 수행할 때도 마찬가지입니다. 코드가 실행되지만 IDENTITY 를 사용하는 것이 코드 단순성과 성능 모두에서 훨씬 선호되는 옵션입니다.
Microsoft SQL Server의 필터링된 인덱스
몇 년 전 Microsoft SQL Server 2008은 진정한 최고의 엔터프라이즈 데이터베이스로 변모시킨 여러 가지 중요한 기능을 도입했습니다. 내 하루를 두 번 이상 저장한 것은 필터링된 인덱스입니다.
필터링된 인덱스는 WHERE 절이 있는 클러스터되지 않은 인덱스(즉, 자체 데이터 파일로 존재하는 인덱스)입니다. 인덱스 파일에는 해당 절과 관련된 데이터 레코드만 포함되어 있음을 의미합니다. 필터링된 인덱스를 최대한 활용하려면 데이터 집합을 반환할 때 필요한 모든 열을 나열하는 INCLUDE 절도 있어야 합니다. 쿼리가 필요한 모든 데이터 요소를 포함하는 필터링된 특정 인덱스를 사용하도록 최적화된 경우 데이터베이스 엔진은 기본 테이블 데이터 파일을 보지 않고도 (작은) 인덱스 파일에만 액세스하면 됩니다.
이것은 몇 년 전 테라바이트 크기의 테이블로 작업할 때 특히 유용했습니다. 문제의 클라이언트는 주어진 시간에 활성화된 레코드의 극히 일부에만 액세스해야 하는 경우가 많았습니다. 이 액세스의 초기 구현(최종 사용자 UI 작업에 의해 트리거됨)은 고통스러울 정도로 느릴 뿐만 아니라 그냥 사용할 수 없었습니다. 필요한 INCLUDE 로 필터링된 인덱스를 추가했을 때 밀리초 미만의 검색이 되었습니다. 이 최적화 작업에 소요한 시간은 단 1시간이었습니다.
물론 필터링된 인덱스에는 몇 가지 제한 사항이 있습니다. 여기에는 LOB 열이 포함될 수 없으며 WHERE 절이 인덱스 자체에 포함할 수 있는 조건에 대한 제한이 있으며 데이터베이스의 저장소 공간에 추가됩니다. 그러나 사용 사례가 이러한 매개변수 내에 적합하다면 스토리지 트레이드오프는 필터링된 인덱스가 제공할 수 있는 상당한 성능 향상에 비해 일반적으로 아주 미미합니다.
Oracle Database의 필터링된 인덱스는 어떻습니까?
나중에 Fortune 500대 기업의 대규모 팀에서 SQL Server에서 Oracle로 마이그레이션 프로젝트의 개발자/DBA로 일하게 되었습니다. 원본 데이터베이스(SQL Server 2008)를 둘러싼 코드는 제대로 구현되지 않았고 성능이 저하되어 변환이 필수적이었습니다. 일일 백 엔드 동기화 작업이 23시간 이상 실행되었습니다. 필터링된 인덱스가 없었지만 새로운 Oracle 11g 시스템에서는 필터링된 인덱스가 매우 유용한 여러 경우를 보았습니다. 그러나 Oracle 11g에는 필터링된 인덱스가 없습니다!
최신 Oracle 18c에서는 필터링된 인덱스도 구현되지 않았습니다.
그러나 기술 전문가로서 우리의 임무는 우리가 가진 것을 최대한 활용하는 것입니다. 그래서 Oracle 11g 시스템에서 필터링된 인덱스와 동일한 기능을 구현했습니다(나중에 12c에서 사용한 것과 동일한 기술). 아이디어는 Oracle이 NULL 을 처리하는 방법을 기반으로 하며 모든 버전의 Oracle에서 사용할 수 있습니다.
Oracle은 NULL 값을 일반 데이터와 같은 방식으로 취급하지 않습니다. Oracle에서 NULL 은 아무것도 아닙니다. 존재하지 않습니다. 결과적으로 인덱싱된 열을 NULLABLE 로 정의하고 NULL 이 아닌 값으로 검색하는 경우 인덱스 데이터 파일에는 관심 있는 레코드만 포함됩니다. Oracle 인덱스 정의에는 INCLUDE 절이 없으므로 결과 집합에 포함되어야 하는 모든 열이 포함된 복합 인덱스를 만들어야 합니다. (이 기술은 SQL Server의 INCLUDE 절에 비해 약간의 오버헤드가 있지만 상당히 중요하지 않습니다.)
이러한 해결 방법 구현은 제한 사항을 추가합니다. 선행 인덱스 열은 NULL 을 허용해야 하므로 테이블의 기본 키가 될 수 없습니다. 그러나 이 성능 최적화 방법을 지원하기 위해 특별히 생성된 파생 또는 계산 열일 수 있습니다. 어떤 의미에서 인덱스의 선행 열은 논리적으로 바이너리입니다. 검색에 포함된 데이터의 경우 NULL 이 아닌 값이고 "보이지 않는" 데이터의 경우 NULL 입니다.
SQL Server 필터링된 인덱스 논리를 Oracle로 마이그레이션할 때 가능한 다른 옵션은 인덱스(또는 전체 테이블)를 분할된 것으로 구현하는 것입니다. 이 경우 데이터베이스 엔진은 관련 인덱스 파티션에만 액세스합니다. 제공된 쿼리는 WHERE 절에서 정확한 파티션 조건을 사용하여 올바르게 구현됩니다.
이것은 규모에 상관없이 비교적 정적인 데이터에서 잘 작동하지만 자주 변경되는 데이터에 적용할 경우 DBA 팀에 높은 유지 관리 부하를 가할 수 있습니다. 예를 들어 시간 중심 애플리케이션에서 오늘날의 데이터에 대한 액세스를 최적화하는 경우가 있습니다. DBA 팀은 매일 파티션을 재정의해야 합니다. 이 재정의는 야간 유지 관리 작업에서 스크립팅할 수 있지만 시스템을 더 복잡하게 만들고 새로운 잠재적인 시스템 오류 지점을 도입합니다.
따라서 SQL Server 필터링된 인덱스 논리를 Oracle로 마이그레이션해야 할 때마다 매우 구체적이고 주의해야 합니다.
변환 처리 방법
Oracle에서 SQL Server로 마이그레이션할 때 필터링된 인덱스를 사용하여 최적화할 수 있는 기회를 찾으십시오. Oracle에서는 필터링된 인덱스를 볼 수 없지만 NULL 값을 포함하는 인덱스는 볼 수 있습니다. 그대로 복사하지 마십시오. 변환에서 성능 향상과 디자인 개선을 얻을 수 있는 가장 좋은 장소일 수 있습니다.
SQL Server에서 Oracle로 마이그레이션하는 경우 필터링된 인덱스가 표시되면 해당 Oracle 코드에서 성능 병목 현상을 방지하는 방법을 찾으십시오. 필터링된 인덱스가 소스 구현에서 제공한 누락된 성능 향상을 보상하기 위해 데이터 흐름을 재설계하는 방법을 확인하십시오.
SQL Server에서 Oracle로 / Oracle에서 SQL Server로의 마이그레이션 과제 해결
어느 방향으로든 Oracle과 SQL Server 간의 마이그레이션 프로젝트의 경우 관련된 메커니즘을 더 깊이 이해하는 것이 중요합니다. 각 데이터베이스(Oracle 18c 및 Microsoft SQL Server 2017*)의 현재 릴리스에 서로의 기능(예: 시퀀스 및 ID)에 대한 어휘적 등가물이 포함되어 있으면 쉽게 승리하는 것처럼 보일 수 있습니다. 그러나 한 RDBMS의 좋은 디자인을 다른 RDBMS로 직접 복사하면 불필요하게 복잡하고 성능이 떨어지는 코드가 될 수 있습니다.
이 시리즈의 다음이자 마지막 부분에서는 읽기 일관성과 마이그레이션 도구 사용에 대해 설명합니다. 계속 지켜봐 주세요!
* SQL Server 2019(또는 "15.x")가 기업에 널리 채택될 만큼 출시된 지 오래되지 않았습니다.