따봉 구걸남's 블로그

- out 파라미터 쓰는 프로시져 호출하기

: 변수를 declare 구문에서 먼저 선언해주고 호출 해야함. 

declare 
   v_out varchar2(10);
 begin  
      sp_test(v_out, 'aaa', 'bbb');
     dbms_out.put_line('Result :'||v_out);
  end;

트랜잭션 고립 수준에 대해서 알아보 ...기 전에

트랜잭션의 4 가지 속성에 대해서 알아보자

1. 트랜잭션의 ACID 속성

 - 원자성 ( Atomicity )
트랜잭션은 더 이상 쪼개질수 없는 최소한의 작업 단위.
부분적으로 성공하는 일이 없어야 함

 - 일관성 ( Consistency )
트랜잭션이 성공적으로 완료되면 일관적인 DB상태를 유지해야한다

 - 격리성 ( Isolation )
트랜잭션끼리는 서로 영향을 주지 않고 독립적으로 실행되야함

 - 지속성 ( Durability )
커밋된 트랜잭션은 영원히 DB에 반영이 되어야 함

이 중 3번째 언급된 Isolation 의 level 에 대해서 정리할 것이다. 

2. 트랜잭션 격리 레벨

1) read uncommitted

  => 변경은 되었지만 아직 커밋이 되지 않은 레코드를 읽기 허용 (dirty read)

    완료되지 않은 작업이 롤백이 될 경우, 이를 읽고 있던 세션에서의 작업은 일관성의 문제가 발생

    대부분의 DBMS 는 디폴트로 허용하지 않음

2) read committed

  => 커밋이 완료된 레코드만 다른 세션에서 읽기가 허용됨. (oracle은 undo 를 통해 구현. DB2 등은 공유락으로 구현)

      1번 레벨에 비해서 일관성의 문제는 개선되었지만 non-repeatable read 문제 발생 

    * non-repeatable read

        한 트랜잭션 내에서 수행한 같은 쿼리가 중간에 들어온 쿼리 때문에 다른 결과를 리턴

     중간에 들어온 tx2 때문에 tx1 의 결과가 예상과 다른결과가 나오게 된다.   

3) repeatable read

      tx1 트랜잭션의  t1 시점의 쿼리에 for update 구문을 적용하면 중간에 들어온 tx2 는 대기를 하게 되고 

     tx1 은 일관성 있는 결과를 리턴하게 된다. 

    그런데 만약 update 가 아닌 중간에 insert 가 들어온다면 이것은 또 일관되지 않은 결과를 리턴하게 될 것이다.  

  * phantom read

  tx1 의 트랜잭션에서 상품의 합계가 t1 시점과 t4 시점이 서로 다른 일관성이 없는 결과를 리턴한다.

4) serializable

 중간에 삽입이되는 트랜잭션도 막아버리려면 고립 수준을 더 올려야 한다. 

set transaction isolation level serializable;

이것은 최고수준의 격리이며 일관성은 보장이 되지만 동시성은 상당히 떨어뜨리게 된다. 

 * oracle 은 scn 사용으로 lock 을 안쓰고도 일관성을 보장할수 있다. 

트랜잭션의 격리 레벨에 따라 일관성을 해치는 결과가 나올수있음을 인지하고 자신의 처한 환경에 따라 적절하게 대응해야 한다.

 아우터 조인은 별거 없으면서도 개발자들이 종종 실수 하는 쿼리 중 하나이다.

 아우터 조인의 개념과 활용을 한번 정리해 보자. 

• 1. 아우터 조인의 개념 

   일반 조인은 집합 A 와 B 가 있을 때 양쪽 집합 모두 조인 조건을 만족하는 rows 만 리턴한다. 

 

 

 

   반면, 아웃터 조인은 A 가 기준이라면 A 의 모든 rows 에 대하여

   집합 B 가 조인 조건에 만족하면 나오고 그렇지 않으면 null 을 리턴한다. 

 

   이때 A 와 B 의 관계가 1:1 인 경우, 결과 rows 는 A 의 갯수와 동일하지만 1:N 인 경우는 결과는 N 이 된다.  

 

• 2. 멀티컬럼 아우터 조인 

    아우터 조인 조건이 여러개인 것은 하나인 것과 다르지 않다. 

    즉, 조인조건이 '조건1' and '조건2' and '조건3' 이라면 조건을 모두 만족하는 rows 만 결과를 리턴한다. 

    나머지는 null 을 리턴한다. 

