오라클 옵티마이저란 ?

DataBase 2019. 1. 9. 13:55


옵티마이저란 사람으로 생각하면 두뇌에 해당하는 부분이다. 옵티마이러를 한마디로 말하면, SQL을 위한 최적의 실행계획을 생성하는 알고리즘이다.
옵티마이저의 종류 크게 두가지이다. 
Rule Base 옵티마이저와 CostBase 옵티마이저이다.



RuleBase 옵티마이저 : SQL Syntax 및 Index의 존재유무 등을 확인하여 실행계획을 생성한다.
Where 조건으로 공급된 커럼의 Index 존재 유무를 확인하여 Index가 존재한다면 해당 Index를 스캔하여 Table을 Access 하고 Index가 존재하지 않을경우 Table Full Scan을 수행하는 방식을 선택한다. 

RuleBase 옵티마이저의 우선순위 
1) ROWID를 통한 Table Access
2) Unique Index를 통한 Table Access
3) Index를 통한 Table Access
4) FullScan을 통한 Table Access

RuleBase 옵티마이저의 장점 : SQL을 작성한 개발자의 의도대로 실행 계획을 생성할 수 있다. 

Cost Base 옵티마이저 : 비용을 산정해서 실행 계획을 생성하는 옵티마이저 방식이다. 해당 Table에 Analyze를 수행하여 통계 데이터를 수집해 놓아야 사용 가느한 옵티마이저 방식이다. 
Cost Base 옵티마이저는 분포도 및 실제 데이터의 통계치를 가지고  실행 계획을 생성하기 때문에 데이터베이스 Migration 등의 작업 후에는 문제를 유발시킬 위험이 많다.

Cost Base 옵티마이저의 종류 :
First Rows : 어떻게 하면 대상 집합 중 첫 번째 Row를 빠르게 추출하냐는것이 목적이다.
예) SELECT * FROM EMP;
All Rows : 빠른 시간내에 원하는 모든 대상 집합을 추출할 수 있디록 실행계획을 생성하는것
JOIN에서 All Rows 방식은 Sort Merge  Join 또는 Hash Join으로 실행계획을 생성하게 된다.
예) SELECT COUNT(*) FROM EMP;


현재 사용하는 DB에 옵티마이저 모드 알기!!

SELECT NAME, VALUE, ISDEFAULT, ISMODIFIED, DESCRIPTION
FROM V$PARAMETER
WHERE NAME LIKE '%optimizer_mode%';




원문: Perfect 오라클 실전튜닝 (권순용님)


'DataBase' 카테고리의 다른 글

MySQL SUM 함수시 NULL 처리 0으로  (0) 2018.10.26
평균값을 구할때 NULL 값  (0) 2018.10.24
SQL 쿼리 잘짜는 방법  (0) 2018.10.10
오라클 문자열 합치기 LISTAGG  (0) 2015.12.17
소계와 합계 구하는 방법 (1)  (0) 2015.12.17
블로그 이미지

클라인STR

,

MySQL SUM 함수시 NULL 처리 0으로

DataBase 2018. 10. 26. 23:11

레코드가 없는경우 SUM 함수 사용시 NULL이 출력된다. 

이때 IFNULL 함수를 사용하여 NULL 일경우 0 처리를 해주면된다. 


SELECT IFNULL(SUM(FIRST_ID),0) FROM OAP_SCORE




'DataBase' 카테고리의 다른 글

오라클 옵티마이저란 ?  (0) 2019.01.09
평균값을 구할때 NULL 값  (0) 2018.10.24
SQL 쿼리 잘짜는 방법  (0) 2018.10.10
오라클 문자열 합치기 LISTAGG  (0) 2015.12.17
소계와 합계 구하는 방법 (1)  (0) 2015.12.17
블로그 이미지

클라인STR

,

평균값을 구할때 NULL 값

DataBase 2018. 10. 24. 14:13

