본문 바로가기

2020/0335

[Oracle] SGA Shared Pool 공유풀 사용 원리 :: 마이자몽 Shared Pool Library Cache : parsing된 정보를 저장 Data Dictionary Cache : Data Dictionary의 정보를 저장 --> Hard Parse 작업을 빨리 해주기 위해 Result Cache : 실행된 결과를 저장 (11g new feature) --> 결과로 적은 내용을 반환해주는 값을 저장해두면 바로 읽을 수 있다. Shared Pool은 Libray Cache, Data Dictionary Cache, Server Result Cache 3가지 구성요소를 갖고 있습니다. 동일한 문장이 반복적으로 들어왔을 때 Parse 작업을 하지 않고 바로 사용할 수 있도록 공유의 목적으로 사용되고 있습니다. 이해를 돕기 위해 우리가 일반적으로 사용하는 SELECT문.. 2020. 3. 31.

[Oracle] 문장수준 읽기 일관성 Consistent Mode, Current Mode 차이 :: 마이자몽 Dirty Read Commit되지 않은 데이터를 다른 Transaction에서 읽을 수 있다면 어떤 현상이 발생할 수 있을까요? 최종 연봉 계산하는 예시로 발생할 수 있는 현상을 알아 보겠습니다. 최종 연봉은 현재 연봉 + 퇴직금 + 인센티브로 최종 결정되는 것으로 가정해보겠습니다. 만약 Commit되지 않은 데이터를 다른 Transaction에서 읽을 수 있다면, 데이터를 읽는 시점에 따라 다른 결과 값을 읽습니다. 퇴직금까지만 합산했을 때 데이터를 읽으면 3900이되고 인센티브까지 합산했을 때 4200의 값을 읽습니다. 즉, 일관성 없이 데이터를 읽기 때문에 연산 과정에서 최종값이 아닌 중간값을 받아오는 문제가 발생할 수 있습니다. 이렇게 Commit되지 않은 데이터를 다른 Transaction에서.. 2020. 3. 30.

[Oracle] PIVOT, UNPIVOT 함수 사용 행열 전환 :: 마이자몽 행열 전환 ORACLE SCOTT 계정 EMP 테이블에서 2개의 칼럼을 이용하여 GROUP BY 결과를 확인해봤습니다. 하고 싶은 작업은 GROUPING한 칼럼을 가로 세로축으로 두고 데이터를 출력하려고 합니다. CASE 표현식이나 DECODE 함수를 이용해서 결과를 출력할 수 있습니다. [Oracle] DECODE 함수 사용 쿼리 결과 가로로 보기 :: 마이자몽 DECODE 함수 DECODE 함수를 사용하여 쿼리내의 조건문 처럼 사용할 수 있습니다. 첫번째 인자로 확인할 대상을 넣어주고 2번째 인자에 확인할 대상의 예측 값이 있으면 3번째 인자를 없으면 4번째 인자의 값을 반.. myjamong.tistory.com DECODE 함수를 사용하면 같은 함수를 계속 사용하면서 중복되는 작업을 해야합니다. .. 2020. 3. 30.

[Oracle] ROW_NUMBER, RANK, DENSE_RANK 함수 순위 매기기 :: 마이자몽 사원들의 급여를 많이 받는 순서대로 순위를 출력하시오. ORACLE SCOTT 계정의 EMPLOYEES 테이블을 이용해서 출력한 결과입니다. 순서를 매기는 방법에는 여러가지 방법이 있습니다. 분석함수를 사용하지 않는다면 아래처럼 구할것 입니다. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT EMPNO ,ENAME ,SAL ,DEPTNO ,ROWNUM AS RNK FROM ( SELECT * FROM EMP ORDER BY SAL DESC ); SELECT 절은 ORDER BY 이전에 실행되기 때문에 SUBQUERY를 이용해서 순위를 매길수 있습니다. 전체 순위중 일부를 출력하기 위해서는 TOP-N쿼리나 ROW LIMIT CLAUSE를 사용해서 출력이 가능합니다. 자세한 내용은 아.. 2020. 3. 29.

