'개발/DataBase' 카테고리의 글 목록

개발/DataBase

[Oracle] ROWNUM의 기본 동작 2018.04.30
[Oracle] Null Column 조회 성능개선 2018.04.29
[Oracle] WHERE절에 number, varchar2 형변환 우선순위 2018.04.22 1
[Oracle]Decode 함수2 2018.04.08
[Oracle] Decode 함수1 2018.04.08
[Oracle] Regular Expression 2018.04.04
[오라클 성능 고도화1] SQL Trace 2018.04.02
[오라클 성능 고도화1] AutoTrace 2018.04.01
[Oracle] UnPivot 함수 2018.04.01
[Oracle] Pivot 함수 2018.04.01

[Oracle] ROWNUM의 기본 동작

2018. 4. 30. 00:04

ROWNUM을 이용한 데이터 기본동작

※ ROWNUM은 DataBase에 저장되지 않는 의사컬럼으로 참조만 되는 컬럼이다.

※ ROWNUM = 1은 사용가능하지만 ROWNUM = N (N > 1), ROWNUM > N (N > 1)은 동작하지 않는다.

아래문장은 정상적으로 작동한다.

SQL> WITH ROWNUM_TEST AS (
    SELECT 1 AS C1, 'C' AS C2, 101 AS C3 FROM DUAL
    UNION ALL SELECT 2 AS C1, 'D' AS C2, 102 AS C3 FROM DUAL
    UNION ALL SELECT 3 AS C1, 'E' AS C2, 103 AS C3 FROM DUAL
    UNION ALL SELECT 4 AS C1, 'F' AS C2, 104 AS C3 FROM DUAL
    UNION ALL SELECT 5 AS C1, 'G' AS C2, 105 AS C3 FROM DUAL
    UNION ALL SELECT 6 AS C1, 'H' AS C2, 106 AS C3 FROM DUAL
    UNION ALL SELECT 7 AS C1, 'I' AS C2, 107 AS C3 FROM DUAL
    UNION ALL SELECT 8 AS C1, 'J' AS C2, 108 AS C3 FROM DUAL
    UNION ALL SELECT 9 AS C1, 'K' AS C2, 109 AS C3 FROM DUAL
    UNION ALL SELECT 10 AS C1, 'L' AS C2, 110 AS C3 FROM DUAL
)
SELECT *
FROM (
    SELECT *
    FROM ROWNUM_TEST
    ORDER BY C1
)
WHERE ROWNUM >=1
AND ROWNUM <=5;

C1   C2   C3   
---- ---- ---- 
   1 C     101
   2 D     102
   3 E     103
   4 F     104
   5 G     105

5 rows selected.

아래문장은 정상적으로 작동하지 않는다.

SQL> WITH ROWNUM_TEST AS (
    SELECT 1 AS C1, 'C' AS C2, 101 AS C3 FROM DUAL
    UNION ALL SELECT 2 AS C1, 'D' AS C2, 102 AS C3 FROM DUAL
    UNION ALL SELECT 3 AS C1, 'E' AS C2, 103 AS C3 FROM DUAL
    UNION ALL SELECT 4 AS C1, 'F' AS C2, 104 AS C3 FROM DUAL
    UNION ALL SELECT 5 AS C1, 'G' AS C2, 105 AS C3 FROM DUAL
    UNION ALL SELECT 6 AS C1, 'H' AS C2, 106 AS C3 FROM DUAL
    UNION ALL SELECT 7 AS C1, 'I' AS C2, 107 AS C3 FROM DUAL
    UNION ALL SELECT 8 AS C1, 'J' AS C2, 108 AS C3 FROM DUAL
    UNION ALL SELECT 9 AS C1, 'K' AS C2, 109 AS C3 FROM DUAL
    UNION ALL SELECT 10 AS C1, 'L' AS C2, 110 AS C3 FROM DUAL
)
SELECT *
FROM (
    SELECT *
    FROM ROWNUM_TEST
    ORDER BY C1
)
WHERE ROWNUM >= 6
AND ROWNUM <= 10;

C1   C2   C3   
---- ---- ---- 

0 rows selected.

ROWNUM       C1   C2   C3   
------------ ---- ---- ---- 
           1    1 C     101     ==> ROWNUM >= 6 AND ROWNUM <= 10 조건에서 조회되지 않고 버려짐.
           2    2 D     102     ==> 위의  행이 버려졌으므로 다시 ROWNUM은 1이 되고 조건에 의해 버려짐
           3    3 E     103     ==> 이하 동일
           4    4 F     104
           5    5 G     105
           6    6 H     106
           7    7 I     107
           8    8 J     108
           9    9 K     109
          10   10 L     110

위의 조회가 정상적으로 되려면 아래와 같이 조회한다.

SQL> WITH ROWNUM_TEST AS (
    SELECT 1 AS C1, 'C' AS C2, 101 AS C3 FROM DUAL
    UNION ALL SELECT 2 AS C1, 'D' AS C2, 102 AS C3 FROM DUAL
    UNION ALL SELECT 3 AS C1, 'E' AS C2, 103 AS C3 FROM DUAL
    UNION ALL SELECT 4 AS C1, 'F' AS C2, 104 AS C3 FROM DUAL
    UNION ALL SELECT 5 AS C1, 'G' AS C2, 105 AS C3 FROM DUAL
    UNION ALL SELECT 6 AS C1, 'H' AS C2, 106 AS C3 FROM DUAL
    UNION ALL SELECT 7 AS C1, 'I' AS C2, 107 AS C3 FROM DUAL
    UNION ALL SELECT 8 AS C1, 'J' AS C2, 108 AS C3 FROM DUAL
    UNION ALL SELECT 9 AS C1, 'K' AS C2, 109 AS C3 FROM DUAL
    UNION ALL SELECT 10 AS C1, 'L' AS C2, 110 AS C3 FROM DUAL
)
SELECT C1, C2, C3 
FROM (
    SELECT ROWNUM RN
        , T1.*
    FROM (
        SELECT * FROM ROWNUM_TEST ORDER BY C1
    ) T1
)
WHERE RN >= 6 AND RN <= 10;

C1   C2   C3   
---- ---- ---- 
   6 H     106
   7 I     107
   8 J     108
   9 K     109
  10 L     110

5 rows selected.

저작자표시 비영리 변경금지

'개발 > DataBase' 카테고리의 다른 글

[Oracle] Null Column 조회 성능개선 (0)	2018.04.29
[Oracle] WHERE절에 number, varchar2 형변환 우선순위 (1)	2018.04.22
[Oracle]Decode 함수2 (0)	2018.04.08
[Oracle] Decode 함수1 (0)	2018.04.08
[Oracle] Regular Expression (0)	2018.04.04

