Analytic Function Analytic Function의 소개 초기 대부분의 RDBMS는 집합적인 개념에 충실하여 만들어졌기 때문에 집합적인 개념에 위배되는 처리는 표준 SQL로 처리가 불가능하였다. 이러한 작업은 프로그램 로직으로 처리하거나 데이터의 복제 등 다양한 응용 SQL을 활용하여 처리해야만 했다. 그러나 프로그램 로직으로 처리할 경우 집합 개념의 RDBMS 시스템에서 심각한 성능 저하를 유발할 수 있다. 즉 고가의 RDBMS를 구입해 놓고 DBMS를 단지 데이터 저장소로만 사용하는 방법이다. 특히 비즈니스 분석작업에서 위와 같은 방법으로 처리할 경우 어떤 결과를 초래하겠는가? 분석작업에 필요한 데이터라면 범위가 일부분에 국한되지 않을 것이다. 때에 따라서 범위를 정하여 분석하는 경우도 있고, 전체를 대상으로 분석하는 경우도 발생하겠지만 거의 대부분은 넓은 범위의 데이터가 대상이 된다는 점이다. 이런 넓은 범위의 다량 데이터를 반복적으로 읽어와서 프로그램 로직으로 처리할 경우 이미 성능을 논할 단계는 넘어간 것이다. 그러면 성능 개선을 위해 RDB의 집합개념을 이용하여 고성능 SQL로 해결하면 어떻게 되는가 ? 해당하는 전체 집합을 정한 후, 이 전체 집합을 보고자 하는 Level로 Group by를 하여 나누어 주고 Decode를 이용하여 분리한 통계를 만들고 다시 직업별 통계를 구하기 위해 원 집합을 복제하여 직업별로 Grouping 작업을 수행한 후 두 집합을 결합하는 하나의 SQL을 만들어 DBMS 에게 수행하라고 명령(Query)하는 것이다. 이 경우 DBMS가 해당하는 대상이 되는 Data들을 읽어서 Group by를 수행하고 조합한 후 다시 순위를 매겨 최종 결과만을 프로그램에 Return하게 된다. - 여기선 비교적 간단한 분석/통계작업의 예를 들었지만 위의 예에다가 Group(직업) 별 순위를 매기로 순위에 따라 고객 신용등급을 다르게 적용하는 등 몇 가지 응용이 추가된다면 Group Serial등 다양한 기법의 고난도 SQL을 적용하게 될 것 이다.

Analytic Function Analytic Function의 소개 그러면 이 두 가지 문제를 통시에 해결할 수는 없을까 ? 그룹 내 순위를 정하기 위해 복잡하게 SQL을 구사한 부분을 하나의 명령으로 DBMS가 처리해 줄 수는 없을까 ? 집합개념에서는 처리가 되지 않는 각 Row 간의 값을 비교할 수는 없을까 ? 즉 사용자의 비즈니스 요구사항이 더욱 더 복잡해 지면서 구현이 용이하고 성능도 향상시킬 수 있는 강력한 SQL의 필요성이 절실하게 요구되었고 이러한 필요에 따라 Red Brick은 DATA ANALYSIS나 DSS (DECISION-SUPPORT SYSTEM)에 적합한 다양하고 강력한 기능을 가진 SQL을 제안하였는데, 새로운 제안에는 집합적 개념인 표준 SQL에서 처리가 어려워 절차적으로 처리를 할 수 밖에 없었던 비즈니스 분석 요구를 수용하기 위해 cume, MovingAvg(n), MovingSum(s), Rank....When, RatioToReport, Tertile, Create Macro와 같은 많은 functions들의 지원을 포함하고 있으며 이는 집합개념에서 수용하지 못하였던 포인터(Pointer)와 오프셋(offset)의 개념을 추가 시킨 것으로 이 SQL을 RISQL(Red Brick intelligent SQL) 이라 하다. 여기서 추가된 개념들이 바로 Analytic Function이다. Analytic function을 지원하는 RDBMS를 사용하는 경우 Self-join 또는 클라이언트 프로그램의 절차적 로직으로 표현한 것 또는 SQL로 표현하기 위해 고난도의 여러 기법을 적용하였던 것을 native SQL에서 하나의 명령어로 바로 적용할 수 있으므로 조인이나 클라이언트 프로그램의 overhead를 줄임으로써 Query 속도를 향상시킬 수 있고, 개발자가 명백하고 간결한 SQL로 복잡한 분석작업을 수행할 수 있으며, 개발 및 유지보수가 편하기 때문에 생산력을 향상시킬 수 있다. 또한 기존 SQL syntax를 그대로 따르기 때문에 기존 Standard SQL을 사용하던 개발자/운영자의 이해가 빠르며 적용하기 쉽고 ANSI SQL 채택으로 향후 다양한 소프트웨어에 적용이 될 것이므로 때문에 표준화에도 유리한 장점이 있으므로 그 활용 정도가 점차 확대될 것이다.

Analytic Function Analytic Function의 소개 Analytic Function의 수행원리 1단계(General Query Processing) : JOIN, WHERE, GROUP BY 등의 기존 Query Processing 수행 단계로서 기존 Standard SQL이 수행되는 동일한 원리에 의해서 대상 집합을 추출하는 단계이다. 2단계(Analytic Function Applying) : 1단계 결과를 가지고 실제 Analytic Function이 적용되어 필요한 계산을 행하는 단계, 즉 대상집합을 필요한 몇 개의 Group으로 분리하고 순위를 결정하며 그룹 순위를 기준으로 명령된 계산을 수행하는 단계이다. 이 단계에서 내부적으로 적용되는 세부 메커니즘은 다음과 같이 수행된다. 대상집합을 Analytic Function이 적용되어야 할 각 Group으로 나눈다(Partitioning). 각 Partition내의 집합에 속한 개체에 조건에 따른 순위를 결정한다. Pointer와 Off-Set개념을 적용하여 각 Row간에 필요한 계산을 수행한다. 3단계(Order by Processing – Optional) : Query에 Order by절이 있다면 최종 결과에 대한 Ordering을 수행하는 단계

Analytic Function Analytic Function의 소개 Analytic Function의 수행원리 Result Set Partitions : query processing with analytic function의 1단계 수행결과를 column이나 expression을 기준으로 grouping한 것, 1단계 수행결과 전체가 하나의 partition에 속할 수도 있고, 적은 rows를 가진 여러 개의 작은 partition으로 쪼개질 수도 있다. 그러나, 한 row는 반드시 하나의 partition에 속한다. (Sliding) Window : current row에 대한 analytic calculation 수행의 대상이 되는 row의 범위(range), window는 current row를 기준으로 하나의 partition 내에서 sliding하며, 반드시 starting row와 ending row를 가진다. window size는 partition 전체가 될 수도 있고 partition의 부분범위가 될 수도 있으나 하나의 partition을 넘을 수는 없다.partition의 부분범위로서 window size를 정할 때는 physical number of rows로 정할 수도 있고 logical interval로 정할 수도 있다. Current Row : 모든 Analytic Function의 적용은 항상 Partition내의 Current Row를 기준으로 수행된다. Current Row는 항상 Window의 Start와 End를 결정하는 기준(Reference Point)으로서 역할을 하므로 Current Row가 없는 Window는 존재하지 않는다.

Analytic Function Analytic Function의 소개 Analytic Function의 수행원리 Result Set Partitions (Sliding) Window Current Row