-- out multi col

-- multi col outer join 
drop table ot_test1;
drop table ot_test2;

create table ot_test1(
col1 int,
col2 varchar2(1),
col3 varchar2(1),
col4 varchar2(1)
);

create table ot_test2(
col1 int,
col2 varchar2(1),
col3 varchar2(1),
col4 varchar2(1)
);

insert into ot_test1 values('1','a','A','');
insert into ot_test1 values('1','a','B','!');
insert into ot_test1 values('1','b','A','@');
insert into ot_test1 values('2','a','A','#');
insert into ot_test1 values('3','a','A','');

insert into ot_test2 values('1','a','A','G');
insert into ot_test2 values('1','a','A','X');
insert into ot_test2 values('1','b','A','F');

select a.col1 a1, a.col2 a2, a.col3 a3, b.col4
  from ot_test1 a,
       ot_test2 b
where 1=1
and a.col1 = b.col1(+)
and a.col2 = b.col2(+)
and a.col3 = b.col3(+)
;

집합 A 에 5 개 rows 를 넣었고 집합 B 에는 3 개의 rows 를 넣었다.

아우터의 기준이 되는 A 의 5 개는 모두 나온다.

col1=1 col2=a col3=A 인 조인조건을 만족하는 B 집합이 2개가 모두 리턴된다.   

## 아우터 조인은 1:1 관계 에서는 스칼라 서브쿼리로 변경 가능하다. 

select a.col1 a1, a.col2 a2, a.col3 a3,
      (select b.col4
         from ot_test2 b
        where 1=1
          and a.col1 = b.col1
          and a.col2 = b.col2
          and a.col3 = b.col3
      ) b4
 from ot_test1 a
order by a1, a2, a3
;
=> ORA-001427

1:1 관계가 아닌 아우터 조인을 스칼라 서브쿼리로 변경하면 ORA-001427 가 뜬다.

이때 min, max 등의 그룹함수를 사용하면 에러를 회피할 수 있다.

select a.col1 a1, a.col2 a2, a.col3 a3,
      (select min(b.col4) 
         from ot_test2 b
         where 1=1
        and a.col1 = b.col1
        and a.col2 = b.col2
        and a.col3 = b.col3
      ) b4
  from ot_test1 a
order by a1, a2, a3
;
=> 스칼라서브쿼리에서 2개 이상의 행이 리턴되는 것을 막기 위해 min 함수를 썼다.

• 3. 연속 아우터 조인

중첩해서 아우터 조인을 사용할 경우도 있다.

 

이럴때는 어디에 (+) 를 표시해야 할까.

 

-- consecutive out join

drop table out_a;
drop table out_b;
drop table out_c;

create table out_a(
col1 int,
col2 varchar2(1)
);

create table out_b(
col1 int,
col2 varchar2(1)
);

create table out_c(
col1 int,
col2 varchar2(1)
);

insert into out_a values(1,'a');
insert into out_a values(2,'b');
insert into out_a values(3,'G');
insert into out_a values(4,'');

insert into out_b values(1,'a');
insert into out_b values(3,'');
insert into out_b values(5,'');
insert into out_b values(6,'d');

insert into out_c values(1,'a');
insert into out_c values(2,'a');
insert into out_c values(3,'c');
insert into out_c values(3,'f');
insert into out_c values(6,'a');

 

 

-- 올바른 중첩 아우터 조인 쿼리 

 

SELECT *
FROM OUT_A A
     ,OUT_B B
     ,OUT_C C
WHERE A.COL1 = B.COL1(+)
AND B.COL1 = C.COL1(+)
ORDER BY A.COL1
;

 

 

 

-- 잘못 작성된 아우터 조인 

 

SELECT *
  FROM OUT_A A
      ,OUT_B B
      ,OUT_C C
 WHERE A.COL1 = B.COL1(+)
   AND B.COL1 = C.COL1
ORDER BY A.COL1
;

 

 

 

 이것의 결과는  일반조인의 결과와 같다. 

 

-- 일반적인 조인  

 

SELECT *
  FROM OUT_A A
      ,OUT_B B
      ,OUT_C C
 WHERE A.COL1 = B.COL1
   AND B.COL1 = C.COL1
