따봉 구걸남's 블로그

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

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

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

프로젝트를 진행할때 SQL 작성의 표준을 정해두면 프로그램의 퀄리티가 한결 높아진다.

보통의 개발자들은 몰라서 안하는 경우가 많았고, 교육을 하면 대부분 수용을 하더란..

물론 개인적 경험이나 고집때문에 설득이 안되는 경우도 존재하는데.. 그땐 적당히 타협을 하던지..

아래는 한 프로젝트에서 진행했던 예시이다. 

1. sql실행시 오류발생
2. sql식별자 표준 미준수 : 누락, 잘못지정된 건
3. select * 사용 
4. no logging 옵션 사용 : dml 수행시 sql 에 nologging 옵션 지정
5. 금지 hint 사용 : 온라인 원장에 append
6. select for update : nowait 혹은 wait 옵션 누락
7. ansi sql 사용 : oracle native sql 이 표준
8. fetch first 사용 : 12 new feature 
9. update/delete 문 where 절 누락 
10. 빈공백 비교 : 빈공백 비교는 null 로 취급. is null is not null 변경
11. to_date 함수 format 누락 : format string 지정
12. decode 문 : case when 으로 대체
13. insert 문에 컬럼 미지정 : 컬럼 추가시 오류발생
14. online sql 의 with 사용 : with 를 인라인뷰로 대체
15. rownum 과 order by 가 동일 쿼리블록 : order by 먼저, 인라인뷰 밖에 rownum 사용
16. 테이블 alias 미사용 : 2개이상 조인시 alias 사용
17. 바인드 변수 누락 : 리터럴 -> 바인드 변수
18. 물리삭제 금지 테이블에 delete 문 사용 : update 로 수정
19. 비인가된 타 앱이 테이블 crud : 메타에 등록된 건만 가능
20. 한줄주석 사용 : /* 사용
21. order by 반복 : 인라인뷰 밖에서 최종 1회 order by
22. union 사용금지 : 가급적 union all
23. from 절에 alias 없는 table
24. order by 절에 순서값 금지 : 컬럼명 기술
25. alias 없는 스칼라서브쿼리
26. parallel degree 미지정
27. index_desc, index_asc 로 최대,최소겂 : min(),max() 사용
28. where 절 인덱스 컬럼 가공 : 함수로 가공된 인덱스컬럼, 함수 제거

위 기준에서 사이트에 따라 어느 것을 버리고 어느것을 선택할지 결정하고 그것을 준수 한다. 

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

트랜잭션의 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;