무결성 종류

설명

SQL 명령문

비고

개체무결성

entity integrity

모든 테이블은 primary key가 있고 primary key 값은 NULL이 아니며 unique 해야한다.

PRIMARY KEY

NOT NULL

3가지

기본

제약

도메인 무결성

domain integrity

도메인의 값은 해당 속성의 타입과 값 제약을 준수해야한다.

CHECK

참조 무결성

referential integrity

다른 릴레이션을 참조하는 속성의 값은 해당 릴레리션에 존재하는 값을 참조해야한다.

FOREIGN KEY

사용자 정의 무결성

(business rule)

예) 1년이 넘지않은 고객의 지난 1달간의 주문실적

select sum(주문수량), sum(주문금액), count(*), count(distinct 고객번호)

from 주문

where 고객번호 in

                     (select 고객번호 from 고객 where 가입일자 >= trunc(add_month(sysdate, -12)))

              and 주문일자 >= trunc(add_month(sysdate, -1))

-> subquery unnesting(4장 2절) 후, NL 조인한다.

-> 만약 고객테이블에 PK 제약이 없다면 sort unique 연산을 먼저 수행해야한다.

     PK 제약이 없으면 4장2절 서브쿼리 unnesting의 (6)번의 서브쿼리가 M쪽 집합이거나

    nonunique 인덱스 일 경우가 된다.

-> 다른 예로 수정가능 조인뷰(updatable join view)의 경우 1쪽 테이블에 PK 제약이 있어야 한다.

    (2장 7절 참조)

예)

select deptno, count(*) from emp

group by deptno 

-> deptno 칼럼에 인덱스가 있는 경우 알고리즘

    if  deptno 속성에 NOT NULL 제약이 있을 경우

    then   index full scan, index fast full scan 사용

    else   table full scan(null 값의 가능성 때문에)

예)

alter table emp modify sal check (sal <= 5000)

-> 옵티마이저는 check 제약을 이용한다.

-> 아래 질의의 경우 filter(null is not null)을 추가해 불필요한 I/O를 줄인다.

    select * from emp where sal > 5000

   Rows Row Source Operation

   ---- ----------------------------------

   0 FILTER(cr=0 pr=0 time=9 us)

   0 TABLE ACCESS FULL EMP ( cr=0 pr=0 pw=0 time=0 us)

(힌트 사용 예) - ordered 힌트를 사용하여 옵티마이저가 최적화를 빠르게 완료했다.

SQL> create table t1 ( a number, b varchar2(100) );

SQL> create table t2 ( a number, b varchar2(100) );

SQL> create table t3 ( a number, b varchar2(100) );

SQL> create table t4 ( a number, b varchar2(100) );

SQL> create table t5 ( a number, b varchar2(100) );

SQL> alter system flush shared_pool;

시스템이 변경되었습니다.

SQL>

SQL> declare

          2 l_cnt number;

          3 begin

          4 for i in 1..10000

          5 loop

          6 execute immediate ' select /*+ ordered */ count(*)' ||

          7 ' from t1, t2, t3, t4, t5 ' ||

          8 ' where t1.a = ' || i ||

          9 ' and t2.a = ' || i ||

         10 ' and t3.a = ' || i ||

         11 ' and t4.a = ' || i ||

         12 ' and t5.a = ' || I into l_cnt;

         13 end loop;

         14 end;

         15 /

PL/SQL 처리가 정상적으로 완료되었습니다.

경 과: 00:00:15.31

SQL>

SQL> alter system flush shared_pool;

시스템이 변경되었습니다.

SQL>

SQL> declare

          2 l_cnt number;

          3 begin

          4 for i in 1..10000

          5 loop

         6 execute immediate ' select count(*)' ||

         7 ' from t1, t2, t3, t4, t5 ' ||

         8 ' where t1.a = ' || i ||

         9 ' and t2.a = ' || i ||

         10 ' and t3.a = ' || i ||

         11 ' and t4.a = ' || i ||

         12 ' and t5.a = ' || I into l_cnt;

         13 end loop;

         14 end;

         15 /

PL/SQL 처리가 정상적으로 완료되었습니다.

경 과: 00:00:33.81

select name, value, isdefault, default_value

from v$sys_optimizer_env;

NAME                           VALUE                     ISD DEFAULT_VA
------------------------------ ------------------------- --- ----------
parallel_execution_enabled     true                      YES true
optimizer_features_enable      10.2.0.1                  YES 10.2.0.1
cpu_count                      2                         YES 2
active_instance_count          1                         YES 1
parallel_threads_per_cpu       2                         YES 2
hash_area_size                 131072                    YES 131072
bitmap_merge_area_size         1048576                   YES 1048576
sort_area_size                 65536                     YES 65536
sort_area_retained_size        0                         YES 0
pga_aggregate_target           198656 KB                 YES 198656 KB
parallel_query_mode            enabled                   YES enabled

NAME                           VALUE                     ISD DEFAULT_VA
parallel_dml_mode              disabled                  YES disabled
parallel_ddl_mode              enabled                   YES enabled
optimizer_mode                 all_rows                  YES all_rows
cursor_sharing                 exact                     YES exact
star_transformation_enabled    false                     YES false
optimizer_index_cost_adj       100                       YES 100
optimizer_index_caching        0                         YES 0
query_rewrite_enabled          true                      YES true
query_rewrite_integrity        enforced                  YES enforced
workarea_size_policy           auto                      YES auto
optimizer_dynamic_sampling     2                         YES 2

NAME                           VALUE                     ISD DEFAULT_VA
statistics_level               typical                   YES typical
skip_unusable_indexes          true                      YES true
optimizer_secure_view_merging  true                      YES true

(SQL 예 - 오라클 7,8,9,10 버전)

- select max(empno) from emp;

- 오라클 7 : 최적화를 위하여 index_desc 힌트와 rownum 조건을 사용

   select /*+ index_desc(emp emp_pk */ from emp where rownum=1

- 오라클 8i, 9i :

    if 통계정보 있으면

    then 옵티마이저가 알아서 처리해준다(INDEX FULL SCAN (MIN/MAX) EMP_PK)

    else (INDEX FULL SCAN EMP_PK)

- 오라클 10g : 동적 샘플링을 통해 바로 동작

    select max(empno) from emp

(SQL 예 1 - 오라클 11g와 MS-SQL Server)

- select min(empno), max(empno) from emp;

- 오라클 11g :

   INDEX FULL SCAN EMP_PK

- MS-SQL Server :

   index를 앞에서 한번 뒤에서 한번 읽는다.

- MS-SQL이 효율적인 방법을 사용한다!!

(SQL 예 2 - 오라클 11g와 MS-SQL Server)

- M쪽 집합 PK가 설정된 1쪽과 Outer 조인을 하면 결과건수는 M쪽 집합의 개수이다.

  조인조건외 1쪽 집합을 참조하지 않으면 1쪽 집합과 조인 액세스는 필요없다.

  (join elimination 혹은 table elimination 이라고 하며 4장 6절에서 설명)

- 오라클 10g : 무조건 1쪽 집합과 조인을 한다.

   select e.empno, e.ename, e.sal, e.hiredate

   from emp e, dept d

   where d.deptno(+)=e.deptno

- MS-SQL Server : M쪽 outer 테이블만 액세스