성능을 고려한 SQL작성비법-7.pdf
2.14MB

안녕하세요.
멀티캠퍼스 과정운영자 김현아입니다.
차주 [Live] 성능을 고려한 SQL 작성비법 4.22(월)~4.23(화) 실제 비대면 회의 번호 안내 드립니다.
구분

 

하석재 강사님

sjha72@gmail.com


control-o 쓰기 enter control-X
oracle 계정
ora_sql_test

강의진행 순서

1.RDBMS의 기초

2.도커기반 오라클 설치 및 예제설치

3.오라클 옵티마이저 이해

4.인덱스

5.조인

6.서브쿼리

7.트랜잭션

8.GROUP BY/ROLLUP/CUBE

9.분석함수

10.페이징 및 SQL 개발 가이드

 

 


상세내용
강의정보
URL : https://zoom.us
강의 회의번호 : 840 7270 6872 / pw : 1234
※ ZOOM 계정이 없는 경우 , 사전 가입 부탁드립니다

1) 로그인 후 상단 참여하기 클릭 (ZOOM 계정이 없으시면, 사전 가입 부탁드립니다.)
2) 회의참가 : 840 7270 6872 입력 후 참가 클릭
3) Zoom Meetings 열기 클릭
4) 회의 암호 입력 : 1234
5) 본인 성함으로 미팅 참여
감사합니다.
김현아 드림

• TEL | 02-3429-5177 (평일 09:00~18:00)
• E-mail| ha8.kim@partner.multicampus.com

02-3429-5740
하석재강사

출석 하루 세번
오전 점심 오후

sjha72@gmail.com

하석재 강사님 2HCUBE

성능을 고려한 SQL 작성비법

마이그레이션 : DB 이사 오라클 -> 다른 db로 이사중인 추세임
cpu 코어수로 가격계산

티베로, 코스트디베로

클로드


SQL 옵티마이저의 역할
1.쿼리 최적화


2.비용기반 


RBO Rule-Based Optimization(RBO)
-
 
CBO Cost-Based Optimization(CBO) [최근에는 CBO 기반으로 가고있음]


SQL 옵티마이저의 역할

1. 쿼리 최적화

2. 비용 기반 최적화

3. 통계 정보 활용

4. 실행 계획 캐싱

5. 동적 최적화


강사의 블로그
https://blog.naver.com/ryu1hwan/221704174010


RDB : 관계형 데이터베이스
- 70년대 초반 나왔지만 80년대에 주류가 되었음


테이블기반(Table based)으로 데이터 저장하는 시스템



RDB : 테이블 기반의 데이터베이스

데이터를 CRUD 하는 시스템


DBMS는 다양한 종류의 인덱스지원
- B-tree/Hash/R-tree/Full-text index/..

- R-tree 거리기반 
- 가장 기본되는 인덱스는 B-tree (정확하게는 b+트리)


B-tree/ Hash

- 데이터의 중복을 정규화(Normalization)를 통해 중복성제거
- 보통 1,2,3, BC(Boyce-codd) 정규화(Normal Form)까지 적용한 것을 정규화(Normal)

정규화 [쪼개기] 보통테이블이 큰 테이블에서 여러개의 작은 테이블로 나눠짐
제 1정규형
-
제 2정규형
-
제 3정규형
-
제 4정규형
-
제 5정규형
-

인덱스(B Tree )  B+
- 보통 B+트리를 말함

리프

인덱스(B treee)
- 보통 B+트리를 말함(인덱스와 데이터 저장소 분리)
- 인덱스파일만 별도로 구성하고 데이터 파일은 참조형태로
- 바이너리 검색(Binary Search)의 변형


인덱스(B treee)
- 약점 
- 연속적인 검색(예.Range search)에 약점
- 연결 리스트(Linked list)

-    Cartesian Product(cross join)
-    두릴레이션을그냥곱해서만들어진조인의형태 
-    Equi-Join(Theta Join)
-    Cartesian Product를통해만들어진릴레이션중실제있는것만필터링한조인 
-    Non-equi join
-    Equi-join의여집합 
-    Natural Join
-    알아서조인(컬럼을지정하지않음) 
-    Self Join
-    동일한테이블을기준으로조인



데이터의 추가패턴에 따라 트리의 모양이 skew 발생


self balanced tree 

red back tree

인덱스를사용하는경우에도느려지는경우는?
-    저장되는데이터가많아지면 B-tree의 Depth(높이)가 
늘어나게되서검색속도가느려짐
-    해결책 -> 파티셔닝 / 샤딩


외래키에 대해서는 인덱스가 안달림


외래키
- 참조무결성 제약조건(RI : Referential Integrity)
- 외래키로 연결되어 있는 테이블의 경우에는 외래키로 연결 테이블을 검색하면 데이터가 존재해야 함

- 삽입/삭제에 순서가 있어야 함
 - 해당 순서를 어기면 에러발생
 - 원 레코드를 삭제하려면 참조하는 레코드를 먼저 삭제해야 가능
 - 외래키를 가지는 레코를 추가하려면 연결테이블에 레코드를 먼저 추가해야함


- (Inner) Join
- 드리이빙(기준) 테이블/ 드리븐(참조) 테이블의 널 값을 허용하지 않음
- A(기준테이블) Join B(드리븐테이블)

- Outer Join
-
조인(JOIN)
E-R(개념-관계) 모델링


스키마 생성(p.18)

select * from tab; 으로 테이블/뷰 리스트 확인
샘플 데이터 생성
set linesize 1000;
set pagesize 200;
set timing on;
column ITM_ID format a10
column ITM_NM format a20
column ITM_TP format a10
column ITM_PRC format a10
select * from M_ITM; 로 샘플테이블 레코드 확인


기본적으로 모든 관계는 M:N(다대다) 관계임
Entity - Relationship - Entity 3개의 테이블이 필요함

엔티티의 개수를 그대로 논리/물리 모델링화 하면 테이블의 개수가 많아짐
M:N -> 1:N / N:1 -> 1:1의 순서로 축소
1:N의 관계는 테이블 2개로 축소가능(외래키 연결)
1:1 관계는 테이블의 컬럼추가로 해결가능

논리모델링의 엔티티/릴레이션쉽/컬럼명은 한글명 지정
물리모델링은 영어로 모두 변환(Grocery)


GraphDB

CBO (Cost-based Optimizer)
- IO회수, CPU Time, 메모리 사용량
- 테이블의 통계 정보기간
 - 통계값이 잘못되면 엉뚱한 결과가 나오기도 함
 

옵티마이저(오라클 vs MySQL)

cf. 자동/수동 미션-자동차




explain plan for select * from T_ORD where ORD_SEQ=4;



select * from table(DBMS_XPLAN.DISPLAY());

루프블록

브랜치블록

리프블록 

- Parent(부모) / Child(자식) / Sibling(형제/자매)
- 일반적으로
- 자식이 부모보다 먼저 실행
- 동일한 레벨(Sibling)에서는 순서대로(형부터) 실행

- index unique scan
- tble access by index rowid

- GATHER_PLAN_STATISTIC(SQL힌트) 또는 세션에 Trace
- /*+ GATHER_PLAN_STATISTICS */
- 운영하는 상황에서는 안 들어가도록 주의 필요함

- DBMS_XPLAN.DISPLAY_CURSOR
- 다음 뷰에 SELECT 권한 필요
- V_$SQL
- V_$SQL_PLAN_STATISTICS_ALL
- V_$SQL_PLAN
- V_$SESSION



SELECT * /*+ GATHER_PLAN_STATISTICS */ FROM T_ORD T1,M_CUS T2
WHERE T1.CUS_ID = T2.CUS_ID
AND T1.ORD_DT >= TO_DATE('20170101','YYYYMMDD') AND T1.ORD_DT < TO_DATE('20170201','YYYYMMDD') AND T2.CUS_GD = 'A';


- ID: 실행계획의 오퍼레이션 ID
- Operation: 해당 단계에 수행한 작업 내용
- Name: 해당 단계에 작업을 수행한 대상 오브젝트(테이블 또는 인덱스)
- Starts:해단 단계를 수행한 회수
- E-Rows:해당 단계의 예상 데이터 건수
- A-Rows:해당 단계의 실제 데이터 건수
- A-Time:해당 단계까지 수행된 실제 시간(누적)
- 복잡한 실행계획이나 병렬 쿼리에서는 부정확한 경우 있슴
- Buffers:해당 단계까지 메모리 버퍼에서 읽은 블록 수(논리적 IO회수, 누적)
- Reads:해당 단계까지 디스크에서 읽은 블록 수(물리적 IO외수, 누적)  -적을수록 좋음
- 0Mem, 1Mem, Used-Mem:SQL처리를 위해 사용한 메모리 수치

select * from M_CUS join T_ORD on M_CUS.CUS_ID = T_ORD.CUS_ID where M_CUS.CUS_ID='CUS_0001' and rownum <=1;


인덱스 정책
- 원칙적으로 PK(기본키)로 검색
- 쿼리 내용에 PK조건을 명시
- 인덱스가 안달린 컬럼을 추가해야 하는 경우
- RDBMS에서는 
- 한 번(인덱스는 안다는 게 맞음)
- 여러 번(인덱스를 달아야 함)
- 어느 컬럼을 선택? 인덱스의 depth(높이)의 작은
- 인덱스를 여러개를 유지?


- 대부분의 NoSQL에서는 
- PK 인덱스만 지원



조인(JOIN)

(Inner)

보안에서의 해시

- 해쉬의 충돌확률을 낮춤(거의 없게 만듬)
- 보안에서는 일종의 지문 개념으로 사용
  - 입력이 다르면 해시값이 다르다
  
 
-    NLJ(Nested Loop Join)
-    가장기본적인조인알고리즘
-    두테이블을중첩루프로순차스캔하여조인조건을만족하는행을찾음 
-    조인조건에인덱스가있는경우에효과적
-    대용량데이터처리에는부적합 
-    SMJ(Sort-Merge Join) aka. MERGE JOIN
-    조인대상테이블이미리정렬되어있다는전제하에동작 
-    정렬된데이터를효율적으로탐색하여조인수행
-    메모리사용량이적어대용량데이터에적합 
-    사전정렬비용이들어부담이될수있음
-    HJ(Hash Join)
-    작은테이블을메모리의해시테이블로구성하여빠른조인처리 
-    조인조건에인덱스가없어도효과적
-    해시테이블구축을위해메모리사용량이많아지는단점이있음


엑사데이터
- 오라클 엑사데이터
- 램이 16기가 인메모리 처리



-    성능
-    해시조인 > 소트-머지조인 > 네스티드루프조인 
-    메모리사용량
-    네스티드루프조인 < 소트-머지조인 < 해시조인 
-    인덱스의존성
-    네스티드루프조인 > 소트-머지조인 = 해시조인 
-    대용량데이터처리적합성
-    소트-머지조인 > 해시조인 > 네스티드루프조인
-    조인대상테이블의크기, 인덱스유무, 메모리용량등을고려하여최적의조인 
알고리즘을선택


select /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_NL (T2) */
T1.RGN_ID, T1.CUS_ID, T1.CUS_NM
,T2.ORD_DT, T2.ORD_ST, T2.ORD_AMT
FROM M_CUS T1, T_ORD T2
WHERE T1.CUS_ID = T2.CUS_ID;




1xxbqrdhdr25f

cmcr9vzc47nmx

select T1.SQL_ID, T1.CHILD_NUMBER, T1.SQL_TEXT from V$SQL T1 where T1.SQL_TEXT like '%GATHER_PLAN_STATISTICS%' order by T1.LAST_ACTIVE_TIME desc;


fmf80mzwg7b70


select * from TABLE(DBMS_XPLAN.DISPLAY('1xxbqrdhdr25f'));


select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1xxbqrdhdr25f',0,'ALLSTATS LAST'));

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('14hmhd1j1n2zu',0,'ALLSTATS LAST'));
SELECT /*+ GATHER_PLAN_STATISTICS LEADING(T1) USE_NL(T2) */
      T1.RGN_ID, T1.CUS_ID, T1.CUS_NM
     ,T2.ORD_DT
 ,T2.ORD_ST
 ,T2.ORD_AMT
     FROM M_CUS T1,T_ORD T2
     WHERE T1.CUS_ID = T2.CUS_ID;

select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('fmf80mzwg7b70',0,'ALLSTATS LAST'));

CREATE INDEX SAMPLE_INDEX ON T_ORD(CUS_ID);

create index sample_index on T_ORD(CUS_ID);

- 실제 실행계획확인
select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR('cmcr9vzc47nmx',0,'ALLSTATS LAST'));


해시조인
-    HJ(Hash Join)
-    작은테이블을메모리의해시테이블로구성하여빠른조인처리 
-    조인조건에인덱스가없어도효과적
-    해시테이블구축을위해메모리사용량이많아지는단점이있음


조인과 성능(p.198~230)
- Driven 테이블 조인 컬럼이 인덱스가 꼭필요하다
- /* LEADING(1) USE_NL INDEX(T2_X_T_ORD_JOIN_T)*/

- A join B = B join A 결과는 동일하지만 성능은 다르다.
- 조인회수를 줄이자
- Driving 테이블이 레코드가 작을수록 유리
- like 조건을 사용하는 경우