평균을 구할때 NULL을 포함시키면 안된다.
평균을 구하고자 하는 컬럼에 값이 0이 있는 경우와 NULL이 있는 경우는 완전히 다르다. 
평균은 SUM/COUNT 로구해지는데, NULL인 경우에는 COUNT에서 제외가 되므로 나누는 수가 달라지게된다. 

 학생1    학생2         학생3       학생4        학생5
 NULL     40             50           60           100

레코드가 다음과 같을때  AVG함수를 사용한다면  
50+40+60+100 / 4 = 62.5  (NULL인 컬럼은 나누는수에서 제외된다.)

학생1    학생2         학생3     학생4        학생5
 0         40             50           60           100
 
0 + 40 +50+60+100 / 5 = 50 (정상적인 경우 평균 값) 

'DataBase' 카테고리의 다른 글

오라클 옵티마이저란 ?  (0) 2019.01.09
MySQL SUM 함수시 NULL 처리 0으로  (0) 2018.10.26
SQL 쿼리 잘짜는 방법  (0) 2018.10.10
오라클 문자열 합치기 LISTAGG  (0) 2015.12.17
소계와 합계 구하는 방법 (1)  (0) 2015.12.17
블로그 이미지

클라인STR

,

SQL 쿼리 잘짜는 방법

DataBase 2018. 10. 10. 22:18





우선 저도 비전공자 입니다.
개발자체를 늦게 시작한 편이구요

강정식 님의 말씀도 맞습니다만..
좀더 부연설명을 하자면

저도 잘 못합니다. 맨날 주변에 물어보곤 하지요...
그래도 그 막연한(?) 답답함에서는 다소 벗어 났다고 생각하기에 
어줍잖게 적어 봅니다.


우선 내가 원하는 최종 결과를 엑셀 등에 적어 놓으세요.
그리고 아래 순서대로 해봅시다.



1. 순서를 생각하자.
    우선 순서를 생각해야 합니다. 
    어떤 순서인고 하니.. 하나의 쿼리를 짜내기 위한 순서 말이죠.
    sql 은 집합적 언어라고 하지만 제가 느끼기엔 집합적(+ 절차적) 이라고 생각합니다.
    순차적으로 생각할수도 있어야 한다는거죠.
    볼까요? 
    편의상 내가 조회해 오려는 결과 데이터를 "결과" 라고 표현 하도록 하죠.
    1) 내가 보여주려는 결과는 어느 테이블에 있는지 생각해봅니다.
    2) 결과를 뽑기위해 하나의 테이블을 사용하지 않으므로 내가 원하는 결과를 가지고 있는 
    테이블들을 나열해 봅니다.
    3) 이제 나열된 테이블중 주테이블이 무엇인지 생각해 봅니다.
    4) 마지막으로 원하는 결과의 값들은 무었이 있는지 생각해 봅니다.
    만약 , 가공이 필요한 컬럼이라면 가공 방법은 제외하고 가공된 값만 생각합니다.



2. From 부터 풀어보자
이제 대략적인 순서를 생각 했으므로 쿼리를 만들어 가보도록 하죠

1) select 절은 SELECT * 딱 한줄이면 됩니다. 에러방지용 이죠.

2) FROM 부터 들어가 봅니다. 주테이블의 순서대로
MAIN_T(주테이블) , TABLE1, TABLE2 , TABLE3 , CODE 테이블이 있다고 쳤을때 
코드 테이블을 제외한 나머지만 적어 봅니다.
여기까지 완성된 SQL 은 
SELECT *
FROM MAIN_T A 
, TABLE1 B
, TABLE2 C
, TABLE3 D
이겠죠....

3) 이제 조건을 걸어봅니다.
JOIN 도 걸고 상수조건도 걸고 ... 상수라면 AND A.PK_COL = '23' 과 같이 텍스트가 빡! 
박혀 있는 조건 입니다. 
변수로 들어오는 조건도 걸어봅니다.

4) 근데 위 3) 의 작업을 하다보면 이제 테이블의 연결이 좀 애매하고..
생각하지 않으려 해도 SELECT 절을 생각하게 되고 그러죠 그러다 보면 
'음...이부분은 INLINE VIEW 로 해야 겠군'
'음...이건 이쪽에서 가져와서 SUBQUERY 로 IN 조건을 줘야 겠군...'
'음...이건 어쩌구...저건 어쩌구...' 이런 고민 하게 되죠....그럼 점심 시간입니다. ㅋㅋ