[Oracle] Null Column 조회 성능개선

2018. 4. 29. 01:48

NULL 조회시 성능개선

1. INDEX가 존재하는 컬럼을 WHERE 조건에 IS NULL로 조회하는 방법.

1.1 해당 컬럼에 Default Value를 지정해서 column_name IS NULL 조회 보다 column_name = default value 형태로 조회한다.

1.2 FBI(Function Based Index) 생성으로 조회시 Index를 사용할 수 있도록 변경한다.

2. INDEX가 존재하는 컬럼을 WHERE 조건에 IS NOT NULL로 조회하는 방법.

2.1 CHAR/VARCHAR2 컬럼의 경우

=> WHERE column_name IS NOT NULL을 WHERE column_name > CHR(0) 형태로 조회한다.

2.2 DATE 타입의 컬럼의 경우

=> WHERE column_name IS NOT NULL을 WHERE column_name > TO_DATE('19000101','YYYYMMDD') 형태로 조회한다.

(19000101은 해당 컬럼에 존재할 수 없는 이전 값)

2.3 NUMBER 타입의 컬럼의 경우

=> WHERE column_name IS NOT NULL을 WHERE column_name >= 0 OR column_name < 0 형태로 조회한다.

저작자표시 비영리 변경금지

'개발 > DataBase' 카테고리의 다른 글

[Oracle] ROWNUM의 기본 동작 (0)	2018.04.30
[Oracle] WHERE절에 number, varchar2 형변환 우선순위 (1)	2018.04.22
[Oracle]Decode 함수2 (0)	2018.04.08
[Oracle] Decode 함수1 (0)	2018.04.08
[Oracle] Regular Expression (0)	2018.04.04

[Oracle] WHERE절에 number, varchar2 형변환 우선순위

2018. 4. 22. 22:37

WHERE절에 number, varchar2 형변환 우선순위

oracle에선 숫자와 문자간의 비교의 경우, 특별히 지정하지 않았다면, 문자를 숫자타입으로 변환하려고 시도합니다.

SQL> desc myemp1

이름 널 유형

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

EMPNO NOT NULL NUMBER

ENAME VARCHAR2(100)

DEPTNO VARCHAR2(1)

ADDR VARCHAR2(100)

SAL NUMBER

SUNGBYUL VARCHAR2(1)

* myemp1 데이터건수 2000만건

* empno는 primary key 인덱스, deptno에도 인덱스가 생성되어 있다.

* where절의 비교대상 컬럼의 데이터 타입이 다르면 number로 자동 형 변환된다.

처음예문은 deptno 인덱스를 사용못하는 형태로 변환된다.(인덱스컬럼에 변환이 생김으로 인덱스사용불가)

-- deptno는 varchar2 컬럼

-- 0초

select count(*) from myemp1

where deptno = '3'

--14초

select count(*) from myemp1

where deptno = 3

==> 자동형변환

select count(*) from myemp1

where to_number(deptno) = 3

-- empno 컬럼은 number

--0초

select * from myemp1

where empno = 1234

--0초

select * from myemp1

where empno = '1234'

==> 자동형변환

select * from myemp1

where empno = to_number('1234')

저작자표시 비영리 변경금지

'개발 > DataBase' 카테고리의 다른 글

[Oracle] ROWNUM의 기본 동작 (0)	2018.04.30
[Oracle] Null Column 조회 성능개선 (0)	2018.04.29
[Oracle]Decode 함수2 (0)	2018.04.08
[Oracle] Decode 함수1 (0)	2018.04.08
[Oracle] Regular Expression (0)	2018.04.04

[Oracle]Decode 함수2

2018. 4. 8. 16:45

Decode 함수2

[출처] SQL튜닝의 시작(박성호,오수영 지음)

※ Decode 함수의 사용으로 Index를 사용할 수 없는 경우 성능이슈를 해결하는 방법에 대해서 알아본다.

Sample Data는 Decode 함수1 의 Posting에서 사용한 데이터를 사용한다.

2018/04/08 - [개발/DataBase] - [Oracle] Decode 함수1

다음 SQL문은 :B1 값이 Null이면 전체를 조회하고, :B1 이 Null이 아닌 경우에는 EMPNO를 :B1 값으로 조회하는 SQL이다.

SELECT *
FROM SALES_TEST A
WHERE EMPNO = DECODE(:B1, NULL, A.EMPNO, :B1)

※ EMPNO는 선택도가 좋은 Column이고 Index가 존재하므로 :B1이 Not Null이라면 Index를 사용하는것이 효율적이고, :B1이 Null이라면 조건절이 EMPNO = A.EMPNO가 되므로 전체 데이터를 읽어야 하므로 Table Full Scan이 가장 효율적인 방법이 된다.

SELECT /*+ INDEX(A IDX_SALES_TEST_02) */
    *
FROM SALES_TEST A
WHERE EMPNO = :BI
AND :B1 IS NOT NULL
UNION ALL
SELECT /*+ FULL(B) */
    *
FROM SALES_TEST B
WHERE EMPNO = EMPNO
AND :B1 IS NULL

※ 위의 SQL문에서 SALES_TEST Table을 2번 Access하는것 처럼 보이지만 WHERE절에 AND :B1 IS NULL(IS NOT NULL) 조건에 의해 :B1의 값에 따른 SQL수행은 실제로 하나의 조건에 대해서만 실행이 된다.

저작자표시 비영리 변경금지

'개발 > DataBase' 카테고리의 다른 글

[Oracle] Null Column 조회 성능개선 (0)	2018.04.29
[Oracle] WHERE절에 number, varchar2 형변환 우선순위 (1)	2018.04.22
[Oracle] Decode 함수1 (0)	2018.04.08
[Oracle] Regular Expression (0)	2018.04.04
[오라클 성능 고도화1] SQL Trace (0)	2018.04.02

[Oracle] Decode 함수1

2018. 4. 8. 01:24

Decode 함수1

[출처] SQL튜닝의 시작(박성호,오수영 지음)

※ Decode 함수는 SQL내에서 if ~ then ~ else if ~ end 사용할 수 있도록 Oracle에서 제공하는 함수이다.

※ 본 posting에서는 Decode를 이용해서 Row를 Column으로 바꾸어 표현하고자 경우에 대해서만 Posting 된 글이다.

※ Oracle 11g 버전부터는 pivot 기능이 동일한 기능을 한다. 아래 링크 참조

2018/04/01 - [개발/DataBase] - [Oracle] Pivot 함수

1. Test를 위한 Table 생성 및 샘플 데이터 입력.