Analytic Function Analytic Function의 소개 Analytic Function의 수행원리 Ranking Family : 대상 집합에 대하여 특정 컬럼(들) 기준으로 순위나 등급을 매기는 Analytic Function 류로서 다음과 같은 종류가 있다. - RANK(), DENSE_RANK(), CUME_DIST(), PERCENT_RANK(), NTILE(), ROW_NUMBER() Window Aggregate Family : 현재 Row(Current Row)를 기준으로 지정된 윈도우(Window) 내의 로우들을 대상으로 집단화(aggregate)를 수행하여 여러 가지 유용한 집계정보(Running Summary, Moving Average 등)를 구하는 Analytic Function 류이며 다음과 같은 종류가 있다. - SUM, AVG, MIN, MAX, STDDEV, VARIANCE, COUNT, FIRST_VALUE, LAST_VALUE Reporting Aggregate Family : 서로 다른 두 가지의 Aggregation Level을 비교하고자 하는 목적으로 사용하는 Analytic Function으로 다음과 같은 종류가 있다. - SUM, AVG, MIN, MAX, COUNT, STDDEV, VARINCE LEAD/LAG Family : 서로 다른 두 Row 값을 비교하기 위한 Analytic Function으로 LEAD와 LAG가 있다.

Analytic Function Analytic Function의 소개 Analytic Function의 수행원리 {Analytic Function} ([ALL | DISTINCT] { | *}) OVER ([PARTITION BY [,...] ] [ORDER BY [,...] ] [Windowing_Clause]) Analytic Function : 하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있다. Analytic Function의 아규먼트는 0에서 3개까지 가능하고 Asterisk(*)는 COUNT()에서만 허용되며 DISTINCT는 해당 집계 함수가 허용할 때만 지원된다. Over analytic_clause : 해당 함수가 쿼리 결과 집합에 대해 적용되라는 지시어로 FROM, WHERE, GROUP BY와 HAVING구 이후에 계산되어 진다. SELECT구 또는 ORDER BY구에 Analytic Function을 사용할 수 있다.

Analytic Function Analytic Function의 소개 Analytic Function의 수행원리 ① PARTITION BY 구 : 하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고, 하나 이상의 컬럼 또는 표현 식에 의한 그룹으로 쿼리의 결과를 파티션한다. 이 구가 생략되면 단일그룹처럼 쿼리 결과 집합이 처리된다. ② ORDER BY 구 : 하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고, 하나 이상의 컬럼 또는 표현식을 기준으로 파티션 내의 데이터를 정렬한다. 표현식은 컬럼의 별칭 또는 위치를 나타내는 숫자를 사용할 수 없다. ③ WINDOW 구 ⓐ Window 구의 예약어 ⊙ CURRENT ROW – 윈도우의 시작 위치 또는 마지막 위치가 현재 로우임을 지시하는 예약어 ⊙ UNBOUNDED PRECEDING – 윈도우의 시작 위치가 Partition의 첫 번째 로우임을 지시하는 예약어 ⊙ UNBOUNDED FOLLOWING – 윈도우의 마지막 위치가 Partition의 마지막 로우임을 ⓑ Physical Window – Physical Window Size는 Rows를 환산하여 표현한다. ⓒ Logical Window ⊙ Time Interval – Logical Window Size는 Time Interval로 환산하여 표현한다. ⊙ Value Range – Logical Window Size는 정렬된 순서에서 Current Value와 이전 Value들과의 차로 환산하여 표현한다. Window 구의 예약어는 Aggregate Family에서 사용하는 예약어 임.

Analytic Function Rank Family Rank Family의 특징 ◎ 특징 Rank Family에 속하는 Analytic Function은 대상 집합에 대하여 특정 컬럼(들)을 기준으로 순위나 등급을 부여하는 것으로 다음과 같은 특징을 가지고 있다. 오름차순 또는 내림차순으로 순위나 등급을 부여할 수 있다. 오름차순, 내림차순과 관계없이 NULL은 순위의 가장 처음 또는 마지막으로 강제 처리 가능하다. Rank Functions는 각 파티션마다 초기화된다. 순위 또는 등급은 GROUP BY CUBE와 ROLLUP절마다 초기화된다. ◎ 문법 {Rank Family} OVER( [PARTITION BY <Value Expression1, ..>] ORDER BY <Value Expression2, ..> [Collate Clause] [ASC | DESC] [NULLS FIRST | NULLS LAST] );

Analytic Function Rank Family Rank Family의 종류 ① RANK() RANK() 함수는 각 로우마다 순위를 매겨주는 함수로 각 Partition 내에서 ORDER BY절에 명시된 대로 정렬한 후의 순위를 의미하고 1부터 시작하여 동일한 값은 동일한 순위를 가지며, 동일한 순위의 수만큼 다음 순위는 건너뛴다. ② DENSE_RANK() DENSE_RANK()는 RANK()와 유사한 함수로 ORDER BY절에 사용된 컬럼이나 표현 식에 대하여 순위를 부여하는데 RANK()와 달리 동일 순위 다음의 순위는 동일 순위의 수와 상관없이 1 증가된 값을 돌려준다. ③ CUME_DIST() : Cumulative Distribution Funtion PARTITION BY에 의해 나누어진 그룹별로 각 로우를 ORDER BY절에 명시된 순서대로 정렬한 후 그룹별 상대적인 위치(누적된 분산정도)를 구한다. 상대적인 위치는 구하고자 하는 값보다 작거나 같은 값을 가진 로우수를 그룹내 총 로우수로 나눈 것을 의미하며 결과값의 범위는 0보다 크고 1보다 작거나 같다. ④ PERCENT_RANK() CUME_DIST()와 유사한 함수이나 Partition별 각 Row의 순위 – 1 / Partition내의 Row수를 결과값으로 하며 결과값 범위는 0 <= 결과값 <= 1이고 집합의 첫 번째 Row의 PERCENT_RANK는 항상 0이 된다.

Analytic Function Rank Family Rank Family의 종류 ⑤ NTILE() NTILE() 함수는 정렬된 Partition을 BUCKET이라 불리우는 그룹별로 나누고 Partition내의 각 Row등을 BUCKET에 배치하는 함수로 각 BUCKET에는 동일한 수의 Row가 배치된다. 예를 들어 Partition내에 100개의 Row를 가지고 있고 4개의 BUCKET으로 나누는 NTILE(4)를 사용하면 1개의 BUCKET당 25개의 ROW가 배정된다. 만일 각 Partition의 수가 정확하게 분배되지 않을 경우 근사치로 배분한 후 남는 값에 대하여 최초 Partition부터 한 개씩 배분한다. 즉 만일 103개의 Row에 대하여 NTILE(5)를 적용하면 첫 번째 BUCKET부터 세 번째 BUCKET까지는 21개의 Row가, 나머지는 20개의 Row가 배치된다. ⑥ ROW_NUMBER() ROW_NUMBER()는 각 Partition내에서 ORDER BY절에 의해 정렬된 순서로 유일한 값을 돌려주는 함수로 ROWNUM과는 관계가 없다.

Analytic Function LEAD/LAG Family LEAD/LAG Family의 특징 ◎ 특징 오름차순 또는 내림차순으로 정렬된 파티션 내에서 상대적으로 상위 또는 하위에 위치하고 있는 특정 로우의 컬럼값을 Offset 지정에 의해서 참조할 수 있다. 파티션 내에서 참조할 로우가 없을 경우 지정한 값(Default=NULL)으로 출력한다. ORDER BY에 기술된 컬럼의 값이 NULL인 경우 오름차순 또는 내림차순과 관계없이 순서상 가장 처음 또는 마지막으로 강제 처리 가능하다. ◎ 문법 LEAD | LAR (Value Expression1) OVER( [PARTITION BY <Value Expression2>] ORDER BY <Value Expression3> [Collate Clause] [ASC | DESC] [NULLS FIRST | NULLS LAST] );