드라이빙 드리븐 


ㅁ NL 조인
Like 검색
- 부분매칭
- exact (=) 검색 매칭의 경우 B+트리와 같은 인덱스를 이용해서 찾아내면 검색 종료
 - 전체 데이터의 일부 검색
 
- 조건에 맞는 레코드를 찾아도 검색이 종료되지 않고 계속 전체검색을 진행
- 전체 데이터를 조건에 맞는지 확인해야 함

- 여러 번 이상의 조인
- 다대다 관계는 무조건 2번의 조인 필요
- DBA 관계를 없애라



ㅁ Merge 조인
- 대량의 데이터
- /* FULL(T2) */ 풀스캔



ㅁ Hash 조인
- 충돌문제 개수제한 존재(주로 메모리 대상)
- 해쉬검색은 한번에 검색가능 속도가 빠르다


RDB는 인덱스 잘알고 조인잘알면 80%는 파악함

보통은 b+트리를 찾다가 찾아내면 검색종료

서브쿼리

대한민국 1년 예산 약 656조 6000억 원이며

1. COUNTRY 테이블 -> 쿼리의 실행 결과 code 'KOR' -> city 테이블

 

 

 

마이그레이션 : 오라클에서 다른 db로 가는 추세임

MySQL의 족쇄

- 메모리 엔진(굉장히 빨라야 함, 읽기는 빠르나 쓰기는 느리고, 락도 테이블락)

엑사 데이터

- 인메모리 db가 나오는 추세

 

-서브 쿼리 최적화

 - in/exists 구현이 느리고 구림

 

mysql 서브쿼리 in exists

 

OLTP(실시간/집계)

 - GROUP BY / SUM() / COUNT()

 

OLAP(분석)

- SUM() ? + OVER

 

DRILL DOWN ROLLUP

 

 ROLLUP(월별) 

ROLLUP VS DRILL-DOWN

 

 

https://bommbom.tistory.com/entry/오라클-힌트hint-종류-및-사용법 

 

오라클 힌트(hint) 종류 및 사용법

이번 포스팅에서는 오라클 힌트 종류 및 사용법에 대해 알아보겠습니다. 힌트(Hint)는 쉽게 말하면 dbms에게 SQL를 최적화를 위해 힌트를 주는 것입니다. 데이터베이스관리자(DBA)나 개발자가 오라

bommbom.tistory.com

 

 

 

[SQL 튜닝] 오라클 힌트(hint)의 개념/ 사용법/자주쓰이는힌트 정리 (tistory.com)

 

[SQL 튜닝] 오라클 힌트(hint)의 개념/ 사용법/자주쓰이는힌트 정리

[SQL 튜닝] 오라클 힌트(hint)의 개념/ 사용법 정리 💡 힌트(Oracle Hint)란 무엇일까? 힌트란 SQL 튜닝의 핵심 부분으로 일종의 지시 구문이다. 즉, 오라클 옵티마이저(Optimizer)에게 SQL문 실행을 위한 데

devuna.tistory.com

 

 

블로그 위치

 

SQL BOOSTER 책 소개 및 전체스크립트 : 네이버 블로그 (naver.com)

 

SQL BOOSTER 책 소개 및 전체스크립트

프로젝트 성공을 위한 SQL 필독서 개발자에게 필요한 오라클 SQL 실전서 SQL BOOSTER 를 ...

blog.naver.com

 

 

 

Sort Merge JOIN -> NL 조인보다 왜 빠른가?

 

Sort Merge Join 

랜덤 I/O -> 순차I/O 변환(이미 데이터 정렬 후)

   예측이 잘되서 버퍼캐시의 효율(hit ratio)가 높아지는 효과

 

 

B tree가 제일 skew가 발생하도록 하려면?

- 정렬하고 난 다음 insert 하면 됨

 - self balanced tree가 아님 (LR, LL, RR, RL회전에 비용)

 

순수한 B-TREE 오라클에 들어있는 B-TREE인지 확인

 

 

 

 

index skew 



식별자 관계로만 설정할 경우의 문제점
단지 식별자관계와 비식별자 관계에 대한 설정을 고려하지않은 것이 개발의 복잡성을 증가시키는 요인이 될까?

반정규화된 테이블의 성능저하 사례

1차 정규화를 적용하면

함수적 종속성(functional Dependency)에 근거한 정규화 수행 필요

함수종속성
결정자(DETERMINANT) -> 종속자(DEPENDENT)

반정규화와 성능저하

반정규화의 정의 

기본적으로 정규화는 입력/수정/삭제에 대한 성능을 향상시킬 뿐만 아니라 조회에 대해서도 성능을 향상시키는 역할을 한다. 그러나 정규화만을 수행하면 엔터티의 개수가증가하고 관계가 많아져 일부 여러개의 조인에 걸려야만 데이터를 
가져오는 경우가 있다. 이러한 경우 업무적으로 조회에 대한처리성능이 중요하다고 판단될때 부분적으로 반정규화를 고려하게 되는 것이다. 또한 정규화의 함수적 종속관계는 위반하지 않지만 데이터의 중복성을 증가시켜야만
데이터 조회의 성능을 향상시키는 경우가 있다. 이러한 경우 반정규화를 통해서 성능을 향상시킬 수 있게 되는 것이다.

반정규화의 기법
넓은 의미에서 반정규화를 고려할 때 성능을 향사시키기 위한 반정규화는 여러가지가 나타날 수 있다.

테이블 반정규화는

테이블병합 
1:1 관계 테이블병합 : 1:1관계를 통합하여 성능향상
1:M 관계 테이블병합 :  1:M관계 통합하여 성능향상
슈퍼/서브타입 테이블병합 : 슈퍼/서브 관계를 통합하여 성능향상

테이블분할 수직분할 : 칼럼단위의 테이블을 디스크 I/O를 분산처리 하기 위해 테이블을 1:1로 분리하여 성능향상(트랜잭션의 처리되는 유형을 파악이 선행되어야 함)
수평분할 : 로우 단위로 집중 발생되는 트랜잭션을 분석하여 디스크 I/O



SQL 최적화 기본 원리


1.NL JOIN 
- NL JOIN은 프로그래밍에서 사용하는 중첩된 반복문과 유사항 방법으로 조인은 수행한다.
B-트리 인덱스 리프 블록

2.Sort Merge JOIN
- Sort Merge Join 은 조인 칼럼을 기준으로 데이터를 정렬하여 조인을 수행한다.

3.Hash Join 
- Hash Join은 해슁 기법을 이용하여 조인을 수행한다.조인을 수행할 테이블의 조인칼럼을 기준으로 해쉬힘수를 수행하여 서로 동일한 해쉬값을 갖는 것들 사이에서 실제값이 같은지를 비교하면서조인을 수행한다.



데이터베이스 아키텍처
아키텍처 개관
Oracle 아키텍처 : 데이터베이스에 대한 정의가 조금씩 다르고 Oracle에서는 디스크에 저장된 데이터 집합(Datafile, Redo Log File, Control File)을 데이터베이스 라고 부른다. 그리고 SGA 공유 메모리 영역과 
이를 액세스하는 프로세스 집합을 합쳐서 인스턴스라고 부른다.

프로세스 : SQL Server는 쓰레드 기반 아키텍처이므로 프로세스 대신 쓰레드라는 표현을 써야한다. SQL Server 뿐만 아니라 Oracle도 Windows 버전에선 쓰레드를 사용하지만, 프로세스와 일일이 구분하면서
설명하려면 복잡해지므로 특별히 쓰레드를 언급해야 할 경우가 아니라면 간단히 '프로세스'로 통칭하기로 한다. 잠시 후 표로써 정리해 보이겠지만, 주요 쓰레드의 역할은 Oracle 프로세스와 크게 다르지 ㅇ낳다.
프로세스는 서버 프로세스(Server Processes)와 백그라운드 프로세스(Background Processes)집합으로 나뉜다. 서버 프로세스는 전면에 나서 사용자가 던지는 각종 명령을 처리하고, 백그라운드 프로세스는
뒤에서 묵묵히 주어진 역할을 수행한다.


가. 서버 프로세스 : 서버프로세스는 사용자 프로세스와 통신하면서 사용자의 각종명령을 처리하며, SQL Server에선 Worker 쓰레드가 같은 역할을 담당한다.좀더 구체적으로 말해, SQL 파싱하고 필요하면 최적화를 수행하며,
커서를 열어 SQL를 실행하면서 블록을 읽고, 읽은 데이터를 정렬해서 클라이언트가 요청한 결과집합을 만들어 네트워크를 통해 전송하는 일련의 작업을 모두 서버 프로세스가 처리해준다. 스스로 처리하도록 구현되지 않은 기능,
이를테면 데이터 파일로부터 DB 버퍼 캐시로 블록을 적재하거나 Dirty블록을 캐시에서 밀어냄으로써 Free 블록을 확보하는일, 그리고 Redo로그 버퍼를 비우는 일등은 OS, I/O 서브시스템, 백그라운드 프로세스가 대신 처리하도록 시스템 Call을
통해 요청한다
클라이언트가 서버 프로세스와 연결하는 방식은 DBMS마다 다르지만 ORacle을 예로들면, 전용 서버 방식과 공유 서버 방식, 두가지가 있다
1)전용서버(Dedicated Server) 방식 : 1.연결요청(Listener) 2.프로세스 생성 및 연결 요청 상속 (Server) 3.Resend 패킷전송(사용자) 4.연결 후 작업 요청 (server) 5.처리 후 결과 전송

2)공유 서버(shared Server) 방식 : 공유 서버는 말 그대로 하나의 서버 프로세스를 여러 사요자 세션이 공유하는 방식으로서, 앞서 설명한 Connection Pooling 기법을 DBMS 내부에 구현해 놓은 것으로 생각하면 쉽다.
즉, 미리 여러개의 서버 프로세스를 띄어 놓고 이를 공유해서 반복 재사용한다.
1.연결요청 (Listener) 2.가용된 Dispatcher 포트번호 전송 3.연결 후 작업 요청 Dispatcher 4.요청등록 SGA(Request Queue) 5.요청접수 Server 6.결과등록 Reponse Queue 7.결과수령 Dispatcher 8.결과 전송하는

나.백그라운드 프로세스 
System Monitor(SMON) :장애가 발생한 시스템을 재기동할 때 인스턴스 복구를 수행하고, 임시 세그먼트와 익스텐트를 모니터링한다

Process Monitor(PMON) : 이상이 생긴 프로세스가 사용하던 리소스를 복구한다.

Database Writers(DBWn) : 버퍼캐시에 있는 Dirty 버퍼를 데이터 파일에 기록한다.

Log Writer(LGWR) : 로그 버퍼 엔트리를 Redo 로그 파일에 기록한다.

Archiver (ARCn) : 꽉 찬 Redo 로그가 덮어 쓰여지기 전에 Archive 로그 디렉토리로 백업한다.

Chckpoint(CKPT) : Checkpoint 프로세스는 이전에 Checkpoint가 일어났던 마지막 시점 이후의 데이터베이스 변경 사항을 데이터파일로 기록하도록 트리거링하고, 기록이 완료되면 현재 어디까지 기록했는지를 컨트로 파일과 데이터 파일 헤더에 저장한다.

Recoverer(RECO) : 분산트랜잭션 과정에 발생한 문제를 해결한다.







계층형 쿼리 Hierarchiacal Query

 : 오라클에서만 지원

 

  • 순방향 전개 : 최상위 노드에서 하위 노드로 이동
  • 역방향 전개 : 하위 노드에서 자신의 부모노드만 방문

Query 작성 예시

  • -- 순방향 전개 (prior 자식 = 부모)
    SELECT LPAD(' ', 2*(LEVEL-1)) || node_name 
    FROM node_table  
    START WITH node_id = '100'
    CONNECT BY PRIOR node_id = parent_id;
  • -- 역방향 전개 (자식 = prior 부모)
    SELECT LPAD(' ', 2*(LEVEL-1)) || node_name  
    FROM node_table  
    START WITH node_id = '246'
    CONNECT BY node_id = PRIOR parent_id;

 

  1. Node : 원모양으로 표시된 항목. 각각의 품목이 하나의 노드가 되며 실제 테이블에서는 하나의 ROW에 대응된다.
  2. Parent : 부모노드라고도 한다. 트리구조에서 상위에 있는 노드를 말한다.
  3. Child : 자식노드라고도 한다. 위 그림에서 모니터는 컴퓨터의 자식노드이며, 컴퓨터는 모니터와 본체의 부모 노드라고 한다
  4. Leaf : 리프노드라고 한다. 더 이상 하위에 연결된 노드가 없는 항목을 말한다. 즉 자식 노드가 없는 노드이다.
  5. Root : 계층형 트리구조에서 최상위에 있는 노드.
  6. Level : 트리구조에서의 각각의 계층 루트에 해당되는 컴퓨터가 1레벨이 되며 그 하위에 있는 모니터, 본체 등이 2레벨이다.

 

 * ORA-01436: CONNECT BY의 루프가 발생되었습니다 

 - 루프 발생 시 nocycle 과 함께 써서 어디 데이터에서 루프가 발생되는지 찾을 수 있다. 