CREATE TABLE SALES_TEST (
    SALE_DT CHAR(8),            /* 판매일자 */
    EMPNO NUMBER,               /* 사원번호 */
    DEPTNO NUMBER,              /* 부서번호 */
    TARGET NUMBER NOT NULL,     /* 목표 판매량 */
    SALECNT NUMBER NOT NULL,    /* 실제 판매량 */    
    SALE_DESC VARCHAR2(200)     /* 목표와 실제 판매량에 대한 상세내용 */
);

INSERT INTO SALES_TEST VALUE
SELECT '20180301'
    , LEVEL
    , MOD(LEVEL, 10)
    , MOD(LEVEL, 710)
    , MOD(LEVEL, 400)
    , 'SALE '||LEVEL||' : '||MOD(LEVEL,710)||' ==> ' || MOD(LEVEL, 400)
FROM DUAL
CONNECT BY LEVEL <= 100000
;

INSERT INTO SALES_TEST VALUE
SELECT '20180302'
    , LEVEL
    , MOD(LEVEL, 10)
    , MOD(LEVEL, 300)
    , MOD(LEVEL, 100)
    , 'SALE '||LEVEL||' : '||MOD(LEVEL,310)||' ==> ' || MOD(LEVEL, 100)
FROM DUAL
CONNECT BY LEVEL+1128 <= 100000
;

INSERT INTO SALES_TEST VALUE
SELECT '20180303'
    , LEVEL
    , MOD(LEVEL, 10)
    , MOD(LEVEL, 128)
    , MOD(LEVEL, 98)
    , 'SALE '||LEVEL||' : '||MOD(LEVEL,128)||' ==> ' || MOD(LEVEL, 98)
FROM DUAL
CONNECT BY LEVEL+528 <= 100000
;

INSERT INTO SALES_TEST VALUE
SELECT '20180304'
    , LEVEL
    , MOD(LEVEL, 10)
    , MOD(LEVEL, 238)
    , MOD(LEVEL, 900)
    , 'SALE '||LEVEL||' : '||MOD(LEVEL,238)||' ==> ' || MOD(LEVEL, 900)
FROM DUAL
CONNECT BY LEVEL+278 <= 100000
;

INSERT INTO SALES_TEST VALUE
SELECT '20180305'
    , LEVEL
    , MOD(LEVEL, 10)
    , MOD(LEVEL, 897)
    , MOD(LEVEL, 1258)
    , 'SALE '||LEVEL||' : '||MOD(LEVEL,897)||' ==> ' || MOD(LEVEL, 1258)
FROM DUAL
CONNECT BY LEVEL+278 <= 100000
;

INSERT INTO SALES_TEST VALUE
SELECT '20180306'
    , LEVEL
    , MOD(LEVEL, 10)
    , MOD(LEVEL, 70)
    , MOD(LEVEL, 40)
    , 'SALE '||LEVEL||' : '||MOD(LEVEL,70)||' ==> ' || MOD(LEVEL, 40)
FROM DUAL
CONNECT BY LEVEL+134 <= 100000
;

INSERT INTO SALES_TEST VALUE
SELECT '20180307'
    , LEVEL
    , MOD(LEVEL, 10)
    , MOD(LEVEL, 8548)
    , MOD(LEVEL, 500)
    , 'SALE '||LEVEL||' : '||MOD(LEVEL,8548)||' ==> ' || MOD(LEVEL, 500)
FROM DUAL
CONNECT BY LEVEL+38119 <= 100000
;

COMMIT;

CREATE INDEX IDX_SALES_TEST_01 ON SALES_TEST(SALE_DT);
CREATE INDEX IDX_SALES_TEST_02 ON SALES_TEST(EMPNO);
EXEC dbms_stats.gather_table_stats('SCOTT', 'SALES_TEST');

2. 각 일자별 판매목표와 판매량 데이터를 colunm형태로 조회하는데 스칼라서브 쿼리를 이용해서 조회하는 경우.

SELECT '2018/03/01' AS SALEDT_0301
    , (SELECT SUM(TARGET) FROM SALES_TEST WHERE SALE_DT = '20180301') AS TARGET_0301
    , (SELECT SUM(SALECNT) FROM SALES_TEST WHERE SALE_DT = '20180301') AS SALE_0301
    , (SELECT SUM(TARGET) FROM SALES_TEST WHERE SALE_DT = '20180302') AS TARGET_0302
    , (SELECT SUM(SALECNT) FROM SALES_TEST WHERE SALE_DT = '20180302') AS SALE_0302
    , (SELECT SUM(TARGET) FROM SALES_TEST WHERE SALE_DT = '20180303') AS TARGET_0303
    , (SELECT SUM(SALECNT) FROM SALES_TEST WHERE SALE_DT = '20180303') AS SALE_0303
    , (SELECT SUM(TARGET) FROM SALES_TEST WHERE SALE_DT = '20180304') AS TARGET_0304
    , (SELECT SUM(SALECNT) FROM SALES_TEST WHERE SALE_DT = '20180304') AS SALE_0304
    , (SELECT SUM(TARGET) FROM SALES_TEST WHERE SALE_DT = '20180305') AS TARGET_0305
    , (SELECT SUM(SALECNT) FROM SALES_TEST WHERE SALE_DT = '20180305') AS SALE_0305
    , (SELECT SUM(TARGET) FROM SALES_TEST WHERE SALE_DT = '20180306') AS TARGET_0306
    , (SELECT SUM(SALECNT) FROM SALES_TEST WHERE SALE_DT = '20180306') AS SALE_0306
    , (SELECT SUM(TARGET) FROM SALES_TEST WHERE SALE_DT = '20180307') AS TARGET_0307
    , (SELECT SUM(SALECNT) FROM SALES_TEST WHERE SALE_DT = '20180307') AS SALE_0307
FROM DUAL

