불친절한 SQL 프로그래밍 정규화(오라클 데이터베이스, SQL)

3. 오라클 데이터베이스

3.1 개념 : 오라클 사에서 개발한 ORDBMS 제품. 오라클의 사전적 의미는 예언자. WOW!

 

3.1.1 사용자(user) : 데이터베이스에 로그인할 수 있는 계정

3.1.2 오브젝트(object) : 논리적인 데이터 구조. 사용자(user)에 종속. 사용자에 종속된 오브젝트의 논리적인 집합이 스키마(schema)

3.1.3 테이블 : 데이터를 구성하는 기본 단위. 행(row), 열(column) 

3.1.4 데이터타입 : 열은 데이터타입 지정 가능 (자세한건 20장에서)

 

3.1.5 데이터 무결성(data integrity) : 데이터의 정확성과 일관성이 유지되고 있는 상태 

 - 개체 무결성 : 엔터티의 인스턴스가 속성이나 속성의 조합으로 식별되어야 함

 - 참조 무결성 : 자식 엔터티의 외래 식별자가 부모 엔터티의 기본 식별자에 존재해야 함

 - 범위 무결성 : 속성값이 지정한 범위에 유효해야 함

 - 사용자 정의 무결성 : 개체/참조/범위 무결성에 속하지 않은 무결성 

 

3.1.6 트랜잭션(transaction) : 함께 수행해야 하는 작업의 논리적 단위, ACID 라는 4가지 특징(자세한건 19장에서)

A 원자성(Atomicity), C 일관성(Consistency), I 독립성(Isolation), D 지속성(Durability)

 

3.1.7 정적 데이터 딕셔너리 뷰 (static data dictionary view) : 데이터베이스의 메타테이터를 조회할 수 있는 읽기 전용 뷰

 - DICTIONARY : 정적 데이터 딕셔너리 뷰에 대한 정보를 제공

3.1.8 동적 성능 뷰 (dynamic performance view) : 데이터베이스의 동적 정보를 조회할 수 있는 읽기 전용 뷰

 - V$FIXED_TABLE : 동적 성능 뷰와 동적 성능 테이블에 대한 정보를 제공

 

 

3.2 구조

파일 / 메모리 / 프로세스 / 프로그램 / 운영체제 

 

3.2.1 데이터베이스와 하나 이상의 인스턴스 

 - 데이터베이스 : 데이터를 저장하는 파일의 모음

 - 인스턴스 : SGA(백그라운드 프로세스와 서버 프로세스의 공유 메모리영역) 와 백그라운드 프로세스로 구성

 - 오라클 데이터베이스는 single 서버라고 한다. instance 1개와 database 1개로 구성

 - RAC(Real Application Clusters) 은 2개이상의 instance와 database 1개

 - v$database에서 데이터베이스에 대한 정보 조회 가능 

 - v$instance에서 인스턴스에 대한 정보를 조회 가능

 

3.2.2 프로세스 구조

 - 백그라운드 프로세스는 인스턴스가 시작될 때! 생성

 - 서버 프로세스는 사용자가 데이터베이스 서버에 접속할 때! 생성

 

3.2.3 메모리 구조

SGA(System Global Area) : 백그라운드 프로세스와 서버 프로세스의 공유 메모리 영역, 인스턴스가 시작될 때 할당되고 종료될 때 해제

PGA(Program Global Area) : 서버 프로세스의 독점 메모리 영역, 서버 프로세스가 생성될 때 할당되고 종료될 때 해제

 

SGA 의 메모리구조 (이 요소들에 알고 싶으면 p.36)

 - shared pool : library cache, data dictionary cache

 - large pool : redo log buffer, database buffer cache

 

PGA는 세션 정보와 SQL 관련 데이터를 저장 : SQL work area, session memory, private SQL area

 

 

3.2.4 저장 구조

오라클 데이터베이스는 물리 저장 구조와 논리 저장 구조로 분리되어있음

물리저장구조를 변경해도 논리저장구조에 영향 X 

 

3.2.4.1 물리 저장 구조 (physical storage structure)

물리저장구조는 파일로 저장되며 OS에서 확인 가능 archived redo log 파일 처럼 DB에 속하지 않는 파일도 존재

물리 저장 구조의 파일

 - data file : 세그먼트 (테이블, 인덱스) 가 저장되는 파일

 - control file : 데이터베이스의 물리적인 구성 요소에 대한 제어 파일

 - online redo log : 데이터베이스의 변경 사항을 저장하는 파일 세트

 - archived redo log : online redo log 의 오프라인 사본 

 

3.2.4.2 논리 저장 구조 (logical storage structure) 

오라클 DB 내부에서 관리. 세그먼트는 하나의 테이블스페이스에 속하고, 다수의 데이터 파일에 저장 가능 