Query 작성 예시

  SELECT LPAD(' ', 2*(LEVEL-1)) || node_name, CONNECT_BY_ISCYCLE
  FROM node_table
  START WITH node_id = '510' 
  CONNECT BY NOCYCLE node_id = PRIOR parent_id;

 

 

1. LEVEL : root데이터면 1, 그 하위 데이터이면2, leaf데이터까지 1씩 증가
2. CONNECT_BY_ISLEAF : 현재 행이 마지막 leaf노드인지 리턴 (1:리프노드, 0:리프노드X)
3. CONNECT_BY_ISCYCLE : 계층형 쿼리에서 해당하는 로우가 자식노드를 가지고 있는데 다시 부모느드 인지를 찾아주는 함수 (중복참조하여 자식노드가 있을 경우 1 , 그렇지 않을 경우 0을 반환)
4. CONNECT_BY_ROOT(컬럼) : 최상위 데이터의 컬럼 정보 조회
5. SYS_CONNECT_BY_PATH(컬럼,'구분자') : 계층 순회 경로 표현, 두번째 인자(경로 표현 구분자)

 

 

계층형 쿼리에서의 조건 처리 start with … connect by example: pruning branches

select
  lpad(' ', 2*level) || child
from
  prune_test
start with
  parent is null
connect by
  prior child = parent and
  parent not in (1, 71)          -- Exclude children below and 71

Query 출력 결과 

  1             -- 가지치기 됌
  6
    61
    63
    65
      653
    69
  7
    71       -- 가지치기 됌
    74
      744
      746
      748
            7481
            7483
            7487

 

 

계층경로 출력하는 방법

  1. SYS_CONNECT_BY_PATH() 사용
        select *
        from (
            select node_id, LTRIM(SYS_CONNECT_BY_PATH(nname,'>'),'>') path, parent_id, connect_by_iscycle
            from node_table
            start with node_id= '510' connect by node_id= prior parent_id
        );
        where parentid = '0';  
  2. LISTAGG() 사용
        select listagg(nname, '>') within group (order by level desc)
        from node_table
        start with node_id= '510' connect by node_id= prior parent_id;

Query 출력 결과 

리트리버>개>포유류>동물>생물

 

 

 

* LISTAGG() 집계함수

 : LISTAGG 함수는 문자열을 병합하여 일련의 문자열 요소를 하나의 문자열로 집계. 선택적으로 인접한 입력 문자열 사이에 삽입되는 구분자 문자열을 제공한다.

즉, 여러 row 데이터를 하나의 row로 문자열을 합쳐서 보여줄 수 있는 함수이다.

 

RANK, DENSE_RANK 함수들처럼 WITHIN GROUP을 사용하여 쓸 수 있다. 

 -> WITHIN GROUP ( ORDER BY 컬럼, 컬럼... [ ASC | DESC ] )

 

Query 작성 예시

   SELECT workdept, LISTAGG(lastname, ', ') WITHIN GROUP(ORDER BY lastname) AS employees

   FROM emp

   GROUP BY workdept

예상 출력 결과 : 부서별로 그룹화되고 알파벳순으로 표시되며 쉼표로 구분된 이름 목록을 생성

 

* ORA-01489: result of string concatenation is too long 
 -  "값"열을 최대 4000 자로 제한을 위반할 때 발생되는 에러

    -> ON OVERFLOW 옵션을 사용

SELECT LISTAGG(product_name, ',' on overflow truncate) WITHIN GROUP(ORDER BY product_name)
FROM products;

 

출처 : https://hwangtree.tistory.com/11

 

[Oracle] 계층형 쿼리 Hierarchiacal Query ( + LISTAGG() 함수)

계층형 쿼리 Hierarchiacal Query : 오라클에서만 지원 순방향 전개 : 최상위 노드에서 하위 노드로 이동 역방향 전개 : 하위 노드에서 자신의 부모노드만 방문 Query 작성 예시 -- 순방향 전개 (prior 자식

hwangtree.tistory.com

 

'DataBase > Oracle' 카테고리의 다른 글

성능을 고려한 SQL 작성비법  (0) 2024.04.23
SQLP 학습정리  (0) 2024.03.06
성능을 향상 쿼리 튜닝  (0) 2023.09.26
성능 쿼리 작성  (0) 2023.09.25
[ERWIN][ORACLE] erwin 연동해서 오라클 테이블 만들기  (0) 2023.08.10

[멀티캠퍼스]  ★PDF 교재 파일 전달

PDF PW : FA00XK

https://classnow.webex.com 

http://naver.me/FcgGEiOK
1111

25124970655
1234

CLOUD
NAME: clould_ora01
사용자 : ora01
비밀번호 : oracle_4U2023 v

접속유형 : 클라우드 전자지갑
구성파일 : wallet_edudb.zip

--Chapter 1 인덱스 활용
--Chapter 2 join 문장을 이용한 데이터 연결
--Chapter 3 Subquery를 이용한 데이터 연결
--Chapter 4 그룹 함수 활용
--Chapter 5 분석 함수 활용
--Chapter 6 계층형 쿼리
--Chapter 7 정렬 작업 속도 개선


Shared Pool
parse 컴파일

1) parse - Shared Pool (Library Cache)
SQL text
실행계획
p-code ==> Shared Pool 의 Library 
soft parse 
hard parse

2) bind

3) execute : DBC : data block의 복사본
   db_block_size = 8k

logical reads :
physical reads :
cache hit ratio 중요 : 

1000 1000
  100   50

server tuning 
sql tuning

4) fetch -- only select



실행계획

Chapter1 
인덱스 활용


인덱스의 주된 목표 
- select 를 빨리하기 위해서

B* Tree (발란스 트리구조)


select EMPNO, ROWIND from EMP order by EMPNO;


select * from TABLE (DBMS_XPLAN.DISPLAY_CURSOR); --실행계획


set linesize 200
set pagesize 100

--SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allSTATS LAST')) ;



SELECT * FROM table(dbms_xplan.display_cursor) ;


@c:\LABS\XPLAN
도구
-환경설정
-데이터베이스


@XPLAN -- 환경설정 도구-환경설정-데이터베이스-워크시트 -스크립트를 찾을기본 경로선택 ㅊ


리버스 키인덱스


실행계획


SELECT *
FROM EMP
WHERE DEPTNO = 20;

WHAT 
HOW -- PROGRAM
EXE PLAN


PARSE : 실행계획 : OPTIMIZER : RULE, COST
FULL TABLE SCAN : 5
INDEX : 

FTS

optimizer_mode = all_rows (default) : 전체처리를 가장 빠르기
first_rows : response time을 빠르기

full table scan : 5

cost : 

EXECUTE

FETCH
;


show parameter optimizer;


/**/ BLOCK Comment 힌트

힌트
INDEX scan

인덱스 스캔
인덱스 access

index scan
range

table random access HWM


시퀀셔 엑세스


인덱스를 제대로 못탈 경우 풀테이블 스캔이 더 나음


6가지 인덱스 스캔
-1.인덱스 유니크 스캔


SELECT *
 FROM EMP
WHERE ENAME ='SCOTT'

@XPLAN
SELECT ENAME, ROWID FROM EMP ORDER BY 1;


힌트 : index (테이블 명 인덱스명)
        index_rs (테이블명 인덱스명)
        index_rs (테이블명(컬럼명))
        index_rs_asc ( 테이블명(컬럼명))
        index_rs_desc (테이블명(컬럼명))


인덱스 풀스캔일 경우

/*+ index_ffs(e(employee_id)) */

select empno
from emp
order by 1
;
select EMPNO
from EMP;

;
@XPLAN
select empno, rowid from emp order by empno;

select * from employees;

select /*+ index_join (e EMPLOYEES_IDX02 EMPLOYEES_ID04 ) */
        department_id, JOB_id
  FROM employees e
where department_id = 80
 and job_id ='SA_REP'
;


인덱스 스킵 스캔
select /*+ index_ss (e empl_name ix) */ employee_id, first_name, last_name, salary,
department_id
from employees e
where first_name ='Steven';


인덱스 활용

인덱스 스캔 불가 사례
1. <>, !=, ^= 등 부정형의 비교
2. 인덱스 컬럼의 변형이 발생한 경우
3. Null 인 데이터를 조회하는 경우
4.like '%문자열' 사용하는경우



NESTED LOOP JOIN

9I NLJ PREFETCH
11g, NLJ_BATCHING 물리적읽기로 인한 지연을 줄이기 위해서 

DBC 데이터베이스 버퍼 캐쉬
인덱스 통해서 블럭단위를 읽을 때 



 https://www.multicampus.com/common/board/boardMstr/124387?p_menu=MzU3I01BSU4=&p_gubun=Qw== 

내선번호 02-3429-5740
설문은 종료 1시간전에 한다고 합니다

-- USER SQL
--ALTER USER "TEST"
--DEFAULT TABLESPACE "SYSTEM"
--TEMPORARY TABLESPACE "TEMP"
--ACCOUNT UNLOCK ;
-- QUOTAS
-- ROLES
--ALTER USER "TEST" DEFAULT ROLE "DBA","CONNECT","RESOURCE";
-- SYSTEM PRIVILEGES
--;
select empno
from emp
order by 1
;
select EMPNO
from EMP;
--@XPLAN
;
select empno, rowid from emp order by empno;

select * from employees;

select /*+ index_join (e EMPLOYEES_IDX02 EMPLOYEES_ID04 ) */
        department_id, JOB_id
  FROM employees e
where department_id = 80
 and job_id ='SA_REP'
;

@IDX

70
70
80 R1
80 R3
80 R7
80 R11

JOB_ID
....
SA_REP R3
SA_REP R4
SA_REP R6
SA_REP R11
SA_REP R20

;
--@XPLAN
select /*+ index_join (e EMPLOYEES_IDX02 EMPLOYEES_ID04 ) */
        department_id, JOB_id
  FROM employees e
where department_id = 80
 and job_id ='SA_REP'
;

--@XPLAN
select /*+ index_join (e EMPLOYEES_IDX02 EMPLOYEES_ID04 ) */
        department_id, JOB_id, salary 
  FROM employees e
where department_id = 80
 and job_id ='SA_REP'
;


SELECT * FROM employees where age BETWEEN 20 and 29
;
create index empl_name_ix ON employees(last_name, first_name);

select /*+ index_ss (e empl_name ix) */ employee_id, first_name, last_name, salary,
department_id
from employees e
where first_name ='Steven'
;

select /*+ INDEX (EMP) */ *
from EMP
WHERE DEPTNO <> 20;

SELECT DEPTNO, ROWID FROM EMP ORDER BY 1;

;
SELECT *
 FROM CUSTS
WHERE CUST_ID LIKE '7%'
;
--@XPLAN
;

select * 
 from employees
 where 1 = 1
  and  first_name = 'Peter'
;

-- create inex 
CREATE INDEX EMP_FNAME_IX ON EMPLOYEES ( FIRST_NAME) ;
CREATE INDEX EMP_FNAME_FBI   ON EMPLOYEES ( UPPER(FIRST_NAME) );
CREATE INDEX EMP_FANME_FBI ON EMPLOYEES( UPPER(FIRST_NAME));
CREATE INDEX EMP_FNAME_IX ON EMPLOYEES ( FIRST_NAME) ;
;


select *
from employees
where upper(
;

update cust set cust_email =null
where cust_id in (100,200);

;
show parameter statistics level
;
alter SYSTEM SET STATISTICS_LEVEL =ALL;
;

SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allSTATS LAST +cost')) ;

create index cust_email_fbi on custs(nvl(cust_email,'NULL'));

select * from custs
where NBL(cust_email,'null') = 'NULL'
;

@IDX
;;
select * 
 from emp
 ;

select * from emp
where ename like 'S%'
;

--@XPLAN
select * from emp
where ename like '%S'
;

--@XPLAN

;

select reverse(ENAME), ROWID from emp order by 1
;
CREATE INDEX emp_ename_fbi ON emp(REVERSE(ename));

select *
  from emp
 where REVERSE(ename) LIKE 'TT%'
 ;
 
SELECT EMPNO, ENAME, HIREDATE, TO_CHAR(HIREDATE, 'YYYY/MM/DD HH24:MI:SS')
FROM EMP
;
 
SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE HIREDATE ='81/04/02'
;
 
SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE HIREDATE ='81/04/02'
;

 NLS_DATE_FORMAT ='RR/MMDD';
 
 
 SELECT EMPNO, ENAME, HIREDATE
 FROM EMP
 WHERE TO_CHAR(HIREDATE,'RR/MM/DD')  = '80/12/17'
 ;
 
 
 SELECT EMPNO, ENAME, HIREDATE
 FROM EMP
 WHERE HIREDATE BETWEEN '80/12/17' AND '80/12/18' -1/24/60/60 ;
 
-- WHERE TO_CHAR(HIREDATE,'RR/MM/DD')  = '80/12/17'
;

;
DESC EMP;
DESC DEPT;

SELECT * FROM EMP;
SELECT * FROM DEPT;
;
 
SELECT EMPNO, ENAME, E.DEPTNO, D.DEPTNO, DNAME
FROM EMP E, DEPT D
;
--ANSI SYNTAX
--
SELECT EMPNO, ENAME, E.DEPTNO, D.DEPTNO, DNAME
FROM EMP E JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)