[Oracle] OVER ... PARTITION BY 분석절 분석함수 ANALYTIC FUNCTION :: 마이자몽 사원 정보를 입력하는데, 전체 급여 평균값과 함께 출력하시오. ORACLE SCOTT 계정 EMP 테이블로 출력된 결과입니다. 위 문제를 풀기 위해 어떻게 쿼리를 작성하실건가요? 1 2 3 4 5 6 7 8 SELECT DEPTNO ,EMPNO ,ENAME ,JOB ,SAL ,ROUND((SELECT AVG(SAL) FROM EMP)) AS AVG FROM EMP; SUBQUERY를 이용할 수도 있지만... ANALYTIC FUNCTION 분석함수를 사용해서 좀 더 편한 방법으로 해결할 수 있습니다. 1 2 3 4 5 6 7 8 SELECT DEPTNO ,EMPNO ,ENAME ,JOB ,SAL ,ROUND(AVG(SAL) OVER()) AS AVG FROM EMP; 분석함수 OVER 절을 사용해서 문제.. 2020. 3. 28.

[Oracle] Multiple INSERT ALL, FIRST 다중 행 삽입 차이 :: 마이자몽 전체 INSERT & 조건부 INSERT 다중행 INSERT에서 ALL과 FIRST로 구분하기 전에 전체 INSERT와 조건부 INSERT로 구분할 수 있습니다. 말그대로 전체는 모든 행을 삽입하는 것이고, 조건부 INSERT는 조건에 따라 INSERT할 수 있는 기능입니다. Multiple Insert를 실습해보기 위해 Oracle HR 계정을 사용하겠습니다. 전체 INSERT ALL 임시 사원 테이블 생성 1 2 3 4 5 6 7 8 9 10 11 --현재 근무하는 사원 테이블 CREATE TABLE CURR_EMP AS SELECT EMPLOYEE_ID ,FIRST_NAME ,HIRE_DATE ,JOB_ID ,SALARY FROM EMPLOYEES WHERE 1=0; Multiple Insert 작.. 2020. 3. 27.

[Oracle] ROLLUP 고급 사용법 GROUPING 함수 :: 마이자몽 GROUP BY 1 2 3 4 5 6 SELECT DEPTNO ,JOB ,SUM(SAL) FROM EMP GROUP BY DEPTNO, JOB; ORACLE SCOTT 계정의 EMP 테이블을 사용해서 실습을 진행했습니다. 일반적으로 사용하는 GROUP BY 문입니다. SUM 함수를 이용해서 각 부서별, 직업별 급여의 합을 구했습니다. ROLLUP 1 2 3 4 5 6 SELECT DEPTNO ,JOB ,SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO, JOB); 위의 GROUP BY절에 ROLLUP을 추가했습니다. 결과를 부서별, 직업별 뿐만아니라 전체 급여의 합과 부서별 급여의 합을 함께 출력합니다. 그런데 여기서 의문이 드는게 있습니다. 전체 급여로도 합을 계산해주고 부서별로.. 2020. 3. 26.

[Oracle] Transaction Dead Lock 발생 실습, 이론 말고 직접 만들어 보자 :: 마이자몽 Transaction Dead Lock 교착상태 잘못된 자원 관리로 인하여 둘 이상의 프로세스가 함께 멈추어 버리는 현상.......... 도저히 무슨 말인지 모르겠습니다. 간단하게 설명하자면, Transaction이 서로 꼬여서 작업이 멈춰버리는 것 입니다. Dead Lock 교착상태에 대한 정보를 검색해보면 위와 비슷한 내용의 정의를 내립니다. 또 교착상태로 빠질 수 있는 4가지조건이라고 해서 상호 배제(Mutual Exclusion), 점유와 대기 Hold and Wait, 비선점(No Preemption), 환형대기(Circular Wait) 이런 이론적인 내용 밖에 안나옵니다. 중요한 내용이고 꼭 알아야하는 내용은 맞지만... 글로만 봐서 대충 무슨 느낌인지는 알겠는데, 정확히 모르겠고... 이.. 2020. 3. 26.

