IV. Schema Management

1. DDL Wait 옵션

• DDL 수행을 위한 Exclusive Lock 을 얻지 못하는 경우 정해진 시간만큼 대기
• 10g 까지는 DDL 수행을 위한 Exclusive Lock 획득 실패시, ORA-54 resource busy 에러 발생
• DDL_LOCK_TIMEOUT
  Default - O (Nowait ), 0 ~ 1,000,000 ( seconds ) ,
  1,000,000 은 lock 을 얻을 때 까지 무한 Wait
• DDL Wait 장점
  모든 DDL을 업무 중에 수행할 수는 없겠지만,
  업무에 영향이 많이 영향이 많지 않은 DDL 에 한해서는 업무시간에 성공적으로 수행할 수 있게 되었다.

SQL> alter session set ddl_lock_timeout = 100 ;
Session altered.

SQL> show parameters ddl_lock_timeout

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
ddl_lock_timeout                     integer                           100

SQL> alter system set ddl_lock_timeout=200 ;
System altered.

SQL> show parameters ddl_lock_timeout

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
ddl_lock_timeout                     integer                           200

h3.2. 컬럼 추가 기능 향상

• 테이블에 column 추가시(Default Value가 지정되고, NOT NULL 제한 사항이 있는 경우) Default Value 가
  Table 에 직접 Insert 되지 않고 Dictionary 에 metadata로만 가지고 있는 기능이다.
• 10g 이전 - add column, modify column default value , update ( transaction size ), modify 칼럼 not null
• 컬럼 추가 기능 향상의 장점
  Add Column 작업이 매우 짧은 시간 내에 끝난다.
  실시간 Default Value 의 Insert 로 인한 부하(Insert 작업과 Redo 발생) 이 없어졌다.
  Add Column 수행 중의 SQL Waiting 현항이 해소된다.

3. 가상 컬럼

• 가상컬럼(Virtual Columns)기능은 물리적 공간을 차지하는 column 이 아닌 Dictionary 에 정의된 column 이다.
• 가상컬럼의 장점
  물리적인 공간을 차지하지 않는다.
  가상 column은 표현식에 포함된 다른 column 값이 변하더라도, 변경된 값이 반영되어 조회되므로 추가적인
  처리 작업이 필요하지 않다.
  복잡한 비즈니스 로직을 가상 Column 으로 유연하게 대처할 수 있다.
• 주의사항
  가상 컬럼에는 데이터를 입력할 수 없다.

SQL> create table sales (   
  2  sales_id number,
  3  cust_id number,
  4  sales_amt number,
  5  sales_category varchar2(6)
  6  generated always as
  7  ( case 
  8    when sales_amt <= 10000 then 'LOW'
  9    when sales_amt > 10000 and sales_amt <= 100000 then 'MEDIUM'
 10    when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'
 11    else 'ULTRA'
 12  end
 13  ) virtual 
 14  ) ;

Table created.

SQL>  create index in_sales_cat on sales ( sales_category ) ;
Index created.

SQL> select index_type from user_indexes where index_name ='IN_SALES_CAT';

INDEX_TYPE
---------------------------------------------------------------------------------
FUNCTION-BASED NORMAL



SQL> select column_expression from user_ind_expressions where index_name ='IN_SALES_CAT';

COLUMN_EXPRESSION
--------------------------------------------------------------------------------
CASE  WHEN "SALES_AMT"<=10000 THEN 'LOW' WHEN ("SALES_AMT">10000 AND "SALES_AMT"
<=100000) THEN 'MEDIUM' WHEN ("SALES_AMT">100000 AND "SALES_AMT"<=1000000) THEN
'HIGH' ELSE 'ULTRA' END


SQL> insert into sales values (5,100,300,'HIGH','XX') ;
insert into sales values (5,100,300,'HIGH','XX')
            *
ERROR at line 1:
ORA-00913: too many values

4. Invisible Index

• Invisible Index 는 물리적으로 Valid 한 Index 를 Optimizer에게만 보이지 않게 하는 기능이다.
  "특히 테스트 서버에서 특정 Index 가 없을 경우, SQL Plan 과 SQL 수행속도가 어떻게 될 것인가 ?"
  에 대한 해답을 제시해준다.
• Index 가 Invible 로 지정된더라도 DML 에 의한 Index 변경은 원래대로 이루어진다.
  다만 optimizer 에게만 보이지 않게 하는 기능이다.
• 주의사항
  Index 가 Rebuild 된 후에는 이전에 정의된 Invible 속성이 Visible 로 바뀐다.

SQL> alter index <인덱스명> invisible ;
Index altered.

SQL> alter index <인덱스명> visible ;
Index altered.

SQL> create table res ( a1 number, a2 varchar2(100)) ;
Table created.

SQL> begin       
  2  for i in 1 .. 10000 loop
  3  insert into res values ( i, 'TEST');
  4  end loop ;
  5  commit ;
  6  end ;
  7  /

PL/SQL procedure successfully completed.

SQL> commit ;
Commit complete.

SQL> create index in_res_a1 on res ( a1 ) ;
Index created.

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  d19nv7npgnd2g, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from res where a1=1

Plan hash value: 989048864

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| RES       |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | IN_RES_A1 |      1 |      1 |      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A1"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)


23 rows selected.

SQL> alter index in_res_a1 invisible ;
Index altered.

SQL> select /*+ gather_plan_statistics */ * from res where a1=1 ;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  d19nv7npgnd2g, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from res where a1=1

Plan hash value: 3824022422

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |      24 |
|*  1 |  TABLE ACCESS FULL| RES  |      1 |      1 |      1 |00:00:00.01 |      24 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A1"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)

22 rows selected.

SQL> select visibility from user_indexes where index_name ='IN_RES_A1';

VISIBILITY
---------------------------
INVISIBLE

5. 읽기 전용 테이블

• 테이블 단위의 read only 지정이 가능하다.
• 기존에는 테이블에 대한 DML을 막는 방법은 가상의 user를 만들고 테이블에 대한 select 권한만을 부여하는 방식이 이용되었다.
  Read only 테이블은 DML 은 제한되어도 DDL은 허용된다.
• 읽기 전용 테이블 장점
  안정된 테이블의 유지보수 작업이 가능하다.
  특정 테이블의 read only 모드 운영이 필요할 경우 손쉽게 구현 가능하다.

SQL> alter table xxxx read only ;
Table altered.

SQL> alter table xxxx read write ;
Table altered.

SQL> select read_only from dba_tables where table_name ='RES';
READ_ONLY
---------
YES

Elapsed: 00:00:00.28
SQL> select count(*) from RES ;

  COUNT(*)
----------
     10000

SQL> delete RES ;
delete RES
       *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SYS"."RES"

SQL> truncate table RES ;
truncate table RES
               *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SYS"."RES"