논리 저장 구조의 단위

 - 블록 (data block) : 데이터를 저장하는 가장 작은 논리적 단위

 - 익스턴트 (extent) : 논리적으로 연속된 data block 의 집합 (공간을 확장하는 단위)

 - 세그먼트 (segment) : 오브젝트에 할당된 extent 의 집합

 - 테이블스페이스 (table space) : 세그먼트를 포함하는 데이터베이스의 저장 단위 

-> 행은 블록에 저장된다. 하나의 행을 조회하더라도 행이 저장된 블록 전체를 읽어야 한다.

 

3.2.5 네트워크 구조 

리스너 (listener) : 데이터베이스 서버에서 동작하는 프로그램. 오라클 데이터베이스의 접속을 처리

listener.ora 파일은 리스너의 설정 파일

tnsnames.ora 파일은 클라이언트의 설정 파일. 데이터베이스 서버의 접속 정보가 저장 

 

동작 순서

1) 클라이언트 프로세스가 데이터베이스 서버에 접속을 요청

2) 리스너가 클라이언트 프로세스의 접속 요청을 수락하고 서버 프로세스를 생성

3) 리스너가 서버 프로세스와 클라이언트 프로세스를 연결한 후 다른 접속 요청을 대기

 

3.2.6 애플리케이션 구조 

 - local : 데이터베이스 서버 내에서 직접 접속

 - client-server : 클라이언트에서 DB 서버로 접속

 - multitier : 애플리케이션서버를 통해서 DB 서버에 접속, 일정 수 이상의 커넥션을 유지할 수 있도록 커넥션 풀을 사용. 커넥션 풀을 사용하면 서버 프로세스 생성과 PGA 할당에 대한 부하를 경감 시킬 수 있음.

 

커넥션과 세션 

커넥션(connection) : 클라이언트 프로세스와 데이터베이스 인스턴스 사이의 물리적 통신 경로

세션(session) : 데이터베이스에 로그인한 사용자의 상태를 나타내는 논리적 객체 

커넥션을 통해 세션이 생성 

 

 

4. SQL (Structured Query Language) 관계형 데이터베이스의 표준 언어

4.1 역사

원래이름은 SEQUEL이었는데 상표권 문제로인해 SQL 로 변경 

ANSI 표준 SQL 을 보통 따라가는데 RDBMS 마다 문법이 조금씩 다름

 

4.2 특징 4.3 종류

 

4.4 처리과정 

SQL -> (syntax check -> semantic check -> shared pool check) 과정이 parsing -> 바로 execution 되면 soft parse.

(optimization -> row source generation) 과정을 거쳐 execution이 되면 hard parse

 

 - syntax check : SQL 문법 검사

 - semantic check : 오브젝트와 권한의 존재 유무를 검사

 - shared pool check : shared pool의 library cache에 SQL이 저장되어 있는지 검사

 - optimization : SQL의 쿼리 변환과 최적화를 수행

 - row source generation : SQL 엔진에 의해 수행될 로우 소스를 생성 

 

4.5 수행과정

SELECT 문이 더 짧음 (DML : Data Manipulation Langauage 인 INSERT, UPDATE, DELETE, MERGE) 보다!!

SELECT 

1) 클라이언트 프로세스가 서버 프로세스로 select 문을 전달

2) buffer cache에 필요한 데이터 블록이 있는지 확인 

3) 없으면 data file에서 데이터 블록을 읽어 buffer cache에 저장

4) 결과 집합을 클라이언트 프로세스에 전달

 

DML 문

1) 클라이언트 프로세스가 서버 프로세스에 DML 문을 전달

2) 언두 세그먼트를 할당하고 buffer cache에 필요한 블록이 있는지 확인

3) 없으면 data file 에서 블록을 읽어 buffer cache에 저장하고 변경할 블록에 락을 설정

4) 데이터 블록과 언두 블록의 변경 사항을 redo log buffer 에 기록

5) 언두 블록에 변경 전 데이터를 저장하고 데이터 블록을 변경 

6) 변경 결과를 클라이언트 프로세스에 전달 

* undo segment는 변경 전 데이터가 저장되는 테이블

 

COMMIT 문

1) 클라이언트 프로세스가 서버 프로세스로 commit 문을 전달

2) 서버 프로세스가 LGWR 백그라운드 프로세스로 처리를 요청

3) redo log buffer 가 online redo log  파일에 모두 저장되면 변경된 블록의 락을 해제

4) 완료 여부를 클라이언트 프로세스에 전달 

변경된 블록이 data file 에 모두 저장되지 않았더라도 redo log buffer 가 online redo log 파일에 모두 저장되었다면 완료여부를 반환