5) 밥먹고 왔으니 SELECT 를 고민할 시간이죠 
SELECT 에 표현될 결과는 어떤거였나요? 맨처음 엑셀등에 적어 두셨죠? 
내가 조인한 테이블들이 그 바로 그 결과를 표현 할 수 있나요? 
아니라면 그담은 기술적인 문제일 뿐입니다.
예를 들어 
가로를 세로로 ... 또는 세로를 가로로... 또는 구성비를 어쩌구 저쩌구 
계층구조가 어쩌구....소계는 이러쿵 총계는 저러쿵 
그룹핑은 이렇게.. 데이터가 없는 테이블도 있으니 이런건 아웃조인 ... 
다 기술적인 부분이죠...이런건 검색해서 물어물어 찾으면 찾아집니다.


대략 이런식이죠...
기술적인 부분은 밥 짓는것과 같아요.
문제는 
밥을 쌀로 만드는지 .... 밀가루로 만드는지
물을 부어야 하는지 우유를 부어야 하는지
소금간을 해야 하는지 말아야 하는지 ... 를 알아야 하는거죠 
기술은
'물을 손등까지 오게 붓는다' 를 아는것과 같아요
한번도 밥을 안지어 본사람은 모르는거죠.

하다보면 
밥을 쌀로 만든다는것 , 물을 부어야 한다는 것 , 소금간은 안한다는것 ... 
물은 손등까지 오게 붓는다.....등을 알게 되죠
이제 그러다 보면 
'아...밥은 쌀로만 하는게 아니군 ... 콩,보리,팥,...'
'어라..오곡밥은 소금간을 좀 해야 하는군...'
'오잉...물대신 우유를 넣어도 되는군..'
뭐 점점 이렇게 알아가는거죠 

뭐 힌트를 쓰니...튜닝을 어떻게 하니...
몇시간 짜리가 몇분에 나오니...이런것들은 다 잊으세요..
24시간이 걸려 한줄이 나와도 내가 원하는 결과를 만드는거에 집.중. 하세요.
지금은 그래도 되는 때입니다.

그리고
조급하게 생각하지 마세요.
뭐든지 단계가 있고 노력이 있어야 합니다.

다만..."sql문 잘짜는 방법없을까요???" 라는 
다소 황당하고 어의없는 (나중에 보시면..^^)
그 질문을 지금 이곳에 했었다는 것만 절대 잊지 마세요.

창피함이 실력을 만듭니다.


보니까 상당히 절차적인거 같죠? 
근데 왜 SQL은 집합적 사고를 가져야 한다...고 할까요?
아마도 저 각 순서 또는 순서 전체를 집합적으로 생각 하라는 뜻일꺼에요 ...
그러니 결국 저 순서도 집합의 순서? ㅋㅋㅋ 
뭔소린지....저도 뭐라고 딱 ! 정의 하기가 좀 ㅎㅎ

뭐 예전 생각도 나고 해서 주저리 주저리 글을 썼네요...ㅎㅎ

근데 이렇게 글쓰고 있는 저는 얼만큼 잘하느냐 궁금하시죠? 
제가 개인적으로 알고있는 오라클클럽 멤버들중 최하위 입니다. 

음...쓰고 보니...좀...^^ 
저것이 정답은 아닐꺼에요...정답 알려드릴 실력도 아니고..
그냥 저는 그렇다는 거니....참고 정도 하시면 되겠네요..


출처 : 오라클클럽 http://www.oracleclub.com/article/53260


'DataBase' 카테고리의 다른 글

MySQL SUM 함수시 NULL 처리 0으로  (0) 2018.10.26
평균값을 구할때 NULL 값  (0) 2018.10.24
오라클 문자열 합치기 LISTAGG  (0) 2015.12.17
소계와 합계 구하는 방법 (1)  (0) 2015.12.17
프로시져란 (PROCEDURE) ?  (0) 2015.12.16
블로그 이미지