[Oracle] 오라클 INDEX 사용하는 이유 원리 장점 단점 :: 마이자몽 INDEX를 사용하는 가장 큰 이유는 속도의 향상을 위해서 사용됩니다. INDEX 구조를 만들어주면 FULL SCAN에서 모든 테이블 데이터를 읽어오는 방법과는 다르게 ROOT - BRANCH - LEAF - DATA BLOCK총 4번의 IO를 통해서 접근이 가능합니다. 데이터가 100건이든 100만건이든 속도차이는 많이 안난다는 장점이 있습니다. INDEX란? 모든 테이블에는 ROWID라는 칼럼이 있습니다. ROWID = FILE 번호 + BLOCK 번호 + ROW번호로 구성되어 있습니다. "7번파일에 132번 블록에 3번째" 이런 식으로 해당 데이터의 주소라고 볼 수 있습니다. INDEX는 이런 ROWID를 통해 DATA BLOCK에 접근 합니다. INDEX는 데이터를 빠르게 찾기 위해 오름차순으로 .. 2020. 3. 25.

[Oracle] Transaction 왜 사용하는 걸까요? :: 마이자몽 은행 송금처리 A계좌에서 B계좌로 $5,000를 정상적으로 송금하는데 처리되는 과정입니다. A계좌에서 B계좌로 송금 요청을 하면 1. A계좌에서 $5,000를 출금합니다. 2. B계좌에 $5,000를 입급합니다. 그럼 결과로 A계좌에는 $5,000 B계좌에는 $10,000가 되어야합니다. 위 이미지는 송금 처리 과정에서 오류가 발생한 경우 입니다. 1. A계좌에서 $5,000를 출급합니다. 2. 에러 발생 송금처리 과정에서 오류가 발생하여 A계좌에서 $5,000를 출금하고 B계좌로는 돈이 입금되지 않아 $5,000가 사라집니다. 실제 은행에서 위와 같은 문제가 발생한다면 여러 방면으로 엄청난 피해가 발생할 것 입니다. 예상하지 못한 상황에서 오류가 발생하여 하여 데이터의 부정합이 발생하는 경우, 다시 원.. 2020. 3. 25.

[ORACLE] CTAS CREATE TABLE AS SELECT 사용 :: 마이자몽 CTAS CREATE TABLE AS SELECT ... SUBQUERY SELECT문을 이용해서 새로운 테이블을 생성하는 방식입니다. CREATE TABLE AS SELECT의 앞 글자를 따서 CTAS라고도 합니다. CTAS문은 주로 OLAP처럼 분석작업이 많은 환경에서 주로 사용하게 됩니다. 필요한 데이터를 원본데이터의 카피로 만들어 속도도 향상시킬수 있고 원데이터를 훼손시키지 않고 작업을 할 수 있습니다. CTAS 생성 Oracle HR계정 EMPLOYEES 테이블에서 90번부서에서 근무하는 사원들만 따로 모은 테이블을 생성하겠습니다. 1 2 3 4 5 6 7 8 9 CREATE TABLE DEPT_90 AS SELECT EMPLOYEE_ID ,FIRST_NAME ,SALARY FROM EMPLOY.. 2020. 3. 24.

[Oracle] ORA-00955 name is already used by an existing object 해결 :: 마이자몽 ORA-00955 ORA-00955 : 기존의 객체가 이름을 사용하고 있습니다. "name is already used by an existing object" 테이블, 뷰, 시퀀스 등 오라클 객체를 생성하려고하는데 위와 같은 에러가 발생합니다. 주로 테이블을 생성하려는데 이런 에러가 발생해서 테이블을 DROP시키려고 하는데 테이블은 존재하지 않는다고 하고... 다시 같은 이름으로 테이블을 생성하려는데 이미 사용하고 있는 이름이라고하고... 도데체 뭐가 문제인지.... 오라클에서는 객체의 타입이 다르더라도 같은 명칭으로 생성할 수 없습니다. 그래서 위와 같은 상황이 벌어졌을 때는 다른 객체 타입으로 해당 명칭이 생성되어 있을 겁니다. 문제를 해결하기 위해 Data Dictionary의 해당 유저가 어떤 .. 2020. 3. 24.