ORDER BY A.COL1
;

 

 

## A = B(+)  and B(+) = C 이런 아우터 조인은 가능할까?

 -- 이런 아우터 조인이 될까 

SELECT *
  FROM OUT_A A
      ,OUT_B B
      ,OUT_C C
 WHERE A.COL1 = B.COL1(+)
   AND B.COL1 (+) = C.COL1
ORDER BY A.COL1
=> 이 결과는 A 와 B 의 아우터 조인 집합과 C의 카티션 곱이다. 

•  4. Full outer join  

오라클은 아우터 조인되는 집합쪽에 (+) 기호를 넣는다. 

그렇다면 양쪽 아우터 조인은 양쪽 집합에 (+) 를 넣으면 될것 같은데 그렇게 하면 에러가 난다. 

 

full outer join 은 ansi 방식으로 작성하거나 왼쪽 아우터 조인 오른쪽 아우터 조인을 union 하는 방식으로 쓸수 있다. 

--  FULL OUTER JOIN

drop table out_a;
drop table out_b;

create table out_a(
col1 int,
col2 varchar2(1)
);

create table out_b(
col1 int,
col2 varchar2(1)
);

insert into out_a values(1,'a');
insert into out_a values(3,'c');
insert into out_a values(5,'');

insert into out_b values(1,'a');
insert into out_b values(4,'%');
insert into out_b values(5,'$');

-- 일반조인

SELECT A.COL1, B.COL1, A.COL2, B.COL2
  FROM out_a A,
       out_b B
 WHERE A.COL1 = B.COL1
;

-- 맞지않는 문법

 
SELECT A.COL1, B.COL1, A.COL2, B.COL2
  FROM out_a A,
       out_b B
 WHERE A.COL1(+) = B.COL1(+)
;
=> ORA-01468

-- ansi 

SELECT A.COL1, B.COL1, A.COL2, B.COL2
  FROM out_a A full outer join out_b B
    ON A.COL1 = B.COL1
ORDER BY a.COL1
;

-- union

SELECT A.COL1, B.COL1, A.COL2, B.COL2
  FROM out_a A,
     out_b B
 WHERE A.COL1 = B.COL1(+)
UNION
SELECT A.COL1, B.COL1, A.COL2, B.COL2
  FROM out_a A,
       out_b B
 WHERE A.COL1(+) = B.COL1
;

•  5. Lateral outer join  

 Lateral 구문은 오라클에서 좀 더 유연하게 아우터 조인을 사용하는 것을 지원한다.

 오라클 아우터 조인은 조인 조건에 in 이나 or 가 올 경우 에러가 난다. 

SELECT A.EMPNO, B.DNAME
  FROM EMP A,
             DEPT B
 WHERE A.DEPTNO = B.DEPTNO(+) OR A.DEPTNO = B.DEPTNO2(+)

 => 에러 

 위의 SQL 은 아래와 같이 or 조건을 union 으로 분리해서 작성해야 정상 동작한다. 

SELECT A.EMPNO, B.DNAME
  FROM EMP A,
    DEPT B
 WHERE A.DEPTNO = B.DEPTNO(+) 
UNION 
SELECT A.EMPNO, B.DNAME
  FROM EMP A,
    DEPT B
 WHERE A.DEPTNO = B.DEPTNO2(+)

해당 SQL은 lateral 을 사용하면 좀 더 간결하게 작성할수 있다. 

SELECT A.EMPNO, B.DNAME
  FROM EMP A 
           , LATERAL (
                     SELECT B.DNAME
                         FROM DEPT B
                     WHERE A.DEPTNO = B.DEPTNO OR A.DEPTNO = B.DEPTNO2 
    ) (+)B

도움이 되셨다면 "공감" 마구 눌러주세요

1. 간단확인

/*+ gather_plan_statistics cost_based */

select * from table(dbms_xplan.display_cursor(null /*sql_id*/, null /*child_cursor*/, 'all allstats last'));

2. trace 파일 

alter session set tracefile_identifier='XXX';

alter session set timed_statistics=true;

alter session set statistics_level=all;

alter session set max_dump_file_size=unlimited;

alter session set events '10046 trace name context forever, level 12';

-- sql 실행

alter session set events '10046 trace name context off';

 서브쿼리란 무엇인지 개념과 활용을 알아보자