클라인STR

,

오라클 문자열 합치기 LISTAGG

DataBase 2015. 12. 17. 22:34

SQL 추출시 하나의 컬럼에  "," 콤마로 문자열을 합치기 위해서 찾아보니 LISTAGG 함수를 발견했다.

Oracle 11g에 추가된 함수이며 그룹핑된 문자열 RECORED 데이터를 하나의 컬럼으로 합칠때 사용된다.

 

1
SELECT * FROM EMP;
 cs

 

 

 

 

1
2
3
SELECT JOB, LISTAGG(ENAME, ',') WITHIN GROUP (ORDER BY ENAME) ENAME
  FROM EMP
 GROUP BY JOB;
 cs

 

 

 

'DataBase' 카테고리의 다른 글

평균값을 구할때 NULL 값  (0) 2018.10.24
SQL 쿼리 잘짜는 방법  (0) 2018.10.10
소계와 합계 구하는 방법 (1)  (0) 2015.12.17
프로시져란 (PROCEDURE) ?  (0) 2015.12.16
COPY_T 테이블이란?  (0) 2015.12.16
블로그 이미지

클라인STR

,

소계와 합계 구하는 방법 (1)

DataBase 2015. 12. 17. 01:09

 

TABLE을 4번복제하기위하여 카티젼프로덕트 이용한다.

1
2
3
4
5
6
7
SELECT * FROM TEST12 A
        ,(
           SELECT ROWNUM RCNT 
             FROM TEST12 
            WHERE ROWNUM < 5        
         )B;
 
 cs

 

 

 

 

4개의 복제된 데이터중 RCNT2를 합계용으로 3을 출판사 소계용으로 가공한다.

 

1
2
3
4
5
6
7
8
9
10
SELECT  DECODE(RCNT,2,'합 계',3,PRESS||'계',PRESS) "출판사" 
        , SUM(PRICE)
        FROM TEST12 A
        ,(
           SELECT ROWNUM RCNT 
             FROM TEST12 
            WHERE ROWNUM < 5        
         )B
         GROUP BY DECODE(RCNT,2,'합 계',3,PRESS||'계',PRESS) ;
 
 cs

 

 

책종류에서는 출판사/종류별 계로 사용할 복제번호와 원본데이터로 사용할 복제번호를 지정한다.

1, 출판사종류계 4를 일반데이터로 지정한다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
      SELECT  DECODE(RCNT,2,'합 계',3,PRESS||'계',PRESS) "출판사" 
               , DECODE(RCNT,1,BOOK_TYPE||' 계',4, BOOK_TYPE) "종류"
               , DECODE(RCNT,4,BOOK_NAME) "책명"
            
        , SUM(PRICE)
        FROM TEST12 A
        ,(
           SELECT ROWNUM RCNT 
             FROM TEST12 
            WHERE ROWNUM < 5        
         )B
         GROUP BY DECODE(RCNT,2,'합 계',3,PRESS||'계',PRESS)
                  , DECODE(RCNT,1,BOOK_TYPE||' 계',4, BOOK_TYPE)
                  , DECODE(RCNT,4,BOOK_NAME) 
              
         ORDER BY DECODE(RCNT,2,'합 계',3,PRESS||'계',PRESS);
 
 cs

'DataBase' 카테고리의 다른 글

SQL 쿼리 잘짜는 방법  (0) 2018.10.10
오라클 문자열 합치기 LISTAGG  (0) 2015.12.17
프로시져란 (PROCEDURE) ?  (0) 2015.12.16
COPY_T 테이블이란?  (0) 2015.12.16
누계를 구하는 방법에 대하여 (2)  (0) 2015.12.14
블로그 이미지

클라인STR

,

프로시져란 (PROCEDURE) ?

DataBase 2015. 12. 16. 23:06