※ 아래는 위의 sql문 실행시 trace파일의 일부이다.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.40       0.44          0      13334          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.42       0.46          0      13334          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84


Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1025 pr=0 pw=0 time=0 us)
 100000   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=1025 pr=0 pw=0 time=114193 us cost=954 size=1224847 card=94219)
 100000    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=281 pr=0 pw=0 time=33887 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=1025 pr=0 pw=0 time=0 us)
 100000   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=1025 pr=0 pw=0 time=58695 us cost=954 size=1224847 card=94219)
 100000    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=281 pr=0 pw=0 time=17263 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=988 pr=0 pw=0 time=0 us)
  98872   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=988 pr=0 pw=0 time=57429 us cost=954 size=1224847 card=94219)
  98872    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=278 pr=0 pw=0 time=14069 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=988 pr=0 pw=0 time=0 us)
  98872   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=988 pr=0 pw=0 time=54871 us cost=954 size=1224847 card=94219)
  98872    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=278 pr=0 pw=0 time=13685 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=981 pr=0 pw=0 time=0 us)
  99472   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=981 pr=0 pw=0 time=55488 us cost=954 size=1224847 card=94219)
  99472    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=281 pr=0 pw=0 time=14959 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=981 pr=0 pw=0 time=0 us)
  99472   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=981 pr=0 pw=0 time=64438 us cost=954 size=1224847 card=94219)
  99472    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=281 pr=0 pw=0 time=19945 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=1018 pr=0 pw=0 time=0 us)
  99722   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=1018 pr=0 pw=0 time=75046 us cost=954 size=1224847 card=94219)
  99722    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=280 pr=0 pw=0 time=15725 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=1018 pr=0 pw=0 time=0 us)
  99722   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=1018 pr=0 pw=0 time=55357 us cost=954 size=1224847 card=94219)
  99722    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=280 pr=0 pw=0 time=12273 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=1032 pr=0 pw=0 time=0 us)
  99722   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=1032 pr=0 pw=0 time=60983 us cost=954 size=1224847 card=94219)
  99722    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=281 pr=0 pw=0 time=14702 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=1032 pr=0 pw=0 time=0 us)
  99722   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=1032 pr=0 pw=0 time=55869 us cost=954 size=1224847 card=94219)
  99722    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=281 pr=0 pw=0 time=13807 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=976 pr=0 pw=0 time=0 us)
  99866   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=976 pr=0 pw=0 time=58307 us cost=954 size=1224847 card=94219)
  99866    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=281 pr=0 pw=0 time=13554 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=976 pr=0 pw=0 time=0 us)
  99866   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=976 pr=0 pw=0 time=55878 us cost=954 size=1224847 card=94219)
  99866    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=281 pr=0 pw=0 time=14193 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=647 pr=0 pw=0 time=0 us)
  61881   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=647 pr=0 pw=0 time=34903 us cost=954 size=1224847 card=94219)
  61881    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=176 pr=0 pw=0 time=8693 us cost=266 size=0 card=94219)(object id 74707)
      1  SORT AGGREGATE (cr=647 pr=0 pw=0 time=0 us)
  61881   TABLE ACCESS BY INDEX ROWID SALES_TEST (cr=647 pr=0 pw=0 time=34008 us cost=954 size=1224847 card=94219)
  61881    INDEX RANGE SCAN IDX_SALES_TEST_01 (cr=176 pr=0 pw=0 time=9716 us cost=266 size=0 card=94219)(object id 74707)
      1  FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

※ Trace 결과를 보면, SALES_TEST Table을 스칼라서버쿼리의 수 만큼 반복해서 Access한다.

위 예제에서 다른 통계정보까지 구해야 한다면 SALE_TEST Table Access는 더 많이 발생하게 되어 반복탐색에 대한 비효율이 증가한다.

3. 각 일자별 판매목표와 판매량 데이터를 colunm형태로 조회하는데 Decode함수를 이용해서 조회하는 경우.

SELECT '2018/03' AS SALEDT_03
    , SUM(DECODE(SALE_DT, '20180301', TARGET, 0)) AS TARGER_0301
    , SUM(DECODE(SALE_DT, '20180301', SALECNT, 0)) AS SALE_0301
    , SUM(DECODE(SALE_DT, '20180302', TARGET, 0)) AS TARGER_0302
    , SUM(DECODE(SALE_DT, '20180302', SALECNT, 0)) AS SALE_0302
    , SUM(DECODE(SALE_DT, '20180303', TARGET, 0)) AS TARGER_0303
    , SUM(DECODE(SALE_DT, '20180303', SALECNT, 0)) AS SALE_0303
    , SUM(DECODE(SALE_DT, '20180304', TARGET, 0)) AS TARGER_0304
    , SUM(DECODE(SALE_DT, '20180304', SALECNT, 0)) AS SALE_0304
    , SUM(DECODE(SALE_DT, '20180305', TARGET, 0)) AS TARGER_0305
    , SUM(DECODE(SALE_DT, '20180305', SALECNT, 0)) AS SALE_0305
    , SUM(DECODE(SALE_DT, '20180306', TARGET, 0)) AS TARGER_0306
    , SUM(DECODE(SALE_DT, '20180306', SALECNT, 0)) AS SALE_0306
    , SUM(DECODE(SALE_DT, '20180307', TARGET, 0)) AS TARGER_0307
    , SUM(DECODE(SALE_DT, '20180307', SALECNT, 0)) AS SALE_0307
FROM SALES_TEST

※ 아래는 위의 sql문 실행시 trace파일의 일부이다.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.48       0.52          0       4856          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.49       0.53          0       4856          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=4856 pr=0 pw=0 time=0 us)
 659535   TABLE ACCESS FULL SALES_TEST (cr=4856 pr=0 pw=0 time=125558 us cost=1346 size=11212095 card=659535)

※ Decode 함수를 사용한 경우에는 SALES_TEST Table을 한번만 Access한다.

반복탐색에 의한 비효율을 제거.

- Trace에서 확인해 보면 스칼라서브쿼리를 사용한 경우와 비교해봤을때 Table을 한번만 읽음으로써 I/O는 많이 개선되었으나 Elapsed Time 및 Cpu Time은 증가하였는데 결과만 보면 Decode함수를 사용하는것이 성능상 불리해 보이지만 이 경우 Table에 적재된 데이터의 순서와 인덱스의 정렬된 순서가 거의 일치함으로써 Index Clustering Factor가 좋아서 나타나는 결과이다.

만일 데이터입력을 Target 순서대로 입력을 하였다면 Decode가 좋은 성능을 보일것이다.

4. 각 일자별 판매목표와 판매량 데이터를 colunm형태로 조회하는데 Decode함수를 이용하는데 불필요한 Default 제거.

SELECT '2018/03' AS SALEDT_03
    , SUM(DECODE(SALE_DT, '20180301', TARGET)) AS TARGER_0301
    , SUM(DECODE(SALE_DT, '20180301', SALECNT)) AS SALE_0301
    , SUM(DECODE(SALE_DT, '20180302', TARGET)) AS TARGER_0302
    , SUM(DECODE(SALE_DT, '20180302', SALECNT)) AS SALE_0302
    , SUM(DECODE(SALE_DT, '20180303', TARGET)) AS TARGER_0303
    , SUM(DECODE(SALE_DT, '20180303', SALECNT)) AS SALE_0303
    , SUM(DECODE(SALE_DT, '20180304', TARGET)) AS TARGER_0304
    , SUM(DECODE(SALE_DT, '20180304', SALECNT)) AS SALE_0304
    , SUM(DECODE(SALE_DT, '20180305', TARGET)) AS TARGER_0305
    , SUM(DECODE(SALE_DT, '20180305', SALECNT)) AS SALE_0305
    , SUM(DECODE(SALE_DT, '20180306', TARGET)) AS TARGER_0306
    , SUM(DECODE(SALE_DT, '20180306', SALECNT)) AS SALE_0306
    , SUM(DECODE(SALE_DT, '20180307', TARGET)) AS TARGER_0307
    , SUM(DECODE(SALE_DT, '20180307', SALECNT)) AS SALE_0307