--WHERE E.DEPTNO = D.DEPTNO

;
--NATURAL JOIN :이름이 같은 모든 컬럼을 조인조건으로 생성 
SELECT EMPNO, ENAME, E.DEPTNO, D.DEPTNO, DNAME
FROM EMP E NATURAL JOIN DEPT D
;
--JOIN USING : 이름이 같은 컬럼 중 USING에 사용된 컬럼만 조인조건으로 사용
SELECT EMPNO, ENAME, DEPTNO, DEPTNO
FROM EMP E JOIN DEPT D
USING (DEPTNO);
;
--NON EQUI JOIN

SELECT EMPNO, ENAME, SAL
FROM EMP;



SELECT EMPNO, ENAME, SAL, GRADE
FROM  EMP, SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL;
SELECT * FROM SALGRADE;

;
SELECT employee_id, last_name,  
d.department_id, department_name
from    employees e join departments d
on     e.department_id = d.department_id
;
--using 
SELECT employee_id, last_name,  
department_id, department_name
from    employees e join departments d
using     (department_id)
;
--
SELECT employee_id, last_name,  
department_id, department_name
from    employees e natural join departments d

--using     (department_id)
;

SELECT employee_id, last_name,  
d.department_id, department_name
FROM employees e, departments d, locations L
where e.department_id = d.department_id 
  and d.LOCATION_ID= L.LOCATION_ID
;


SELECT employee_id, last_name,  
d.department_id, department_name
FROM employees e, departments d, 
on e.department_id = d.department_id 
join LOCATIONS L
  on d.LOCATION_ID= L.LOCATION_ID
;

select *
FROM EMP CROSS JOIN DEPT
;

CREATE TABLE EMP2 AS SELECT * FROM EMP;
INSERT INTO EMP2 ( EMPNO, ENAME) VALUES ( 9000,'TEST');
COMMIT;
SELECT * FROM EMP2;
SELECT EMPNO, ENAME,  D.DEPTNO ,DNAME
FROM EMP2 E, DEPT D
WHERE E.DEPTNO= D.DEPTNO;

;
CREATE TABLE EMP2 AS SELECT * FROM EMP;
INSERT INTO EMP2(EMPNO, ENAME) VALUES (9000,'TEST');
COMMIT;

SELECT * FROM EMP2;

SELECT employee_id, last_name,  
department_id, department_name
from    employees e join departments d
using     (department_id);
;
--OUTER JOIN
SELECT EMPNO, ENAME, D.DEPTNO, DNAME 
FROM EMP2 E, DEPT D
WHERE E.DEPTNO = D.DEPTNO(+)
;
--OUTER JOIN
SELECT EMPNO, ENAME, D.DEPTNO, DNAME
FROM EMP2 E LEFT OUTER JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)
;

SELECT EMPNO, ENAME, D.DEPTNO, DNAME
FROM EMP2 E RIGHT OUTER JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)
;
SELECT EMPNO, ENAME, D.DEPTNO, DNAME
FROM EMP2 E RIGHT OUTER JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)
;
--옵티마이저가 조인을 만나면 DRIVING 
--Nested Loop join
;
outer table leading, driving , driven

;
dept    ->  EMP
4           4

풀스캔 4

;
--Nested Loop join
--기본적인 조인 방법

driving 조건으로 몇 개의 행을 실행
;

SELECT /*+ USE_NL ( E, D) */
    e.ename, e.deptno, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno;
--@XPLAN
;
SELECT /*+ USE_MERGE ( E, D) */
    e.ename, e.deptno, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno;

--@XPLAN
;


PGA         EMP

PGA
SORT MARGE

;
--
SORT            EMP

SORT MERGE JOIN
--정렬작업이 완료되기전까지 조인 결과 도출 불가능

NESTED LOOP
--- 처음 몇개행이 빨리 튀어나오는 방식

hash join
SP
PGA 
olap

--조인 관련 힌트
USE_NL (t1 t2)

;

SELECT /*+ USE_HASH (D E) LEADING(E) */
        EMPNO, ENAME, D.DEPTNO, DNAME
FROM DEPT D, EMP E
WHERE E.DEPTNO = D.DEPTNO;

;
SELECT /*+ USE_HASH (D E) LEADING(E) */
        EMPNO, ENAME, D.DEPTNO, DNAME
FROM DEPT D, EMP E
WHERE E.DEPTNO = D.DEPTNO;

;

-- 조인 처리 방식의 이해

CREATE TABLE CP_EMP AS SELECT * FROM EMP;
CREATE TABLE CP_DEPT AS SELECT * FROM DEPT;

;
SELECT EMPNO, ENAME, D.DEPTNO, DNAME
FROM CP_EMP E, CP_DEPT D
WHERE E.DEPTNO = D.DEPTNO
;
-- HASH 조인 
;
;
SELECT /*+ USE_NL(E D) LEADING(E) */
        EMPNO, ENAME, D.DEPTNO, DNAME
FROM CP_EMP E, CP_DEPT D
WHERE E.DEPTNO = D.DEPTNO
;

SELECT /*+ USE_NL(E D) LEADING(E) */
        EMPNO, ENAME, D.DEPTNO, DNAME
FROM CP_EMP E, CP_DEPT D
WHERE E.DEPTNO = D.DEPTNO
;
--@XPLAN

--조인 키 컬럼의 인덱스는 필수



;

CREATE INDEX cp_emp_deptno on cp_emp(deptno);
CREATE INDEX cp_dept_deptno on cp_dept(deptno);
;
-- Shared Pool
-- 논리적, 물리적 
--

--인덱스 쿼리 튜닝기법
--퍼포먼스

--인덱스 스캔 방법
--index full, fase full scan, 

--유일하게 멀티블럭 하는 인덱스 index fast full scan multiblock io

--조인 유형
--nested loop 조인 

--hash join 
--해쉬 조인의 경우 리딩 테이블의 중요하다
;
;
SELECT /*+ leading(c) use_hash(s) */ count(*)
from customers c, sales s
where c.cust_id= s.cust_id
;
;
--join 튜닝 사례

---
select /*+ swap_join_inputs(d) leading(d) use_hash(e) */ d.department_id
    ,department_name
    ,e.department_id
    ,last_name
    ,salary
from departments d, employees e
where D.department_id(+) = e.department_id
and e.salary(+) > 6000
;

--NL JOIN TUNING

--Nested Loops join 의 최적화
--Looping을 최소화시킬 수 있도록 Driving table을 선택한다

select c.cust_id. c.cust_las_name, c.cust_city
from custs c, sales s



create index sales_custid_timeid_ix on sales(cust_id, time_id);

SELECT /*+ use_nl(c s) index_rs_asc (s sales_custid_timeid_ix) */
    c.cust_id, c.cust_last_name, c.cust_ciry, c.cust_credit
from custs c, sales s
where c.cust_id = s.cust_id
  and c.cust_city = 'Los Angeles'
  
;

Nested SubQuert : where 절에 사용되는 subquery 

--
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP 
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME='SCOTT') ;
--메인 쿼리와 서브쿼리

;

--단일행 서브쿼리 다중행 서브쿼리 실행계획 차이점 
--쿼리 트랜스포메이션
select EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME ='SCOTT');

                
;
--OPTIMIZER 가실행계획을 작성할때
QUERY TRANSFORMER   :   SUBQUERY -> JOIN    서브쿼리 UNNEST
COST ESTIMATOR
;

Nested Subquety : where 절에 사용되는 Subquery

;

상관 서브 쿼리

-- 회사 평균보다 급여를 많이 받는 사원 정보를 조회하세요.

SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP T1
WHERE 1 = 1
  AND SAL >= (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = T1.DEPTNO)
;
SELECT AVG(SAL) FROM EMP
;
--Subquery Null

--In과 Not in 비교

create table emp2 as select * from emp;
insert into emp2(empno, ename) values (9000,'SCOTT');


select *
 from dept
where deptno in (select deptno from emp2);
;

select *
from   dept
where deptno not in ( select deptno from emp2);

--not in의 경우 서브쿼리에 null값이 포함되면 결과가 나오지 않는다
select *
from   dept
where deptno not in (select deptno from emp2 where deptno is not null)
;
-- exists
select *
from   dept
where exists (select deptno from emp2 where deptno = dept.deptno)
;
-- not exists
select *
from   dept
where not exists (select 'X' from emp2 where deptno = dept.deptno)
;

select * 
from emp
where deptno in( select deptno from dept)
;

select e.*
from emp e, dept d
where e.deptno = d.deptno
;

--alter table emp disable constraint emp_deptno_fk; --제약조건 제외


;
select *
from    dept
where   deptno in (select deptno from emp);

--실행 결과 다름
select d.*
from    emp e, dept d
where   e.deptno = d.deptno;

;
select *
from    dept
where   deptno in (select deptno from emp);

--INLINE View의 특징
--Simple View
--조건절과 조인문만을 포함
--언제든 Merging이 일어난다
--힌트 : MERGE, No_MERGE

--Complex view
--Group by, distinct 를 포함하는 view
--10g 이후 Merging이

Simple view
SELECT *
FROM (SELECT * FROM emp WHERE job ='SALESMAN') e
,(SELECT * FROM dept WHERE loc ='CHICAGO') d
where e.deptno = d.deptno;

select *
from emp e, dept d
where e.deptno = d.deptno
where e.job = 'SALESMAN'
;

--join 후에 distinct 가 수행되었다.
--먼저 distinct 를 서브쿼리에서 수행후에 join 을 할경우 cost 가 줄어든다

-- hash unique 인라인 뷰에서 distinct 이후에 처리 하는방법이 효율적

--스칼라 서브쿼리

--

select empno, ename, sal, (SELECT DNAME FROM DEPT WHERE DEPTNO = EMP.DEPTNO) DNAME
from EMP
;
--NVL이 적용이안됨
select empno, ename, sal, (SELECT NVL(DNAME, '부서없음') FROM DEPT WHERE DEPTNO = E.DEPTNO) DNAME
from EMP2 E

;
--NVL이 적용됨
select empno, ename, sal, NVL((SELECT DNAME FROM DEPT WHERE DEPTNO = E.DEPTNO), '부서없음') DNAME
from EMP2 E
;


SELECT CUST_ID
    ,NVL((SELECT /*+ NO_INDEX(S (CUST_ID)) */ SUM(AMOUNT_SOLD)
          FROM  SALES S
          WHERE CUST_ID = C.CUST_ID),0) AMT_SOLD
          
 FROM CUSTOMERS C
;

--COMPLEX view
SUBQUERRY FACTORING (WITH)
--WITH 쓰는 목적 가독성, 성능향상
;

--힌트사용 nf : materialize
--             inline

with sum_sal as ( select deptno, sum(sal) as sum
                  from emp
                  group by deptno)
select *
from    sum_sal
where   sum > (SELECT AVG(sum) from sum_sal);

with sum_sal as ( select /*+ INLINE */ deptno, sum(sal) as sum
                  from emp
                  group by deptno)
select *
from    sum_sal
where   sum > (SELECT AVG(sum) from sum_sal);
;

--MATERIALIZE   


;
--그룹 함수 활용
--단원 그룹함수
--행집합

--AVG
--COUNT
--MAX
--MIN
--SUM
--
;

SELECT SUM(SAL), AVG(SAL), COUNT(*)
FROM EMP;

SELECT DEPTNO, SUM(SAL), AVG(SAL), COUNT(*)
FROM EMP2
GROUP BY DEPTNO;

--SELECT SAL
SELECT EMPNO, ENAME, SAL , (SELECT DNAME FROM DEPT WHERE DEPTNO = E.DEPTNO) DNAME
FROM EMP2 E;


;
--데이터 그룹 


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

SELECT DEPTNO, SUM(SAL), AVG(SAL), COUNT(*)
FROM EMP2
GROUP BY DEPTNO;
;
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY DEPTNO,JOB
ORDER BY 1,2
;

SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY 1,2
;
SELECT SUM(SAL)
FROM EMP
--GROUP BY DEPTNO
--ORDER BY 1,2
;
--ROLLUP, CUBE

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY DEPTNO,JOB
ORDER BY 1,2;

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP (DEPTNO,JOB)    --N +1
ORDER BY 1,2;

SELECT DEPTNO, JOB, SUM(SAL)   
FROM EMP
GROUP BY CUBE (DEPTNO,JOB)      --2의 N승
ORDER BY 1,2;


--GROUPING 함수
--SELECT

SELECT DEPTNO, JOB, SUM(SAL)
FROM    EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY 1,2;

SELECT DEPTNO,GROUPING(DEPTNO),  JOB, GROUPING(JOB), SUM(SAL)
FROM    EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY 1,2;

SELECT DECODE (GROUPING(DEPTNO),1, '모든부서', DEPTNO) G_DEPT,
    DECODE(GROUPING(JOB),1,'모든직무',JOB) G_JOB, SUM(SAL)