[Oracle] Synonym 동의어 왜사용할까요? :: 마이자몽 Synonym 이란? Oracle에는 Synonym이란 객체가 있습니다. 객체에 대해서 영속적으로 별명을 지어주는 것. 테이블 ALIAS와는 다르게 임시적인 별명이 아닌 영속적으로 별명을 부여하는 것이 Synonym입니다. Oracle Database를 사용해보면서 한번도 Synonym을 만들어보지 않아서 사용을 한번도 안했다! 라고 생각하실수도 있는데... 우리는 이미 Synonym을 사용하고 있습니다. 1 SELECT SYSDATE FROM DUAL; DUAL이라는 임시 테이블은 SYNONYM입니다. DUAL테이블은 모든 계정에서 스키마 없이 DUAL이라는 명칭으로 사용되고 있습니다. DUAL도 임시적으로 사용되는 테이블인데 왜 스키마 없이 DUAL이라는 명칭으로 사용이 가능했을까요? DUAL 테이블.. 2020. 3. 24.

[Oracle] 오라클 DELETE, TRUNCATE 차이(HIGH WATER MARK) :: 마이자몽 오라클에서는 데이터를 삭제하는 명령어로 DELETE, TRUNCATE, DROP 이 있습니다. DROP의 경우 테이블의 데이터까지 전부삭제를 해주고 DELETE와 TRUNCATE은 테이블을 남기고 데이터만 삭제합니다. DELETE와 TRUNCATE 차이 DML과 DDL DELETE와 TRUNCATE의 가장 큰 차이는 DML과 DDL이라는 것입니다. DDL의 경우 하나의 명령이 트랜잭션의 시작과 끝이기 때문에 바로 반영이 되어버립니다. 그래서 TRUNCATE은 ROLLBACK이 불가능한 반면 DELETE은 DML 명령어이므로 TRANSACTION 단위로 ROLLBACK과 COMMIT이 가능합니다. 저장공간 반납과 유지 EMP 테이블에서 192번사원을 찾는다고 가정해봅니다. SQL 명령 순서에 의해서 FRO.. 2020. 3. 23.

[Oracle] 오라클 WITH, RECURSIVE WITH 재귀 쿼리 사용 :: 마이자몽 나라별 급여 평균의 평균보다 많이 급여를 주고 있는 나라와 나라의 평균 급여를 구하시오. (나라명과 나라의 평균 급여만 출력) 위의 문제를 풀기 위해 어떻게 해야할까요? 나라별 평균 급여를 조인문으로 이용해서 구하는 쿼리를 쿼리A라고 하겠습니다. 평균의 평균값을 구하기 위해서는 쿼리A를 SUBQUERY로 사용해서 평균을 구할 수 있습니다. 평균의 평균값보다 높은 결과를 출력하기 위해서 쿼리A 결과의 PROJECTION이 필요합니다. 위 과정을 생각해봤을때 쿼리A는 전체 쿼리에서 2번 사용해야합니다. 2번 사용하게되면 퍼포먼스도 낮아지고 쿼리의 가독성도 떨어지게 됩니다. 이러한 문제를 해결하기 위해서 WITH절을 사용해서 쿼리문을 재사용할 수 있습니다. 실습은 Oracle HR 계정으로 진행했습니다. WI.. 2020. 3. 23.

[Oracle] 오라클 Sequence auto_increment 사용 및 정리 :: 마이자몽 새로운 사원 INSERT 새로운 사원이 입사하여 Database에 Insert하려고합니다. 순차적인 번호로 사원ID를 부여해줍니다. 이때 1006이라는 사원ID를 어떻게 받아와서 Insert 시킬까요? MAX(사원ID) + 1 ? MAX 함수를 이용해서 사원ID의 최대값을 받아서 1을 더해준 값을 Insert할 수 있습니다. 하지만 성능의 문제가 생깁니다. 1000정도의 숫자는 무리가 없지만 데이터의 양이 늘어날수록 부하는 커질 것 입니다. INDEX 사용 ? INDEX를 사용할수도 있습니다. index의 맨 마지막 블럭을 찾도록 하면 Root - Branch - Leaf 총 3단계에 걸쳐 마지막 번호를 받아올 수 있습니다. 퍼포먼스를 높일 수 있는 좋은 방법이지만, Sequence를 사용하면 더 빠르게.. 2020. 3. 23.