FROM SALES_TEST

※ 아래는 위의 sql문 실행시 trace파일의 일부이다.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.36       0.39          0       4856          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.37       0.40          0       4856          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=4856 pr=0 pw=0 time=0 us)
 659535   TABLE ACCESS FULL SALES_TEST (cr=4856 pr=0 pw=0 time=102904 us cost=1336 size=11212095 card=659535)

※ Decode함수에서 Default Value를 지정하고 Sum함수를 수행하는데

Sum함수의 특성상 Row를 Sum할때는 Null을 제외하고 Sum을 하므로 Decode함수에서 Default Value값을 제거함으로써 성능의 향상을 기대할 수 있다.

5. Decode함수의 Default 값 제거에 따른 오류데이터 발생 가능성을 NVL함수로 제거.

SELECT '2018/03' AS SALEDT_03
    , NVL(SUM(DECODE(SALE_DT, '20180301', TARGET)),0) AS TARGER_0301
    , NVL(SUM(DECODE(SALE_DT, '20180301', SALECNT)),0) AS SALE_0301
    , NVL(SUM(DECODE(SALE_DT, '20180302', TARGET)),0) AS TARGER_0302
    , NVL(SUM(DECODE(SALE_DT, '20180302', SALECNT)),0) AS SALE_0302
    , NVL(SUM(DECODE(SALE_DT, '20180303', TARGET)),0) AS TARGER_0303
    , NVL(SUM(DECODE(SALE_DT, '20180303', SALECNT)),0) AS SALE_0303
    , NVL(SUM(DECODE(SALE_DT, '20180304', TARGET)),0) AS TARGER_0304
    , NVL(SUM(DECODE(SALE_DT, '20180304', SALECNT)),0) AS SALE_0304
    , NVL(SUM(DECODE(SALE_DT, '20180305', TARGET)),0) AS TARGER_0305
    , NVL(SUM(DECODE(SALE_DT, '20180305', SALECNT)),0) AS SALE_0305
    , NVL(SUM(DECODE(SALE_DT, '20180306', TARGET)),0) AS TARGER_0306
    , NVL(SUM(DECODE(SALE_DT, '20180306', SALECNT)),0) AS SALE_0306
    , NVL(SUM(DECODE(SALE_DT, '20180307', TARGET)),0) AS TARGER_0307
    , NVL(SUM(DECODE(SALE_DT, '20180307', SALECNT)),0) AS SALE_0307
FROM SALES_TEST

※ 아래는 위의 sql문 실행시 trace파일의 일부이다.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      0.37       0.41          0       4856          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.38       0.41          0       4856          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=4856 pr=0 pw=0 time=0 us)
 659535   TABLE ACCESS FULL SALES_TEST (cr=4856 pr=0 pw=0 time=99064 us cost=1336 size=11212095 card=659535)

※ 해당 날짜에 데이터가 없는 경우에는 Null이 반환되므로 위의 쿼리에서 NVL함수로 데이터를 보정해야 한다.

또한 NVL함수를 추가할때는 함수의 수행횟수가 가장 적은 위치에 추가해야 한다.

- NVL(SUM(DECODE(SALES_DT,'20180301',TARGET)),0) ⇒ 효율이 좋다.

- SUM(DECODE(SALES_DT,'20180301',NVL(TARGET,0))) ⇒ 효율이 좋지 않다.

저작자표시 비영리 변경금지

'개발 > DataBase' 카테고리의 다른 글

[Oracle] WHERE절에 number, varchar2 형변환 우선순위 (1)	2018.04.22
[Oracle]Decode 함수2 (0)	2018.04.08
[Oracle] Regular Expression (0)	2018.04.04
[오라클 성능 고도화1] SQL Trace (0)	2018.04.02
[오라클 성능 고도화1] AutoTrace (0)	2018.04.01

[Oracle] Regular Expression

2018. 4. 4. 00:40

Oracle Regular Expression

※ Oracle에서 정규표현식 관련해서는 함수와 조회조건에 사용할 수 있다.

1. REGEXP_LIKE : 조회조건으로 패턴에 맞는 컬럼이 있는 데이터를 조회한다.

2. REGEXP_REPLACE : 해당 컬럼에서 패턴에 매치되는 부분을 치환하는 함수이다.

3. REGEXP_INSTR : 해당 컬럼에서 패턴에 매치되는 부분이 존재하는 위치를 반환하는 함수이다.

4. REGEXP_SUBSTR : 해당 컬럼에서 패턴을 검색하여 문자열을 추출하는 함수이다.

5. REGEXP_COUNT : 해당 컬럼에서 패턴을 검색하여 발견된 횟수를 반환하는 함수이다.

1. REGEXP_LIKE 조회조건 사용 예

- hr.employees Table에서 first name 이 Steven 또는 Stephen 인 레코드를 조회

SELECT * FROM EMPLOYEES
WHERE REGEXP_LIKE (FIRST_NAME, '^Ste(v|ph)en$');

2. REGEXP_REPLACE 함수 사용 예

- hr.countries Table에서 country name 필드에 각각의 문자를 문자+space로 치환한다.

SELECT TA.*, REGEXP_REPLACE(COUNTRY_NAME, '(.)', '\1 ')
FROM COUNTRIES TA;

3. REGEXP_INSTR 함수 사용 예

- hr.employees Table에서 email 필드에 Email주소 형식이 있는지 확인을 한다.

SELECT TA.*, REGEXP_INSTR(EMAIL, '\w+@\w+(\.\w+)+')
FROM HR.EMPLOYEES TA;

SELECT REGEXP_INSTR('lunajin@naver.com', '\w+@\w+(\.\w+)+')
FROM DUAL;

4. REGEXP_SUBSTR 함수 사용 예

SELECT REGEXP_SUBSTR('Oracle 2010', 'O r a c l e', 1, 1, 'x')
FROM DUAL;

5. REGEXP_COUNT 함수 사용 예

SELECT REGEXP_COUNT('Albert Einstein', 'e', 7, 'c')
FROM DUAL;

저작자표시 비영리 변경금지

'개발 > DataBase' 카테고리의 다른 글

