DBMS 1
DA, SQL, DB보안 등 실무자를 위한 위한 DB기술 바이블!
일상적인 작업을 훨씬 빠르고 단순하게 만들어 주는 새로운 기능들을 이용하여 좀 더 효과적으로 데이터베이스 오브젝트를 관리할 수 있습니다. Oracle Database 11g는 관리 작업의 단순화를 위한 매우 다양한 기능을 포함하고 있을 뿐 아니라, 어떤 경우에는 매우 번거로운 작업을 단 한 줄의 명령으로 간소화시키기도 합니다. 이번 연재에서는 이러한 기능들 중 몇 가지에 대해 배워 보기로 하겠습니다. Acme Retailers의 DBA인 질은 SALES 테이블에 TAX_CODE라는 컬럼을 추가하는 작업을 진행 중입니다. 질은 아래와 같이 SQL 명령을 실행합니다: 하지만 "Table altered"라는 결과 대신, 아래와 같은 에러를 확인합니다. 에러 메시지가 모든 것을 말해 줍니다. 테이블이 다른 트랜잭션에 의해 사용되고 있어, 테이블에 Exclusive Lock을 설정하는 것이 불가능하다는 내용입니다. 물론 테이블의 로우에 걸린 락은 언젠가는 풀리게 마련입니다. 세션이 커밋을 실행하면 로우에 대한 락은 해제됩니다. 하지만 그 전에 다른 세션이 테이블의 다른 로우에 업데이트를 시작할 수 있으며, 어쩌면 테이블에 Exclusive Lock을 설정할 수 있는 기회를 영영 얻을 수 없을 지도 모릅니다. 일반적인 비즈니스 환경에서 테이블 락을 설정할 수 있는 기회는 주기적으로 찾아 오지만, DBA가 이 시간대에 alter 커맨드를 수행할 수 있으리라는 보장이 없습니다. 물론 질은 Exclusive Lock을 설정할 수 있을 때까지 커맨드를 쉬지 않고 입력하거나, 또는 미쳐 버리거나 둘 중의 한 가지를 선택할 수도 있습니다. Oracle Database 11g은 더 바람직한 대안을 제공합니다. DDL Wait 옵션이 바로 그것입니다. 이번에는 질이 아래와 같이 명령을 실행합니다: 이제부터 실행되는 세션의 DDL 구문이 Exclusive Lock을 설정할 수 없는 경우라도 에러는 발생하지 않습니다. 그 대신, 세션은 10 초간을 대기합니다. 이 10 초 동안, 세션은 명령이 성공하거나 또는 제한 시간이 경과할 때까지 DDL 작업을 연속적으로 시도합니다. 이제 질이 아래와 같이 실행합니다: 이번에는 에러가 발생하지 않습니다. Exclusive Lock을 얻을 수 있을 때까지 반복적으로 명령을 입력하는 대신, 질은 Database 11g의 반복 시도 기능을 활용합니다. 이 기능은 마치 통화 중인 전화번호에 대해 반복 연결 시도하는 전화기처럼 동작합니다. 질은 이 기능에 너무도 만족해 하면서 다른 DBA들에게 이 정보를 공유합니다. 운영 중인 시스템에서 DDL 명령을 내려 본 일이 있는 데이터베이스 관리자라면 누구든 이 새로운 기능을 반가운 소식으로 받아들일 것입니다. 질은 또 생각합니다. 이 동작 방식을 디폴트로 설정해서 매번 ALTER SESSION 구문을 반복적으로 실행할 필요가 없게 만든다면 더 좋지 않을까요 가능합니다. "ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 10"과 같이 실행하는 경우 세션은 DDL 작업이 진행되는 동안 자동으로 DDL WAIT 옵션을 사용합니다. 다른 ALTER SYSTEM 구문과 마찬가지로, 이 옵션은 ALTER SESSION 구문에 의해 무시(override)될 수 있습니다. 질은 이 기능 하나만으로도 무척 만족해 하고 있습니다. 하지만 앞의 문제와 관련하여 또 고민해야 할 일이 생겼습니다. 질이 추가하려는 TAX_CODE 컬럼은 NOT NULL 제약 조건이 적용되어 있어야 합니다. 이미 데이터가 입력된 테이블에 NOT NULL 컬럼을 추가하기 위해 디폴트 값 'XX'를 적용해야 합니다. 질은 아래와 같은 SQL 구문을 작성합니다: 하지만 문제가 완전히 해결된 것은 아닙니다. SALES 테이블은 약 4 억 개의 로우를 포함하는 거대한 테이블입니다. 이 명령을 실행한다면 컬럼이 추가되기는 하겠지만, 모든 'XX' 로우에 값이 업데이트되기 전까지는 테이블에 대한 컨트롤을 회복할 수 없을 것입니다. 4 억 개의 로우를 업데이트하는 작업은 매우 오랜 시간이 걸릴 뿐 아니라, 언두 세그먼트를 가득 채우고, 대량의 리두 데이터가 쌓이고, 상당한 수준의 성능 오버헤드가 발생하게 될 것입니다. 결국 질은 시스템을 중단시킬 수 있을 때까지 작업을 유보하기로 합니다. Oracle Database 11g라면 뭔가 다른 대안을 제시해 주지 않을까요 대안이 있습니다. 11g에서는 위 명령을 실행하더라도 테이블의 모든 레코드에 대한 업데이트가 실행되지 않습니다. 새로 추가되는 레코드의 경우 컬럼의 디폴트 값이 자동으로 입력되므로 문제될 것이 없습니다. 하지만 사용자가 기존의 레코드에서 이 컬럼을 조회하면 NULL이 반환되지 않을까요 그렇지 않습니다. 사용자가 기존 레코드의 컬럼을 선택하면, 오라클은 데이터 딕셔너리에서 디폴트 값을 조회한 후 이 값을 사용자에게 반환합니다. 디폴트 값을 갖는 NOT NULL 컬럼을 새로 추가하고도 리 Acme사의 데이터베이스에 저장된 SALES 테이블은 아래와 같은 구조를 갖고 있습니다: 일부 사용자들이 세일즈 유형의 구분을 위한 SALES_CATEGORY 컬럼을 추가해 달라고 요청하고 있습니다. 이 컬럼은 판매액, 고객 분류 등을 기준으로 LOW, MEDIUM, HIGH, ULTRA 등으로 설정됩니다. 이 컬럼 값을 기준으로 처리 담당 직원을 할당하고 적절한 조치를 수행하게 될 것입니다. 이 컬럼의 값을 결정하기 위한 로직이 아래와 같습니다: 이 컬럼의 구현 작업이 비즈니스적으로 매우 중요한 사안임에도 불구하고, 개발 팀에서 이 코드를 당장 구현하기에는 어려움이 있어 보입니다. 물론 데이터베이스 관리자가 테이블에 sales_category라는 새로운 컬럼을 추가하고 트리거를 이용하여 컬럼 값을 생성하는 방법을 사용할 수도 있습니다. 하지만 트리거 코드의 컨텍스트 스위칭으로 인해 성능 문제가 발생할 소지가 있습니다. Oracle Database 11g에서는 이런 상황에서 트리거를 사용할 필요가 없습니다. 가상 컬럼(virtual column)을 이용하면 간단하게 작업을 마무리할 수 있습니다. 가상 컬럼을 이용하면, 복잡성 또는 성능 문제를 전혀 수반하지 않고 비즈니스 로직을 만족하는 컬럼을 유연하게 생성할 수 있습니다. 테이블의 구현 방법이 아래와 같습니다: 6, 7 번 라인을 참고하십시오. 이 컬럼의 "generate always as"는, 컬럼 값이 테이블의 일부로서 저장되지 않고 런타임에 생성된다는 의미입니다. 그리고 이 구문 뒤의 CASE 문을 통해 값을 계산하는 방법이 정의되고 있습니다. 마지막으로 15 번 라인의 "virtual"은 이 정의가 가상 컬럼을 위한 정의임을 다시 한 번 확인하고 있습니다. 이제 새로운 레코드를 추가해 봅시다: 가상 컬럼 값은 모두 정상적으로 표시되고 있습니다. 이 컬럼이 실제로 저장되어 있는 것은 아니지만, 테이블의 다른 컬럼과 마찬가지로 어떤 상황에서든 조회가 가능합니다. 가상 컬럼에 대해 인덱스를 생성하는 것도 가능합니다. 그 결과로 함수 기반 인덱스(function-based index)가 생성됩니다. 본 시리즈의 파티셔닝 부분에서 설명한 것처럼 가상 컬럼을 기준으로 파티션을 생성할 수도 있습니다. 하지만 이 컬럼에 값을 입력할 수는 없습니다. 실제로 값 입력을 시도한다면 아래와 같은 결과를 얻을 것입니다: 가끔 우리는, 우리가 생성한 인덱스가 정말로 사용자 쿼리에 도움이 되는지 자문해 볼 때가 있습니다. 어쩌면 한 사람에게 도움이 되는 동안 다른 10 명에게 피해를 끼칠 수도 있습니다. 인덱스는 INSERT 구문의 성능에 부정적인 영향을 미칩니다. DELETE, UPDATE 구문의 경우에도 성능 문제가 발생할 수 있습니다. 이는 WHERE 조건절에 인덱스 컬럼이 포함되었는지의 여부와 무관합니다. 이런 질문을 던져 볼 필요가 있습니다. 인덱스가 정말 사용되고 있는 것일까요 인덱스가 드롭 되면 쿼리 성능에는 어떤 변화가 있을까요 물론 인덱스를 드롭 처리하고 쿼리 성능의 변화를 살펴 볼 수도 있습니다. 하지만 이런 작업은 실제로는 그리 간단하지 않습니다. 드롭 처리한 인덱스가 실제로 쿼리 성능에 도움이 되고 있었다면 인덱스를 처음부터 다시 생성해야 합니다. 또 인덱스 생성이 완료되기 전까지는, 인덱스 사용이 전혀 불가능합니다. 그리고 인덱스의 재생성 작업에는 많은 리소스가 필요합니다. 특정 쿼리에서만 인덱스를 사용하도록 하는 옵션이 있다면 좋지 않을까요 Oracle Database 11g 이전 버전에서는 ALTER INDEX ... UNUSABLE 구문을 사용할 수가 없었습니다. 이 구문을 실행하면 테이블에 대한 모든 DML 작업이 실패하기 때문입니다. 하지만 이제 인비저블 인덱스(invisible indexes)라는 새로운 대안을 사용할 수 있게 되었습니다. 간단하게 설명하자면, 인덱스를 옵티마이저에서 "보이지 않게" 설정함으로써 쿼리가 사용할 수 없도록 하는 방법입니다. 쿼리에서 인비저블 인덱스를 사용하려면, 명시적으로 힌트를 적용해야 합니다. 그 예가 아래와 같습니다. RES 테이블에 아래와 같이 인덱스를 생성하였습니다: 아래 명령을 실행하고 실행 계획을 확인하면, 인덱스가 사용되고 있음을 확인할 수 있습니다: 이제 이 인덱스를 인비저블 인덱스로 설정해 봅시다: 인덱스는 더 이상 사용되지 않고 있습니다. 옵티마이저에서 인덱스를 사용하도록 하려면, 힌트에 인덱스 이름을 명시해야 합니다: 인덱스가 다시 옵티마이저에 의해 사용되고 있습니다. 또는 세션 레벨 매개변수에서 인비저블 인덱스를 사용하도록 설정할 수도 있습니다: 이 기능은 코드를 변경할 수 없는 써드 파티 애플리케이션 환경에서 매우 유용하게 활용됩니다. 인덱스를 생성하는 시점에 INVISIBLE 조건을 사용해서 인비저블 인덱스로 생성할 수도 있습니다. USER_INDEXES 딕셔너리 뷰를 사용해서 인덱스의 현재 설정을 확인할 수 있습니다. 이 인덱스의 리빌드 작업을 수행하면, 인덱스가 'VISIBLE' 상태로 전환된다는 점에 주의하시기 바랍니다. 리빌드를 완료한 후 다시 한 번 명시적으로 'INVISIBLE'로 설정해 주어야 합니다. 그렇다면 여기서 'INVISIBLE'이라는 것이 정확히 의미하는 바가 무엇일까요 사용자에게 보이지 않는다는 의미는 아니며, 단지 옵티마이저가 인덱스를 볼 수 없다는 의미입니다. INSERT, UPDATE, DELETE 등의 데이터베이스 작업에서는 이 인덱스가 계속적으로 업데이트될 것입니다. 인비저블 인덱스를 생성할 때 한 가지 주의할 점이 있습니다. DML 작업으로 인한 성능 오버헤드가 발생하는 동안에는 인덱스로 인한 성능 효과를 확인할 수 없습니다. Acme 데이터 웨어하우스 시스템의 개발자인 로빈이 한 가지 고전적인 문제를 놓고 고민 중입니다. 바로 ETL 프로세스에서 여러 테이블을 각각 서로 다른 주기로 업데이트하는 문제입니다. 업데이트가 진행되는 동안에도, 비즈니스 룰에 따라 사용자에게 테이블 접근을 허용해야 합니다. 하지만 사용자가 테이블의 데이터를 업데이트할 수는 없습니다. 따라서 이 테이블들에서 사용자의 DML 권한을 취소시키는 것은 대안이 될 수 없습니다. 결국 로빈은 테이블의 읽기/쓰기 모드와 읽기 전용 모드 간을 전환할 수 있는 메커니즘을 필요로 하고 있습니다. 이 작업은 일견 간단해 보이지만 실제로는 무척 까다롭습니다. 로빈이 생각할 수 있는 대안으로 어떤 것이 있을까요 O한 가지 방법으로, 테이블에 INSERT, DELETE, UPDATE 작업에 대해 익셉션을 발생시키는 트리거를 생성할 수 있습니다. 하지만 트리거로 인한 컨텍스트 스위칭이 성능을 저하시킨다는 점이 문제입니다. 또 다른 대안으로 "1=2"처럼 항상 False 값을 반환하는 Virtual Private Database(VPD) 정책을 생성하는 방법이 있습니다. 테이블에 VPD 정책이 실행될 때마다 False 값이 반환되므로 DML 작업은 실패합니다. 이 방법은 트리거 옵션보다 성능적으로는 나은 편이지만, 사용자들이 "policy function returned error"와 같은 에러 메시지를 확인하게 된다는 점에서 그리 바람직하지 못합니다. Oracle Database 11g는 한층 개선된 대안을 제공합니다. 아래와 같은 방법으로 테이블을 읽기 전용으로 전환하기만 하면 됩니다: 이제 사용자가 아래와 같은 DML 구문을 실행하면, Oracle Database 11g는 바로 에러를 발생시킵니다. 이 에러 메시지는 관리자가 의도하는 내용을 담고 있으며, 트리거, VPD 정책의 경우처럼 성능 오버헤드를 수반하지도 않습니다. 이제 DML 작업이 성공적으로 실행될 것입니다: 테이블이 읽기 전용 모드로 전환된 상태에서는 DML 작업만이 허용되지 않습니다. 인덱스 생성, 파티션 관리 등의 DDL 작업은 모두 실행이 가능합니다. 따라서 이 기능은 테이블 유지보수 관리를 수행할 때 매우 유용하게 활용됩니다. 테이블을 읽기 전용 상태로 만들어 놓고 필요한 DDL 구문을 수행한 뒤, 다시 읽기/쓰기 모드로 변경하면 간단하게 작업을 완료할 수 있습니다. 테이블의 상태는 dba_tables 데이터 딕셔너리 뷰의 read_only 컬럼에서 확인할 수 있습니다. 지금까지 설명 드린 것처럼, Oracle Database 11g 환경에서는 과거에는 무척 번거로웠던 작업들이 한층 단순해졌을 뿐 아니라, 일상적인 작업을 효과적으로 수행하기 위한 전혀 새로운 대안이 제공되고 있습니다. 필자는 오랜 세월 동안 오라클 데이터베이스의 기능 개선을 여러 차례 목격해 왔으며, 때로는 비즈니스 관점에서 새로운 이정표가 될 만한 개선이 이루어지기도 했습니다. 이번 버전 역시 이러한 범주에 포함시킬 수 있을 것입니다.스키마 관리
스키마 관리
간략한 개요
DDL Wait 옵션
SQL> alter table sales add (tax_code varchar2(10));alter table sales add (tax_code varchar2(10))
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SQL> alter session set ddl_lock_timeout = 10;
Session altered.
SQL> alter table sales add (tax_code varchar2(10));컬럼 추가 시에 디폴트 값 사용하기
alter table sales add tax_code varchar2(20) default 'XX' not null;SALES_ID NUMBER
CUST_ID NUMBER
SALES_AMT NUMBER
If sale_amt is more than:
And sale_amt is less than or equal to:
Then sale_category is:
0
1000
LOW
10001
100000
MEDIUM
100001
1000000
HIGH
1000001
Unlimited
ULTRA
SQL> create table sales
2 (
3 sales_id number,
4 cust_id number,
5 sales_amt number,
6 sale_category varchar2(6)
7 generated always as
8 (
9 case
10 when sales_amt <= 10000 then 'LOW'
11 when sales_amt > 10000 and sales_amt<= 100000 then 'MEDIUM'
12 when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'
13 else 'ULTRA'
14 end
15 ) virtual
16 );
SQL> create index in_sales_cat on sales (sale_category);
Index created.
insert into sales values (5,100,300,'HIGH','XX')
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
인비저블 인덱스 (Invisible Index)
SQL> create index in_res_guest on res (guest_id);SQL> select * from res where guest_id = 101;
SQL> alter session set optimizer_use_invisible_indexes = true;SQL> select visibility
2 from user_indexes
3 where index_name = 'IN_RES_GUEST';
VISIBILITY
---------
INVISIBLE
읽기 전용 테이블
SQL> alter table TRANS read only;
Table altered.
SQL> delete trans;delete trans
*ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TRANS"
테이블을 업데이트 가능한 상태로 다시 변경하려면 아래와 같이 실행합니다:SQL> alter table trans read write;
Table altered.
SQL> update trans set amt = 1 where trans_id = 1;
1 row updated.
SQL> select read_only from user_tables where table_name = 'TRANS';
REA
---
NO
결론