Analytic Function LEAD/LAG Family LEAD/LAG의 종류 ① LEAD() LEAD()는 Offset에 지정된 값(Default=1) 만큼 상대적으로 하위에 위치한 로우(오름차순의 경우 기준 로우의 정렬 컬럼 값보다 큰 값을 갖는 로우, 내림차순의 경우 기준 로우의 정렬 컬럼 값보다 작은 값을 갖는 로우)를 참조하기 위해 사용된다. ② LAG() LAG() 함수는 파티션 내에서 Offset에 지정된 값(Default=1) 만큼 상대적으로 상위에 위치한 로우(오름차순 의 경우 기준 로우의 정렬 컬럼 값보다 작은 값을 갖는 로우, 내림차순의 경우 기준 로우의 정렬 컬럼 값보다 큰 값을 갖는 로우)를 참조하기 위해 사용된다.

Analytic Function Aggregate Family Window Aggregate의 개념 ◎ Window Aggregate Family(윈도우 집계 유형) 윈도우 집계 함수는 윈도우를 근간으로 하여 정렬된 로우들의 집합과 그 각각의 로우들에 대한 집계 값을 반환한다. 이 함수들은 집계 함수(SUM, COUNT, MAX, MIN 등)의 파티션에 속하는 로우들을 Sliding Window에 대한 계산을 수행하기 위해 윈도우 문법(Window Syntax)을 추가하여 확장한 형태이다. ◎ Reporting Aggregate Family(보고용 집계 유형) 한 집합 레벨에 대한 집계 값과 다른 집합 레벨에 대한 집계 값의 비교를 통해 분석작업을 하고자 하는 경우이다. 예를 들어, 한 사원의 급여와 해당 부서의 평균 급여를 비교하고자 하는 경우나, 그 사원의 급여를 제외한 해당 부서의 평균 급여를 알고자 할 때, 보고용 집계 유형은 셀프 조인을 할 필요 없이 다른 집합 레벨에 대한 집계 값을 계산하여 반환한다. 한 그룹에 대해 하나의 집계 값을 반환하는 집계 함수와 다르게 보고용 집계 함수는 윈도우 레벨에서 작업한다. 이 함수는 윈도우 안의 모든 로우에 대해 같은 집계 값을 반환한다. 보고용 집계 함수는 전체 윈도우에 대한 집계 값을 반환하거나 해당 로우를 제외한 전체 윈도우의 집계 값을 계산하여 반환한다. 이 함수들의 대부분은 윈도우 집계 함수와 유사하고, 비슷한 기능을 수행한다.