[Oracle]Decode 함수2 (0)	2018.04.08
[Oracle] Decode 함수1 (0)	2018.04.08
[오라클 성능 고도화1] SQL Trace (0)	2018.04.02
[오라클 성능 고도화1] AutoTrace (0)	2018.04.01
[Oracle] UnPivot 함수 (0)	2018.04.01

[오라클 성능 고도화1] SQL Trace

2018. 4. 2. 23:56

SQL Trace

[출처] 오라클 성능 고도화 원리와 해법(조시형저)

※ SQL Trace는 실행되는 SQL문의 실행통계를 세션별로 모아서 Trace 파일을 만든다.

※ SQL Trace는 세션과 인스턴스 레벨에서 SQL문장들을 분석 할 수 있다.

※ SQL Trace파일에 의해 생성된 파일의 확장자는 .trc이고 .trc파일은 직접 읽기 불편해서 TKPROF 유틸리티를 이용해서 변환 후 분석

※ 인스턴스 레벨별로 Trace를 수행할 경우 전체적인 성능이 20~30% 떨어지므로 세션레벨로 Trace파일을 생성해야 한다.

자기 세션에 Trace 걸기

SQL> alter session set sql_trace = true;
ERROR:
ORA-01031: insufficient privileges

현재 사용자에게 alter session 권한이 없는 경우이다. 이때는 관리자 계정에서 사용자에게 권한을 설정한다.

SQL> grant alter session to scott;
Grant succeeded.

SQL> conn scott
Enter password:
Connected.
SQL> alter session set sql_trace = true;
Session altered.

scott user 계정으로 접속 후 trace파일을 생성해 본다.

SQL> alter session set sql_trace = true;
Session altered.

SQL> select * from emp where empno = 7900;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM    DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------    ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                           30


SQL> select * from dual;
D
-
X

SQL> alter session set sql_trace = false;
Session altered.

trace파일 뒤쪽에 식별자를 붙여서 자신의 트레이스 파일을 쉽게 찾을 수 있게 한다.

SQL> alter session set tracefile_identifier='scott'; Session altered. 파일의 생성결과는 아래와 같다. [oracle@localhost trace]$ pwd /usr/oracle/app/diag/rdbms/orcl/orcl/trace [oracle@localhost trace]$ ls -altr *scott.trc -rw-r-----. 1 oracle oinstall 1921 Apr 3 00:03 orcl_ora_5240_scott.trc

TKPRof 유틸리티를 사용해서 trace 파일을 보기좋게 포맷팅을 한다.

[oracle@localhost trace]$ tkprof orcl_ora_5240_scott.trc scott.prf

TKPROF: Release 11.2.0.1.0 - Development on Tue Apr 3 00:13:51 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


[oracle@localhost trace]$ ls -altr *scott.*
-rw-r-----. 1 oracle oinstall   65 Apr  3 00:03 orcl_ora_5240_scott.trm
-rw-r-----. 1 oracle oinstall 1921 Apr  3 00:03 orcl_ora_5240_scott.trc
-rw-r--r--. 1 oracle oinstall 3764 Apr  3 00:13 scott.prf

tkprof 유틸리티로 변환된 scott.prf의 전체 내용은 아래와 같다.

[oracle@localhost trace]$ vi scott.prf


TKPROF: Release 11.2.0.1.0 - Development on Tue Apr 3 00:13:51 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Trace file: orcl_ora_5240_scott.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: 8am3c6wqp3upw
Plan Hash: 2949544139
select *
from
 emp where empno = 7900

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          2          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=0 us cost=1 size=38 card=1)
      1   INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 73202)
********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          2          0           1

Misses in library cache during parse: 0


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    1  user  SQL statements in session.
    0  internal SQL statements in session.
    1  SQL statements in session.
********************************************************************************
Trace file: orcl_ora_5240_scott.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
       1  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       1  SQL statements in trace file.
       1  unique SQL statements in trace file.
      39  lines in trace file.
      16  elapsed seconds in trace file.

SQL문 바로 아래에 있는 Call 통계 컬럼(위에서 붉은색으로 표기된 부분임)의 의미는 아래 표와 같다.

항목	설명
call	커서 상태에 따라 Parse, Execute, Fetch 세 개의 call로 나누어 각각의 통계정보를 보여준다. ＊Parse : 커서를 파싱하고 실행계획을 생성하는 작업에 대한 통계 ＊Execute : 커서의 실행 단계에 대한 통계 ＊Fetch : 레코드를 실제로 Fetch하는데 대한 통계
count	Parse, Execute, Fetch 각 단계가 수행된 횟수
cpu	현재 커서가 각 단계에서 사용한 cpu time
elapsed	현재 커서가 각 단계를 수행하는데 소요된 시간
disk	디스크로 부터 읽은 블록 수
query	Consistent 모드에서 읽은 버퍼 블록 수
current	Current 모드에서 읽은 버퍼 블록 수
rows	각 단계에서 읽거나 갱신한 처리 건수

Call 통계 바로 아래에 있는 Row Source Operation(위에서 파란색으로 표기된 부분임)의 의미는 아래와 같다.

＊가장 왼쪽의 Rows는 각 수행 단계에서 풀력된 로우 수를 의미한다.

＊오른쪽의 cr, pr, pw, time 의 의미는 아래와 같다.

cr : Consistent 모드 블럭 읽기

pr : 디스크 블럭 읽기

pw : 디스크 블럭 쓰기

time : 소요시간

※ 각 단계의 값은 부모는 자식노드의 값을 누적한 값으로 표현이된다.

저작자표시 비영리 변경금지

'개발 > DataBase' 카테고리의 다른 글

[Oracle] Decode 함수1 (0)	2018.04.08
[Oracle] Regular Expression (0)	2018.04.04
[오라클 성능 고도화1] AutoTrace (0)	2018.04.01
[Oracle] UnPivot 함수 (0)	2018.04.01
[Oracle] Pivot 함수 (0)	2018.04.01

[오라클 성능 고도화1] AutoTrace

2018. 4. 1. 22:34

AutoTrace

[출처] 오라클 성능 고도화 원리와 해법(조시형저)

※ AutoTrace 결과에는 SQL을 튜닝하는데 필요한 정보들을 많이 포함하고 있다.

포함정보에는 쿼리수행결과, 실행계획, 실행통계를 포함

※ 아래는 AutoTrace를 한 결과화면이다.

(파란색: 실행결과, 핑크색: 실행계획, 그레이색: 실행통계)

SQL> set autotrace on
SQL> select * from scott.emp where empno=7900;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30


Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("EMPNO"=7900)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        889  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

1. AutoTrace 설정시 옵션 조합에 따른 출력양식

1-1. set autotrace on

- SQL을 실제 수행하고 그 결과와 함께 실행계획 및 실행통계를 출력한다.

