Oracle 10g: FORALL 문으로 기능 향상

Technology PL/SQL Oracle 10g: FORALL 문으로 기능 향상 Steven Feuerstein FORALL로 시작하고, BULK COLLECT로 구축하고 VALUES OF로 완성하십시오. Oracle Database 10g의 PL/SQL 기능 향상에 관한 저의 두 번째 문서에서는 비연속 구동 인덱스와 함께 FORALL 문을 사용하는 새로운 기능에 대해 살펴볼 것입니다. 첫 번째 문서는 2003년 11월/12월호를 참조하십시오. 처음에는 상당히 어렵게 느껴질 수 있지만, FORALL 문의 핵심적인 기능 향상을 사용하면 PL/SQL 프로그램 내의 수많은 데이타 행을 업데이트하는 데 사용되는 프로그램의 코드를 훨씬 더 단순화시키고 성능을 개선할 수 있습니다. 우선 FORALL 문의 기능에 대해 살펴보고 Oracle Database 10g가 지원하는 비연속 구동 인덱스의 영향에 대해 살펴봅니다. FORALL 문으로 더 향상된 DML 오라클은 Oracle8i의 PL/SQL에 사용되는 두 개의 새로운 DML(Data Manipulation Language) 문으로 BULK COLLECT와 FORALL을 소개했습니다. 두 문은 PL/SQL에서 배열 처리의 형식을 구현합니다. BULK COLLECT는 고속 데이타 검색을 촉진해 주며, FORALL은 INSERT, UPDATE 및 DELETE 작업 성능을 상당히 개선해 줍니다. 오라클 데이타베이스는 이 두 문을 사용하여 PL/SQL 및 SQL 문 실행 엔진 사이의 컨텍스트 전환 횟수를 줄임으로써 상당한 성능 향상을 실현합니다. BULK COLLECT를 사용하면 개별 변수나 레코드 대신 여러 행을 하나 이상의 컬렉션으로 가져올 수 있습니다. 다음 예제에서 BULK COLLECT는 제목에 "PL/SQL"이 포함된 모든 서적을 연결형 레코드 배열로 검색해 줍니다. 모든 레코드는 단일 경로로 데이타베이스에 연결되어 있습니다. DECLARE TYPE books_aat IS TABLE OF book%ROWTYPE INDEX BY PLS_INTEGER; books books_aat; BEGIN SELECT * BULK COLLECT INTO book FROM books WHERE title LIKE '%PL/SQL%'; ... END; 마찬가지로 FORALL은 컬렉션을 사용하는 지정된 테이블로 PL/SQL 컬렉션의 데이타를 전달합니다. 다음 코드 예제는 중첩된 서적 정보 테이블을 수락하고 해당 컬렉션(바인딩 배열)의 모든 내용을 서적 테이블에 삽입하는 프로시저를 나타냅니다. 참고로, 이 예제에서는 레코드를 테이블에 직접 삽입(INSERT)할 수 있는 Oracle9i의 FORALL 기능 향상을 사용합니다. BULK COLLECT와 FORALL은 매우 유용하며 성능을 개선할 뿐 아니라, PL/SQL의 SQL 작업용으로 작성해야 하는 코드를 단순화할 수 있습니다. 다음의 여러 FORALL INSERT 행은 오라클 데이타베이스에 가장 적합한 프로그래밍 언어로 PL/SQL을 고려해야 하는 이유를 명확하게 보여줍니다. CREATE TYPE books_nt IS TABLE OF book%ROWTYPE; / CREATE OR REPLACE PROCEDURE add_books ( books_in IN books_nt) IS BEGIN FORALL book_index IN books_in.FIRST .. books_in.LAST INSERT INTO book VALUES books_in(book_index); ... END; 하지만 Oracle Database 10g 이전에는 FORALL과 함께 컬렉션을 사용하는 데 중요한 제약이 있었습니다. 데이타베이스는 IN 범위 절의 처음 행에서 마지막 행까지 컬렉션 내용을 순차적으로 읽습니다. 정의되지 않은 범위 내에서 행이 발견되면 오라클 데이타베이스는 ORA-22160 예외 "ORA-22160: element at index [N] does not exist."를 발생합니다. ORA-22160: element at index [N] does not exist FORALL을 단순한 용도로 사용할 경우 이 규칙은 크게 문제되지 않습니다. 하지만 광범위한 환경에서 FORALL을 최대한 사용하려는 경우에는, FORALL 구동 배열에 순차적으로 값을 채워야 하는 요구 사항으로 인해 프로그램이 복잡해지고 성능이 저하될 수 있습니다. 이제 Oracle Database 10g에서 PL/SQL은 INDICES OF와 VALUES OFF의 두 절을 FORALL 문에 제공합니다. 이 두 절을 사용하면 구동 배열의 어떤 행을 확장 DML 문에서 처리해야 하는지를 매우 선택적으로 선택할 수 있습니다. INDICES OF는 바인딩 배열이 드문드문 떨어져 있거나 빈 공간을 포함하고 있는 경우에 매우 편리합니다. 이 문의 구문은 다음과 같습니다. FORALL indx IN INDICES OF sparse_collection INSERT INTO my_table VALUES sparse_collection (indx); VALUES OF는 서로 다른 상황에 다르게 반응합니다. 저의 바인딩 배열이 드문드문 떨어져 있거나 그렇지 않을 수 있지만 이 배열에 하나의 요소 집합만을 사용하려고 합니다. 이 경우 VALUES OF를 사용하여 저의 DML 작업에 사용할 값만을 가리킬 수 있습니다. 이 문의 구문은 다음과 같습니다. FORALL indx IN VALUES OF pointer_array INSERT INTO my_table VALUES binding_array (indx); 지금부터는 CURSOR FOR 루프를 FORALL 구현으로 이동하는 방법과 VALUES OF 절을 사용하여 프로그래밍 작업을 편하게 하는 방법에 대해 설명합니다. FOR 루프를 FORALL로 이동 자격이 되는 직원(comp_analysis.is_eligible 함수로 결정)을 승진시키는 프로시저를 작성하고 승진 자격이 되지 않는 직원에 대한 보고서를 employee_history 테이블로 작성하려는 경우를 예로 들어 보겠습니다. 저는 매우 큰 회사에서 근무하기 때문에 직원의 수가 엄청납니다. PL/SQL 개발자에게 이것은 엄청나게 심각한 문제입니다. 목록 1의 설명과 같이 저는 업무 수행을 위해 BULK COLLECT나 FORALL을 사용할 필요조차 없습니다. 그 대신 CURSOR FOR 루프와 개별 INSERT 및 UPDATE 문을 사용합니다. 이 코드는 쉬운 코드이지만, 불행히도 이 코드를 실행하는 데 10분을 소요했으며 저의 뒤떨어진 방식으로는 30분 이상을 소요합니다. 다행히도 회사에서 Oracle9i로 업그레이드하였고 더욱 다행인 것은 제가 최근의 오라클 세미나와 오라클 기술 네트워크에서 제공하는 뛰어난 데모 설명을 통해 일괄 프로세싱에 대해 배웠다는 것입니다. 그래서 컬렉션과 일괄 프로세싱을 사용하여 저 자신만의 프로그램을 다시 작성하기로 했습니다. 이 결과는 목록 2에 있습니다. 목록 1 과 목록 2를 쭉 살펴보면 한 가지 사실을 명확히 알 수 있습니다. 즉, 컬렉션과 일괄 프로세싱으로 전환하면 코드가 늘어나고 복잡해진다는 단점이 있습니다. 하지만 성능이 상당히 향상된다면 이러한 단점은 충분히 정당화될 수 있습니다. 이제 본론으로 들어가서 FORALL을 사용할 때 CURSOR FOR 내의 조건부 논리를 처리하는 방법에 대해 살펴보겠습니다. 컬렉션 종류 및 컬렉션 정의 목록 2에서 선언 구역의 첫 번째 구역(6-11줄)에는 여러 개의 다른 컬렉션 TYPE을 정의합니다. 이 때 직원 테이블을 검색할 각 열에 대해 각각 하나의 컬렉션 TYPE을 정의합니다. 저는 employee%ROWTYPE에 따라 단일 컬렉션 TYPE을 선언하고 싶지만 FORALL은 제가 개별 필드를 참조할 레코드 컬렉션에 대해 아직 작업을 지원하지 않습니다. 그래서 직원 ID, 봉급 및 고용 날짜의 각 열에 대해 별도의 컬렉션을 선언해야 합니다. 그 다음 각 열에 필요한 컬렉션을 선언합니다(13-21줄). 먼저 쿼리된 열에 해당하는 컬렉션을 정의합니다(13-15줄). employee_ids employee_aat; salaries salary_aat; hire_dates hire_date_aat; 그 다음 승진이 승인된 직원의 ID만 보관하는 새 컬렉션이 필요합니다(17줄). approved_employee_ids employee_aat; 마지막으로 승진 자격이 되지 않는 직원을 기록하는 데 사용할 컬렉션을 각 열에 대해 선언합니다(19-21줄). denied_employee_ids employee_aat; denied_salaries salary_aat; denied_hire_dates hire_date_aat; 코드 분석 데이타 구조가 적절하게 선언되면 이제 이 프로시저의 실행 구역(72-75줄)으로 가서 컬렉션이 프로세스 속도를 향상시켜주는 방법에 대해 알아보겠습니다. retrieve_employee_info; partition_by_eligibility; add_to_history; give_the_raise; 저는 이 프로그램을 작성하기 위해 단계적 수정 방식(일명 “하향식 설계)을 사용했습니다. 그래서 매우 길고 따라 하기 어려운 실행 구역을 만드는 대신, 프로시저의 각 단계를 이름별로 설명하는 4줄짜리 구역을 만듭니다. 우선 지정된 부서의 모든 직원에 대한 직원 정보를 검색합니다. 그 다음 승진이 승인된 직원과 거부된 직원을 구분하거나 나눕니다. 직원을 구분한 후 승진이 거부된 직원은 직원 이력 테이블에 추가하고 나머지 직원은 승진을 시킬 수 있습니다. 이런 방식으로 코드를 작성하면 최종 결과를 훨씬 더 쉽게 이해할 수 있습니다. 그 다음 제가 관심이 있는 프로그램의 모든 부분을 분석할 수 있습니다. 컬렉션이 선언되면 이제 BULK COLLECT를 사용하여 직원 정보를 검색할 수 있습니다(23 - 30행). 이렇게 하면 CURSOR FOR 루프가 효과적으로 이동됩니다. 이제 데이타가 컬렉션으로 로드됩니다. 구분 논리(32 - 46행)에서는 제가 방금 전에 값을 채운 컬렉션의 각 행을 검토해서 승진 적격 여부를 확인해야 합니다. 이 확인 후에 직원 ID를, 쿼리를 채운 컬렉션에서 승인 컬렉션으로 복사합니다. 승진에 부적격한 직원이 경우, employee_history 테이블에 삽입하기 위해 직원 ID, 봉급 및 고용 날짜를 복사합니다. 원래의 데이타가 이제 두 컬렉션으로 구분되면 각 컬렉션을 51행 및 66행에서 시작하는 다른 두 FORALL 문의 구동 컬렉션으로 사용할 수 있습니다. 이제 거부된 컬렉션 데이타를 employee_history 테이블(add_to_history)에 일괄적으로 삽입하고, give_the_raise 프로시저에서 직원 테이블의 승인된 직원 정보를 일괄적으로 업데이트합니다. add_to_history(48 - 61행)를 간략히 살펴봄으로써 이 프로시저 재작성에 대한 검토를 마치겠습니다. FORALL 문(51행)에는 일괄 INSERT에 사용할 행 번호의 범위를 지정하는 IN 절이 들어 있습니다. 또 다른 프로시저 재작성에 대해 설명할 때는 이 범위 정의에 사용된 컬렉션을 “구동 컬렉션”이라고 부를 것입니다. 하지만 이 버전의 add_to_history에서는 denied_employee_ids에 정의된 모든 행을 사용하십시오. 저는 거부된 세 개의 컬렉션을 INSERT 내에 모두 사용하며 이 컬렉션을 “데이타 컬렉션”이라고 부를 것입니다. 보시다시피 구동 컬렉션과 데이타 컬렉션은 일치할 필요가 없습니다. 이 점은 Oracle Database 10g에서 새 기능 사용 방법을 배울 때 특히 중요합니다. 요컨대, 목록 2 재작성의 경우 목록 1보다 코드 행의 수가 거의 두 배나 많았지만 목록 2 코드가 원하는 시간 범위 내에 실행됩니다. Oracle Database 10g 이전에는 이런 경우라면 시간 내에 실행된 것으로 만족하고 다음 작업으로 넘어갔을 것입니다. 하지만 이제는 Oracle Database 10g의 최신 PL/SQL 버전을 사용하여 성능, 가독성 및 코드 크기를 더 향상시킬 수 있습니다. 프로세스에 VALUES OF 사용 이제 Oracle Database 10g에서는 FORALL 문에 사용할 행 하위 집합을 구동 컬렉션에 지정할 수 있습니다. 다음 방법 중 하나로 하위 집합을 지정할 수 있습니다. 데이타 컬렉션의 행 번호를 구동 컬렉션의 행 번호와 일치시킵니다. 이를 위해 INDICES OF 절을 사용할 수 있습니다. 데이타 컬렉션의 행 번호를 구동 컬렉션의 정의된 행 번호에 있는 값과 일치시킵니다. 이를 위해 VALUES OF 절을 사용할 수 있습니다. give_raises_in_department를 두 번째이자 마지막으로 재작성할 때는 VALUES OF 절을 사용할 것입니다. 목록 3에는 이 버전의 전체 코드가 들어 있습니다. 이 프로그램에서 이전 버전과 똑같은 부분은 건너뛸 것입니다. 컬렉션 선언을 시작할 때 주의할 점은, 승진이 승인되거나 거부된 직원 정보가 보관되는 추가 컬렉션을 더 이상 정의하지 않는다는 것입니다. 그 대신 목록 3 (17 - 21행)에서 두 개의 “가이드” 컬렉션을 선언합니다. 하나는 승진이 승인된 직원을 위한 것이고, 다른 하나는 승진이 거부된 직원을 위한 것입니다. 두 컬렉션의 데이타 형식은 부울 형식입니다. 곧 알게 되겠지만, 컬렉션의 데이타 형식은 전혀 중요하지 않습니다. FORALL 문에게 중요한 유일한 정보는 어떤 행이 정의되었느냐 하는 것입니다. 직원 테이블에 50,000개의 행이 있는 give_raises_in_department의 세 가지 구현에 대한 경과 시간. 구현 경과 시간 CURSOR FOR 루프 00:00:38.01 Oracle Database 10g 이전의 일괄 프로세싱 00:00:06.09 Oracle Database 10g 일괄 프로세싱 00:00:02.06 직원 테이블에 100,000개의 행이 있는 give_raises_in_department의 세 가지 구현에 대한 경과 시간 구현 경과 시간 CURSOR FOR 루프 00:00:58.01 Oracle Database 10g 이전의 일괄 프로세싱 00:00:12.00 Oracle Database 10g 일괄 프로세싱 00:00:05.05 표 1: 행 50,000개와 행 100,000개의 시간 테스트 대신(상대적으로 느린 동작), 직원 ID 컬렉션의 행 번호와 일치하는 행을 해당 가이드 컬렉션에 정의합니다(행에 TRUE 값을 지정하여 정의). 이제 approved_list 컬렉션과 denied_list 컬렉션을 49행 및 65행에서 시작하는 다른 두 FORALL 문의 구동 컬렉션으로 사용할 수 있습니다. employee_history 테이블에 삽입하려면 다음 공식을 사용합니다. FORALL indx IN VALUES OF denied_list 업데이트를 수행하여 직원을 승진시키려면 다음 공식을 사용합니다. FORALL indx IN VALUES OF approved_list 두 DML 문에서 데이타 컬렉션은 BULK COLLECT 검색 단계에서 채워진 원본 컬렉션이므로 복사본을 만들지 않았습니다. 오라클 데이타베이스는 VALUES OF를 사용하여 데이타 컬렉션에서 행을 선택하므로, 행 번호가 구동 컬렉션의 행 번호와 일치하는 행만이 사용됩니다. Next Steps 이 문서의 코드 다운로드 otn.oracle.com/oramag/oracle/04-jan/o14tech_plsql.zip Oracle Database 10g 추가 문서 otn.oracle.com/kr/products/database Feuerstein 추가 문서 otn.oracle.com/kr/pub/articles http://www.oreilly.com/ 이 프로그램에서 VALUES OF를 사용하여 전체 정보 레코드를 복사하지 않고도 각 레코드를 간단한 행 번호 목록으로 대체할 수 있었습니다. 대규모 배열의 경우 전체 복사 시간이 상당히 오래 걸릴 수 있습니다. Oracle Database 10g 방식의 장점을 테스트하기 위해 저는 직원 테이블 로드해서 각각 50,000개와 100,000개의 데이타 행으로 테스트를 수행했습니다. 또한 더 많은 실제 시나리오를 에뮬레이션하기 위해 Oracle Database 10g 이전의 일괄 프로세싱 구현을 수정하여 더 많은 컬렉션 내용을 복사하도록 했습니다. 그 다음 SQL*Plus SET TIMING ON을 사용하여 다양한 구현을 각각 실행을 각각 실행하는 데 걸린 경과 시간을 표시했습니다. 결과가 표 1에 있습니다. 이 시간 테스트의 결론은 명확합니다. 즉, 개별 DML 문을 일괄 프로세싱으로 전환하면 행 50,000개의 경우 38초에서 6초로, 행 100,000개의 경우 58초에서 12초로 경과 시간이 급격히 감소합니다. 또한 VALUES OF를 사용하여 데이타를 복사하지 않고도 경과 시간을 거의 절반으로 줄일 수 있었습니다. VALUES OF 절과 INDICES OF 절은 성능을 향상시키지는 않지만 PL/SQL 언어의 유연성을 더해주고 개발자가 더 직관적이고 관리가 용이한 코드를 쉽게 작성하도록 해줍니다. 현재의 제품 수명 단계에서 PL/SQL은 완벽하고 강력한 언어입니다. 그 결과로 수많은 새로운 기능들이 추가되고 발전을 거듭할 것입니다. 이 새로운 기능들은 애플리케이션의 성능과 개발자의 생산성에 엄청난 차이를 가져올 것입니다. VALUES OF는 이런 기능의 대표적인 예입니다. Steven Feuerstein (steven@stevenfeuerstein.com)은 PL/SQL 언어의 대가입니다. 그는 O'Reilly & Associates(oracle.oreilly.com)에서 모두 9권의 PL/SQL 관련 서적을 저술하였으며, 이 중에는 Oracle PL/SQL Best Practices and Oracle PL/SQL Programming이 있습니다. 그는 http://www.minmaxplsql.com/에서 PL/SQL 언어에 관한 교육을 제공하고 있으며 Quest Software의 수석 기술 고문이기도 합니다. 원본은 http://www.oracle.com/technology/oramag/oracle/04-jan/o14do14tech_plsql.html에서 확인할 수 있습니다.