프로시저는 PL/SQL을 통해 만들어지고 특정 작업을 수행하는 서브 프로그램이다. 자주 사용되는 SQL문을 DB 객체로 생성해서 저장한 후 사용시에 프로시저명을 호출해서 사용한다. PL/SQL에서 FUNCTION 은 리턴값을 반환하는데 반해 프로시저는 지정된 작업을 수행 후에 결과값을 반환할 수도 있고 반화나지 않을 수 있다. FUNCTION과 프로시저의 차이점은 FUNCTION은 SQL문 내부에서 사용할 수 있지만, 프로시저는 EXEC 또는 EXECUTE의 실행문을 통해서 사용된다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE OR REPLACE PROCEDURE "프로시저명" (
  PARAMETER IN|OUT DATATYPE
 
)  IS
 
  
  --변수선언부
  
  
  BEGIN
 
  --처리내용
 
  EXCEPTION 
  --예외처리부분
          
  END;
 
 cs

CREATE OR REPLACE PROCEDURE : 프로시저를 생성 또는 이미 있으면 기존 프로시저를 대체

프로시저명 : 생성할 프로시저이름

파라메터 : 프로시저에게 전달할 파라미터 이름과 데이터 타입을 명시한다.

IN  : 변수값을 입력받을때 사용

OUT : 프로시저 처리 후 리턴할 변수명

INOUT : 파라미터로 변수값을 받고 프로시저 처리후 리턴할 변수명

 

변수 선언 부 : 사용할 변수를 명시한다.

 

 

 

출처:실전 DB 모델링과 SQL for ORACLE

'DataBase' 카테고리의 다른 글

오라클 문자열 합치기 LISTAGG  (0) 2015.12.17
소계와 합계 구하는 방법 (1)  (0) 2015.12.17
COPY_T 테이블이란?  (0) 2015.12.16
누계를 구하는 방법에 대하여 (2)  (0) 2015.12.14
누계를 구하는 방법에 대하여 (1)  (0) 2015.12.10
블로그 이미지

클라인STR

,

COPY_T 테이블이란?

DataBase 2015. 12. 16. 00:57

COPY_T 테이블이란 기준 테이블을 원하는 배수만큼 복제하여 결과 집합을 구할 때 사용하거나 데이터 체크에 사용된다.

1
2
3
4
5
CREATE TABLE COPY_T
AS 
SELECT ROWNUM AS NUM , TO_CHAR(ROWNUM , '009') AS VAL
FROM USER_TABLES
WHERE ROWNUM < 101;
 cs

 

 

 

COPY_T테이블을 이용하여 총인원수 합계를 구해보자

1
2
3
4
5
6
7
SELECT  
        A.*,B.NUM
       
   FROM  EMP2 A
       , COPY_T B
  WHERE A.EMP_TYPE = '정규직'
    AND B.NUM < 3 ;
 cs

 

 

B.NUM <3 조건에 의하여 2개의 레코드만 추출되고 여기서 EMP2 테이블과 카디젼 조인이 되어 10*2 = 20으로 뻥튀기 된다.

NUM=2인 레코드만 합산해서 구한다.

1
2
3
4
5
6
7
8
9
10
11
12
SELECT  
          DECODE(NUM,1,DEPTNO,'합계') AS DEPTNO
        , DECODE(NUM,1,NAME,'합계') AS NAME 
        , COUNT(DECODE(NUM,1,NAME,'합계')) AS TOTAL
       
   FROM  EMP2 A
       , COPY_T B
  WHERE A.EMP_TYPE = '정규직'
    AND B.NUM < 3
    GROUP BY DECODE(B.NUM,1,A.DEPTNO,'합계')
           , DECODE(NUM,1,NAME,'합계')
      ORDER BY DEPTNO;
 cs

 

 

'DataBase' 카테고리의 다른 글

소계와 합계 구하는 방법 (1)  (0) 2015.12.17
프로시져란 (PROCEDURE) ?  (0) 2015.12.16
누계를 구하는 방법에 대하여 (2)  (0) 2015.12.14
누계를 구하는 방법에 대하여 (1)  (0) 2015.12.10
SUBSTR 함수  (0) 2015.12.09
블로그 이미지

클라인STR

,

공지사항

글 보관함

달력

«   2024/04   »
일 월 화 수 목 금 토
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30

티스토리툴바