1-2. set autotrace on explain

- SQL을 실제 수행하고 그 결과와 함께 실행계획을 출력한다.

1-3. set autotrace on statistics

- SQL을 실제 수행하고 그 결과와 함께 실행통계를 출력한다.

1-4. set autotrace traceonly

- SQL을 실제 수행은 하지만 그 결과는 출력하지 않고 실행계획과 실행통계만 출력한다.

1-5. set autotrace traceonly explain

- SQL을 실제 수행하지 않고 실행계획만 출력한다.

1-6. set autotrace traceonly statistics

- SQL을 실제 수행은 하지만 그 결과는 출력하지 않고 실행통계만 출력한다.

2. AutoTrace 실행시 사용되는 Table 및 View는 아래와 같다.(관리자 권한이 아니라 일반사용자권한이라면 plan_table만 조회권한이 있다.)

- plan_table : 실행계획의 결과를 저장하는 테이블 (일반사용자도 조회권한이 있음)

- v_$sesstat : 수행하는 세션별로 통계치를 확인하는 View (일반사용자 조회권한이 없음)

- v_$statname : 수행하는 stat의 고유번호에 대한 이름을 확인하는 View(일반사용자 조회권한 없음)

- v_$mystat : 현재 접속해 있는 자신의 세션에 대한 수행통계를 확인하는 View(일반사용자 조회권한 없음)

3. 일반사용자에게 AutoTrace에 대한 권한주기

- 일반사용자가 실행계획만 보고자 한다면 plan_table 에 조회권한을 가지기 때문에 권한을 부여할 필요는 없다.

- 일반사용자에게 실행통계까지 보여주고자 할때는 위에서 기술한 View에 대한 조회권한을 주면 되지만 관리상 plustrace roll을 생성하고 필요한 사용자에게 plustrace roll을 부여하는 방식으로 관리한다.

※ plustrace roll 생성

SQL> create role plustrace;
Role created.

SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.

SQL> grant select on v_$statname to plustrace;
Grant succeeded.

SQL> grant select on v_$mystat to plustrace;
Grant succeeded.

SQL> grant plustrace to dba with admin option;
Grant succeeded.

※ scott 사용자에게 plustrace roll 부여하기 전 실행결과

SQL> conn scott Enter password: Connected. SQL> set autotrace on SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report

※ scott 사용자에게 plustrace roll 부여한 후 실행결과

SQL> grant plustrace to scott;
Grant succeeded.

SQL> conn scott
Enter password:
Connected.
SQL> set autotrace on
SQL> select * from emp where empno = 7900;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30


Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("EMPNO"=7900)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        889  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

저작자표시 비영리 변경금지

'개발 > DataBase' 카테고리의 다른 글

[Oracle] Regular Expression (0)	2018.04.04
[오라클 성능 고도화1] SQL Trace (0)	2018.04.02
[Oracle] UnPivot 함수 (0)	2018.04.01
[Oracle] Pivot 함수 (0)	2018.04.01
[오라클 성능 고도화2] 다양한 Index Scan 방식 (0)	2018.03.23

[Oracle] UnPivot 함수

2018. 4. 1. 12:14

UnPivot 함수

※ UnPivot 함수는 가로방향으로 조회된 데이터를 세로방향으로 변환하는 기능을 제공한다.

2018/04/01 - [개발/DataBase] - [Oracle] Pivot 함수

※ 기본문법

SELECT . . .

FROM <pivot_table>

UNPIVOT (

column FOR <pivot-column> IN (<value1>,<value2>,...,<valuen>)

WHERE . . .

EMP 테이블을 이용해서 기본데이터 생성한다.

WITH T AS ( 
    SELECT
      TO_CHAR(EMPNO) EMPNO,
      ENAME,
      JOB,
      TO_CHAR(MGR) MGR,
      TO_CHAR(HIREDATE) HIREDATE,
      TO_CHAR(SAL) SAL,
      TO_CHAR(COMM) COMM,
      TO_CHAR(DEPTNO) DEPTNO
   FROM EMP
   WHERE EMPNO=7788
)   
SELECT * FROM T;

EMPNO      ENAME      JOB       MGR         HIREDATE SAL      COMM              DEPTNO              
---------- ---------- --------- ----------- -------- -------- ----------------- ------------
7788       SCOTT      ANALYST   7566        87/04/19 3000                       20

가로로 PIVOT된 결과를 세로로 columnname: value 형태로 세로로 UnPivot 한다.

WITH T AS ( 
    SELECT
      TO_CHAR(EMPNO) EMPNO,
      ENAME,
      JOB,
      TO_CHAR(MGR) MGR,
      TO_CHAR(HIREDATE) HIREDATE,
      TO_CHAR(SAL) SAL,
      TO_CHAR(COMM) COMM,
      TO_CHAR(DEPTNO) DEPTNO
   FROM EMP
   WHERE EMPNO=7788
)   
SELECT * FROM T
UNPIVOT (
   VALUE FOR COL IN ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
);

COL      VALUE                                   
-------- ----------------------------------------
EMPNO    7788                                    
ENAME    SCOTT                                   
JOB      ANALYST                                 
MGR      7566                                    
HIREDATE 87/04/19                                
SAL      3000                                    
DEPTNO   20                                      

7개 행이 선택되었습니다.

UnPivot결과에서 COMM 컬럼은 NULL이므로 제외된다. NULL을 포함하려면 INCLUDE NULLS 옵션을 사용하면 된다.

WITH T AS ( 
    SELECT
      TO_CHAR(EMPNO) EMPNO,
      ENAME,
      JOB,
      TO_CHAR(MGR) MGR,
      TO_CHAR(HIREDATE) HIREDATE,
      TO_CHAR(SAL) SAL,
      TO_CHAR(COMM) COMM,
      TO_CHAR(DEPTNO) DEPTNO
   FROM EMP
   WHERE EMPNO=7788
)   
SELECT * FROM T
UNPIVOT INCLUDE NULLS  (
   VALUE FOR COL IN ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO )
);

COL      VALUE                                   
-------- ----------------------------------------
EMPNO    7788                                    
ENAME    SCOTT                                   
JOB      ANALYST                                 
MGR      7566                                    
HIREDATE 87/04/19                                
SAL      3000                                    
COMM                                             
DEPTNO   20                                      

8개 행이 선택되었습니다.

저작자표시 비영리 변경금지

'개발 > DataBase' 카테고리의 다른 글

[오라클 성능 고도화1] SQL Trace (0)	2018.04.02
[오라클 성능 고도화1] AutoTrace (0)	2018.04.01
[Oracle] Pivot 함수 (0)	2018.04.01
[오라클 성능 고도화2] 다양한 Index Scan 방식 (0)	2018.03.23
[오라클 성능 고도화2] Index의 기본원리 (0)	2018.03.22