Analytic Function Aggregate Family Window Aggregate의 개념 ◎ 문법 {SUM | AVG | MAX | MIN | COUNT | STDDEV | VARIANCE | FIRST VALUE | LAST VALUE} ({<Value Expression1> | *}) OVER ([PARTITION BY <Value Expression2>] ORDER BY <Value Expression3> [Collate Clause] [ASC | DESC] [NULLS FIRST | NULLS LAST] ROWS | RANGE {{UNBOUNDED PRECEDING | <Value Expression4> PRECEDING} | BETWEEN {UNBOUNDED PRECEDING | <Value Expression5> PRECEDING} | AND {CURRENT ROW | VALUE Ecpression6> FOLLOWING}} OVER : FROM, WHERE, GROUP BY, HAVING 절이 처리된 후에 적용되며, 함수를 적용하기 위한 행의 정렬 기준 또는 대상 행 집합에 대한 윈도우 정의 ROWS | RANGE : 윈도우 크기를 결정하기 위한 행 집합을 정의 ① ROWS는 물리적인 단위에 의해 윈도우 크기 지정 ② RANGE는 논리적인 상대 번지에 의해 윈도우 크기 지정 BETWEEN AND : 윈도우의 시작 위치와 마지막 위치 지정 UNBOUNDED PRECEDING : 윈도우의 시작 위치는 각 분할의 첫 번째 행 UNBOUNDED FOLLOWING : 윈도우의 마지막 위치는 각 분할의 마지막 행

Analytic Function Aggregate Family Windowing 구의 분류 및 의미 각 윈도우의 크기는 해당 파티션의 크기를 넘을 수 없고, 윈도우 구(Windowing_Clause)에 따라 해당 파티션 내에서 윈도우의 크기가 유기적으로 결정된다. 여기서 Physical Window와 Logical Window(Time Interval, Value Range)에 의한 분류 및 윈도우 구에 의해 유기적인 크기가 정해지는 Cumulative, Moving, Centered 형태의 분류에 따른 결과의 차이에 대해 살펴볼 것이다. 끝으로 Logical Window의 경우 PARTITION BY 구와 ORDER BY 구의 표현식의 결합에 의해 로우가 정렬되어 질 때 정렬이 Unique한 정렬인지 Non Unique한 정렬인지에 따라 결과가 어떻게 다른지를 살펴볼 것이다. ※ 다음 표는 위의 내용을 설명하기 위한 데이터 임(SALE_TBL).

Analytic Function Aggregate Family Windowing 구의 분류 및 의미 ① Cumulative Aggregate Function 각 파티션의 시작 위치가 윈도우의 시작 위치가 되고 현재 로우의 물리적 또는 논리적 위치가 윈도우의 종료 위치가 되어 여기에 해당 집계 함수를 적용하는 형태.

Analytic Function Aggregate Family Windowing 구의 분류 및 의미 ① Cumulative Aggregate Function SELECT CUSTCODE, SALEDATE, SALE_AMT, SUM(SALE_AMT) OVER(PARTITION BY CUSTCODE ORDER BY SALEDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ACC_AMT1, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ACC_AMT2, SUM(SALE_AMT) OVER(PARTITION BY CUSTCODE ORDER BY TO_DATE(SALEDATE, 'YYYYMMDD') RANGE BETWEEN UNBOUNDED PRECEDING AND INTERVAL '0' DAY FOLLOWING) ACC_AMT3, SUM(SALE_AMT) OVER(PARTITION BY CUSTCODE ORDER BY SALEDATE) ACC_AMT4 FROM SALE_TBL; 위의 쿼리문에서 굵은 글자가 Windowing_Cluase를 의미하고 ACC_AMT1은 Physical Window에 의해, ACC_AMT2는 Logical Window 중에서 Value Range에 의해, ACC_AMT3은 Logical Window 중에서 Time Interval, ACC_AMT4는 Window_Clause를 생략했지만 ACC_AMT2와 같은 의미를 가진다.

Analytic Function Aggregate Family Windowing 구의 분류 및 의미 ① Cumulative Aggregate Function 위의 도표에서 CUSTCODE가 ‘100’이고 SALEDATE가 ‘20020304’, SALE_AMT가 900원인 로우를 기준으로 보면 굵은 실선 화살표는 파티션, 얇은 실선 화살표는 물리적인 윈도우 크기, 얄은 점선 화살표는 논리적인 윈도우 크기를 의미한다. 여기서 주의해서 봐야 할 것은 논리적인 윈도우 크기가 물리적인 윈도우 크기보다 한 로우 더 내려간 것을 볼 수 있을 것이다. 이것은 데이터가 CUSTCODE, SALEDATE 순으로 정렬되므로, CUSTCODE ‘100’, SALEDATE ‘20020304’ 기준으로 보면 SALE_AMT가 900원 300원이 발생했지만 정렬 순위가 같으므로 논리적으로는 1,200원으로 처리된다.

Analytic Function Aggregate Family Windowing 구의 분류 및 의미 ② Moving Aggregate Function 각 파티션 내의 윈도우 종료 위치가 현재 로우의 물리적 또는 논리적 위치가 윈도우의 종료위치가 되고 이 종료 위치를 기준으로 Offset을 적용하여 시작위치가 결정되고 여기에 해당 집계 함수를 적용하는 형태.

Analytic Function Aggregate Family Windowing 구의 분류 및 의미 ② Moving Aggregate Function SELECT CUSTCODE, SALEDATE, SALE_AMT, SUM(SALE_AMT) OVER(PARTITION BY CUSTCODE ORDER BY SALEDATE ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ACC_AMT1, SUM(SALE_AMT) OVER(PARTITION BY CUSTCODE ORDER BY TO_DATE(SALEDATE, 'YYYYMMDD') RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) ACC_AMT2, RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND CURRENT ROW) ACC_AMT3 FROM SALE_TBL; 위의 쿼리문에서 굵은 글자가 Windowing_Clause를 의미하고 ACC_AMT1은 Physical Window에 의해, ACC_AMT2는 Logical Window 중 Value Range에 의해, ACC_AMT3은 Logical Window 중 Time Interval에 대한 의미를 가진다.

Analytic Function Aggregate Family Windowing 구의 분류 및 의미 ② Moving Aggregate Function 위의 도표에서 CUSTCODE가 ‘100’이고 SALEDATE가 ‘20020304’, SALE_AMT가 900원인 로우를 기준으로 보면 굵은 실선 화살표는 파티션, 얇은 실선 화살표는 물리적인 윈도우 크기, 얇은 점선 화살표는 논리적인 윈도우 크기를 의미한다. 여기서 주의해서 봐야 할 것은 논리적인 윈도우 크기가 물리적인 윈도우 크기보다 한 로우 더 내려간 것을 볼 수 있을 것이다. 이것은 데이터가 CUSTCODE, SALEDATE 순으로 정렬되므로, CUSTCODE ‘100’, SALEDATE ’20020304’ 기준으로 보면 판매액이 900원 300원이 발생했지만 정렬순위가 같으므로 논리적으로는 1,200원으로 처리된다.

Analytic Function Aggregate Family Windowing 구의 분류 및 의미 ③ Centered Aggregate Function 각 파티션 내의 윈도우의 크기가 현재 로우의 물리적 또는 논리적 위치를 기준으로 주어진 Offset을 적용하여 윈도우의 시작위치와 종료위치가 결정되고 여기에 해당 집계함수를 적용하는 형태.

Analytic Function Aggregate Family Windowing 구의 분류 및 의미 ③ Centered Aggregate Function SELECT CUSTCODE, SALEDATE, SALE_AMT, SUM(SALE_AMT) OVER(PARTITION BY CUSTCODE ORDER BY SALEDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ACC_AMT1, SUM(SALE_AMT) OVER(PARTITION BY CUSTCODE ORDER BY TO_DATE(SALEDATE, 'YYYYMMDD') RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) ACC_AMT2, RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING) ACC_AMT3 FROM SALE_TBL; 위의 쿼리문에서 굵은 글자가 Windowing_Clause를 의미하고 ACC_AMT1은 Physical Window에 의해, ACC_AMT2는 Logical Window 중 Value Range에 의해, ACC_AMT3은 Logical Window 중 Time Interval에 대한 의미를 가진다.

Analytic Function Aggregate Family Windowing 구의 분류 및 의미 ③ Centered Aggregate Function 위의 도표에서 CUSTCODE가 ‘100’이고 SALEDATE가 ‘20020304’, SALE_AMT가 900원인 로우를 기준으로 보면 굵은 실선 화살표는 파티션, 얇은 실선 화살표는 물리적인 윈도우 크기, 얇은 점선 화살표는 논리적인 윈도우 크기를 의미한다. 여기서 주의해서 봐야 할 것은 논리적인 윈도우 크기가 물리적인 윈도우 크기보다 한 로우 더 내려간 것을 볼 수 있을 것이다. 이것은 데이터가 CUSTCODE, SALEDATE 순으로 정렬되므로, CUSTCODE ‘100’, SALEDATE ’20020304’ 기준으로 보면 판매액이 900원 300원이 발생했지만 정렬순위가 같으므로 논리적으로는 1,200원으로 처리된다.

Analytic Function Aggregate Family Windowing 구의 분류 및 의미 ④ Window Size가 Logical Offset으로 결정될 경우 Ordering이 Unique할 때와 Non Unique할 때의 차이점 SELECT CUSTCODE, SALEDATE, SALE_AMT, SUM(SALE_AMT) OVER(PARTITION BY CUSTCODE ORDER BY SALEDATE RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ACC_AMT1, SUM(SALE_AMT) OVER(PARTITION BY CUSTCODE ORDER BY SALEDATE, SALE_AMT RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ACC_AMT2 FROM SALE_TBL; 위의 쿼리문에서 이텔릭 글자가 데이터 정렬에 기준이 되는 PARTITION BY와 ORDER BY구이고 굵은 글자가 Windowing_Clause를 의미하고 ACC_AMT1과 ACC_AMT2는 둘 다 Value Range에 의한 논리적 윈도우의 결과를 반영하지만, ORDER BY의 표현식을 다르게 하였다. ACC_AMT1의 정렬은 CUSTCODE, SALEDATE 기준으로 수행되고 데이터와 비교해보면 정렬의 순서가 같은 데이터들이 있다. 그러나 ACC_AMT2는 데이터 정렬이 CUSTCODE, SALEDATE, SALE_AMT 기준으로 수행되고 데이터와 비교해보면 정렬순서가 Unique하게 됨을 알 수 있다.

Analytic Function Aggregate Family Windowing 구의 분류 및 의미 ④ Window Size가 Logical Offset으로 결정될 경우 Ordering이 Unique할 때와 Non Unique할 때의 차이점 위의 도표에서 CUSTCODE가 ‘100’이고 SALEDATE가 ‘20020304’, SALE_AMT가 300원인 로우를 기준으로 보면 굵은 실선 화살표는 파티션, 얇은 실선 화살표는 ACC_AMT1의 논리적인 윈도우 크기, 얇은 점선 화살표는 ACC_AMT2의 논리적인 윈도우 크기를 의미한다. 여기서 주의해서 봐야 할 것은 ACC_AMT1의 논리적인 윈도우 크기가 ACC_AMT2의 논리적인 윈도우 크기보다 한 로우 더 내려간 것을 볼 수 있다. 이것은 ACC_AMT1의 경우 데이터가 CUSTCODE, SALEDATE 순으로 정렬되므로 CUSTCODE ‘100’, SALEDATE ‘20020304’ 기준으로 보면 판매액이 300원, 900원이 발생했지만 정렬순서가 같으므로 논리적으로는 1,200원으로 처리된다.

Analytic Function Aggregate Family Windowing 구의 분류 및 의미 ④ Window Size가 Logical Offset으로 결정될 경우 Ordering이 Unique할 때와 Non Unique할 때의 차이점 그러나 ACC_AMT2의 경우 데이터가 CUSTCODE, SALEDATE, SALE_AMT 순으로 정렬되므로 CUSTCODE ‘100’, SALEDATE ‘20020304’이고 SALE_AMT가 300원인 경우가 900원인 경우 보다 정렬 우선 순위가 앞서므로 각각의 로우가 별개로 처리됨을 알 수 있다. 위의 도표에서 알 수 있듯이 논리적인 윈도우를 지정하게 되면 정렬의 기준이 되는 PARTITION BY와 ORDER BY구에 의한 정렬의 기준이 Unique한 경우와 Non Unique한 경우 처리 결과가 다름을 알 수 있다.

Analytic Function Aggregate Family FIRST VALUE, LAST VALUE 윈도우의 정렬된 값 중에서 첫 번째 값을 반환 ② LAST VALUE 윈도우의 정렬된 값 중에서 마지막 값을 반환 ◎ 문법 FIRST VALUE | LAST VALUE(Value Expression1) OVER ( [PARTITION BY <Value Expression2> ORDER BY <Value Expression3> [Collate Clause] [ASC | DESC] [NULLS FIRST | NULLS LAST] )

Analytic Function Aggregate Family FIRST VALUE, LAST VALUE ◎ 예제 SELECT CUSTCODE, SALEDATE, SALE_AMT, FIRST_VALUE(SALE_AMT) OVER(PARTITION BY CUSTCODE ORDER BY SALEDATE) FIRST_SALE_AMT, LAST_VALUE(SALE_AMT) OVER(PARTITION BY CUSTCODE ORDER BY SALEDATE RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LAST_SALE_AMT FROM SALE_TBL;

Analytic Function Aggregate Family Reporting Aggregate Function의 활용 다음은 EMP 테이블을 이용하여 Reporting Aggregate Function의 활용 사례에 대해 설명을 할 것이다.

Analytic Function Aggregate Family Reporting Aggregate Function의 활용 ① Reporting Aggregate Family – SUM() - EMP 테이블에서 각각의 부서별로, 총 판매액이 가장 많은 직무를 아래 표와 같이 구한다. Analytic Function을 사용하지 않은 경우 SELECT V2.DEPTNO, V2.JOB, V2.SUM_SAL, V1.MAX_SUM_SAL FROM (SELECT DEPTNO, MAX(SUM_SAL) MAX_SUM_SAL FROM (SELECT DEPTNO, JOB, SUM(SAL) SUM_SAL FROM EMP GROUP BY DEPTNO, JOB) GROUP BY DEPTNO) V1, (SELECT DEPTNO, JOB, SUM(SAL) SUM_SAL GROUP BY DEPTNO, JOB) V2 WHERE V2.DEPTNO = V1.DEPTNO AND V2.SUM_SAL = V1.MAX_SUM_SAL;

Analytic Function Aggregate Family Reporting Aggregate Function의 활용 ① Reporting Aggregate Family – SUM() - EMP 테이블에서 각각의 부서별로, 총 판매액이 가장 많은 직무를 아래 표와 같이 구한다. Analytic Function을 사용한 경우 SELECT DEPTNO, JOB, SUM_SAL, MAX_SAL FROM (SELECT DEPTNO, JOB, SUM(SAL) SUM_SAL, MAX(SUM(SAL)) OVER(PARTITION BY DEPTNO) MAX_SAL FROM EMP E GROUP BY DEPTNO, JOB) A WHERE SUM_SAL = MAX_SAL;

Analytic Function Aggregate Family Reporting Aggregate Function의 활용 ① Reporting Aggregate Family – RATIO_TO_REPORT() - 각각의 부서 총 판매액에 대한 부서별, 직무 별 총 판매액의 비율을 알고자 할 경우 Analytic Function을 사용한 경우와 사용하지 않은 경우를 살펴보자. 참고적으로 RATIO_TO_REPORT()는 윈도우 내의 합계에 대한 비율을 계산하는 함수이다.

Analytic Function Aggregate Family Reporting Aggregate Function의 활용 ① Reporting Aggregate Family – RATIO_TO_REPORT() Analytic Function을 사용하지 않은 경우 SELECT V2.DEPTNO, V2.JOB, V2.SUM_SAL, V1.SUM_TOTAL, V2.SUM_SAL / V1.SUM_TOTAL RATIO_TO_SUM FROM (SELECT DEPTNO, SUM(SAL) SUM_TOTAL FROM EMP GROUP BY DEPTNO) V1, (SELECT DEPTNO, JOB, SUM(SAL) AS SUM_SAL GROUP BY DEPTNO, JOB) V2 WHERE V2.DEPTNO = V1.DEPTNO;

Analytic Function Aggregate Family Reporting Aggregate Function의 활용 ① Reporting Aggregate Family – RATIO_TO_REPORT() Analytic Function을 사용한 경우 SELECT DEPTNO, JOB, SUM(SAL) SUM_SAL, SUM(SUM(SAL)) OVER(PARTITION BY DEPTNO) TOTAL_SAL, RATIO_TO_REPORT(SUM(SAL)) OVER(PARTITION BY DEPTNO) RATIO_TO_SUM FROM EMP GROUP BY DEPTNO, JOB;

ETC Function MERGE ◎ 문법 MERGE INTO TABLE_NAME ALIAS USING (TABLE | VIEW | SUBQUERY) ALIAS ON (JOIN CONDITION) WHEN MATCHED THEN UPDATE SET COL1 = VAL1, COL2 = VAL2… WHEN NOT MATCHED THEN INSERT (COL1, COL2, ..) VALUES (VAL1, VAL2, ..); MERGE는 UPDATE와 INSERT를 결합한 문장으로 각 쓰임새는 다음과 같다. INTO 절 : 데이터가 UPDATE되거나 INSERT될 대상 테이블 USING 절 : 대상 테이블의 데이터와 비교한 후 UPDATE 또는 INSERT할 때 사용할 Data Source ON 절 : UPDATE나 INSERT를 하게 될 Condition으로 해당 조건을 만족하는 Row가 있으면 WHEN MATCHED THEN 이하를 실행하게 되고 없으면 WHEN NOT MATCHED THEN 이하를 실행하게 된다. WHEN MATCHED THEN 절 : ON 절의 조건이 TRUE인 ROW에 수행할 내용 WHEN NOT MATCHED THEN 절 : ON 절의 조건에 맞는 ROW가 없을 때 수행할 내용 MERGE는 ORACLE 9i부터 지원 됨.

ETC Function MERGE ◎ 예제 CREATE TABLE EMP_HISTORY( EMP NUMBER(4), UPDATE_DATE DATE, SAL NUMBER(7,2), SALARY NUMBER(7, 2) ); MERGE INTO EMP_HISTORY EH USING EMP E ON (E.EMPNO = EH.EMP) WHEN MATCHED THEN UPDATE SET EH.SALARY = E.SAL WHEN NOT MATCHED THEN INSERT VALUES (E.EMPNO, SYSDATE, E.SAL, E.SAL * 1.5);

ETC Function START WITH, CONNETC BY ◎ 특징 하나의 테이블에서 순환적(RECURSIVE)으로 참조하여 계층형 데이터를 나타내고자 할 때 사용한다. ◎ 문법 SELECT [LEVEL], Column(s), Expression, .. FROM Table WHERE Condition(s) START WITH Condition(s) CONNECT BY PRIOR Condition(s) [ORDER SIBLINGS BY Condition(s)]  9i부터 지원; LEVEL : 계층적 질의문에서 검색된 결과에 대해 계층별로 레벨 번호 표시 루트 노드는 1, 하위 레벨로 갈수록 1씩 증가 START WITH : 계층적인 출력형식을 표현하기 위한 최상위의 행 CONNECT BY PRIOR : 계층 관계의 데이터를 지정하는 컬럼 ◎ 출력 형태 Top – Down 방식 : 루트 노드부터 먼저 출력 Ex) CONNECT BY PRIOR col1 = col2  col1 – 자식 키, col2 – 부모 키 Bottom – Up 방식 : 단말 노드부터 먼저 출력 Ex) CONNECT BY PRIOR col1 = col2  col1 – 부모 키, col2 – 자식 키 PRIOR : 부모 노드를 가리키는 예약어 임. SIBLINGS : 같은 레벨 내에서 정렬(9i 이후).

ETC Function START WITH, CONNETC BY ◎ 예제 Top – Down 방식 SELECT LEVEL, EMPNO, ENAME, MGR, JOB FROM EMP START WITH JOB = 'PRESIDENT' CONNECT BY PRIOR EMPNO = MGR; Bottom – Up 방식 START WITH JOB = 'CLERK' CONNECT BY PRIOR MGR = EMPNO;

ETC Function START WITH, CONNETC BY ◎ 데이터가 많아질 경우 첫째로 풀리는 START WITH col1 = con1 컬럼에 Index가 생성되어 있지 않는다면 속도를 보장할 수 없음 둘째로 풀리는 CONNECT BY PRIOR col1 = col2 역시 PRIOR 쪽의 컬럼 값이 상수가 되기 때문에 col2 컬럼에 Index를 생성하여야 속도를 보장 받을 수 있음 계층 구조를 START WITH, CONNECT BY로 풀면 부분 범위처리가 불가능하고, DESC로 표현하기가 어려움

ETC Function ROLLUP, CUBE ◎ ROLLUP 주로 GROUP BY와 함께 사용되며 GROUPING 조건에 따라 각 그룹의 그룹핑 항목이 있으면 우측부터 하나씩 제외 하면서 그 결과를 반환한다. SELECT DECODE(GROUPING(DEPTNO), 1, 'ALL TOT', DEPTNO) DEPTNO, DECODE(GROUPING(JOB), 1, 'JOB TOT', JOB) JOB, COUNT(SAL) CNT, SUM(SAL) SUM_SAL FROM EMP GROUP BY ROLLUP(DEPTNO, JOB);

ETC Function ROLLUP, CUBE ◎ CUBE SELECT DECODE(GROUPING(DEPTNO), 1, 'ALL TOT', DEPTNO) DEPTNO, DECODE(GROUPING(JOB), 1, 'JOB TOT', JOB) JOB, COUNT(SAL) CNT, SUM(SAL) SUM_SAL FROM EMP GROUP BY CUBE(DEPTNO, JOB);

ETC Function Row를 Column으로 풀기 SELECT NVL(SUM(DECODE(TO_CHAR(E.HIREDATE, 'mm'), '01', COUNT(*))), 0) JANUARY, NVL(SUM(DECODE(TO_CHAR(E.HIREDATE, 'mm'), '02', COUNT(*))), 0) FEBRUARY, NVL(SUM(DECODE(TO_CHAR(E.HIREDATE, 'mm'), '03', COUNT(*))), 0) MARCH, NVL(SUM(DECODE(TO_CHAR(E.HIREDATE, 'mm'), '04', COUNT(*))), 0) APRIL, NVL(SUM(DECODE(TO_CHAR(E.HIREDATE, 'mm'), '05', COUNT(*))), 0) MAY, NVL(SUM(DECODE(TO_CHAR(E.HIREDATE, 'mm'), '06', COUNT(*))), 0) JUNE, NVL(SUM(DECODE(TO_CHAR(E.HIREDATE, 'mm'), '07', COUNT(*))), 0) JULY, NVL(SUM(DECODE(TO_CHAR(E.HIREDATE, 'mm'), '08', COUNT(*))), 0) AUGUST, NVL(SUM(DECODE(TO_CHAR(E.HIREDATE, 'mm'), '09', COUNT(*))), 0) SEPTEMBER, NVL(SUM(DECODE(TO_CHAR(E.HIREDATE, 'mm'), '10', COUNT(*))), 0) OCTOBER, NVL(SUM(DECODE(TO_CHAR(E.HIREDATE, 'mm'), '11', COUNT(*))), 0) NOVEMBER, NVL(SUM(DECODE(TO_CHAR(E.HIREDATE, 'mm'), '12', COUNT(*))), 0) DECEMBER FROM EMP E GROUP BY TO_CHAR(E.HIREDATE, 'mm');

ETC Function Column을 Row로 풀기 SELECT SUBDEPT.DEPTNO, SUBDEPT.DNAME, DECODE(DUMY.X, 0, TO_CHAR(SUBDEPT.DEPTNO), SUBDEPT.DNAME) TEMP FROM (SELECT DEPTNO, DNAME FROM DEPT) SUBDEPT, (SELECT 0 X FROM DUAL UNION ALL SELECT 1 X FROM DUAL) DUMY ORDER BY SUBDEPT.DEPTNO;

Appendix PFILE & SPFILE PFILE, SPFILE ◎ PFILE(Parameter File) 8i 이전까지 Text File 형태로 Database Initialization Parameter를 저장한다. 일반적으로 $ORACLE_HOME/dbs/init<SID>.ora에 저장된다. 인스턴스가 기동될 때 이 PFILE을 읽어서 데이터 베이스에 적용된다. 인스턴스 기동 중 ALTER SYSTEM이나 ALTER SESSION으로 Parameter를 변경할 수 있으나, 인스턴스가 재 기동될 때는 PFILE에 있는 값으로 다시 적용된다. 따라서 완전히 파라미터를 변경하고자 할 경우에는 PFILE을 수정해야 한다. ◎ SPFILE(Server Parameter File) 9i부터 도입된 방식으로 Binary File 형식으로 Database Initialization Parameter를 데이터베이스에서 관리하는 방식이다. 인스턴스가 기동될 때 PFILE이 아닌 SPFILE을 참고하여 데이터 베이스에 적용된다. - 오라클에서는 먼저 spfile<SID>.ora 파일이 존재하는지 찾아보고, 없을 경우에는 spfile.ora 파일을 찾는다. 두 개 파일이 모두 존재하지 않을 경우 init<SID>.ora 파일을 찾는다. 만약 init<SID>.ora 파일도 존재하지 않는다면 오라클은 시작되지 못한다(에러 발생). SPFILE의 장점은 RAC 구성 시 공유 디스크에 SPFILE을 설정해 두면 한번의 파라미터 변경으로 모든 인스턴스가 동일한 파라미터로 적용된다. RAC(Real Application Cluster) : 인스턴스의 노드 간 로드 밸런싱, 페일 오버 및 데이터베이스 확장성을 제공하는 유사 고가용성 구성이다.

Appendix PFILE & SPFILE SPFILE Oracle 9i부터는 데이터베이스의 Initial Parameter를 지정하는 init<SID>.ora 파일 이외에 Server Parameter File 이라고 불리우는 spfile<SID>.ora 파일이 추가 되었다. 이 SPFILE을 사용하게 되면 ALTER SYSTEM 명령어를 통해 데이터베이스가 운영 중에 Parameter를 수정할 수 있게 되면서 Parameter를 수정할 때마다 데이터베이스를 재시작시켜야 하는 필요를 줄여주고, Server Tuning에 중요한 역할을 할 수 있다. ◎ spfile<SID>.ora 파일을 직접 변경한 경우의 영향 SPFILE이 크지 않은 관계로 에디터 등으로 읽으면 마치 Text 파일처럼 Parameter가 보이는데 이 파일은 실제로는 Binary File로 직접 변경하면 안된다. 특히 이 파일의 헤더에는 Checksum과 기본적인 Meta-Data 정보 등을 포함하여 Manual하게 변경하게 되면 이 Checksum의 마지막 값이 맞지 않게 되면서, 이후 Startup 시 이 파일을 읽지 않고 init<SID>.ora 파일을 읽게 된다. 데이터베이스를 운영하면서 SPFILE에 계속 Parameter가 변경된 경우라면 이러한 문제로 Tuning 등을 통해 변경된 Parameter Value를 모두 잃게 될 수 있는 것이다. UNIX : $ORACLE_HOME/dbs Window NT : %ORACLE_HOME%/database

Appendix PFILE & SPFILE SPFILE ◎ 데이터베이스 OPEN 시 SPFILE을 읽었는지 확인하는 방법 조회하면 된다. Value값이 없을 경우 init<SID>.ora 파일을 사용. SQL> show parameter pfile NAME TYPE VALUE ----------------------------------------------------------------- spfile string %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA

Appendix PFILE & SPFILE SPFILE ◎ init<SID>.ora 파일을 spfile<SID>.ora 파일로 Migration하거나 그 반대의 방법 spfile<SID>.ora 파일을 사용하다가 그 파일의 내용을 init<SID>.ora 파일에 백업 차원에서 반영시켜 두거나, 혹은 spfile<SID>.ora 파일 대신 init<SID>.ora 파일을 사용하고자 하는 경우, 또는 그 반대로 init<SID>.ora 파일을 참조하여 spfile<SID>.ora 파일을 생성하고자 하는 경우 다음과 같이 작업하면 된다. SQL>create pfile='initLHKDB.ora' from spfile='spfileLHKDB.ora'; SQL>create spfile=‘spfileLHKDB.ora’ from pfile=‘initLHKDB.ora’; 위 문장에서 파일명 대신 절대 PATH로 지정할 수 있고, 파일명은 임의로 지정 후 나중에 사용 시 init<SID>.ora 파일이나 spfile<SID>.ora 파일 형태로 만들어 줄 수 있다. Default인 $ORACLE_HOME/database이고 SID가 붙는 형태이면 간단히 다음과 같이 지정하여도 된다. SQL>create pfile from spfile; SYSDBA 권한이 없으면 권한 부족 오류가 발생한다.

Appendix PFILE & SPFILE SPFILE ◎ ALTER SYSTEM으로 Parameter 변경 시 SCOPE에 대하여 spfile<SID>.ora 파일을 사용하게 되면 앞에서도 언급한 것과 같이 spfile<SID>.ora 파일을 직접 변경하는 대신 ALTER SYSTEM 명령으로 초기화 파라미터를 수정할 수 있다. 이때 ALTER SYSTEM 명령어 뒤에 SCOPE를 지정할 수 있는데 SCOPE로 지정 가능한 값은 MEMORY/SPFILE/BOTH 중 한가지를 지정한다. MEMORY(기본값) : 변경이 현재 상태에만 영향을 미치며 데이터베이스가 재시작되면 변경 이전 값으로 돌아간다. SPFILE : 변경 내용을 SPFILE에만 저장을 하고 현재 상태에는 영향을 미치지 않게 한다. Static Parameter의 경우 이 SCOPE만이 지정가능하다. 즉, SPFILE을 사용하더라도 Static Parameter에 대해서는 데이터베이스 운영중에 바로 변경하여 재시작없이 반영하는 것은 불가능하다. BOTH : 변경 내용을 현재 상태에도 반영을 하고, SPFILE에도 반영시켜, 이후 재시작시에도 영향을 미치도록 한다. 지정하는 방법은 다음과 같다. SQL> alter system set open_cursor=300; SQL> alter system set open_cursor=300 scope=spfile; SQL> alter system set open_cursor=300 scope=both; spfile<SID>.ora 파일을 참조하지 않고 init<SID>.ora 파일을 참조하여 OPEN된 경우 오류가 발생 - ORA-02095: specified initialization parameter cannot be modified

Appendix PFILE & SPFILE SPFILE ◎ V$PARAMETER와 V$SPPARAMETER에 대하여(Static Parameter 확인) 초기화 파라미터의 종류에는 데이터베이스가 운영 중에는 값을 변경하여 반영시킬 수 없고, 재시작 후에만 변경 된 값이 반영이 되는 파라미터가 있다. 이러한 파라미터를 Static Parameter라고 부른다. 이 Static Parameter의 경우에는 그래서 ALTER SYSTEM으로 변경하더라도 SCOPE=SPFILE로만 지정 가능한 것이다. 그럼 Static Parameter는 어떻게 확인할 수 있는가? SQL> select name, issys_modifiable from v$parameter; 여기에서 보면 issys_modifiable의 값이 다음 세 가지로 나타난다. FALSE : Static Parameter로 SCOPE=SPFILE로만 값을 변경 가능하다. IMMEDIATE : 값을 변경하면 현재 SESSION부터 바로 영향을 받게된다. DEFERRED : 변경된 값이 이후 접속되는 SESSION부터 영향을 준다. ALTER SYSTEM을 통해 파라미터를 변경하는 경우 변경된 값이 반영이 잘 되었는가를 확인하려면 다음과 같이 SHOW PARAMETER나 V$PARAMETER를 조회하고, 현재 반영은 안 되었더라도 SPFILE에 저장되었는지를 확인하려면 V$SPPARAMETER를 조회하면 된다. SQL> show parameter open_cursor; SQL> select value from v$parameter where name=‘open_cursor’; SQL> select value from v$spparameter where name=‘open_cursor’; 즉 SCOPE=SPFILE로 변경한 경우 V$SPPARAMETER에만 변경된 값이 나타나고, SHOW PARAMETER 또는 V$PARAMETER에서는 변경되기 이전 값이 조회된다.

Appendix PFILE & SPFILE SPFILE ◎ Oracle 9i 인스턴스 구동을 위한 SPFILE 및 PFILE Oracle 9i 이전 버전에서는 인스턴스는 텍스트 파일인 init<SID>.ora 파일을 이용하여 구동되었으며, 이 파일은 기본적으로 $ORACLE_HOME/dbs 디렉토리 아래 위치하였다. Oracle 9i 에서는 SPFILE이라는 것이 새로 추가되었는데, 데이터베이스 서버 내에 저장되는 바이너리 파일이다. 인스턴스에 적용하고자 하는 파라미터 값의 변동 사항이 인스턴스를 내리거나 구동시킬 때마다 계속해서 적용되게 할 수 있다. 9i 에서는 인스턴스에 적용시킬 파라미터를 SPFILE 또는 PFILE에 저장시킬 수 있다. 초기화 파일을 지정하지 않은 경우 SQL> startup 만약 spfile<SID>.ora 파일, spfile.ora 파일 또는 init<SID>.ora 파일이 없을 경우 오라클은 시작이 안된다.

Appendix PFILE & SPFILE SPFILE ◎ Oracle 9i 인스턴스 구동을 위한 SPFILE 및 PFILE init.ora 파일의 지정 인스턴스 구동 시에 init.ora 파일을 명시적으로 지정하는 방법은 이전 버전과 동일하다. 유의해야 할 점은 SPFILE은 PFILE과 같이 인스턴스 구동 시 동적으로 지정할 수 없다는 것이다. 만약 SPFILE을 지정할 경우 에러가 발생한다. SQL> startup pfile=C:\oracle\admin\LHKDB\pfile\init.ora ORACLE 인스턴스가 시작되었습니다. Total System Global Area 118255568 bytes Fixed Size 282576 bytes Variable Size 83886080 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes 데이터베이스가 마운트되었습니다. SQL> startup spfile=C:\oracle\admin\LHKDB\pfile\init.ora SP2-0714: 부적합한 STARTUP 옵션 조합입니다

Appendix PFILE & SPFILE SPFILE ◎ 사용하고자 하는 SPFILE의 지정 사용하고자 하는 SPFILE을 지정하고자 할 경우에는 init.ora 파일을 사용하여야 하며, init.ora 파일에는 SPFILE의 절대 경로만 지정되어 있어야 한다. 이 경우에는 SQL*PLUS에서 인스턴스 구동 시 사용된 SPFILE을 다음과 같이 확인해 볼 수 있다. SQL> startup pfile=C:\oracle\admin\LHKDB\pfile\init.ora ORACLE 인스턴스가 시작되었습니다. Total System Global Area 118255568 bytes Fixed Size 282576 bytes Variable Size 83886080 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes 데이터베이스가 마운트되었습니다. 데이터베이스가 열렸습니다. SQL> show parameter pfile NAME TYPE VALUE -------------------------------------------------------- spfile string C:\oracle\ora90\database\SPFILELHKDB.ORA

Appendix No Archivelog Mode & Archivelog Mode Archivelog Mode 설정 SQL> archive log list 데이터베이스 로그 모드 아카이브 모드가 아님 자동 아카이브 사용 아카이브 대상 c:\oracle\oradata\LHKDB 가장 오래된 온라인 로그 순서 19 현재 로그 순서 21 SQL> select log_mode from v$database; LOG_MODE ------------------------ NOARCHIVELOG

Appendix No Archivelog Mode & Archivelog Mode Archivelog Mode 설정 로그 스위치가 발생할 때마다 ARCn이 자동으로 로그 파일을 아카이브하도록 설정 SQL> alter system set log_archive_start=true scope=spfile; 시스템이 변경되었습니다. 아카이브 파일이 쓰여질 위치를 지정 - 10g에서는 이 옵션이 적용이 안됨. - 자동으로 자동 복구 디렉토리내에 Archive 파일이 생성 됨. SQL> alter system set log_archive_dest='c:\oracle\oradata\LHKDB' scope=spfile; 생성될 Archive 파일의 이름 규칙을 지정한다. SQL> alter system set log_archive_format='arch%s.log' scope=spfile;

Appendix No Archivelog Mode & Archivelog Mode Archivelog Mode 설정 데이터베이스 종료 SQL> shutdown abort ORACLE 인스턴스가 종료되었습니다. 데이터베이스를 MOUNT SQL> startup mount ORACLE 인스턴스가 시작되었습니다. Total System Global Area 118255568 bytes Fixed Size 282576 bytes Variable Size 83886080 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes 데이터베이스가 마운트되었습니다.

Appendix No Archivelog Mode & Archivelog Mode Archivelog Mode 설정 데이터베이스 모드 변경 SQL> alter database archivelog; alter database archivelog * 1행에 오류: ORA-00265: 인스턴스 복구가 요구됩니다. ARCHIVELOG 모드를 지정할 수 없습니다 훔.. 에러다. 일단 데이터베이스 복구를 시도해본다. SQL> recover database; 매체 복구가 완료되었습니다. 복구하라고 해서 했는데도 똑같은 에러다. 어쩌라고~~

Appendix No Archivelog Mode & Archivelog Mode Archivelog Mode 설정 데이터베이스 모드 변경 RECOVER를 해도 똑같은 에러가 나오는 것이 바로 shutdown abort 때문이다. 또는 로그 스위치를 강제로 시켜주면 된다. ※ 오라클 메시지 참고 00265, 00000, "instance recovery required, cannot set ARCHIVELOG mode"         // *Cause:  The database either crashed or was shutdown with the ABORT         //          option. Media recovery cannot be enabled because the online         //          logs may not be sufficient to recover the current datafiles.         // *Action: Open the database and then enter the SHUTDOWN command with the         //          NORMAL or IMMEDIATE option. 다시 STARTUP 한 후 shutdown normal 또는 immediate하면 모두 정상적으로 실행 될 것이다.

Appendix No Archivelog Mode & Archivelog Mode Archivelog Mode 설정 데이터베이스 모드 변경 데이터베이스를 오픈하여 정상적으로 다시 shutdown 처리해야 한다. SQL> alter database open; 데이타베이스가 변경되었습니다. SQL> shutdown immediate; 데이터베이스가 닫혔습니다. 데이터베이스가 마운트 해제되었습니다. ORACLE 인스턴스가 종료되었습니다. SQL> startup mount ORACLE 인스턴스가 시작되었습니다. Total System Global Area 118255568 bytes Fixed Size 282576 bytes Variable Size 83886080 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes 데이터베이스가 마운트되었습니다.

Appendix No Archivelog Mode & Archivelog Mode Archivelog Mode 설정 데이터베이스 모드 변경 SQL> alter database archivelog; 데이타베이스가 변경되었습니다. SQL> archive log list; 데이터베이스 로그 모드 아카이브 모드 자동 아카이브 사용 아카이브 대상 c:\oracle\oradata\LHKDB 가장 오래된 온라인 로그 순서 22 아카이브할 다음 로그 24 현재 로그 순서 24 드디어 Archivelog Mode로 설정 되었다.

Appendix No Archivelog Mode & Archivelog Mode Archivelog Mode 설정 데이터베이스 모드 변경 SQL> alter database open; 데이타베이스가 변경되었습니다. SQL> alter system switch logfile; 시스템이 변경되었습니다. SQL> alter system archive log stop; SQL> alter system archive log start;

Appendix No Archivelog Mode & Archivelog Mode No Archivelog Mode 설정 데이터베이스 종료 SQL> shutdown 데이터베이스가 닫혔습니다. 데이터베이스가 마운트 해제되었습니다. ORACLE 인스턴스가 종료되었습니다. 데이터베이스 마운스 SQL> startup mount ORACLE 인스턴스가 시작되었습니다. Total System Global Area 118255568 bytes Fixed Size 282576 bytes Variable Size 83886080 bytes Database Buffers 33554432 bytes Redo Buffers 532480 bytes 데이터베이스가 마운트되었습니다.

Appendix No Archivelog Mode & Archivelog Mode No Archivelog Mode 설정 데이터베이스 모드 변경 SQL> alter database noarchivelog; 데이타베이스가 변경되었습니다. SQL> archive log list; 데이터베이스 로그 모드 아카이브 모드가 아님 자동 아카이브 사용 아카이브 대상 c:\oracle\oradata\LHKDB 가장 오래된 온라인 로그 순서 23 현재 로그 순서 25 간단히 No Archivelog Mode로 변경되었다.

Appendix No Archivelog Mode & Archivelog Mode No Archivelog Mode 설정 데이터베이스 모드 변경 SQL> alter database open; 데이타베이스가 변경되었습니다. SQL> select log_mode from v$database; LOG_MODE ------------------------ NOARCHIVELOG

참고한 Site ◎ http://blog.naver.com/oriwolf/ ◎ http://cafe.naver.com/flyoracle.cafe ◎ http://blog.naver.com/jeany4u