FROM    EMP
GROUP BY ROLLUP(DEPTNO, JOB)
ORDER BY 1,2;
;
GROUPING_ID
;

--HAVING 절
;
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE SUM(SAL) > 9000
GROUP BY DEPTNO;
ORDER BY DEPTNO;
;

--1번이 더 속도가 빠르다
--1 
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE DEPTNO > 20
GROUP BY DEPTNO
HAVING SUM(SAL) > 9000
AND DEPTNO > 20
ORDER BY DEPTNO
;
--2
SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING SUM(SAL) > 9000
AND DEPTNO > 20
ORDER BY DEPTNO
;
@XPLAN
;

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

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

SELECT DEPTNO, EMPNO,ENAME, SUM(SAL) AS SAL
FROM EMP
GROUP BY DEPTNO, ROLLUP((EMPNO, ENAME));

SELECT DEPTNO, EMPNO, ENAME, SUM(SAL) AS SAL
FROM    EMP
GROUP BY ROLLUP(DEPTNO,(EMPNO,ENAME));

--Chapter 5 분석함수

;
--분석함수
select Analytic_function (arguments)
--        Over(
;
--select empno, ename, sal, avg(sal) over() avg_sal
--from emp
--group by ROLLUP(DEPTNO, JOB)

;

--select d.deptno, d.dname, e.ename, e.sal
;
SELECT EMPNO, ENAME, DEPTN, SAL, SUM(SAL) OVER ()
FROM EMP;
;

SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(PARTITION BY DEPTNO)
FROM EMP;


SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(ORDER BY SAL) SUM_SAL
FROM EMP;

;
SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(ORDER BY SAL) SUM_SAL
FROM EMP;

SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(ORDER BY SAL 
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) SUM_SAL
FROM EMP
;
--WINDOW SORT 
SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(ORDER BY SAL 
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) SUM_SAL
FROM EMP;

SELECT EMPNO, ENAME, DEPTNO, SAL, ROUND( AVG(SAL) OVER (),2) AS AVG_SAL
FROM EMP
;

--WINDOW SORT 
SELECT EMPNO, ENAME, DEPTNO, SAL, AVG(SAL) OVER(ORDER BY SAL 
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) SUM_SAL
FROM EMP;

SELECT EMPNO, ENAME, DEPTNO, SAL, ROUND( AVG(SAL) OVER (PARTITION BY DEPTNO),2) AS AVG_SAL
FROM EMP
;

--ROWS(물리적 윈도우 )

SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(ORDER BY SAL 
                    RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING ) SUM_SAL
FROM EMP;

SELECT EMPNO, ENAME, DEPTNO, SAL, SUM(SAL) OVER(ORDER BY SAL 
                    RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING ) SUM_SAL
FROM EMP;

-- 7788-100 = 7688
-- 7788 + 100 =7888
;


SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER() SUM_SAL,
                        MAX(SAL) OVER() MAX_SAL,
                        MIN(SAL) OVER() MIN_SAL,
                        AVG(SAL) OVER() AVG_SAL,
                        COUNT(SAL) OVER() CNT_SAL
FROM EMP;

SELECT EMPNO, ENAME, DEPTNO,SAL,
    SUM(SAL) OVER(PARTITION BY DEPTNO) DEPT_SUM,
    TRUNC(AVG(SAL) OVER(PARTITION BY DEPTNO)) DEPT_AVG,
    MIN(SAL) OVER(PARTITION BY DEPTNO) DEPT_MIN,
    MAX(SAL) OVER(PARTITION BY DEPTNO) DEPT_MAX
FROM EMP;


--SELECT ENAME, TO_CHAR(HIREDATE,'YYYY/MM/DD') HIREDATE, SAL,
--    SUM(SAL) OVER(ORDER BY HIREDATE)
--    RANGE BET
;
SELECT EMPNO,ENAME, SAL, DEPTNO,
    ROUND(AVG(SAL) OVER(PARTITION BY DEPTNO),2) AS AVG_SAL
FROM EMP
ORDER BY DEPTNO;
;
--Co
--WINDOW 함수

--분석함수에서 조건절 사용시 주의 사항

SELECT EMPNO, ENAME, SAL, DEPTNO, JOB, 
    ROUND(AVG(SAL) OVER(PARTITION BY DEPTNO),2) AS AVG_SAL
FROM EMP
WHERE JOB = 'CLERK'
ORDER BY DEPTNO
;

SELECT EMPNO, ENAME, SAL, DEPTNO, JOB, 
    ROUND(AVG(SAL) OVER(PARTITION BY DEPTNO),2) AS AVG_SAL
FROM EMP
WHERE JOB = 'CLERK'
ORDER BY DEPTNO
;

SELECT EMPNO, ENAME, SAL, DEPTNO, JOB, 
    ROUND(AVG(SAL) OVER(PARTITION BY DEPTNO),2) AS AVG_SAL
FROM (SELECT EMPNO, ENAME, JOB, SAL, HIREDATE, DEPTNO,
            ROUND(AVG(SAL) OVER(PARTITION BY DEPTNO),2) AS AVG_SAL
        FROM EMP)
WHERE JOB = 'CLERK'
ORDER BY DEPTNO
;

--분석함수 순위관련

--SELECT empno, ename, sal, deptno,
--    RANK()  over(ORDER
    
;
-- TOP-N 쿼리
;
SELECT *
FROM    (SELECT * FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM <= 3
;

--RANK

SELECT EMPNO, ENAME, DEPTNO, SAL
FROM EMP;


SELECT EMPNO, ENAME, DEPTNO, SAL, RANK() OVER(ORDER BY SAL DESC) RANK
                                ,DENSE_RANK() OVER(ORDER BY SAL DESC) DRANK
                                ,ROW_NUMBER() OVER(ORDER BY SAL DESC) RNUM
                                
--                                ,    
FROM EMP;
SELECT EMPNO, ENAME, DEPTNO, SAL, RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RANK
FROM EMP;

--12 부터 가능함수
SELECT EMPNO, ENAME, SAL
FROM EMP
ORDER BY SAL DESC
FETCH FIRST 5 ROW ONLY;

SELECT * FROM V$VERSION;

;

--12 부터 가능함수
SELECT EMPNO, ENAME, SAL
FROM EMP
ORDER BY SAL DESC
FETCH FIRST 2 rows with ties;

;
show user

---순서 관련 함수
--LAG, LEAD


SELECT EMPNO, ENAME, DEPTNO, SAL, LAG(SAL) OVER(ORDER BY SAL)  LAG1
--        LAG(SAL,1,0) OVER()
FROM EMP;

SELECT EMPNO, ENAME, DEPTNO, SAL, LAG(SAL,2,0) OVER(ORDER BY SAL)  LAG1
--        LAG(SAL,1,0) OVER()
FROM EMP;

SELECT EMPNO, ENAME, DEPTNO, SAL, LEAD(SAL,2,0) OVER(ORDER BY SAL)  LAG1
        ,LAG(SAL,1,0) OVER(ORDER BY SAL ) LAG2
FROM EMP;

;
--비율관련 함수
--CUME_DIST
--PERCENT_RANK
--NTILE
--RATIO_TO_REPORT

--비율 관련 분석함수
--NTILE : WINDOW 그룹의 행을 정렬 후 지정한 개수의 범위(등급)으로 나눈 후 각 값이 가지고 있는 등급 값을 보여준다.
;

SELECT EMPNO, ENAME, SAL, NTILE(5) OVER(ORDER BY SAL DESC) GRADE
FROM EMP;

--RATIO_TO_REPORT : WINDOW 영역의 합계 내에서 현재 값이 차지하는 백분율, 별도의 WINDOWING 절의 설정하는것은 불가능

;
--백분율 급여분석
SELECT DEPTNO,ENAME,SAL, ROUND(100 * RATIO_TO_REPORT(SAL) OVER(),2) AS PCT_ALL
FROM EMP;
;
--RATIO_TO_REPORT
SELECT DEPTNO,ENAME,SAL, ROUND(100 * RATIO_TO_REPORT(SAL) OVER(),2) AS PCT_ALL
                        , ROUND(100 * RATIO_TO_REPORT(SAL) OVER(PARTITION BY DEPTNO),2) AS PCT_DEPT --부서별 급여액                        
FROM EMP;


--
@IDX
;
SELECT DEPTNO, ENAME
FROM EMP
ORDER BY DEPTNO;

SELECT DEPTNO, LISTAGG(ENAME) WITHIN GROUP (ORDER BY ENAME)
FROM EMP
GROUP BY DEPTNO;


SELECT DEPTNO, LISTAGG(ENAME||'(' || SAL || ')' ,', ') WITHIN GROUP (ORDER BY ENAME)
FROM EMP
GROUP BY DEPTNO;

SELECT DEPTNO, LISTAGG(ENAME||'(' || SAL || ')' ,', ') WITHIN GROUP (ORDER BY SAL DESC0)
FROM EMP
GROUP BY DEPTNO
;

SELECT DEPTNO, LISTAGG(ENAME ||'(' || SAL || ')', ', ') WITHIN GROUP(ORDER BY SAL DESC)
FROM EMP
GROUP BY DEPTNO;
;
--@XPLAN

--6 계층형 쿼리
--셀프 조인

--부하직원(W)       상사(M)
--EMP               EMP

SELECT W.EMPNO, W.ENAME, M.EMPNO, M.ENAME
FROM EMP W, EMP M
WHERE w.mgr = m.empno
;

--계층쿼리
--START WITH
--CONNECT BY 
;
SELECT LPAD(' ',2*LEVEL) || ENAME, LEVEL
FROM EMP
CONNECT BY PRIOR EMPNO = MGR
START WITH ENAME ='KING'
;

SELECT LPAD('#',2*(LEVEL -1), '*') || ENAME, LEVEL
FROM EMP
CONNECT BY PRIOR EMPNO = MGR
START WITH ENAME ='KING'
;


SELECT LPAD(' ',2*(LEVEL -1)) || ENAME, LEVEL
FROM EMP
CONNECT BY PRIOR EMPNO = MGR
START WITH ENAME ='KING'
;



SELECT LPAD(' ',2*(LEVEL-1)) || ENAME AS NAME, LEVEL, EMPNO, MGR
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR

;
--WHERE 절줄때
SELECT LPAD(' ',2*(LEVEL-1)) || ENAME AS NAME, LEVEL, EMPNO, MGR
FROM EMP
WHERE ENAME <> 'SCOTT'
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
;
--
SELECT LPAD(' ',2*(LEVEL*1)) || ENAME AS NAME, LEVEL, EMPNO, MGR
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

;
SELECT LPAD(' ',2*(LEVEL*1)) || ENAME AS NAME, LEVEL, EMPNO WORKER, SAL W_SAL, PRIOR ENAME MANAGER, PRIOR SAL MANAGER_SAL 
FROM EMP
CONNECT BY PRIOR EMPNO = MGR
AND ENAME <> 'SCOTT'
START WITH ENAME ='KING'
;
--최상위 급여와 아래 급여의 금액차이
SELECT LPAD(' ', LEVEL*2-2)|| ENAME AS NAME,
        LEVEL,EMPNO, MGR, SAL,
        CONNECT_BY_ROOT SAL - SAL AS DIFF
    FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
;
--ORDER 기준 X
SELECT LPAD( ' ',2*(LEVEL-1)) || ENAME, LEVEL
FROM EMP
CONNECT BY PRIOR EMPNO = MGR
START WITH ENAME = 'KING'
ORDER BY ENAME;

--ORDER 기준 O
SELECT LPAD( ' ',2*(LEVEL-1)) || ENAME, LEVEL
FROM EMP
CONNECT BY PRIOR EMPNO = MGR
START WITH ENAME = 'KING'
ORDER SIBLINGS BY ENAME;

SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 10;
;

SELECT SYSDATE + LEVEL-1, LEVEL
FROM DUAL
CONNECT BY LEVEL <= 1000;

-- Chapter 7 정렬 작업 속도 개선


--SORT UNIQUE
--SORT AGGREGATE
--SORT GROUP BY 
--PGA UNIQ

SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES      --HASH UNIQUE 91
;

SELECT DISTINCT DEPTNO
FROM EMP --HASH UNIQUE 91
ORDER BY 1
;

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

SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY 2
;
@XPLAN

ALTER SESSION SET "_GBY_HASH_AGGREGATION_ENABLED" = FALSE;

;
SELECT DISTINCT DEPARTMENT_ID
FROM EMPLOYEES
;

ALTER SESSION SET "_GBY_HASH_AGGREGATION_ENABLED" = TRUE;

SELECT /*+ NU_USE_HASH_AGGREGATION */ DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO;

SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY 2
;

SELECT EMPNO
FROM EMP;

;
@XPLAN

;
@IDX
;
SELECT EMPNO
FROM EMP
ORDER BY EMPNO;
;
SELECT CUST_ID, CUST_LAST_NAME, CUST_CREDIT_LIMIT
FROM CUSTS
ORDER BY CUST_CREDIT_LIMIT;

ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS
;

SHOW PARAMETER OPTIMIZER_MOD
;
--OLTP 옵티마이저 제어
ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS;
;

SELECT DEPARTMENT_ID
FROM EMPLOYEES;
;

SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY DEPTNO
ORDER BY 2;

CREATE INDEX CUST_CLIMIT_IX ON CUSTS (CUST_CREDIT_LIMIT) NOLOGGING; --리커버리 불가능 하지만 속도 향상 

--CTLR+ END  맨아래 데이터보기

--리두로그 발생 줄이기


SELECT CUST_ID, CUST_LAST_NAME, CUST_CREDIT_LIMIT
FROM CUSTS
ORDER BY CUST_ID;


DESC CUSTS
;
SELECT CUST_ID, CUST_LAST_NAME, CUST_CREDIT_LIMIT
FROM CUSTS
WHERE CUST_CREDIT_LIMIT IS NOT NULL
ORDER BY CUST_CREDIT_LIMIT;
@XPLAN

--READS 피지컬 니즈



;
SELECT cust_id, cust_last_name, cust_credit_limit
from custs
where cust_city ='Los Angeles'
order by cust_credit_limit;

create index cust_city_limit_idx on custs(cust_city, cust_credit_limit);

SELECT cust_id, cust_last_name, cust_credit_limit
from custs
where cust_city ='Los Angeles'
order by cust_credit_limit;
;
---------------------------------------------------------------------------------
| Id  | Operation                   | Name                | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |        |    90 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTS               |     90 |    90   (0)|
|*  2 |   INDEX RANGE SCAN          | CUST_CITY_LIMIT_IDX |     90 |     2   (0)|
---------------------------------------------------------------------------------
 
;
SELECT CUST_CITY, CUST_CREDIT_LIMIT, ROWID FROM CUSTS WHERE CUST_CITY ='Los Angeles' order by 1,2;

create index CUST_CITY_LIMIT_IX ON CUSTS(cust_city, cust_credit_limit) NOLOGGING;
;

SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID =50
ORDER BY SALARY;
;
CREATE INDEX EMP_NEW ON EMPLOYEES( DEPARTMENT_ID, SALARY);

;

;
SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID =50
ORDER BY SALARY;
;
@XPLAN

;
CREATE INDEX EMP_NEW2 ON EMPLOYEES( DEPARTMENT_ID, SALARY, EMPLOYEE_ID);

;
SELECT /*+ INDEX (E EMP_NEW2) */ EMPLOYEE_ID, DEPARTMENT_ID, SALARY
FROM EMPLOYEES E
WHERE DEPARTMENT_ID =50
ORDER BY SALARY;
;
@XPLAN

;
SELECT DEPARTMENT_ID, SALARY, EMPLOYEE_ID, ROWID FROM EMPLOYEES ORDER BY 1,2,3;

--성능을 고려한 튜닝



OPTIMIZER
QUERY TRANSFORMER : SUBQUERY -> JOIN UNNEST
COST ESTIMATOR


OLTP : INDEX 튜닝
JOIN -  NL 메모리 사용 X
MERGE
HASH 큰 메모리 사용


SORT 튜닝

성능상 NL 사용하는것이 더 좋음

OLAP : FULL SCAN 을 하기때문에 병렬, 파티션, BITMAP 인덱스 


조유미 ymchokr@naver.com

[멀티캠퍼스]  ★PDF 교재 파일 전달
PW : FA00XK
https://classnow.webex.com 

http://naver.me/FcgGEiOK
1111

25124970655
1111

Shared Pool
parse 컴파일

1) parse - Shared Pool (Library Cache)
SQL text
실행계획
p-code ==> Shared Pool 의 Library 
soft parse 
hard parse

2) bind

3) execute : DBC : data block의 복사본
   db_block_size = 8k