[Oracle] Pivot 함수

2018. 4. 1. 02:17

pivot 함수

※ pivot 함수는 세로로 조회된 데이터를 가로로 변환하는 기능을 제공한다.

2018/04/01 - [개발/DataBase] - [Oracle] UnPivot 함수

※ 기본 문법

SELECT . . .

FROM <table-expr>

PIVOT (

aggregate-function(<column>)

FOR <pivot-column> IN (<value1>, <value2>, . . . <valuen>)

WHERE . . .

※ pivot 절에 기술된 컬럼 이외에 컬럼을 조회하게 되면 <table-expr>테이블의 모든 컬럼중 <pivot-column>컬럼을 제외하고 Group By 하므로 컬럼의 정제가 필요하다.

SELECT * FROM EMP
PIVOT(
    SUM(SAL)
    FOR DEPTNO IN (10, 20, 30)
);

     EMPNO ENAME      JOB              MGR HIREDATE       COMM         10         20         30
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 81/09/28       1400                             1250
      7698 BLAKE      MANAGER         7839 81/05/01                                        2850
      7934 MILLER     CLERK           7782 82/01/23                  1300                      
      7521 WARD       SALESMAN        7698 81/02/22        500                             1250
      7566 JONES      MANAGER         7839 81/04/02                             2975           
      7844 TURNER     SALESMAN        7698 81/09/08          0                             1500
      7900 JAMES      CLERK           7698 81/12/03                                         950
      7839 KING       PRESIDENT            81/11/17                  5000                      
      7876 ADAMS      CLERK           7788 87/05/23                             1100           
      7902 FORD       ANALYST         7566 81/12/03                             3000           
      7788 SCOTT      ANALYST         7566 87/04/19                             3000           

     EMPNO ENAME      JOB              MGR HIREDATE       COMM         10         20         30
---------- ---------- --------- ---------- -------- ---------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 81/06/09                  2450                      
      7369 SMITH      CLERK           7902 80/12/17                              800           
      7499 ALLEN      SALESMAN        7698 81/02/20        300                             1600

14개 행이 선택되었습니다.

JOB, DEPTNO 로 GROUP BY 결과

SELECT JOB, DEPTNO, SUM(SAL) AS SUM
FROM EMP
GROUP BY JOB, DEPTNO;

JOB           DEPTNO        SUM
--------- ---------- ----------
MANAGER           20       2975
PRESIDENT         10       5000
CLERK             10       1300
SALESMAN          30       5600
ANALYST           20       6000
MANAGER           30       2850
MANAGER           10       2450
CLERK             30        950
CLERK             20       1900

9개 행이 선택되었습니다.

JOB을 기준으로 부서별 급여합계를 PIVOT한 결과

WITH TEMP AS (
    SELECT JOB,DEPTNO, SAL
    FROM EMP
)
SELECT * FROM TEMP
PIVOT(
   SUM(SAL) AS SUM
    FOR DEPTNO IN (10, 20, 30)
);

JOB           10_SUM     20_SUM     30_SUM
--------- ---------- ---------- ----------
CLERK           1300       1900        950
SALESMAN                              5600
PRESIDENT       5000                      
MANAGER         2450       2975       2850
ANALYST                    6000

복수의 컬럼을 PIVOT 할수도 있다.

- 복수의 컬럼을 집계해서 PIVOT할 경우 집계합수에 반드시 Alias로 별칭을 지정해야 한다.

WITH TEMP AS (
    SELECT JOB,DEPTNO, SAL
    FROM EMP
)
SELECT * FROM TEMP
PIVOT(
   SUM(SAL) AS SUM, AVG(SAL) AS AVG
   FOR DEPTNO IN (10, 20, 30)
);

JOB           10_SUM     10_AVG     20_SUM     20_AVG     30_SUM     30_AVG
--------- ---------- ---------- ---------- ---------- ---------- ----------
CLERK           1300       1300       1900        950        950        950
SALESMAN                                                    5600       1400
PRESIDENT       5000       5000                                            
MANAGER         2450       2450       2975       2975       2850       2850
ANALYST                               6000       3000

PIVOT IN절에 Alias로 별칭을 지정할 수 있다.

WITH TEMP AS (
    SELECT JOB,DEPTNO, SAL
    FROM EMP
)
SELECT * FROM TEMP
PIVOT(
   SUM(SAL) AS SUM, AVG(SAL) AS AVG
    FOR DEPTNO IN (10 AS ACCOUNTING, 20 AS RESEARCH, 30 AS SALES)
);

JOB       ACCOUNTING_SUM ACCOUNTING_AVG RESEARCH_SUM RESEARCH_AVG  SALES_SUM  SALES_AVG
--------- -------------- -------------- ------------ ------------ ---------- ----------
CLERK               1300           1300         1900          950        950        950
SALESMAN                                                                5600       1400
PRESIDENT           5000           5000                                                
MANAGER             2450           2450         2975         2975       2850       2850
ANALYST                                         6000         3000

저작자표시 비영리 변경금지

'개발 > DataBase' 카테고리의 다른 글

[오라클 성능 고도화1] AutoTrace (0)	2018.04.01
[Oracle] UnPivot 함수 (0)	2018.04.01
[오라클 성능 고도화2] 다양한 Index Scan 방식 (0)	2018.03.23
[오라클 성능 고도화2] Index의 기본원리 (0)	2018.03.22
[Oracle]User Function 성능 향상 Tip (0)	2018.03.15

PREV 1 2 NEXT

코딩하는 야매 기타맨

개발/DataBase

[Oracle] ROWNUM의 기본 동작

'개발 > DataBase' 카테고리의 다른 글

[Oracle] Null Column 조회 성능개선

'개발 > DataBase' 카테고리의 다른 글

[Oracle] WHERE절에 number, varchar2 형변환 우선순위

'개발 > DataBase' 카테고리의 다른 글

[Oracle]Decode 함수2

'개발 > DataBase' 카테고리의 다른 글

[Oracle] Decode 함수1

'개발 > DataBase' 카테고리의 다른 글

[Oracle] Regular Expression

'개발 > DataBase' 카테고리의 다른 글

[오라클 성능 고도화1] SQL Trace

'개발 > DataBase' 카테고리의 다른 글

[오라클 성능 고도화1] AutoTrace

'개발 > DataBase' 카테고리의 다른 글

[Oracle] UnPivot 함수

'개발 > DataBase' 카테고리의 다른 글

[Oracle] Pivot 함수

'개발 > DataBase' 카테고리의 다른 글

+ Recent posts

티스토리툴바