[Oracle] 오라클 뷰(View) 사용법 및 정리 :: 마이자몽 View 란? View는 저장된 SELECT 문이다. 오라클에는 테이블말고도 데이터를 읽어 올 수 있는 오브젝트가 있습니다. FROM절에서 사용할 수 있는 것은 테이블만이 아니라는 것입니다. 대표적으로 View라는 오라클 오브젝트를 이용해서 데이터를 조회할 수 있고 실제로 현업에서도 많이 사용되는 오브젝트입니다. 그럼 View는 무엇이고 왜 사용하는 것 일까요? "View는 저장된 SELECT 문"이라고 표현했습니다. 사전적 의미로 "데이터의 논리적 부분집합"이라고 하는데요, 실질적으로 View를 SELECT문을 이용해서 생성하기 때문에 위와 같이 표현했습니다. 그럼 데이터의 논리적 부분집합이라는게 무엇일까요? 데이터의 논리적 부분집합? 데이터의 논리적 부분집합이란 것은 물리적으로 존재하지 않는 테이블을.. 2020. 3. 22.

[Oracle] Correlated Subquery 상호연관쿼리 :: 마이자몽 Correlated Subquery Correlated Subquery는 내부 Subquery에서 외부테이블의 값을 참조할 때 사용됩니다. Subquery와는 다르게 Inner Query 부터 Outer Query 순서대로 실행되는 것이 아니라 Outer Query에서 읽어온 행을 갖고 Inner쿼리를 실행하는 것을 반복하여 결과를 출력해줍니다. Outer Query와 Inner Query에서 같은 테이블을 참조한다면 Outer Query의 테이블에 Alias를 사용해서 구분해줍니다. 예시문제 Oracle HR 계정 Employees 테이블을 사용해서 소속된 부서의 평균 급여보다 많은 급여를 받는 사원을 출력하시오. 1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT FIRST_NAME ,.. 2020. 3. 21.

[Oracle] DECODE 함수 사용 쿼리 결과 가로로 보기 :: 마이자몽 DECODE 함수 DECODE 함수를 사용하여 쿼리내의 조건문 처럼 사용할 수 있습니다. 첫번째 인자로 확인할 대상을 넣어주고 2번째 인자에 확인할 대상의 예측 값이 있으면 3번째 인자를 없으면 4번째 인자의 값을 반환해줍니다. CASE 표현식과는 다르게 범위식의 조건을 넣을수 없고 2번째 인자가 있는지 없는지 확인합니다. Oracle Scott 계정으로 DECODE 함수 사용 예시를 알아보겠습니다. 세로결과를 가로로 1 2 3 4 5 6 SELECT DEPARTMENT_ID ,SUM(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID 2020. 3. 20.

[Oracle] NON-EQUI JOIN 비등가 조인 정리 :: 마이자몽 NON-EQUI JOIN 두개의 테이블을 특정한 키를 기준으로 합쳐서 데이터를 표현하는 것을 JOIN이라고 합니다. 사원 테이블에 부서 번호가 있고 부서 테이블에 부서명이 있어서 사원의 이름과 부서명을 확인하기 위해서 사원테이블의 부서번호와 부서 테이블의 부서번호로 JOIN 시켜 데이터를 표현할 수 있습니다. 이렇게 특정 칼럼은 같다는 형식으로 조인을 시키는 것은 EQUI JOIN이라고 합니다. 그럼 NON-EQUI JOIN은 뭘까요? 상호평가 등급, 성적 등급과 같이 특정 범위와 등급을 지정하고 해당 범위에 들었을 때 등급으로 데이터를 표현하기 위해 사용되는 것이 NON-EQUI JOIN입니다. NON-EQUI JOIN은 EQUI JOIN과 다르게 특정 칼럼을 범위로 JOIN 시킵니다. Oracle S.. 2020. 3. 19.

이전 1 2 다음

티스토리툴바