logical reads :
physical reads :
cache hit ratio 중요 : 

1000 1000
  100   50

server tuning 
sql tuning

4) fetch -- only select



실행계획

Chapter1 
인덱스 활용


인덱스의 주된 목표 
- select 를 빨리하기 위해서

B* Tree (발란스 트리구조)


select EMPNO, ROWIND from EMP order by EMPNO;


select * from TABLE (DBMS_XPLAN.DISPLAY_CURSOR); --실행계획


set linesize 200
set pagesize 100

--SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allSTATS LAST')) ;



SELECT * FROM table(dbms_xplan.display_cursor) ;


@c:\LABS\XPLAN
도구
-환경설정
-데이터베이스


@XPLAN -- 환경설정 도구-환경설정-데이터베이스-워크시트 -스크립트를 찾을기본 경로선택 ㅊ


리버스 키인덱스


실행계획


SELECT *
FROM EMP
WHERE DEPTNO = 20;

WHAT 
HOW -- PROGRAM
EXE PLAN


PARSE : 실행계획 : OPTIMIZER : RULE, COST
FULL TABLE SCAN : 5
INDEX : 

FTS

optimizer_mode = all_rows (default) : 전체처리를 가장 빠르기
first_rows : response time을 빠르기

full table scan : 5

cost : 

EXECUTE

FETCH
;


show parameter optimizer;


/**/ BLOCK Comment 힌트

힌트
INDEX scan

인덱스 스캔
인덱스 access

index scan
range

table random access HWM


시퀀셔 엑세스


인덱스를 제대로 못탈 경우 풀테이블 스캔이 더 나음


6가지 인덱스 스캔
-1.인덱스 유니크 스캔


SELECT *
 FROM EMP
WHERE ENAME ='SCOTT'

@XPLAN
SELECT ENAME, ROWID FROM EMP ORDER BY 1;


힌트 : index (테이블 명 인덱스명)
        index_rs (테이블명 인덱스명)
        index_rs (테이블명(컬럼명))
        index_rs_asc ( 테이블명(컬럼명))
        index_rs_desc (테이블명(컬럼명))


인덱스 풀스캔일 경우

/*+ index_ffs(e(employee_id)) */

select empno
from emp
order by 1
;
select EMPNO
from EMP;

;
@XPLAN
select empno, rowid from emp order by empno;

select * from employees;

select /*+ index_join (e EMPLOYEES_IDX02 EMPLOYEES_ID04 ) */
        department_id, JOB_id
  FROM employees e
where department_id = 80
 and job_id ='SA_REP'
;


인덱스 스킵 스캔
select /*+ index_ss (e empl_name ix) */ employee_id, first_name, last_name, salary,
department_id
from employees e
where first_name ='Steven';


인덱스 활용

인덱스 스캔 불가 사례
1. <>, !=, ^= 등 부정형의 비교
2. 인덱스 컬럼의 변형이 발생한 경우
3. Null 인 데이터를 조회하는 경우
4.like '%문자열' 사용하는경우



NESTED LOOP JOIN

9I NLJ PREFETCH
11g, NLJ_BATCHING 물리적읽기로 인한 지연을 줄이기 위해서 

DBC 데이터베이스 버퍼 캐쉬
인덱스 통해서 블럭단위를 읽을 때 



 

erwin이란?

 

DB를 설계 하기 위해 필요한 ERD개체 관계계다이어그램을 만드는 프로그램이다.

 

DB를 다루는 개발자들에게는 알면 좋은 프로그램인데

깔기 조금 어렵고 복잡하니 ㅜㅜ 구글링으로 열심히 깔아보자 ㅜㅜ

 

 

 

 

erwin을 깔았다 치고 실습하기>>>>>>>>>>>>>>>>>>>>>>>>>

 

 

 

 

 

                                                                     첫 화면

 

 

 

erwind을 시작하면 첫화면이 뜨는데 무족권 close 눌러주기

show tip 해제하면 뜨진 않치만 혹시 나중에 정보를 알 수 있으니 해제는 본인 맘 ㅎㅎ

 

 

 

첫 파일 생성은 [file] ->[new] 를 눌러줍니다.

 

 

 

 

 

파일 생성 창이 뜨는 데 여기선 무족권 logical/physical을 눌러주고

db는 본인이 사용하고 있는 데이터 베이스 프로그램으로 연동시켜주면 됩니다!

(저는 오라클이여서 오라클로 연동했어요~~~^^)

만약? 여기서 데이터베이스 연동을 잘못했다면 

걱정 ㄴㄴ physical로 바꾸어서 database ->choose database 누르면 바꿀 수 있습니다~~

 

 

 

 

 

 

파일 생성을 했다면 이제 내가 사용할 수 있는 도구를 만들어 주기 위해서 모델로 들어가서

 

 

 

 

 

 

전부 IE로 설정해 줍니당아아

 

 

 

 

그러면 도구가 이렇게 바뀌어용 바꾸고 나서 확인해보기

 

 

 

 

바꾸었다면 이제 테이블을 만드는 데요 테이블을 만들땐 저위엔 엔티티(빨간박스)를 눌러줘서 본인이

넣고싶은 테이블 명 > 코드 > 테이블 정보를 입력해 주시면 됩니다앙ㅇ

 

 

 

정보를 다 입력했다면 그에 맞는 값을 줘야겠죠?? 문자는 문자수에 맞게 숫자는 INT인지!!!!!

 

여기서 중요!!!! 무족권 PHYSICAL모드로 바꾸어야 가능합니다

논리적으로 된 상태에서 테이블을 만들고

피지컬로 바꾼 다음에 화면에 오른쪽 마우스 커서를 누르고 [table display] -> [column datatype]을 눌러주면

전이미 눌러놔서 보이는데 저렇게 정보마다 값을 매겨줘요

하지만 이건 본인 입맛에 바꾸기!!!!

문자열 값을 늘리고 싶거나 숫자로 바꾸고 싶으면 테이블을 더블 클릭하면 바꿀 수 있습니다.

 

 

 

 

요렇게요요용ㅇㅇㅇ 바꾸어주기

 

 

 

 

 

전 이렇게바꾸어줬어요 참고로 대소문자는 관련없습니다~~

 

 

 

 

 

 

다 만드셨으면 저장 해주시면 됩니다.

[file] ->[save as] 눌러주면 저장 창이뜹니다 씨드라이브에 파일하나만들어서 아예 거기에만 넣어주시는게 좋아요~~

 

 

 

 

 

저장이 다 되셨다면

 

 

 

 

 

 

툴을 저희 사용하는 걸로 맞춰야해요

[tool] -> [forward engineer] ->[schema generation]

 

 

 

 

 

여기서 원래 체크되있던 것을 해제해야 합니다!

1. column -> physical order 해제

 

 

 

 

 

 

2.trigger -> erwin,user 전부 체크되있던 것을 해제 해줍니다

 

 

그리고 preview를 눌러주면!

 

 

 

 

 

 

테이블 스키마가 자동 완성됩니다!!!

복붙해서 쓰셔도 되고 generate 눌러서 오라클 연동해서 쓰셔도 됩니다~~~~~~~

전 그냥 복붙해서 정보까지 넣어서 실행하는 편입니다~~

앞으로 테이블 일일이 다쓰지 마시고 erwin 하세요~~@@

 

출처  :https://hwantech.tistory.com/38

'DataBase > Oracle' 카테고리의 다른 글

성능을 향상 쿼리 튜닝  (0) 2023.09.26
성능 쿼리 작성  (0) 2023.09.25
오라클 엑사데이터  (0) 2023.08.10
SGA 메모리 구조의 이해  (0) 2023.08.10
[Oracle] 오라클 조인 방법 쉽게 설명 (ANSI JOIN, Oracle Join)  (0) 2023.08.10

http://www.databaser.net/moniwiki/pds/OracleServer/oracle-exadata-ch1.pdf

◆ 오라클 데이터베이스 서버구조

→ 오라클 데이터베이스 서버는 하나 이상의 오라클 데이터베이스 인스턴스로 구성된다.

→ 인스턴스는 메모리 구조와 백그라운드 프로세스로 구성된다.

→ 인스턴스가 시작될 때마다 SGA(System Global Area) 라는 공유 메모리 영역이 할당되고 백그라운드 프로세스가 시작된다.

◆ 오라클 데이터베이스 SGA(System Global Area) 메모리 구조

→ 데이터베이스 버퍼캐시 : 데이터베이스 파일에서 검색된 데이터 블록을 캐시에 저장한다.

→ 리두 로그 버퍼 : 물리적 파일에 기록하기 전에 Recovery 정보를 캐시에 저장한다.

→ Shared Pool : 세션 간에 공유할 수 있는 다양한 구성 요소를 캐시에 저장한다.

→ Java Pool : JVM(Java Virtual Machine) 내의 세션 별 Java 코드 및 데이터에 사용된다.

→ Stream Pool : Oracle Streams 에서 캡처 및 적용 프로세스에 대한 정보를 저장하는데 사용된다.

→ PGA(Program Global Area) : 서버 또는 백그라운드 프로세스에 대한 데이터 및 제어정보를 포함하는 메모리 영역이다.

◆ 데이터베이스 버퍼 캐시

→ 데이터 파일에서 읽은 데이터 블록의 복사본을 보관하는 SGA 부분이다.

→ 인스턴스에 동시에 연결하는 모드 유저는 데이터베이스 버퍼 캐시에 대한 액세스를 공유한다.

→ 특정 데이터 피스를 처음 사용해야 하는 경우 데이터베이스 버퍼 캐시에서 데이터를 검색한다. 이미 있는 데이터를 발견하는 경우(캐시적중) 메모리에서 데이터를 직접 읽을 수 있다.(빠름)

