DBMS 1
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
이 기능을 설명하기 전에, 먼저 MV(materialized view)의 리프레시 과정에서 수행되는 파티션 프루닝(partition pruning)에 대해 이해하고 넘어갈 필요가 있습니다. ACCOUNTS 테이블이 ACC_MGR_ID 컬럼에 대해 파티셔닝 되어 있고, ACC_MGR_ID의 값에 따라 파티션이 달라지는 경우를 가정해 봅시다. DBA는 ACCOUNTS 테이블을 기반으로 ACC_VIEW라는 이름의 MV를 생성하였습니다. 이 MV 역시, ACC_MGR_ID 컬럼에 대해 파티셔닝 되어 있고, ACC_MGR_ID의 값에 따라 파티션이 달라집니다 (아래 그림 참고): 이제, ACCOUNTS 테이블의 p1 파티션의 데이터가 업데이트되었다고 가정해 봅시다. MV를 가장 신속하게 리프레시(refresh)하려면, (전체 테이블을 리프레시하는 대신) p1 파티션만을 리프레시하는 것이 바람직할 것입니다. 오라클은 partition Change Tracking (pCT) 기능을 통해 파티션의 변경 내역을 자동으로 추적합니다. 하지만 여기에서 주의해야 할 점이 한 가지 있습니다. 패스트 리프레시(fast refresh) 과정에서 pCT를 활성화하려면, 먼저 테이블에서 변경된 로우(row)의 데이터를 저장할 MV 로그를 생성해야 합니다. 리프레시 명령이 실행되면, 리프레시 프로세스는 MV 로그를 읽어 변경 내역을 확인합니다. 물론 이로 인해 작업 수행 시간이 다소 길어지는 것은 불가피합니다. 또 추가적인 INSERT 작업으로 인해 CpU 사이클과 I/O 대역폭을 소모하게 됩니다. Oracle Database 10g Release 2는, MV 로그를 사용하지 않고도 pCT를 이용할 수 있게 함으로써 이러한 문제를 해결하였습니다. 실제 활용 사례를 살펴봅시다. 먼저, ACCOUNTS 테이블에 대한 MV 로그가 존재하지 않음을 확인합니다. 이제, 테이블의 레코드 중 하나를 업데이트합니다. 이 레코드는 p3 파티션에 위치합니다. 하지만 먼저, ACCOUNTS 테이블의 모든 세그먼트에 대해 세그먼트 레벨 통계정보를 기록해 둘 필요가 있습니다. 이 정보는 나중에 어떤 세그먼트가 사용되었는지 확인하기 위해 필요합니다. 패스트 리프레시(fast refresh) 작업을 수행하여 ACC_VIEW MV를 리프레시합니다. 매개변수 'F'는 패스트 리프레시가 수행됨을 의미합니다. 하지만 과연 MV 로그가 없는데도 제대로 동작할까요 리프레시 작업이 완료되면, 다시 ACCOUNTS 테이블의 세그먼트 통계정보를 확인해 봅니다. 그 결과가 아래와 같습니다: 위에서 logical read 과정에서 읽어온 세그먼트의 수를 확인할 수 있습니다. 이 수치는 누적되므로, 앞에서 기록한 수치와 비교하여 차이가 있는지 살펴보아야 합니다. 비교 결과, p3 파티션의 값만이 변경되었음을 알 수 있습니다. 따라서 리프레시 과정에서 (전체 테이블이 아닌) p3 파티션에 대해서만 작업이 수행되었으며, 따라서 MV 로그가 존재하지 않는 상황에서 pCT를 활용할 수 있음을 확인할 수 있습니다. MV 로그를 사용하지 않고도 패스트 리프레시 작업을 수행할 수 있다는 것은 성능 면에서 매우 중요한 장점입니다. 필자는 이 기능을, Oracle Database 10g Release 2에서 추가된 가장 유용한 데이터 웨어하우징 관련 기능으로 꼽겠습니다. Oracle8i에서 처음 소개된 Query Rewrite 기능은 데이터 웨어하우스 개발자와 DBA들로부터 뜨거운 호응을 불러일으켰습니다. Query Rewrite는 사용자 쿼리가 테이블이 아닌 MV를 조회하게 함으로써, 기존에 수행된 계산작업 결과를 활용하고 성능을 개선할 수 있게 합니다. 한 메이저 호텔 체인에서 구축한 데이터베이스에 다음과 같은 3가지 테이블이 구성되어 있다고 가정해 봅시다: HOTELS 테이블은 호텔에 대한 기본 정보를 저장하고 있습니다. RESERVATIONS 테이블에는 고객의 객실 예약 정보가 저장되며, 예약된 객실요금 정보를 포함하고 있습니다. 고객이 체크아웃하면서 지불한 객실료 정보는 TRANS 테이블에 저장됩니다. 이 호텔은 고객이 체크아웃하기 이전에, 공실 여부, 업그레이드, 인센티브 등의 조건에 따라 고객에게 다른 요금을 제시할 수 있도록 프로세스를 변경하기로 결정하였습니다. 따라서 고객이 체크아웃하기 전까지 객실 요금은 언제든 변경될 수 있습니다. 이러한 변화를 제대로 기록하려면, TRANS 테이블에 매일 단위로 객실 요금 정보를 저장하고 있어야 합니다. 쿼리의 응답시간을 개선하기 위해, 아래와 같이 MV를 생성했다고 가정해 봅시다: 따라서, 아래와 같은 쿼리는: 다음과 같이 “rewrite” 됩니다: “query_rewrite_enabled = true”로 설정해 둔 상태라면, autotrace를 활성화함으로써 MV가 제대로 이용되고 있는지 확인할 수 있을 것입니다: 쿼리가 테이블(HOTELS, RESERVATIONS) 대신 MV(MV_HOTEL_RESV)를 통해 데이터를 조회했음을 주목하시기 바랍니다. 마찬가지로, 각 예약 번호 별로 적용된 객실 요금을 계산하는 쿼리를 실행한 경우에도 TRANS 테이블 대신 MV_ACTUAL_SALES MV가 사용될 것입니다. 이번엔 다른 쿼리를 실행해 봅시다. 각 도시 별로 발생한 매출을 계산하려 합니다: 위 쿼리는, MV_ACTUAL_SALES에서 RESV_ID와 매출 정보를, MV_HOTEL_RESV에서 CITY와 RESV_ID를 가져오는 구조로 작성되었습니다. 서로 다른 MV를 조인하는 것이 가능할까요 물론 가능합니다. 하지만 Oracle Database 10g Release 2 이전 버전에서는 두 가지 MV 중 하나만을 사용하여 사용자 쿼리를 자동. Oracle9i Database에서 확인한 쿼리의 실행 계획이 아래와 같습니다. MV_HOTEL_RESV와 TRANS 테이블에 대한 풀 테이블 스캔이 사용되었습니다. 위와 같은 방법은 효율적이라고 보기 어려울 것입니다. 따라서 최적화된 성능을 얻어내려면 3개의 테이블을 모두 JOIN하는 새로운 MV를 만드는 방법 밖에는 없습니다. 하지만 이로 인해 MV의 수가 늘어나고, 리프레시 작업에 소요되는 시간도 그만큼 길어진다는 점을 감안해야 합니다. Oracle Database 10g Release 2에서는 이러한 문제로 더 이상 고민할 필요가 없게 되었습니다. 위 쿼리는 두 가지 MV를 이용하여 재작성됩니다. Release 2에서 확인한 실행 계획이 아래와 같습니다: 베이스 테이블은 전혀 사용되지 않았으며, 두 가지 MV가 모두 사용되었음을 확인할 수 있습니다. 사용자는 쿼리 유형 별로 MV를 일일이 만들어 놓을 필요가 없으며, 따라서 상당한 수준의 성능 개선 효과를 기대할 수 있습니다. 몇 가지 MV만 만들어 놓으면, 오라클이 자동적으로 여러 개의 MV를 동시에 활용하도록 쿼리를 재작성하게 됩니다. Oracle8i에서 처음 소개된 Transportable Tablespace는 서로 다른 데이터베이스 간의 고속 데이터 전송을 가능하게 하였습니다. 이 기능을 타겟 데이터베이스에 전송하고, 메타데이터를 import하여 타겟 데이터베이스에 테이블스페이스에 “플러그인” 처리하는 것이 가능합니다. 이와 같이 하면 테이블스페이스의 데이터를 타겟 데이터베이스에서 바로 활용할 수 있습니다. Transportable Tablespace는 당시까지 데이터 웨어하우징 분야에서 가장 까다로운 난제의 하나로 인식되던 데이터의 이동 속도 문제를 해결하였다는 점에서 큰 의미를 갖습니다. 하지만 OLTp 데이터베이스 환경에서는 이러한 작업이 항상 가능한 것만은 아닙니다. 특히 OLTp 데이터베이스를 데이터 웨어하우스의 데이터 소스로 이용하는 환경에서 Transportable Tablespace를 이용하여 데이터를 로드하는 경우는 거의 없을 것입니다. Oracle Database 10g Release 2에서는 테이블스페이스를 또 다른 형태의 소스, 즉 백업으로부터 플러그인할 수 있는 기능을 제공합니다. 예를 들어, ACCDATA 테이블스페이스를 전송하고자 하는 경우, 다음과 같이 RMAN 커맨드를 수행할 수 있습니다: 위 명령은 /home/oracle 디렉토리에 “auxiliary” 인스턴스를 생성하고, 백업된 파일을 이곳으로 복구하도록 지시하고 있습니다. “auxiliary” 인스턴스의 이름은 랜덤하게 생성됩니다. 인스턴스가 생성되고 나면, 프로세스는 해당 디렉토리에 디렉토리 오브젝트를 생성하고 ACCDATA 테이블의 파일을 복구합니다. 이 모든 작업은 완전 자동화된 형태로 수행됩니다. /home/oracle 디렉토리에는 ACCDATA 테이블스페이스의 모든 데이터파일, 메타데이터를 포함한 덤프파일, 그리고 impscrpt.sql라는 이름의 스크립트가 저장됩니다. 이 스크립트는 테이블스페이스를 타겟 테이블스페이스에 플러그인하는데 필요한 모든 명령을 포함하고 있습니다. 테이블스페이스는 impdp 커맨드를 통해 전송되지 않으며, 대신 dbms_streams_tablespace_adm.attach_tablespaces 패키지의 호출을 통해 전송됩니다. 관련된 모든 커맨드는 impscrpt.sql 스크립트에서 확인하실 수 있습니다. 하지만 이 과정에서 문제가 발생한다면 어떻게 될까요 그리 걱정할 필요는 없습니다. 먼저, “auxiliary” 인스턴스의 로그 파일은 $ORACLE_HOME/rdbms/log 디렉토리에 저장됩니다. 두 번째로, 아래 RMAN 커맨드를 이용하면 RMAN 커맨드와 그 실행 결과를 로그 파일에 저장할 수 있습니다: 위 명령은 모든 실행 결과를 tts.log 파일에 저장하도록 명시하고 있습니다. 마지막으로, 모든 파일은 /home/oracle의 TSpITR＿ 디렉토리에 복구됩니다. 예를 들어, 메인 데이터베이스의 SID가 ACCT이고, RMAN에 의해 생성된 "auxiliary” 인스턴스의 SID가 KYED인 경우, 디렉토리명은 “TSpITR_ACCT_KYED”로 설정됩니다. 이 디렉토리는 두 개의 서브디렉토리를 가집니다. datafile 디렉토리는 데이터파일을 위해, onlinelog 디렉토리는 리두로그를 위해 사용됩니다. 새로운 테이블스페어떤 파일들이 복구되는지 확인할 수 있습니다 (이 파일은 모든 과정이 완료되면 자동 삭제됩니다). 이 기능은 DBA가 이미 오래 전부터 요구해오던 것입니다. 하지만 백업본을 소스로 사용하는 경우 최신 있습니다. 다음과 같은 경우를 생각해 봅시다. 파티셔닝된 테이블이 하나 있습니다. 월말이 되어 다음달을 위한 파티션을 정의해야 하는데, 그만 잊어버리고 말았습니다. 이런 경우 어떻게 해야 할까요 maxvalue 파티션을 두 개로 분리하여, 새로운 달을 위한 파티션과 새로운 maxvalue 파티션을 생성하는 것이 유일한 방법입니다. 하지만 파티셔닝된, index-organized 테이블(IOT)에서 이러한 방법을 사용하는 것은 조금 위험합니다. 이 경우, 물리적인 파티션이 먼저 생성되고 여기에 maxvalue 파티션의 로우(row)를 이동해야 하므로 추가적인 I/O와 CpU 사이클을 소모하기 때문입니다. Oracle Database 10g Release 2에서는 이 과정이 대폭적으로 단순화되었습니다. 아래 그림에서처럼, 5월까지의 파티션을 정의하고 그 밖의 모든 데이터를 pMAX 파티션에 입력하는 경우를 생각해 봅시다. 6월을 위한 파티션이 존재하지 않으므로, 6월분의 데이터는 pMAX 파티션에 입력됩니다. 그림에서 회색으로 표시된 영역은 데이터가 존재하는 세그먼트임을 의미합니다. pMAX 파티션의 경우 절반 정도만 데이터가 입력되어 있음을 확인할 수 있습니다. 6월 30일에 pMAX 파티션을 June 파티션과 새로운 pMAX 파티션으로 분리하는 작업이 수행됩니다. 이 경우 현재 pMAX에 존재하는 모든 데이ase 10g Release 2는 기존의 pMAX 파티션을 June 파티션으로 변경하고, 이와 별도로 새로운 pMAX 파티션을 생성합니다. 따라서 데이터 이동 작업은 전혀 발생하지 않으며, ROWID 또한 변경되지 않습니다. 운영중인 데이터 웨어하우스에 많은 양의 텍스트 데이터가 입력되어 있는 환경에서는, LONG 데이터타입을 갖는 컬럼이 여럿 존재할 가능성이 높습니다. LONG 데이터타입은 SUBSTR과 같은 데이터 처리 함수에서 활용될 수 없다는 단점이 있으며, 따라서 LOB 컬럼으로 변환하는 것이 바람직합니다. DBMS_REDEFINITION 패키지를 이용하면 온라인 상태에서 LONG 데이터타입을 LOB 데이터타입으로 변환할 수 있습니다. 하지만 Oracle Database 10g Release 2 이전 버전에는 중요한 제약사항이 존재했습니다. LONG 컬럼을 LOB 컬럼으로 변환하는 작업은 최대한 신속하게 완료되어야 합니다. 테이블이 파티셔닝되어 있는 경우, 프로세스는 각 파티션 별로 병렬적으로 수행됩니다. 하지만 테이블이 파티셔닝되어 있지 않다면, 모든 작업은 순차적으로 수행되며 따라서 매우 오랜 시간이 걸릴 수 있습니다. Oracle Database 10g Release 2는 파티셔닝되어 있지 않은 테이블에 대해서도 LONG->LOB 변환 작업을 병렬적으로 수행합니다. 예를 들어 설명해 보기로 하겠습니다. 고객에게 전송할 이메일 메시지를 저장한 테이블이 있습니다. 메시지 본문의 저장에 사용되는 MESG_TEXT 컬럼은 매우 긴 텍스트를 포함하므로, 이 컬럼은 LONG 데이터타입으로 정의되었습니다. 이 컬럼을 CLOB 데이터타입으로 변환하려 합니다. 먼저, 마지막 컬럼(CLOB 데이터타입)을 제외하고는 동일한 구조를 갖는 임시 테이블을 생성합니다: 이제 재정의 작업을 시작합니다. 6번째 라인에서, 컬럼이 매핑되는 과정을 주목하시기 바랍니다. 처음 두 컬럼에는 아무런 변화가 없지만 세 번째 MESG_TEXT 컬럼은 소스 테이블 컬럼에 TO_LOB 함수를 적용한 후 타겟 테이블의 MESG_TEXT 컬럼으로 변환하도록 정의되어 있습니다. 테이블의 사이즈가 큰 경우에는, 소스 테이블과 타겟 테이블을 정기적으로 동기화시켜 주어야 합니다. 이렇게 해 두면 최종적인 동기화 작업을 훨씬 빨리 끝낼 수 있습니다. 위 커맨드의 실행시간은 테이블의 사이즈에 따라 크게 달라질 수 있습니다. 마지막으로, 아래와 같이 재정의 작업을 마무리합니다: ACC_MESG 테이블이 다음과 같이 변경되었습니다: MESG_TEXT 컬럼은 이제 LONG 대신 CLOB 데이터타입으로 구성되었습니다. 이 기능은 잘못 정의된 테이블, 또는 레거시 테이블을 변환하는 경우에 매우 유용하게 활용됩니다. 트랜잭션 히스토리를 저장한 TRANS 테이블이 있다고 가정해 봅시다. 이 테이블은 TRANS_DATE를 기준으로 파티셔닝 되며, 각 분기별로 새로운 파티션이 생성됩니다. 가장 최근에 생성된 파티션은 매우 빈번하게 업데이트되지만, 분기가 마감되고 나면 해당 파티션에 대해 트랜잭션이 거의 발생하지 않으므로 파티션을 다른 위치로 옮겨도 무방합니다. 하지만 파티션을 이동하는 과정에서 테이블에 락(lock)이 걸리고 액세스가 차단될 수 있다는 것이 문제입니다. 그렇다면 가용성을 저해하지 않으면서 파티션을 이동할 수 있는 방법이 있을까요 Oracle Database 10g Release 2는 단일 파티션에 대한 온라인 재구성 기능을 제공합니다. 이 작업은 전체 테이블을 대상으로 하는 온라인 재구성 작업과 동일한 방법으로 수행되지만 (DBMS_REDEFINITION 패키지가 사용됩니다), 그 내부 메커니즘에서는 차이가 있습니다. 일반 테이블은 소스 테이블에 대한 MV(materialized view)를 생성하는 방법으로 재구성되는 반면, 단일 파티션을 재구성할 때에는 “파티션을 교환하는(exchange partition)” 방법이 사용됩니다. 예를 통해 설명해 보겠습니다. TRANS 테이블의 구조가 아래와 같습니다: 이 테이블은 다음과 같이 파티셔닝 되어 있습니다: 일정 시간이 지난 후, Y03Q2 파티션을 저가형 스토리지로 구성된 다른 테이블스페이스(TRANSY03Q2)로 이동하려 합니다. 이 작업을 수행하려면, 먼저 테이블의 온라인 재정의가 가능한지 확인해야 합니다: 아무런 결과가 출력되지 않으면, 정상적으로 확인이 된 것으로 간주합니다. 다음에는, 파티션의 데이터를 저장할 임시 테이블을 생성합니다: TRANS 테이블이 영역(range)를 기준으로 파티셔닝 되어 있으므로, 임시 테이블은 파티셔닝 되지 않았음을 참고하시기 바랍니다. 이 테이블은 TRANSY03Q2 테이블스페이스 내에 생성됩니다. TRANS 테이블에 로컬 인덱스가 사용되고 있는 경우, 이 인덱스 또한 TRANS_TEMp 테이블에 생성해 주어야 합니다 (물론 파티셔닝은 하지 않습니다) 이제 재정의 작업을 수행할 준비가 완료되었습니다: 위의 호출 과정에서 참고할 만한 사항이 몇 가지 있습니다. 먼저, col_mapping 매개변수는 NULL로 설정되어 있습니다. 단일 파티션을 재정의하는 작업에서는 이 매개변수가 아무런 의미를 갖지 않습니다. 두 번째로 part_name 매개변수는 재정의 대상이 되는 파티션을 지정하는 용도로 사용됩니다. 세 번째로, COPY_TABLE_DEPENDENTS 매개변수가 사용되지 않고 있습니다. TRANS 테이블 자체에는 아무런 변화가 없으며 단지 파티션 의미가 없습니다. 테이블의 사이즈가 큰 경우에는 이 작업에 오랜 시간이 걸릴 수 있습니다. 따라서 동기화 작업을 수행해 주는 것이 좋습니다. 이와 같이 하여, Y03Q2 파티션이 TRANSY03Q2 테이블스페이스에 생성되었습니다. 테이블에 글로벌 인덱스가 사용되고 있는 경우, 인덱스를 “UNUSABLE”로 마킹하고 rebuild 작업을 수행해야 합니다. 단일 파티션의 재정의 기능은 다른 테이블스페이스로 파티션을 이동하는 작업에서 유용하게 활용됩니다. 이러한 작업은 정보 생명주기 관리 차원에서 자주 사용되곤 합니다. 물론, 제약사항이 없는 것은 아닙니다. 재정의 작업이 수행되는 동안에는 파티셔닝 방법을 변경(예: range -> hash)할 수 없으며, 테이블의 구조를 변경할 수도 없습니다. 파티셔닝된 테이블을 삭제(drop)하는데 얼마나 오랜 시간이 걸리는지 경험해 본 일이 있으실 것입니다. 이처럼 시간이 오래 걸리는 것은 각 파티션을 별도의 세그먼트로 처리하기 때문입니다. Oracle Database 10g Release 2에서는 파티셔닝된 테이블을 삭제할 때, 각 파티션을 개별적으로 삭제합니다. 각각의 파티션이 개별적으로 삭제되기 때문에, 전체 테이블을 삭제하는 경우에 보다 적은 리소스를 사용하게 됩니다. 그 동작 방식을 확인하기 위해, 10046 트레이스를 활성화하고 세션의 트레이스를 수행해 봅시다. 그런 다음 테이블을 삭제합니다. 트레이스 파일을 조사해 보면, 파티셔닝된 테이블이 다음과 같은 과정을 거쳐 삭제되었음을 알 수 있습니다. 위에서 확인할 수 있듯, 파티션은 순차적으로 삭제되었습니다. 이와 같은 방법으로 삭제 과정에서의 리소스 사용량을 최소화하고 성능을 개선할 수 있습니다.데이터 웨어하우징 및 통합 관련 기능
데이터 웨어하우징 및 통합 관련 기능
MV 로그를 이용하지 않고 파티션 변경 여부 추적하기
SQL> select *
2 from dba_mview_logs
3 where master = 'ACCOUNTS';no rows selected
update accounts set last_name = '...'
where acc_mgr_id = 3;
select SUBOBJECT_NAME, value from v$segment_statistics
where owner = 'ARUp'
and OBJECT_NAME = 'ACCOUNTS'
and STATISTIC_NAME = 'logical reads'
order by SUBOBJECT_NAME
/SUBOBJECT_NAME VALUE
------------------------------ ----------
p1 8320
p10 8624
p2 12112
p3 11856
p4 8800
p5 7904
p6 8256
p7 8016
p8 8272
p9 7840
pMAX 25611 rows selected.
execute dbms_mview.refresh('ACC_VIEW','F')
SUBOBJECT_NAME VALUE
------------------------------ ----------
p1 8320
p10 8624
p2 12112
p3 14656
p4 8800
p5 7904
p6 8256
p7 8016
p8 8272
p9 7840
pMAX 256
다수 MV를 활용한 Query Rewrite
SQL> DESC HOTELS
Name Null Type
----------------------------------------- -------- -------------
HOTEL_ID NOT NULL NUMBER(10)
CITY VARCHAR2(20)
STATE CHAR(2)
MANAGER_NAME VARCHAR2(20)
RATE_CLASS CHAR(2)SQL> DESC RESERVATIONS
Name Null Type
----------------------------------------- -------- -------------
RESV_ID NOT NULL NUMBER(10)
HOTEL_ID NUMBER(10)
CUST_NAME VARCHAR2(20)
START_DATE DATE
END_DATE DATE
RATE NUMBER(10)SQL> DESC TRANS
Name Null Type
----------------------------------------- -------- -------------
TRANS_ID NOT NULL NUMBER(10)
RESV_ID NOT NULL NUMBER(10)
TRANS_DATE DATE
ACTUAL_RATE NUMBER(10)HOTELS
create materialized view mv_hotel_resv
refresh complete
enable query rewrite
as
select city, resv_id, cust_name
from hotels h, reservations r
where r.hotel_id = h.hotel_id;
and
create materialized view mv_actual_sales
refresh complete
enable query rewrite
as
select resv_id, sum(actual_rate) from trans group by resv_id;
select city, cust_name
from hotels h, reservations r
where r.hotel_id = h.hotel_id;
select city, cust_name
from mv_hotel_resv;
SQL> set autot traceonly explain
SQL> select city, cust_name
2> from hotels h, reservations r
3> where r.hotel_id = h.hotel_id;Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=80 Bytes=2480)
1 0 MAT_VIEW ACCESS (FULL) OF 'MV_HOTEL_RESV' (MAT_VIEW) (Cost=3 Card=80 Bytes=2480)
select city, sum(actual_rate)
from hotels h, reservations r, trans t
where t.resv_id = r.resv_id
and r.hotel_id = h.hotel_id
group by city;
Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=6 Bytes=120)
1 0 SORT (GROUp BY) (Cost=8 Card=6 Bytes=120)
2 1 HASH JOIN (Cost=7 Card=516 Bytes=10320)
3 2 MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_HOTEL_RESV' (MAT_VIEW REWRITE)
(Cost=3 Card=80 Bytes=1040)
4 2 TABLE ACCESS (FULL) OF 'TRANS' (TABLE)
(Cost=3 Card=516 Bytes=3612)
Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=6 Bytes=120)
1 0 SORT (GROUp BY) (Cost=8 Card=6 Bytes=120)
2 1 HASH JOIN (Cost=7 Card=80 Bytes=1600)
3 2 MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE)
(Cost=3 Card=80 Bytes=560)
4 2 MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_HOTEL_RESV' (MAT_VIEW REWRITE)
(Cost=3 Card=80 Bytes=1040)
백업을 이용한 Transportable Tablespace의 생성
RMAN> transport tablespace accdata
2> TABLESpACE DESTINATION = '/home/oracle'
3> auxiliary destination = '/home/oracle';
rman target=/ log=tts.log
파티셔닝된 Index-Organized 테이블의 신속한 파티션 분리
온라인 재정의를 통한 LONG -> LOB 변환
SQL> desc acc_mesg
Name Null Type
----------------------------------------- -------- --------- ACC_NO NOT NULL NUMBER
MESG_DT NOT NULL DATE
MESG_TEXT LONG
create table ACC_MESG_INT
(
acc_no number,
mesg_dt date,
mesg_text clob
);
1 begin
2 dbms_redefinition.start_redef_table (
3 UNAME => 'ARUp',
4 ORIG_TABLE => 'ACC_MESG',
5 INT_TABLE => 'ACC_MESG_INT',
6 COL_MAppING => 'acc_no acc_no, mesg_dt mesg_dt, to_lob(MESG_TEXT) MESG_TEXT'
7 );
8* end;
begin
dbms_redefinition.sync_interim_table(
uname => 'ARUp',
orig_table => 'ACC_MESG',
int_table => 'ACC_MESG_INT'
);
end;
/
begin
dbms_redefinition.finish_redef_table (
UNAME => 'ARUp',
ORIG_TABLE => 'ACC_MESG',
INT_TABLE => 'ACC_MESG_INT'
);
end;
/
SQL> desc acc_mesg
Name Null Type
----------------------------------------- -------- --------- ACC_NO NOT NULL NUMBER
MESG_DT NOT NULL DATE
MESG_TEXT
단일 파티션의 온라인 재구성
SQL> desc trans
Name Null Type
--------------------------------- -------- -------------------------
TRANS_ID NUMBER
TRANS_DATE DATE
TXN_TYpE VARCHAR2(1)
ACC_NO NUMBER
TX_AMT NUMBER(12,2)
STATUS
partition by range (trans_date)
(
partition y03q1 values less than (to_date('04/01/2003','mm/dd/yyyy')),
partition y03q2 values less than (to_date('07/01/2003','mm/dd/yyyy')),
partition y03q3 values less than (to_date('10/01/2003','mm/dd/yyyy')),
partition y03q4 values less than (to_date('01/01/2004','mm/dd/yyyy')),
partition y04q1 values less than (to_date('04/01/2004','mm/dd/yyyy')),
partition y04q2 values less than (to_date('07/01/2004','mm/dd/yyyy')),
partition y04q3 values less than (to_date('10/01/2004','mm/dd/yyyy')),
partition y04q4 values less than (to_date('01/01/2005','mm/dd/yyyy')),
partition y05q1 values less than (to_date('04/01/2005','mm/dd/yyyy')),
partition y05q2 values less than (to_date('07/01/2005','mm/dd/yyyy'))
)
begin
dbms_redefinition.can_redef_table(
uname => 'ARUp',
tname => 'TRANS',
options_flag => dbms_redefinition.cons_use_rowid,
part_name => 'Y03Q2');
end;
/
create table trans_temp
(
trans_id number,
trans_date date,
txn_type varchar2(1),
acc_no number,
tx_amt number(12,2),
status varchar2(1)
)
tablespace transy03q2
/
begin
dbms_redefinition.start_redef_table(
uname => 'ARUp',
orig_table => 'TRANS',
int_table => 'TRANS_TEMp',
col_mapping => NULL,
options_flag => dbms_redefinition.cons_use_rowid,
part_name => 'Y03Q2');
end;
/
begin
dbms_redefinition.sync_interim_table(
uname => 'ARUp',
orig_table => 'TRANS',
int_table => 'TRANS_TEMp',
part_name => 'Y03Q2');
end;
/Finally, finish the process with
begin
dbms_redefinition.finish_redef_table(
uname => 'ARUp',
orig_table => 'TRANS',
int_table => 'TRANS_TEMp',
part_name => 'Y03Q2');
end;
파티션 단위 테이블 Drop
alter session set events '10046 trace name context forever, level 12';
delete from tabpart$ where bo# = :1
delete from partobj$ where obj#=:1
delete from partcol$ where obj#=:1
delete from subpartcol$ where obj#=:1