1. 조인과의 차이 

조인과 서브쿼리는 구조적으로 차이가 있다.
  조인은 A, B 두 집합이 동등한 관계인 반면 서브쿼리는 종속적 관계가 나타난다. 

  Select a.col1, b.col2
    from A, B
   where a.col1 = b.col1 

  조인의 결과는 조인에 성공한 만큼 나온다. 
  A 와 B 의 관계가 1: N 이면 N 만큼 결과가 나오고 1:1 이면 1 만큼 나온다. 
  조인의 한쪽은 인라인 뷰가 될수도 있다.
  이때 둘의 관계는 동일하다고 했으므로 인라인 뷰에서 바깥쪽 테이블 컬럼을 사용할수가 없다. 

  Select a.col1, b.col2
    from A, (select b.col1 from B where a.col1 = b.col)  => error
   where a.col1 = b.col1 

  반면 서브쿼리는 종속적 관계가 나타난다고 했다. 
  쿼리의 결과는 1 : N 관계에도 언제나 1 이다.
  종속적 관계라서 서브쿼리에 A 의 컬럼을 사용할수 있다. 

  select a.col1
    from A
   where exists (select '1' from B where a.col1 = b.col1 )  => ok

 스칼라서브쿼리도 동일한 성질을 갖는다. ( 결과row 가 변하지 않는다, 종속적 관계이다.)


2. 서브쿼리 역할  

 - 확인 : 결과 데이터가 증가하지 않는다. 감소만 할뿐 (N 이상이 되지 않는다.) 
 - 공급 : 주쿼리로 상수화된 결과를 공급

# 선/후 수행 서브쿼리
- 선수행 서브쿼리 : 서브쿼리 테이블이 driving, 모든 값에 엑세스. 
unique 정렬 수행. 메모리에 임시 집합 구성. 
- 후수행 서브쿼리 : driving 에서 조건에 만족하는 한건의 데이터에 대해 
    서브쿼리를 한번 수행. 만족하면 결과로 추출. 
    주 쿼리의 데이터 건수 만큼 반복 수행. 
    
3. 서브쿼리 실행계획 제어

1. NL join -> Hash join

select colC, sum(colD)
 from tab a
 where colA in (select col
   from tab b
    where colB = 'X')
  and colB between '20190101' and '20191010'
 group by colC;
=>

1-1) 힌트

select /*+ USE_HASH(a,@SQ) */
colC, sum(colD)
 from tab a
 where colA in (select /*+ QB_NAME(SQ) */
   col
   from tab b
    where colB = 'X')
  and colB between '20190101' and '20191010'
 group by colC; 
 
 
1-2) 인덱스 사용 못하게
 
select colC, sum(colD)
 from tab a
 where trim(colA) in (select trim(col)
                                   from tab b
                                 where colB = 'X')
  and colB between '20190101' and '20191010'
 group by colC; 
   
1-3) from 절에 풀기
   
select /*+ ORDERED USE_HASH(A,B) */
 a.colC, sum(a.colD)
  from tab a, tab b
 where a.colA = b.col
 and b.colB = 'X'
   and a.colB between '20190101' and '20191010'
 group by a.colC; 

## 메인쿼리 : 서브쿼리 = 1:M 이면 데이터가 증가. => 인라인 뷰로 1:1 관계

select /*+ USE_HASH(A,B) */
 a.colC, sum(a.colD)
  from tab a, 
   ( select col
       from tab b
      where b.colB='X'
   group by col
   )
 where a.colA = b.col
   and a.colB between '20190101' and '20191010'
 group by a.colC; 

2. 조인 순서 변경

- QB_NAME 힌트 => 선수행 서브쿼리

select /*+ PUSH_SUBQ(@SQ) */
colC, sum(colD)
 from tab a
 where colA in (select /*+ QB_NAME(SQ) */
   col
   from tab b
    where colB = 'X')
  and colB between '20190101' and '20191010'
 group by colC; 

- 일반 조인으로 변경 

select /*+ ORDERED USE_HASH(A,B) */
 a.colC, sum(a.colD)
  from tab a, 
   ( select col
       from tab b
      where b.colB='X'
   group by col
   )
 where a.colA = b.col
   and a.colB between '20190101' and '20191010'
 group by a.colC;