→ 데이터를 발견하지 못하면(캐시실패) 데이터에 액세스 하기 전에 데이터 블록을 디스크의 데이터 파일에서 캐시의 버퍼로 복사해야 한다.

◆ 리두(REDO) 로그 버퍼

→ 데이터베이스에 대한 변경 사항 관련 정보가 포함된 SGA의 순환버퍼 이다.

→ Insert, Update, Delete, Create, Alter, Drop 작업에 따른 데이터베이스의 변경 사항을 재생성하데 필요한 정보를 포함한다. 필요한 경우 데이터베이스 복구에 사용된다.

→ 서버 프로스세에 의해 유저의 메모리 영역에서 리두로그 버퍼로 복사된다.

→ 연속되는 순차적 공간을 차지한다.

→ LGWR(로그 기록자) 백그라운드 프로세스는 리두로그버퍼를 디스크의 활성 리두 로그(또는 파일 그룹)에 기록한다.

→ CPU 수에 따라 리두 로그 버퍼가 두 개 이상 있을 수 있으며 자동으로 할당된다.

◆ Shared Pool

오라클 데이터베이스는 데이터 딕셔너리에 자주 액세스 하므로 두 곳의 특수 위치에 딕셔너리 정보를 보관하도록 지정한다.(라이브러리 캐시, 데이터 딕셔너리 캐시)

→ 라이브러리 캐시 : SQL문, PL/SQL, 프로시저 및 패키지의 공유 가능한 부분이 포함된다.

→ 데이터 딕셔너리 캐시(행 캐시) : 데이터베이스에 대한 참조 정보를 포함하는 데이터베이스 테이블의 모음이다.

→ 제어구조 : 반드시 필요한 Lock 구조를 포함한다.

◆ DML 문 처리

1. 서버 프로세스는 해당 명령문을 받아 라이브러리 캐시에서 유사한 SQL 문이 포함된 공유 SQL영역이 있는지 확인한다.

발견되면 기존 공유 SQL영역이 명령문 처리에 사용된다. 발견되지 않을 경우 구문분석이 시작되고 새 공유 SQL 영역이 해당

명령문에 대해 할당된다.

2. 데이터 및 언두 세그먼트 블록이 버퍼 캐시에 없는 경우, 서버 프로세스는 데이터 파일에서 버퍼캐시로 읽어 들인다.

수정해야 할 행은 lock 한다.

3. 서버 프로세스는 데이터 버퍼에 수행될 변경 사항과 언두 변경 사항을 기록한다. 이러한 변경사항은 버퍼가 수정되기 전에

리두 로그 버퍼에 기록된다. 이것을 먼저 쓰기 로깅(write-ahead logging) 이라고 한다.

4. 언두 세그먼트 버퍼에서는 수정되기 전의 데이터 값이 들어 있다. 언두 버퍼는 필요한 경우 DML문이 롤백될 수 있도록

데이터의 이전 이미지를 저장하는데 사용된다. 데이터버퍼는 새로운 데이터 값을 가지고 있다.

◆ COMMIT 처리

1. 서버 프로세스는 SCN(시스템 변경 번호)과 함께 커밋 레코드를 리두 로그 버퍼에 배치한다. SCN은 일정하게 증가하며

데이터베이스 내에서 고유한 번호이다. SCN은 데이터 파일에서 데이터를 검색할 때 데이터를 동기화하고 읽기 일관성을

제공하기 위한 내부 시간 기록으로 오라클 데이터베이스에 의해 사용된다.

2. LGWR 백그라운드 프로세스는 커밋 레코드를 포함하여 모든 리두 로그 버퍼 항목을 리두로그파일에 연속적으로 기록한다.

이 시점부터 인스턴스 실패가 발생할 경우에도 변경사항이 손실되지 않도록 보장된다.

3. 수정된 블록이 SGA에 계속 존재하거나 해당 블록을 수정 중인 세션이 없을 경우 데이터베이스는 블록에서 lock 관련

트랜잭션 정보를 제거한다. 이것을 커밋삭제라고 부른다.

◆ Large Pool

대규모 메모리 할당을 제공하기 위해 구성된다. 아래 경우에 권장된다.

→ 병렬 실행 : Parallel Query 사용시

→ 오라클 데이터베이스 백업 및 복구 작업 : Recovery Manager 사용시

Large Pool을 사용하면 크로 작은 할당에서 동일한 메모리 영역을 공유하는 것과 관련하여 발생 가능한 단편화 문제를 피할 수 있다. Shared Pool과 달리 Large Pool에는 LRU list가 없다.

◆ Java Pool 및 Streams Pool

→ Java Pool 메모리 : JVM의 모든 세션 별 Java 코드 및 데이터에 사용된다.

→ Streams Pool 메모리 : 데이터베이스 내에서 또는 데이터베이스 간에 데이터 스트림의 데이터, 트랜잭션 및 이벤트를 전달하고 관리한다.

◆ PGA(Program Global Area)

→ 서버프로세스가 클라이언트 프로세스를 대신하여 요청을 수행하기 위해 사용하는 임시적인 메모리 영역

→ 작업이 끝나면 클라이언트 요청에 대한 세부정보를 PGA에 보관한 다음, PGA 영역을 클라이언트에게 넘겨준다

◆ 백그라운드 프로세스

DBWR(데이터베이스 기록자) : 데이터베이스 버퍼 캐시의 수정된 (더티)버퍼를 비동기적으로 디스크에 기록한다.

LGWR(로그기록자) : 디스크에 있는 리두 로그 파일에 로그 버퍼의 Recovery 정보를 기록한다.

CKPT(체크포인트 프로세스) : 콘트롤 파일의 체크포인트 정보와 각 데이터 파일 헤더를 기록한다.

SMON(시스템모니터) : 인스턴스 시작 시 Recovery를 수행하고 사용되지 않은 임시 세그먼트를 정리한다.

RCBG(결과 캐시 백그라운드) : Shared Pool에 저장된 결과 캐시를 유지 관리하는데 사용된다.

CJQ0(작업 큐 프로세스) : 스케줄러를 통해 일괄 처리에 사용되는 유저 작업을 실행한다.

ARCn(아카이버 프로세스) : 로그스위치가 발생한 후에 리두 로그 파일을 지정된 저장장치로 복사한다.

QMNn(큐 모니터 프로세스) : Oracle Streams 메시지 큐를 모니터한다.

MMON(관리 효율성 모니터링 프로세스) : 관리 효율성 관련 백그라운드 작업을 수행한다.

MMAN(메모리 관리자 백그라운드 프로세스) : SGA 및 PGA 메모리 구성 요소를 자동으로 관리하는데 사용한다.

◆ 자동 공유 메모리 관리(Automatic Shared Memory Management)

→ 10g 이후 사용할 수 있게 되었다.

→ SGA_TARGET파라미터를 사용한다.

→ SGA 구성을 단순화하여 모든 SGA 구성요소에 사용되는 전체 메모리 양을 간편하게 지정할 수 있도록 할 수 있다.

→ 오라클 데이터베이스는 작업 로드 요구 사항에 따라 자동으로 튜닝된 구성 요소 간에 메모리를 주기적으로 다시 분산한다.

→ 이 기능을 사용하려면 STATISTICS_LEVEL 을 TYPICAL 또는 ALL로 설정해야 한다.

◆ 자동 SQL 실행 메모리 관리

→ PGA 작업 영역에 메모리를 자동으로 할당 하는 모드를 제공한다.

→ PGA_AGGREGATE_TARGET 파라미터를 사용한다.

→ 인스턴스 세션의 PGA 영역에 할당되어야 할 전체 메모리 양을 지정할 수 있다.

→ 자동 모드에서 메모리 사용량이 많은 연산자(정렬 및 Hash Join)에 의해 상용되는 작업 영역은 자동 및 동적으로 조정될 수 있다.

→ 전체적인 시스템 성능이 극대화되고 가용 메모리가 query 사이에 더욱 효율적으로 할당되어 처리량과 응답 시간을 모두 최적화 할 수 있다. 특히 메모리 활용도가 개선되어 로드가 많은 경우의 처리량이 향상된다.

→ 정렬 또는 Hash Join 연산자는 작업 로드가 계속해서 변경되기 때문에 자동 PGA 메모리 관리를 활성화된 상태로 유지하는 것 좋다.

◆ 자동 메모리 관리

→ 인스턴스의 다양한 메모리 영역 크기는 SQL 처리 속도에 직접적인 영향을 미친다. 자동 메모리 관리(Automatic Memory Management)를 사용하면 필요한 작업 로드 메모리 양에 맞게 자동으로 각 메모리 구성 요소의 크기가 적용된다.

→ MEMORY_TARGET 초기화 파라미터를 사용한다.

→ MMAN 백그라운드 프로세스에서 자동으로 SGA 내부 구성 요소 간에, 그리고 SGA와 집계PGA 간에 필요한 메모리를 다시 분산하여 대상 메모리 크기를 튜닝한다.

→ 두 개의 프로세스(MMON, MMAN)으로 구현된 SGA 메모리 Broker를 사용한다.

→ 통계 및 메모리 Advisory 데이터는 MMON을 통해 메모리에서 주기적으로 캡처된다. 그 후 이 두 개의 프로세의 결정에 따라 메모리 구성 요소 크기가 조정된다.

◆ 데이터베이스 저장 영역 구조

→ 콘트롤 파일 : 데이터베이스 자체에 대한 데이터(즉, 물리적 데이터베이스 구조정보) 를 포함한다. 이 파일은 매우

중요하다. 이 파일이 없으면 데이터베이스 내의 데이터에 액세스할 때 데이터 파일을 열 수 없다.

→ 데이터 파일 : 데이터베이스의 유저 또는 응용 프로그램 데이터, 메타데이터 및 데이터 딕셔너리를 포함한다.

→ 온라인 리두 로그 파일 : 데이터베이스의 인스턴스 Recovery를 가능하게 한다. 데이터베이스 서버가 손상되었지만 해당

데이터 파일은 손실되지 않은 경우 인스턴스는 이 파일을 사용하여 데이터베이스를 Recovery할 수 있다.

→ 파라미터 파일 : 인스턴스 시작 시 어떻게 인스턴스를 구성할 지 정의하는데 사용된다.

→ Password file : sysdba, sysoper 및 sysasm이 데이터베이스에 원격으로 연결하여 관리 작업을 수행할 수 있도록 한다.

→ 백업파일 : 데이터베이스 Recovery 에 사용된다. 백업 파일은 일반적으로 Media Failure 또는 User Error 로 원본 파일이

손상되었거나 삭제되었을 경우에 복원한다.

→ 아키이브된 리두 로그 파일 : 인스턴스에 의해 생성되는 데이터 변경(리두)에 대한 기록을 지속적으로 포함한다. 이 파일과

데이터베이스 파일, 두 개를 사용하면 손실된 데이터파일을 Recovery 할 수 있다. 즉 복원된 데이터 파일의 Recovery를

가장 최신의 데이터로 가능하게 하는 역할을 한다.

→ Trace file : 각 서버와 백그라운드 프로세스는 Tracle File에 정보를 기록한다. 시스템 오류가 프로세스에서 감지되면

프로세스는 오류에 대한 정보를 해당 Trace File에 덤프한다. Trace File에 기록된 정보 중 일부는 개발자가 사용하고 일부는

오라클 고객지원센터에서 사용하게 된다.

→ Alert Log File : 특수 Trace 항목으로, 데이터베이스의 Alert Log에는 메시지와 오류가 시간순으로 기록되어 있다.

각 인스턴스에는 한 개의 Alert log file이 있다. 이 파일을 정기적으로 검토하는 것이 좋다.

◆ 논리적 및 물리적 데이터베이스 구조

→ 스키마 : 데이터베이스 유저가 소유하는 데이터베이스 객체의 모음으로 논리적 개념이다. 스키마 객체에는 테이블, 뷰, 시퀀스, 내장 프로시저, 동의어, 인덱스, 클러스터, 데이터베이스 링크 등의 구조가 있다.

→ 데이터 블록 : 가장 작은 세분성 레벨에서 오라클 데이터베이스의 데이터는 데이터 블록에 저장된다. 하나의 데이터 블록은 디스크에서 특정 바이트 수의 물리적 데이터베이스 공간에 해당한다. 데이터 블록 크기는 생성시 각 테이블스페이스에 대해 지정된다. 데이터 베이스는 사용 가능한 데이터베이스 공간을 Oracle 데이터 블록으로 사용 및 할당한다.

→ Extent : 그 다음 레벨의 논리적 데이터베이스 공간이다. Extent는 단일 할당으로 얻은 일정수의 연속적인 데이터 블록으로, 특정 유형의 정보를 저장하는데 사용된다.

→ 세그먼트 : 그 다음 레벨의 논리적 데이터베이스 공간이다. 세그먼트에는 다음과 같은 유형이 있다.

세그먼트
내용
데이터 세그먼트
클러스터화 되지 않은 각각의 비인텍스 구성 테이블(Non-index organized Table)에는 데이터세그먼트가 있다.
(단, External Table, Global 임시테이블(Temporary Table), Partition 테이블은 예외)
각 클러스터는 데이터세그먼트를 가진다. 클러스터에 있는 모든 테이블의 데이터는 클러스터의 데이터세그먼트에 저장된다.
인덱스 세그먼트
각 인덱스는 해당 데이터를 모두 저장하는 인덱스 세그먼트를 가진다. Partition 인덱스의 경우 각 Partition은 인덱스 세그먼트를 가진다.
언두 세그먼트
인스턴스 한 개 당 하나의 UNDO 테이블스페이스가 생성된다. 이 테이블스페이스에는 언두 정보를 임시로 저장하기 위한 다수의 언두 세그먼트가 포함되어 있다.
언두세그먼트 정보는 데이터베이스 Recovery 중 유저에게 커밋되지 않은 트랜잭션을 롤백하기 위해 읽기 일관성 데이터베이스 정보를 생성하는데 사용된다.
임시 세그먼트
SQL 문에서 실행을 완료할 임시 작업 영역이 필요할 때 생성된다. 모든 유저에 대해 기본 임시 테이블스페이스를 지정하거나 데이터베이스 차원에서 사용할 기본 임시 테이블스페이스를 지정한다.(Temp Tablespace)

오라클 데이터베이스는 동적으로 공간을 할당한다. 세그먼트의 기존 Extent가 가득 차면 다른 Extent가 추가된다. Extent는 필요에 따라 할당되므로 세그먼트의 Extent는 디스크 상에서 인접해 일을 수도 있고 그렇지 않을 수도 있다.

◆ 세그먼트, Extent 및 블록

→ 데이터 블록은 데이터베이스의 가장 작은 I/O 단위이다.

→ 데이터베이스에서 OS(운영체제)의 데이터 블록 집합을 요청하면 OS는 이를 저장 장치의 디스크 블록이나 실제 파일시스템으로 매핑한다. 따라서 데이터베이스에 있는 데이터의 물리적 주소를 알 필요는 없으며, 데이터 파일을 여러 디스크에 스트라이핑 또는 Mirroring할 수도 있다.

→ 데이터 블록의 크기는 데이터베이스 생성 시 설정할 수 있다. 대부분의 경우 기본 크기는 8KB 가 적합하다.

큰 데이블 및 인덱스를 사용하는 데이터웨어하우스(DW) 응용프로그램을 지원하는 경우 블록의 크기를 크게 지정하는 것이 효과적일 수 있다.

→ 읽기와 쓰기가 무작위로 발생하는 경우 데이터블록 크기를 작게 지정하는 것이 유용할 수 있다. 최소 데이터블록 크기는 OS에 따라 다르다. 최소 Oracle 블록 크기는 2KBm인데 이렇게 작은 크기는 거의 사용되지 않는다.

◆ SYSTEM 및 SYSAUX 테이블 스페이스

→ 각 오라클 데이터베이스에는 생성시 자동으로 생성되는 SYSTEM 테이블스페이스 및 SYSAUX 테이블스페이스가 포함되어 있어야 한다.

→ 테이블스페이스는 온라인(액세스 가능) 상태이거나 오프라인(액세스 불가) 상태일 수 있다. 테이블스페이스가 열려 있는 경우 SYSTEM 테이블스페이스는 항상 온라인 상태이다.

→ SYSTME 테이블스페이스는 데이터 딕셔너리 정보 등 데이터베이스의 핵심 기능을 지원하는 테이블을 저장한다.

→ SYSAUX 는 SYSTEM의 보조 테이블스페이스 이다. 테이블스페이스 Recovery 를 수행하기 위해 오프라인으로 전환할 수 있지만 SYSTEM 테이블스페이스의 경우 불가능하다. 그리고 두 테이블스페이스 중 어느 한 쪽도 읽기 전용으로 설정할 수 없다.

 

 

출처 : https://blog.naver.com/qowndyd/220995596404

오라클에서 조인을 할 때 오라클 조인(Oracle Join)과 안시 조인(ANSI JOIN)을 사용할 수 있다. 오라클 9i 까지는 오라클 조인만 사용할 수 있으며, 오라클 10g부터는 안시 조인을 추가로 사용할 수 있다. 최근 구축되는 시스템은 대부분 안시 조인을 사용하지만, 과거에 구축되어 있는 시스템은 오라클 조인을 많이 사용하고 있기 때문에 오라클 조인 방식도 꼭 알고 있어야 한다.

 

안시 조인과 오라클 조인 비교 (INNER JOIN)

 

조인은 크게 위의 5가지 정도로 분류할 수 있다. 조인 (INNER JOIN) 아우터 조인 (LEFT OUTER JOIN)은 아주 많이 사용하니, ANSI JOIN과 Oracle Join을 비교하여 두 가지 방식 모두 익혀 두어야 한다.

 

조인 (INNER JOIN)

안시 조인 (ANSI JOIN)

SELECT a.empno
     , a.ename
     , a.deptno
     , b.dname
  FROM emp a
 INNER JOIN dept b
    ON a.deptno = b.deptno
 WHERE a.job = 'MANAGER'

 

 

 

위의 예제는 EMP 테이블과 DEPT 테이블을 조인(내부 조인)하여 DEPT 테이블의 DNAME(부서명)을 조회한 쿼리이다.

조인(INNER JOIN)은 메인 테이블과 조인 테이블에 조인 칼럼(deptno)의 값이 동시에 존재해야 조회가 된다.

 

 

 

위의 조인 구조를 보면 EMP 테이블의 "GENT"는 DEPT 테이블과 조인이 안되었기 때문에 데이터 조회에서 제외된다.

 

▼ 위의 ANSI JOIN을 Oracle Join으로 변경하면 아래와 같다.

 

오라클 조인 (Oracle Join)

SELECT a.empno
     , a.ename
     , a.deptno
     , b.dname
  FROM emp a
     , dept b
 WHERE a.job = 'MANAGER'
   AND a.deptno = b.deptno

 

 

 

오라클 조인은 조인 칼럼(deptno) 조건을 WHERE 절에 작성하면 된다.

 

아우터 조인 (LEFT OUTER JOIN)

안시 조인 (ANSI JOIN)

SELECT a.empno
     , a.ename
     , a.deptno
     , b.dname
  FROM emp a
  LEFT OUTER JOIN dept b
    ON a.deptno = b.deptno
 WHERE a.job = 'MANAGER'

 

 

 

위의 예제는 EMP 테이블과 DEPT 테이블을 아우터 조인(외부 조인)하여 DEPT 테이블의 DNAME(부서명)을 조회한 쿼리이다. 아우터 조인(OUTER JOIN)은 조인 테이블의 값이 존재하지 않아도 메인 테이블의 데이터가 조회된다. 조인 테이블의 값을 가져오지 못하면 NULL로 표시된다.

 

(메인 테이블) LEFT OUTER OIN (조인 테이블) 왼쪽 테이블이 메인 테이블이 된다.

 

 

 

아우터 조인은 메인 테이블의 데이터가 모두 조회되고, 조인 테이블의 값을 참조하여 조인이 되었을 경우 해당 값(dname)을 표시하고 조인이 되지 않았을 경우 NULL로 표시한다. 조인(INNER JOIN)인 처럼 조인이 되지 않았다고 조회에서 제외하지 않는다.

 

▼ 위의 ANSI JOIN을 Oracle Join으로 변경하면 아래와 같다.

 

오라클 조인 (Oracle Join)

SELECT a.empno
     , a.ename
     , a.deptno
     , b.dname
  FROM emp a
     , dept b
 WHERE a.job = 'MANAGER'
   AND a.deptno = b.deptno(+)

 

 

 

조인 조건을 ON절에서 WHERE 절로 위치가 변경되었고 조인 칼럼에 (+)이 붙어있다.

조인 칼럼에 (+)를 붙이면 해당 칼럼의 테이블이 조인 테이블이 된다.

 

아우터 조인의 핵심은 메인 테이블의 데이터는 무조건 조회가 되고 조인 테이블은 참조 용도로만 사용된다.

 

아우터 조인 (RIGHT OUTER JOIN)

안시 조인 (ANSI JOIN)

SELECT a.empno
     , a.ename
     , a.deptno AS emp_deptno
     , b.deptno
     , b.dname
  FROM emp a
 RIGHT OUTER JOIN dept b
    ON a.deptno = b.deptno
 WHERE (a.job = 'MANAGER'
     OR a.job IS NULL)

 

 

 

RIGHT OUTER JOIN은 LEFT OUTER JOIN과 동일한 아우터 조인이며, 다른 점은 오른쪽(RIGHT) 테이블이 메인 테이블이 된다. (조인 테이블) RIGHT OUTER OIN (메인 테이블)

 

DEPT 테이블이 메인 테이블이기 때문에 DEPT 테이블의 모든 데이터가 조회되고 EMP 테이블은 참조 용도로만 사용된다. 메인 테이블의 위치만 바뀌었을 뿐 LEFT OUTER JOIN과 조인 방법은 동일하다. 특별한 경우가 아니면 RIGHT OUTER JOIN은 사용하지 않는 것이 차후 쿼리문을 분석할 때 가독성을 좋게 할 수 있다.

 

 

 

DEPT 테이블이 메인 테이블이 되어서 EMP 테이블과 아우터 조인이 된다.

 

▼ 위의 ANSI JOIN을 Oracle Join으로 변경하면 아래와 같다.

 

오라클 조인 (Oracle Join)

SELECT a.empno
     , a.ename
     , a.deptno
     , b.deptno
     , b.dname
  FROM emp a
     , dept b
 WHERE a.deptno(+) = b.deptno
   AND (a.job = 'MANAGER'
     OR a.job IS NULL)

 

 

 

오라클 조인에서는 RIGHT OUTER JOIN이라는 용어는 사용 안 하지만, 조인 칼럼의 (+) 위치를 변경 함으로써 RIGHT OUTER JOIN과 비슷하게 만들 수 있다.

 

RIGHT OUTER JOIN은 개념을 이해하고 있으면 된다. 아우터 조인은 LEFT OUTER JOIN을 확실히 익혀 두도록 하자.

 

크로스 조인 (CROSS JOIN)

안시 조인 (ANSI JOIN)

SELECT a.empno
     , a.ename
     , b.deptno
     , b.dname
  FROM emp a
 CROSS JOIN dept b
 WHERE a.job = 'MANAGER'

 

 

 

크로스 조인은 두 테이블의 모든 데이터를 서로 한 번씩 조인을 한다고 생각하면 된다.

CROSS JOIN 절에 테이블 작성하고 메인 테이블과 조인 칼럼을 연결하지 않는다.

 

 

 

두 테이블의 데이터가 서로 한 번씩 조인이 되기 때문에, 사원 테이블 (4행) * 부서 테이블(3행) = 총 12행이 조회된다.

크로스 조인은 아주 가끔씩 사용하기 때문에 개념을 이해하고 있으면 차후 도움이 될 수도 있다.

 

▼ 위의 ANSI JOIN을 Oracle Join으로 변경하면 아래와 같다.

 

오라클 조인 (Oracle Join)

SELECT a.empno
     , a.ename
     , b.deptno
     , b.dname
  FROM emp a
     , dept b
 WHERE a.job = 'MANAGER'
   AND b.deptno IN (10, 20, 30)

 

 

 

오라클 조인에서 크로스 조인을 하기 위해서는 FROM 절에 테이블을 작성하고 WHERE 절에서 조인 칼럼을 작성하지 않으면 두 개의 테이블이 서로 크로스 조인된다.

 

풀 아우터 조인 (FULL OUTER JOIN)

안시 조인 (ANSI JOIN)

SELECT a.empno
     , a.ename
     , a.deptno
     , b.dname
  FROM emp a
  FULL OUTER JOIN dept b
    ON a.deptno = b.deptno

 

 

 

두 개의 테이블을 조인하여 조인된 데이터는 조인된 상태로 조회되고, 조인 안된 데이터는 조인이 안된 상태로 조회된다. 조인되어도 조회되고 조인이 안되어도 모두 조회된다고 생각하면 된다.

 풀 아우터 조인은 ANSI JOIN에서만 사용 가능하고 Oracle Join에서는 사용할 수 없다. 자주 사용하는 조인 방법은 아니기 때문에 개념만 이해하고 있으면 좋을 듯하다.

 

 

오라클 쿼리에 익숙하지 않으면 가장 어려운 부분이 조인(Join)이다. 아래의 예제 파일을 다운로드하여 여러 번 반복해서 쿼리문을 작성해 보는 것이 가장 빨리 이해하는 방법일 것이다.

 

 

출처 : https://gent.tistory.com/469

BEGIN
  FOR cur_rec IN (  
    SELECT OBJECT_NAME, OBJECT_TYPE 
      FROM USER_OBJECTS
     WHERE OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION')
       AND STATUS != 'VALID'
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' ' || cur_rec.object_name || ' COMPILE';
    EXCEPTION
      WHEN OTHERS        
      THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);     
    END;
  END LOOP;
END;
 
DBA 권한을 가지고 있다면 USER_OBJECTS 대신에 DBA_OBJECTS 를 사용하여 모든 스키마의 오브젝트를 재컴파일할 수 있다.
 
출처 : https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=kurishin&logNo=60066147708

+ Recent posts