sqld 개인공부 정리 part1

모델링 : 사람, 사물, 개념 등의 표기법과 규칙을 가지고 표기하는 것 자체를 의미, 즉 모델을 만들어가는 일 자체를 모델링으로 정의할 수 있다.

 

모델링의 특징 : 추상화, 단순화, 명확화의 3대 특징으로 요약할 수 있다.

추상화 : 현실세계를 일정한 형식에 맞추어 표현을 한다는 의미

단순화 :  복잡한 현실세계를 약속된 규약에 의해 제한된 표기법이나 언어로 표현하여 쉽게    

               이해할 수 있도록 하는 개념

명확화 :  누구나 이해하기 쉽게 하기 위해 대상에 대한 애매모호함을 제거하고 정확하게 

               현상을 기술

 

모델링 관점 : 데이터관점, 프로세스관점, 상관관점

 

데이터 관점 : 업무가 어떤 데이터와 관련이 있는지 또는 데이터간의 관계는 무엇인지에 대한 모델링

프로세스 관점 : 업무가 실제로 하고 있는 일은 무엇인지 또는 무엇을 해야 하는지를 모델링하는 방법

상관관점 : 업무가 처리하는 일의 방법에 따라 데이터는 어떻게 영향을 받고 있는지 모델링하는 방법

 

모델링의 정의 : DB의 골격을 이해하고 그 이해를 바탕으로 SQL문장을 기능과 성능적인 측면에서 효율적으로

작성하기 위해 꼭 알아야 하는 핵심요소

 

데이터 모델링이란..

정보시스템을 구축하기 위한 데이터 관점의 업무 분석 기법

현실세계의 데이터(WHAT)에 대해 약속된 표기법에 의해 표현하는 과정

데이터베이스를 구축하기 위한 분석/설계의 과정

 

데이터 모델이 제공하는 기능

• 시스템을 현재 또는 원하는 모습으로 가시화하도록 도와준다
• 시스템의 구조와 행동을 명세화 할 수 있게 도와준다
• 시스템을 구축하는 과정에서 결정한 것을 문서화한다
• 다양한 영역에 집중하기 위해 다른 영역의 세부 사항은 숨기는 다양한 관점 제공
• 특정 목표에 따라 구체화된 상세 수준의 표현방법 제공

 

→ 복잡한 정보 요구사항의 간결한 표현, 데이터 품질로 정리 가능

 

데이터 모델링 3단계 : 개념적데이터모델, 논리적데이터모델, 물리적데이터모델

개념적 데이터 모델링 : 추상화 수준이 높고 업무중심적이고 포괄적인 수준의 모델링 진행, 전시적 데이터 모델링, EA수립시 많이 사용

→ 엔터티-관계 다이어그램 생성

 

논리적 데이터 모델링 : 시스템으로 구축하고자 하는 업무에 대해 KEY, 속성, 관계 등을 정확하게 표현, 재사용성이 높음

→ 비즈니스 정보의 논리적인 구조와 규칙을 명확하게 표현하는 기법 또는 과정, 데이터 모델링이 최종적으로 완료된 상태, 즉 물리적인 스키마 설계를 하기 전 단계의 데이터 모델

→ 핵심 과정이며 정규화를 수행

물리적 데이터 모델링 : 실제로 데이터베이스에 이식할 수 있도록 성능, 저장 등 물리적인 성격을 고려하여 설계

→ 테이블, 칼럼 등으로 표현되는 물리적인 저장 구조와 사용될 저장 장치, 자료를 추출하기 위해 사용될 접근 방법 등이 있다.

 

데이터 독립성의 필요성

• 유지보수 비용 증가
• 데이터 중복성 증가
• 데이터 복잡도 증가
• 요구사항 대응 저하

 

데이터 독립성 효과

• 각  VIEW의 독립성을 유지하고 계층별 VIEW에 영향을 주지 않고 변경이 가능
• 단계별 Schema에 따라 데이터 정의어(DDL)과 데이터 조작어(DML)가 다름을 제공

 

DB의 3단계 구조

외부 스키마 : VIEW 단계 여러개의 사용자 관점으로 구성, 개인적 DB 스키마, DB의 개개 사용자나 프로그래머가 접근하는 DB 정의 → 사용자 관점, 접근하는 특성에 따른 스키마 구성

개념스키마 : 모든 사용자 관점을 통합한 조직 전체의 DB를 기술하는 것, USER들이 필요로 하는 데이터를 통합한 조직 전체의 DB를 기술한 것으로 DB에 저장되는 DATA와 그들간의 관계를 표현 → 통합관점

내부 스키마 : DB가 물리적으로 저장된 형식, 물리적 장치에서 데이터가 실제적으로 저장되는 방법을 표현하는 스키마 → 물리적 저장구조

 

• 두 영역의 데이터 독립성

논리적 독립성 : 개념스키마가 변경되어도 외부 스키마에는 영향을 미치지 않도록 지원하는 것, 논리적 구조가 변경되어도 응용 프로그램에 영향 없음

→ 사용자 특성에 맞는 변경 가능, 통합 구조 변경가능

물리적 독립성 : 내부 스키마가 변경되어도 외부/개념 스키마는 영향을 받지 않도록 지원하는 것, 저장장치의 구조 변경은 응용 프로그램과 개념 스키마에 영향 없음

→ 물리적 구조 영향 없이 개념 구조 변경 가능

→ 개념 구조 영향없이 물리적인 구조 변경 가능

 

즉 논리적인 데이터 독립성은 외부의 변경에도 개념스키마가 변하지 않는 특징이 있다.

• Mapping이란?

외부적/개념적 사상(논리적 사상) : 외부 view와 개념적 view의 상호관련성 정의 

→ 사용자가 접근하는 형식에 따라 다른 타입의 필드를 가질 수 있음. 개념적 뷰의 필드 타입은 변화가 없음

 

개념적/내부적 사상(물리적 사상) : 개념적 뷰와 저장된 DB의 상호관련성 정의

→ IF 저장된 데이터베이스 구조가 바뀐다면 개념적/내부적 사상이 바뀌어야 함. 그래야 개념적 스키마가 그대로 남아있게 됨

 

데이터 모델링의 세가지 요소

• Things : 사물이나 사건 등을 바라 볼 때 전체를 지칭하는 용어
• Attributes : 어떠한 것이 가지는 세부적인 사항
• Relationships : 각각의 어떤 것은 다른 어떤 것과 연관성을 가질 수 있는데 바로 이것인 관계를 지칭한다 

 

 

ERD란?

엔터티 : 사각형

관계 : 마름모

속성 : 타원형

 

좋은 데이터 모델의 요소

완전성

중복배제

업무규칙

데이터 재사용

의사소통

통합성

 

엔터티란 : 쉽게 말해 실체, 객체라고 생각

→ 업무에 필요하고 유용한 정보를 저장하고 관리하기 위한 집합적인 것(Things)

→ 즉 과목이라는 entity가 존재한다면 수학, 영어, 국어와 같은 인스턴스가 과목이라는 엔터티에 포함되는 것이다.

→ 엔터티는 인스턴스의 집합으로 나타나게 된다.

→ 인스턴스라는 것은 엔터티의 하나의 값에 해당한다.

→ entity는 그 집합에 속하는 개체들의 특성을 설명할 수 있는 속성(Attribute)을 갖는다.

→ 공통속성, 개별속성

• 엔터티 특징

1. 반드시 해당 업무에서 필요하고 관리하고자 하는 정보여야 한다.
2. 유일한 식별자에 의해 식별이 가능해야 한다.
3. 영속적으로 존재하는 인스턴스의 집합이어야 한다.
4. 엔터티는 업무 프로세스에 의해 이용되어야 한다.
5. 엔터티는 반드시 속성이 있어야 한다.

1. 엔터티는 다른 엔터티와 최소 한 개 이상의 관계가 있어야 한다.

• 엔터티의 분류 → 엔터티 자신의 성격에 의해 실체유형에 따라 구분하거나 업무를 구성하는 모습에 따라 구분이 되는 발생시점에 의해 분류해 볼 수 있다.

 

• 유무형에 따른 분류(todo)

1. 유형엔터티 : 물리적인 형태가 있고 안정적이며 지속적으로 활용하는 엔터티
2. 개념엔터티 : 물리적인 형태는 존재하지않고 관리해야 할 개념적 정보로 구분
3. 사건엔터티 : 업무를 수행함에 따라 발생되는 엔터티로서 통계자료에 이용

 

• 발생시점에 따른 분류(todo)

1. 기본엔터티 : 그 업무에 원래 존재하는 정보로서 다른 엔터티와 관계에 의해 생성되지 않고 독립적으로 생성이 가능하고 자신은 타 엔터티의 부모의 역할을 수행
2. 중심엔터티 : 기본엔터티로부터 발생되고 그 업무에 있어서 중심적인 역할을 수행, 다른 엔터티와의 관계를 통해 많은 행위엔터티를 생성한다.
3. 행위엔터티 : 두 개 이상의 부모엔터티로부터 발생되고 자주 내용이 바뀌거나 데이터량이 증가

 

엔터티의 명명

• 현업업무에서 사용되는 용어 사용
• 약어를 사용하지 않는다.
• 단수명사 사용
• 모든 엔터티에서 유일하게 이름이 부여되어야 함
• 엔터티 생성 의미대로 이름을 부여

 

속성의 개념(todo) : 업무에서 필요로 하는 인스턴스로 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위

• 업무에서 필요로 한다
• 의미상 더 이상 분리되지 않는다.
• 엔터티를 설명하고 인스턴스의 구성요소가 된다

 

엔터티, 인스턴스, 속성, 속성값의 관계(todo)

엔터티에는 두 개 이상의 인스턴스가 존재하고 각각의 엔터티에는 고유의 성격을 표현하는 속성정보를 두 개 이상 갖는다.

 

• 엔터티, 인스턴스, 속성, 속성값에 대한 관계 분석(todo)
• 한 개의 엔터티는 두 개 이상의 인스턴스의 집합이어야 한다
• 한 개의 엔터티는 두 개 이상의 속성을 갖는다
• 한 개의 속성은 한 개의 속성값을 갖는다

 

속성의 특징

• 엔터티와 마찬가지로 반드시 해당 업무에서 필요하고 관리하고자 하는 정보이어야 함
• 정규화 이론에 근간하여 정해진 주식별자에 함수적 종속성을 가져야 한다.
• 하나의 속성에는 한 개의 값만을 가진다. 하나의 속성에 여러 개의 값이 있는 다중값일 경우 별도의 엔터티를 이용하여 분리한다.

 

속성의 특성

• 기본속성 : 업무로부터 추출한 모든 속성이 여기에 해당, 가장 일반적이고 많은 속성 
• 설계속성 : 업무를 규칙화하기 위해 속성을 새로 만들거나 변형하여 정의하는 속성
• 파생속성 : 다른 속성의 영향을 받아 발생하는 속성으로서 계산된 값을이 이에 해당

엔터티 구성방식에 따른 분류

• 엔터티를 식별할 수 있는 속성 : PK속성
• 다른 엔터티와의 관계에서 포함된 속성 : FK속성 
• 엔터티에 포함되어 있고 PK, FK에 포함되지 않는 속성 : 일반속성

• 단일값속성 : 속성 하나에 한개의 값을 가지는 경우
• 다중값속성 : 속성하나에 여러개의 값을 가지는 경우
• → 다중값 속성은 하나의 엔터티에 포함될 수 없으므로 1차 정규화를 하거나 별도의 엔터티를 만들어 관계로 연결해야 한다.

 

도메인 : 각 속성은 가질 수 있는 값의 범위가 있는데 이를 그 속성의 도메인 이라 한다.

→ 엔터티 내에서 속성에 대한 데이터 타입과 크기 그리고 제약사항을 지정

 

속성의 명명

• 해당업무에서 사용하는 이름을 부여한다.
• 서술식 속성명은 사용하지 않는다.
• 약어 사용은 가급적 제한한다.
• 전체 데이터모델에서 유일성 확보하는 것이 좋다.

 

관계의 정의 : 엔터티의 인스턴스 사이의 논리적인 연관성으로 존재의 형태로서나 행위로서 서로에게 연관성이 부여된 상태

 

관계의 패어링 : 엔터티안에 인스턴스가 개별적으로 관계를 가지는 것(패어링)이고 이것의 집합을 관계로 표현한다.

→ 따라서 개별 인스턴스가 각각 다른 종류의 관계를 가지고 있다면 두 엔터티 사이에 두 개 이상의 관계가 형성될 수 있다.

연관관계 : 항상 이용하는 관계로 존재적 단계에 해당

의존관계 : 상대방 클래스의 행위에 의해 관계가 형성될 때 구분하여 표현

 

식별자 개념 : 엔터티는 인스턴스들의 집합이라고 하였다. 여러 개의 집합체를 담고 있는 하나의 통에서 각각을 구분할 수 있는 논리적인 이름이 있어야 한다. 이 구분자를 식별자라고 한다. 식별자란 하나의 엔터티에 구성되어 있는 여러 개의 속성 중에 엔터티를 대표할 수 있는 속성을 의미하며 엔터티는 반드시 하나의 유일한 식별자가 존재해야 한다.

→ 식별자와 키는 동일하지 않음, 키는 DB테이블에 접근을 위한 매개체로서 물리 데이터 모델링 단계에서 사용된다.

 

주식별자 특징

• 주식별자에 의해 엔터티내에 모든 인스턴스들이 유일하게 구분되어야 한다.
• 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 한다.
• 지정된 주식별자의 값은 자주 변하지 않는 것이어야 한다.
• 주식별자가 지정이 되면 반드시 값이 들어와야 한다.

 

1. 유일성 : 주식별자에 의해 엔터티내에 모든 인스턴스들을 유일하게 구분함
2. 최소성 : 주식별자를 구성하는 속성의 수는 유일성을 만족하는 최소의 수가 되어야 함
3. 불변성 : 주식별자가 한 번 특정 엔터티에 지정되면 그 식별자의 값은 변하지 않아야함 
4. 존재성 : 주식별자가 지정되면 반드시 데이터값이 존재(NULL 안됨)

 

• 자신의 엔터티 내에서 대표성을 가지는 가에 따라 주식별자와 보조식별자로 구분
• 엔터티 내에서 스스로 생성되었는지에 따라 내부식별자와 외부 식별자로 구분
• 단일 속성으로 식별되었는가에 따라 단일식별자, 복합식별자로 구분
• 본질식별자, 인조식별자로도 구분

 

주식별자 : 엔터티 내에서 각 어커런스를 구분할 수 있는 구분자이며, 타 엔터티와 참조관계를 연결할 수 있는 식별자

 

보조식별자 : 엔터티 내에서 각 어커런스를 구분할 수 있는 구분자이나 대표성을 가지지 못해 참조관계 연결을 못함

 

식별자관계 : 부모로부터 받은 식별자를 자식엔터티의 주식별자로 이용하는 경우는 NULL값이 오면 안되므로 반드시 부모엔터티가 생성되어야 하며 자기 자신의 엔터티가 생성되는 경우이다.

 

비식별자관계 : 부모 엔터티 로부터 속성을 받았지만  자식엔터티의 주식별자로 사용하지 않고 일반적인 속성으로만 사용

 

성능데이터 모델링 : 데이터베이스 성능향상을 목적으로 설계단계의 데이터 모델링 때부터 성능과 관련된 사항이 데이터 모델링에 반영될 수 있도록 하는 것이다.

→ 사전에 수행할수록 비용이 들지 않는다.

 

성능 데이터 모델링 고려사항

1. 데이터 모델링을 할 때 정규화를 정확하게 수행
2. 데이터베이스 용량산정을 수행
3. 데이터베이스에 발생되는 트랜잭션의 유형을 파악
4. 용량과 트랜잭션의 유형에 따라 반정규화를 수행
5. 이력모델의 조정, PK/FK 조정, 슈퍼타입/서브타입 조정 등을 수행
6. 성능관점에서 데이터 모델을 검증

 

데이터 모델링을 할 때 기본적으로 정규화를 완벽하게 수행해야 함. 정규화된 모델이 데이터를 주요 관심사별로 분산시키는 효과가 있기 때문에 그 자체로 성능을 향상시킴

→ 엔터티에 대한 용량산정 해야함

→ 트랜잭션의 유형 파악 해야함

→ 앞선 기준을 근거로 정확하게 테이블에 대해 반정규화를 적용해야함

 

정규화 : 중복성을 제거하여 성능을 향상시킬 수 있음

→ 반정규화만이 조회 성능을 향상시키지는 않는다.

 

함수의 종속성에 근거한 정규화 수행 필요 : 데이터들이 어떤 기준값에 의해 종속되는 현상을 지칭하는 것이다. 이 때 기준값을 결정자라고 하고 종속되는 값을 종속자라고 한다.

 

반정규화 : 정규화된 엔터티, 속성, 관계에 대해 시스템의 성능향상과 개발과 운영의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링 기법

→ 대량 범위, 범위처리, 통계처리 

→ 데이터를 중복하여 성능을 향상시킴

→ 성능을 향상시키기 위해 정규화된 데이터 모델에서 중복, 통합, 분리 등을 수행하는 모든 과정

→ 무작정 반정규화를 하는 것이 아닌 응용시스템 변경, 클러스터링 / 인덱스, 뷰를 활용해 대상을 검토 한 후 반정규화 실시

→ 사용이유는? → 조인으로 인한 성능 저하 예상 or 칼럼을 계산하여 읽을 때 성능이 저하될 것을 예상 등등

→ 데이터 무결성을 해치지 때문에 데이터의 무결성을 보장할 수 있는 방법을 찾을 후 구현

→ but 데이터를 입력, 수정, 삭제할 때는 성능이 떨어짐

로우체이닝(row chaining) : 로우 길이가 너무 길어서 데이터 블록 하나에 데이터가 모두 저장되지 않고 두 개 이상의 블록에 걸쳐 하나의 로우가 저장되어 있는 형태

 

로우마이그레이션(row migration) : 데이터 블록에서 수정이 발생하면 수정된 데이터를 해당 데이터 블록에서 저장하지 못하고 다른 블록의 빈 공간을 찾아 저장하는 방식

 

대량 데이터 저장 및 처리로 인해 성능

• 테이블에 많은 양의 데이터가 예상될 경우 파티셔닝을 적용하거나 PK에 의해 테이블을 분할하는 방법을 적용할 수 있다. ORACLE의 경우 LIST PARTITION(특정값 지정), RANGE PARTITION(범위),  HASH PARTITION(해시적용), COMPOSITE PARTITION(범위와 해시가 복합)등이 가능하다

 

RANGE PARTITION 적용 : 가장 많이 사용, 대상 테이블이 날짜 또는 숫자값으로 분리가 가능하고 각 영역별로 트랜잭션이 분리될 경우 사용, 또한 데이터주기에 따라 테이블에 데이터를 쉽게 지우는 것이 가능하다.

 

LIST PARTITION 적용 : 핵심 코드값으로 PK가 구성되어 있고 대량의 데이터가 있는 테이블이라면 값 각각에 의해 파티셔닝이 되는 LIST PARTITION을 적용할 수 있다.(순차적)

 

HASH PARTITION 적용 : 지정된 HASH조건에 따라 해싱알고리즘이 적용되어 테이블이 분리되며 설계자는 테이블에 데이터가 정확하게 어떻게 들어갔는지 알 수 없다. 역시 성능향상을 위해 사용하며 데이터 보관주기에 따라 쉽게 삭제하는 기능은 제공할 수 없다.

(비순차적)

 

수평분할/수직분할의 절차

• 데이터 모델링을 완성한다.
• 데이터베이스 용량산정을 한다.
• 대량 데이터가 처리되는 테이블에 대해서 트랜잭션 처리 패턴을 분석한다.
• 칼럼 단위로 집중화된 처리가 발생하는지, 로우단위로 집중화된 처리가 발생되는지 분석하여 집중화된 단위로 테이블을 분리하는 것을 검토한다.

 

슈퍼 서브타입

→ 트랜잭션 용량 작은경우 → one to one 

→ 트랜잭션 용량 큰 경우 → 트랜잭션 → 공통점과 차이점, 전체 통합(single type, plus type)

 

슈퍼 서브타입 성능저하 이유

1. 트랜잭션은 항상 일괄로 처리하는데 테이블은 개별로 유지되어 UNION연산에 의해 성능이 저하됨
2. 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합되어 있을 때
3. 트랜잭션은 항상 슈퍼+서브 타입을 공통으로 처리하는데 개별로 유지되어 있거나 하나의 테이블로 집약되어 있을 때

 

→ 개별로 발생되는 트랜잭션은 개별 테이블로 구성

→ 슈퍼타입+서브타입에 대해 발생되는 트랜잭션에 대해서는 슈퍼타입+서브타입 테이블로 구성

→ 전체를 하나로 묶어 트랜잭션이 발생할 때는 하나의 테이블로 구성

 

PK/FK 칼럼 순서와 성능개요 데이터를 조회할 때 가장 효과적으로 처리될 수 있도록 접근경로를 제공하는 오브젝트가 바로 인덱스이다.

→ 테이블이 발생되는 트랜잭션의 조회 패턴에 따라 PK/FK칼럼의 순서를 조정해야 함

→ PK 칼럼의 순서를 조정하지 않거나 잘못 지정하면 성능이 저하됨

 

분산 데이터베이스의 정의

• 여러 곳으로 분산되어 있는 데이터베이스를 하나의 가상 시스템으로 사용할 수 있도록 한 데이터베이스
• 논리적으로 동일한 시스템에 속하지만, 컴퓨터 네트워크를 통해 물리적으로 분산되어 있는 데이터들의 모임, 물리적 Site 분산, 논리적으로 사용자 통합-공유

→ 즉 분산 데이터베이스는 데이터베이스를 연결하는 네트워크 환경을 이용하여 데이터베이스를 여러 지역 여러 노드로 위치시켜 사용성/성능 등을 극대화 시킨 데이터베이스라고 정의할 수 있다.

 

분산 데이터베이스의 투명성

1. 분할 투명성(단편화) : 하나의 논리적 Relation이 여러 단편으로 분할되어 각 단편의 사본이 여러 site에 저장
2. 위치 투명성 : 사용하려는 데이터의 저장 장소 명시 불필요. 위치정보가 System Catalog에 유지되어야 함
3. 지역사상 투명성 : 지역 DBMS와 물리적 DB 사이의 Mapping 보장. 각 지역시스템 이름과 무관한 이름 사용 가능
4. 중복 투명성 : DB 객체가 여러 site에 중복 되어 있는지 알 필요가 없는 성질
5. 장애 투명성 : 구성요소(DBMS)의 장애에 무관한 Transaction의 원자성 유지
6. 병행 투명성 : 다수 Transaction 동시 수행시 결과의 일관성 유지, Time Stamp, 분산 2단계 Locking을 이용한 구현

 

분산 데이터베이스 적용방법 : 데이터베이스 구조설계(아키텍처)라고 봐도 무관

• 장점
• 지역 자치성, 점증적 시스템 용량 확장
• 신뢰성과 가용성
• 효용성과 융통성
• 빠른 응답 속도와 통신비용 절감
• 데이터의 가용성과 신뢰성 증가
• 시스템 규모의 적절한 조절
• 각 지역 사용자의 요구 수용 증대
• 단점
• 소프트웨어 개발 비용
• 오류의 잠재성 증대
• 처리 비용의 증대
• 설계, 관리의 복잡성과 비용
• 불규칙한 응답 속도
• 통제의 어려움
• 데이터 무결성에 대한 위협

 

분산 데이터베이스의 활용 방향성 : 분산 데이터베이스는 업무적인 기능이 다양해지고 데이터의 양이 증가하는 최근 환경에서 적용하는 기술이다.

 

데이터베이스 분산구성의 가치 : 통합된 데이터베이스에서 제공 할 수 없는 빠른 성능을 제공, 네트워크 부하 및 트랜잭션 집중에 따른 성능 저하의 원인을 분산 데이터베이스 환경으로 해결 가능

 

테이블 위치 분산 : 테이블의 구조는 변하지 않음, 또한 테이블이 다른 데이터베이스에 중복되어 생성되지도 않는다. 다만 설계된 테이블의 위치를 각각 다르게 위치시키는 것

테이블 분할 분산 : 테이블 분할 분산은 단순히 다른 곳에 두는 아니라 각각의 테이블을 쪼개어 분산하는 방법이다.

 

수평분할 : node에 따라 테이블을 특정 컬럼의 값을 기준으로 로우 단위로 분리, 컬럼은 분리되지 않는다. pk에 의해 중복이 발생하지 않는다.

→ node별로 사용하는 row가 다를 때 이용한다.

수직분할 : 컬럼 단위로 분할, node에 따라 테이블 컬럼을 기준으로 row를 분리, 컬럼을 기준으로 분할하였기 때문에 각각의 테이블에는 동일한 pk구조와 값이 있다.

→ 데이터를 집합시켜 놓아도 동일한 pk는 하나로 표현하면 되므로 데이터 중복은 없다.

테이블 복제 분산 : 동일한 테이블을 다른 지역이나 서버에서 동시에 생성하여 관리하는 유형

• 부분복제 : 통합된 테이블을 한군데에 가지고 있으면서 각 지사별로는 지사에 해당된 row를 가지고 있는 형태이다.
• 광역복제 : 통합된 테이블을 한군데에 가지고 있으면서 각 지사에도 본사와 동일한 데이터를 모두 가지고 있는 형태이다.

 

분석요약 : 동일한 테이블 구조를 가지고 있으면서 분산되어 있는 동일한 내용의 데이터를 이용하여 통합된 데이터를 산출하는 방식

통합요약 : 분산되어 있는 다른 내용의 데이터를 이용하여 통합된 데이터를 산출하는 방식

 

옵티마이저 : 사용자가 질의한 SQL문에 대해 최적의 실행방법을 결정하는 역할을 수행한다.

이러한 최적의 실행 방법을 실행계획이라고 한다. 관계형 데이터베이스는 궁극적으로 SQL문을 통해서만 데이터를 처리할 수 있다.

 

규칙기반 옵티마이저 : 규칙(우선 순위)를 가지고 실행계획을 생성한다. 실행계획을 생성할 때 참조하는 정보에는 SQL문을 실행하기 위해서 이용 가능한 인덱스 유무와 (유일, 비유일, 단일, 복합 인덱스)종류, SQL문에서 사용하는 연산자(=, <, <>, LIKE, BETWEEN 등)의 종류 그리고 SQL 문에서 참조하는 객체(힙 테이블, 클러스터 테이블 등)의 종류가 있다.

 

비용기반 옵티마이저 : 규칙기반 옵티마이저의 단점을 극복하기 위해서 출현, SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식이다. 여기서 비용이란 SQL문을 처리하기 위해 예상되는 소요시간 또는 자원 사용량을 의미한다.

→ 통계정보가 없는 경우 정확한 비용예측을 할 수 없다.(정확한 통계정보 유지해야함)

 

실행계획 : SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 의미, 실행계획을 생성한다는 것은 SQL을 어떤 순서로 어떻게 실행할지를 결정하는 작업

조인 순서는 A → B이다

 

인덱스 특징과 종류 : 데이터를 쉽게 찾을 수 있도록 함, 테이블을 기반으로 선택적으로 생성할 수 있는 구조이다. 테이블에 인덱스를 생성하지 않아도 되고 여러 개를 생성해도 된다.

→ 검색 성능의 최적화, DML작업은 오히려 느려질 수도 있음(insert, update, delete)

 

브랜치 블록은 분기를 목적으로 하는 블록이다. 브랜치 블록은 다음 단계의 블록을 가리키는 포인터를 가지고 있다. 리프 블록은 트리의 가장 아래 단계에 존재한다.

 

리프블록 : 인덱스를 구성하는 컬럼의 데이터와 해당 데이터를 가지고 있는 행의 위치를 가리키는 레코드 식별자로 구성되어 있다.

인덱스 데이터는 인덱스를 구성하는 컬럼의 값으로 정렬된다.

 

리프 블록은 양방향 링크를 가지고 있다. 이것을 통해서 오름차순과 내림차순 검색을 할 수 있다(범위 검색)

 

클러스터형 인덱스 : 리프 페이지가 곧 데이터 페이지이다. 때문에 클러스터형 인덱스의 리프 페이지를 탐색하면 해당 테이블의 모든 컬럼 값을 곧바로 얻을 수 있다. 또한 리프페이지의 모든 로우(=데이터)는 인덱스 키 컬럼순으로 물리적으로 정렬되어 저장된다.(한가지 순서로만 → 클러스터형 인덱스는 테이블당 한 개만 생성 가능)

 

전체 테이블 스캔 : 테이블에 존재하는 모든 데이터를 읽어가면서 조건에 맞으면 결과로서 추출하고 조건에 맞지 않으면 버리는 방식 → 모든 블록의 데이터를 읽음

→ 사용할때 : SQL문에 조건이 존재하지 않는 경우, SQL 문의 주어진 조건에 사용 가능한 인덱스가 존재하지 않는 경우, 옵티마이저 취사 선택

 

인덱스스캔 : 인덱스를 구성하는 컬럼의 값을 기반으로 데이터를 추출하는 엑세스 기법,

인덱스의 리프 블록은 인덱스를 구성하는 컬럼과 레코드 식별자로 구성되어 있다.

→ 따라서 검색을 위해 인덱스의 리프 블록을 읽으면 인덱스 구성 컬럼의 값과 테이블의 레코드 식별자를 알 수 있다.

 

인덱스 유일 스캔 : 단 하나의 데이터를 추출하는 방식, 중복을 허락하지 않는 인덱스

→ ‘=’로 값이 주어진 경우에만 가능

인덱스 범위 스캔 : 인덱스를 사용하여 한 건 이상의 데이터를 추출하는 방식

→ ‘=’로 값이 주어지지 않은 경우와 비유일 인덱스를 이용

 

규칙기반 옵티마이저

인덱스를 경유해서 읽는 (인덱스 스캔방식) → 사용 가능한 적절한 인덱스가 존재할때만 사용

→ 검색용도

 

테이블의 전체 데이터를 모두 읽으면서 데이터를 추출하는 (전체 테이블 스캔 방식)

→ 인덱스의 존재 유무와 상관없이 항상 이용 가능

→ 대부분의 데이터를 읽는 용도

 

조인 : 두 개 이상의 테이블을 하나의 집합으로 만드는 연산

→ FROM절에 두 개 이상의 테이블이 나열된 경우

NL Join : 중첩된 반복문과 유사한 방식으로 조인을 수행한다. 반복문의 외부에 있는 테이블을 선행 테이블 또는 외부 테이블(Outer Table)이라고 하고, 반복문의 내부에 있는 테이블을 후행테이블 또는 내부 테이블(Inner Table)이라고 한다

→ 선행 테이블의 조건을 만족하는 행을 추출하여 후행테이블을 읽으면서 조인을 수행

→ 이 작업은 선행 테이블의 조건을 만족하는 모든 행의 수만큼 반복 수행

→ 즉 선행테이블의 양이 많으면 후행테이블은 조인 작업을 반복 수행하고

→ 선행테이블의 양이 적다면 전체적인 일량이 줄어든다.

 

1. 선행 테이블에서 주어진 조건을 만족하는 행을 찾음
2. 선행 테이블의 조인 키 값을 가지고 후행테이블에서 조인 수행
3. 선행 테이블의 조건을 만족하는 모든 행에 대해 1번 작업 반복 수행

 

→ 조인이 성공하면 사용자에게 바로 결과를 보여줌 즉 결과를 빠르게 보여주는 프로그램에 적당한 기법임

 

Sort Merge Join : 조인 컬럼을 기준으로 데이터를 정렬하여 조인을 수행

NL Join은 주로 랜덤 액세스 방식으로 데이터를 읽는 반면 Sort Merge Join은 주로 스캔 방식으로 데이터를 읽는다.(넓은 범위의 데이터 처리 시 유리 but 정렬할 데이터가 많을 경우에는 성능이 떨어짐 → hash join이 성능상 유리) 그러나 ‘비동등 조인과 동등 조인’ 가능

→ 정렬작업이 선행되어 있을 경우 정렬작업 발생x

 

Hash Join : 해싱기법을 이용하여 조인 수행, 조인을 수행할 테이블의 조인 컬럼을 기준으로 해시 함수를 수행하여 서로 동일한 해시값을 갖는 것들 사이에서 실제 값이 같은지를 비교하면서 조인을 수행한다. 

→ NL Join의 랜덤 액세스 문제와 Sort Merge Join의 문제점인 정렬 작업의 부담을 해결하기 위한 대안

→ 해시 테이블을 메모리에 생성해야함, 때문에 결과 행의 수가 적은 테이블을 선행 테이블로 두어야 한다.

데이터 모델링의 중요성 및 유의점

• 중복 : 같은 시간 같은 데이터 제공
• 비유연성 : 사소한 업무변화에 데이터 모델이 수시로 변경되면 안됨
• 비일관성 : 신용 상태에 대한 갱신 없이 고객의 납부 이력 정보 갱신 안됨

 

데이터 모델링 : 개념적, 논리적, 물리적 데이터 모델링

 

외부 스키마 : 개개 사용자가 보는 개인적 db 스키마

개념 스키마 : 모든 사용자 관멎을 통합한 전체 db

내부 스키마 : 물리적 장치에서 데이터가 실제적 저장

 

논리적 독립성 : 개념 스키마가 변경되어도 외부 스키마에 영향x

물리적 독립성 : 내부 스키마가 변경되어도 외부/개념 스키마는 영향x

 

mapping : 상호 독립적인 개념을 연결

 

데이터모델링 : 논리적, 개념적, 물리적

 

엔터티 : 집합, 보이지 않는 개념 포함, 두 개 이상의 인스턴스의 집합, 유일한 식별자에 의해 식별 가능, 반드시 속성이 있어야 함, 다른 엔터티와 최소 1개 이상의 관계가 있어야 함

인스턴스 : 단수

 

모델링 특징 : 단순화, 추상화, 정확화\

 

엔터티 분류 : 유형 → 물리적 형태, 개념 → 개념적정보, 사건 → 업무 수행시 발생

 

속성의 분류 

기본 : 업무로부터 추출한 모든 일반적인 속성

설계 : 업무를 규칙화하기 위해 새로 만들거나 변형

파생 : 다른 속성에 영향을 받아 발생하는 속성

 

도메인 : 각 속성이 가질 수 있는 값의 범위

 

관계 : 엔터티의 인스턴스 사이의 논리적인 연관성으로서 존재의 형태로서나 행위로서 서로에게 연관성이 부여된 상태

 

연관관계(실선) : 항상 이용하는 관계

의존관계(점선) : 상대방 행위에 의해 발생하는 관계

 

식별자 : 엔터티내에서 인스턴스를 구분하는 구분자

식별자는 논리 데이터 모델링 단계에 사용, key는 물리 데이터 모델링 단계에 사용

 

식별자의 특징 : 유일성, 최소성, 불변성, 존재성

 

주식별자 : 엔터티 내에서 각 어커런스를 구분할 수 있는 구분자, 타 엔터티와 참조관계를 연결할 수 있음

 

보조식별자 : 어커런스를 구분할 수 있는 구분자이나 대표성을 가지지 못해 참조관계 연결 불가

 

내부식별자 : 스스로 생성되는 식별자

외부식별자 : 타 엔터티로부터 받아오는 식별자

단일식별자 : 하나의 속성으로 구성

복합식별자 : 2개 이상의 속성으로 구성

 

대체 여부

본질 : 업무에 의해 만들어지는 식별자

인조 : 인위적으로 만든 식별자

 

주식별자 : 부모로부터 받은 식별자를 자식엔터티의 주식별자로 이용하는 경우

→ 강한 연결관계 표현, 실선 표기

 

비식별자 : 부모 속성을 자식의 일반 속성으로 사용

 

로우체이닝 : 로우의 길이가 너무 길어서 두 개 이상의 블록에 걸쳐 하나의 로우를 저장

 

로우마이그레이션 : 데이터블록에서 수정이 발생하면 수정된 데이터를 해당 데이터 블록에서 저장하지 못하고 다른 블록의 빈 공간을 찾아 저장하는 방식

 

RANGE PARTITION : 대상 테이블이 날짜 또는 숫자값으로 분리가 가능, 각 영역별로 트랜잭션이 분리되는 경우

 

LIST PARTITION : 필드값으로 PK구성, 대량의 데이터가 있는 테이블의 경우

 

HASH PARTITION : 지정된 HASH조건에 따라 해시 알고리즘이 적용되어 테이블이 분리

 

지역사상 투명성 : 지역 DBMS와 물리적 DB 사이의 Mapping 보장

중복 투명성 : DB 객체가 여러 site에 중복되어 있는지 알 필요가 없는 성질

장애 투명성 : 구성요소의 장애에 무관한 트랜잭션의 원자성 유지

병행 투명성 : 다수 트랜잭션 동시 수행 시 결과의 일관성유지. 분산 2단계 Locking이용

 

분산 db 장점 : 지역자치성, 신뢰성, 가용성, 효용성, 융통성, 비용절강

분산 db 단점 : 무결성 위협, 설계관리 복잡성, 불규칙한 응답속도, 오류의 잠재성 증대

 

테이블 분할

• 수평분할 : 로우 단위로 분리
• 수직분할 : 칼럼 단위로 분리

 

부분복제 : 마스터 DB에서 테이블의 일부의 내용만 다른 지역이나 서버에 위치

광역복제 : 마스터 DB테이블의 내용을 각 지역이나 서버에 존재

 

테이블 요약 분산 : 지역 간에 또는 서버 간에 데이터가 비슷하지만 서로 다른 유형으로 존재하는 경우

분석요약 : 동일한 테이블 구조를 가지고 있으면서 분산되어 있는 동일한 내용

통합요약 : 분산되어 있는 다른 내용의 데이터를 이용하여 통합된 데이터를 산출하는 방식

 

db : 특정 기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장

 

dbms : 효율적인 데이터 관리 뿐만 아니라 예기치 못한 사건으로 인한 데이터의 손상을 피하고 필요한 데이터를 복구하기 위한 강력한 기능의 sw

 

DML(조작) : SELECT, INSERT, UPDATE, DELETE

DDL(정의) : CREATE, ALTER, DROP, RENAME

DCL(제어) : GRANT, REVOKE

TCL : COMMIT, ROLLBACK

 

정규화 : 데이터 정합성 확보 & 이상현상 방지

 

기본키 : 테이블에 존재하는 각 행을 한 가지 의미로 특정할 수 있는 한 개 이상의 컬럼

외부키 : 다른 테이블의 기본키로 사용되고 있는 관계를 연결하는 컬럼

 

CHAR : 고정 길이 문자열 정보

VARCHAR : 가변 길이 문자열 정보

NUMERIC : 정수, 실수 등 숫자 정보

 

PRIMARY KEY(기본키) : 기본키 정의

UNIQUE KEY(고유키) : 고유키 정의

CHECK : 입력 값 범위 제한

FOREIGN KEY(외래키) : 외래키 정의

 

DESC(RIBE) 테이블명 : 테이블 구조 확인

EXEC sp_help ‘db0.테이블명’ → SQL server

 

DDL 명령어의 경우 실행시 AUTO COMMIT 하지만 DML의 경우 COMMIT를 입력해야 한다.

SQL Server의 경우 DML도 AUTO COMMIT

 

• 와일드카드

*-모든

%-모든

-:한글자

 

합성 연산자

||(oracle)

+(SQL Server)

 

트랜잭션 : 밀접히 관련되어 분리될 수 없는 1개 이상의 db 조작

SAVEPOINT : 저장 지점

트랜잭션의 특성

• 원자성 : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않아야 함 
• 일관성 : 트랜잭션 실행 전 DB 내용이 잘못 되지 않으면 실행 후도 잘못되지 않아야
• 고립성 : 트랜잭션 실행 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
• 지속성 : 트랜잭션이 성공적으로 수행되며 DB 내용은 영구적 저장

 

 

ROLLBACK TO 테이블명(ORACLE)

SAVE TRAN 테이블명(SQL SERVER)

ROLLBACK TRAN 테이블명(SQL SERVER)

 

BETWEEN a AND b : a와 b값 사이에 있으면 됨

IN : 리스트에 있는 값중 어느 하나라도 일치

LIKE ‘문자열’ : 비교문자열과 형태가 일치

IS NULL : NULL 값인 경우

NOT IN (list) : list의 값과 일치하지 않는다.

IS NOT NULL : NULL 값을 갖지 않는다.

 

연산자 우선순위 : () → NOT → 비교연산자 → AND → OR

 

• NULL 값과의 수치연산은 NULL 값을 리턴한다
• NULL 값과의 비교연산은 거짓을 리턴한다

 

ROWNUM : 원하는 만큼의 행을 가져올 때 사용(ORACLE)

→ TOP(SQL SERVER)

SUBSTR/SUBSTRING : 문자열 중 m 위치에서 n개의 문자 반환

→ SUBSTR(‘SQL Expert’, 5, 3) → ‘Exp’

 

LTRIM(‘xxxYYZZxYZ’, ‘x’) → ‘YYZZxYZ’ → 왼쪽에 x가 존재한다면 x를 제외한 첫문자부터

RTRIM(‘XXYYzzXYzz’, ‘z’) → ‘XXYYzzXY’  → 오른쪽에 z가 존재한다면 z를 제외한 첫문자

TRIM(‘x’ FROM ‘xxYYZZxYZxx’) → ‘YYZZxYZ’ → 양옆의 x를 제외한 첫문자부터

 

LPad : 왼쪽부터 특정문자로 채움

→ LPAD(‘문자열’ OR ‘열이름’, 만들어질자릿수, ‘채워질 숫자’) -> 만들어질 자릿수=숫자지정

RPad : 오른쪽부터 특정문자로 채움

 

날짜 데이터 + 100 → 100일 이후(day로 인식)

 

SIGN(n) : 숫자가 양수면1, 음수면-1, 0이면 0반환

MOD : 숫자1을 숫자2로 나누어 나머지 반환

CEIL/CEILING(n) : 크거나 같은 최소 정수 반환 → 올림

FLOOR(n) : 작거나 같은 최대 정수 리턴 → 내림

 

ROUND(38.5235, 3) → ROUND는 반올림 → 38.524

TRUNC(38.5235, 3) → 지정자리수 제외하고 나머지 버림 → 38.523

 

SYSDATE / GETDATE() : 현재날짜와 시각 출력

 

EXTRACT / DATEPART : 날짜에서 데이터 출력

NVL(식1, 식2) / ISNULL(식1, 식2) : 식1의 값이 NULL이면 식2 출력

 

NULLIF(식1, 식2) : 식1이 식2와 같으면 NULL을 아니면 식1을 출력

 

COALESCE(식1, 식2) : 임의의 개수표현식에서 NULL이 아닌 최초의 표현식,

모두 NULL이면 NULL 반환 → 앞순서의 null을 제외한 첫번째 값을 찾음

 

집계함수

• 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려줌
• GROUP BY절은 행들을 소그룹화 한다
• SELECT, HAVING, ORDER BY 절에 사용 가능
• ALL : Default 옵션
• DISTINCT : 같은 값을 하나의 데이터로 간주하는 옵션

 

COUNT(*) : NULL 포함 행의 수 → *이 아닐경우 NULL은 안 셈

COUNT(표현식) : NULL 제외한 행의 수

SUM, AVG : NULL제외 합계, 평균 연산

STDDEV : 표준 편차

VARIAN : 분산

 

GROUP BY, HAVING 절의 특징

1. GROUP BY 절을 통해 소그룹별 기준을 정한 후, SELECT 절에 집계 함수를 사용.
2. 집계 함수의 통계정보는 NULL값을 가진 행을 제외하고 수행한다.
3. GROUP BY 절에서는 ALIAS 사용 불가
4. 집계 함수는 WHERE 절에 올 수 없다.
5. HAVING 절에는 집계함수를 이용하여 조건 표시 O
6. HAVING 절은 일반적으로 GROUP BY 뒤에 위치

 

CASE WHEN LOC = ‘a’ THEN ‘b’

CASE LOC WHEN ‘a’ THEN ‘b’

→ 이 두 문장은 같은 의미이다.

 

• ORDER BY 절에 컬럼명 대신 ALIAS 명이나 컬럼 순서를 나타내는 정수도 사용 가능
• DEFAULT 값으로 오름차순(ASC)이 적용되며 DESC옵션을 통해 내림차순 정렬이 가능
• SQL 문장의 제일 마지막에 위치
• SELECT 절에서 정의하지 않은 컬럼 사용 가능

 

Oracle에서는 NULL을 가장 큰 값으로 취급하며 SQL Server에서는 NULL을 가장 작은 값으로 취급한다.

 

SELECT 문장 실행 순서

• FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

 

JOIN : 일반적으로 행 들은 PK나 FK값의 연관에 의해 JOIN이 성립된다. 어떤 경우에는 PK, FK 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립가능하다.

 

EQUI JOIN : 2개의 테이블간에 컬럼 값들이 서로 정확하게 일치하는 경우 사용, 대부분 PK, FK의 관계를 기반으로 한다.

 

NON EQUI JOIN : 2개의 테이블 간에 컬럼 값들이 서로 정확하게 일치하지 않는 경우에 사용

→ ‘=’ 연산자가 아닌 BETWEEN, >, <= 등 연산자 사용

 

집합 연산자 : 조인을 사용하지 않고 연관된 데이터를 조회, SELECT절의 컬럼 수가 동일하고 SELECT 절의 동일 위치에 존재하는 컬럼의 데이터 타입이 상호 호환 할 때 사용

 

일반 집합 연산자

• UNION : 합집합(중복 행은 1개로 처리), 중복배제, 정렬o, 느림
• UNION ALL : 합집합(중복 행도 표시), 빠름, 중복o, 정렬x
• INTERSECT : 교집합(INTERSECTION), 정렬o
• EXCEPT, MINUS : 차집합, 정렬o
• CROSS JOIN : 곱집합

 

INNER JOIN : JOIN 조건에서 동일한 값이 있는 행만 반환, USING나 ON 절을 필수적으로 사용, NULL값은 안가져옴

 

NATURAL JOIN : 두 테이블 간의 동일한 이름을 갖는 모든 컬럼들에 EQUI JOIN 수행, NATURAL JOIN이 명시되면 추가로 USING, ON, WHERE 절에서 JOIN조건을 정의할 수 없음, 중복제거

 

USING 조건절 : 같은 이름을 가진 컬럼들 중에서 원하는 컬럼에 대해서만 선택적으로 EQUI JOIN을 수행, JOIN 컬럼에 대해서 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없음. 

 

ON 조건절 : 컬럼명이 다르더라도 JOIN 조건 사용 가능, ALIAS나 테이블명 반드시 사용

 

CROSS JOIN : 양쪽 집합의 M*N건의 데이터 조합이 발생한다.

 

OUTER JOIN : JOIN 조건에서 동일한 값이 없는 행도 반환 가능, USING이나 ON조건절 반드시 사용

 

LEFT OUTER JOIN : 조인 수행시 좌측 테이블 데이터를 읽은 후 우측 테이블에서 JOIN대상 데이터를 읽어온다. 우측 값에서 같은 값이 없는 경우 NULL값으로 채운다.

 

RIGHT OUTER JOIN : LEFT OUTER JOIN의 반대

 

FULL OUTER JOIN : 양쪽 모두에 조건이 일치하지 않는 것들까지 모두 결합, 중복데이터 삭제

• 계층형 질의 : 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용

START WITH : 계층 구조 전개의 시작 위치 지정

CONNECT BY : 다음에 전개될 자식 데이터 지정

 

PRIOR : CONNECT BY 절에 사용되며 현재 읽은 컬럼을 지정, PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 부모→자식 방향으로 전개하는 순방향 전개를한다. 반대는 역방향 전개

 

NOCYCLE : 동일한 데이터가 전개되지 않음

ORDER SIBLINGS BY : 형제 노드간의 정렬 수행

WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출(필터링)

 

LEVEL : 루트 데이터이면 1, 그 하위 데이터면 2, 리프 데이터까지 1씩 증가

CONNECT_BY_ISLEAF : 해당 데이터가 리프 데이터면 1, 그렇지 않으면 0

CONNECT_BY_ISCYCLE : 해당 데이터가 조상이면 1, 아니면 0(CYCLE 옵션 사용시에만 사용가능)

 

SYS_CONNECT_BY_PATH : 루트 데이터로부터 현재 전개할 데이터까지의 경로 표시

CONNECT_BY_ROOT : 현재 전개할 데이터의 루트 데이터 표시(단항 연산자)

 

셀프 조인 : 동일 테이블 사이의 조인, FROM 절에 동일 테이블이 2번 이상 나타난다, 반드시 테이블 별칭 사용

 

서브쿼리 : 알려지지 않은 기준을 이용한 검색에 이용

 

서브쿼리 주의사항

• 서브쿼리를 괄호로 감싸서 사용
• 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용가능
• 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야, 복수 행 비교 연산자는 결과 건수와 상관없다.
• 서브쿼리에서는 ORDER BY를 사용하지 못한다.
• SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET절에 사용

 

단일행 비교 연산자 : =, <, >, <> 등

 ‘<>’는 부등호(≠)연산자로서 ‘<>’좌우의 값들이 다르면 True를 돌려준다.

→ NOT, != 연산자와 같은 기능

다중 행 비교 연산자 : IN, ALL, ANY, SOME 등

 

스칼라 서브쿼리 : 한 행, 한 컬럼만을 반환하는 서브쿼리(SELECT)

인라인 뷰 : 테이블명이 올 수 있는 곳에서 사용, ORDER BY 사용(FROM)

→ 메인쿼리의 컬럼 사용 가능

뷰 : 가상테이블, 실제데이터 존재X

 

뷰 : 독립성, 편리성, 보안성

ROLLUP : SUBtotal을 생성하기 위해 사용(TODO)

GROUPING : Subtotal의 total을 생성

CUBE : 결합 가능한 모든 값에 대하여 다차원 집계를 생성, ROLLUP에 비해 시스템 부하 심함

GROUPING SETS : 인수들에 대한 개별 집계를 구할 수 있음, 다양한 소계 집합 생성 가능

 

윈도우함수 : 행과 행간의 관계를 정의하거나 행과 행간을 비교, 연산

RANK : 동일한 값에 대해서 동일한 순위 부여(1,2,2,4)

DENSE_RANK : 동일한 순위를 하나의 등수로 간주(1,2,2,3)

ROW_NUMBER : 동일한 값이라도 고유한 순위 부여

 

LAG : 파티션별 윈도우에서 이전 몇번째 행의 값을 가져올 수 있다.

LEAD : 파티션별 윈도우에서 이후 몇번째 행의 값을 가져올 수 있다.

 

RATIO_TO_REPORT : 파티션별 내 전체 SUM값에 대한 행별 컬럼값의 백분율을 소수점으로 구할 수 있다. 결과값은 0보다 크고 1보다 작거나 같다

 

PERCENT_RANK : 파티션별 윈도우에서 가장 먼저 나오는것을 0, 제일 늦게 나오는 것을 1로

 

CUME_DIST : 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구함

 

NTILE : 파티션별 전체 건수를 인수값으로 N등분한 결과를 구함

 

DCL : 유저 생성하고 권한을 제어

• 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받을 수 있음

ROLE : 유저에게 알맞은 권한들을 한 번에 부여하기 위해 사용

CASCADE : 하위 오브젝트까지 삭제

절차형 SQL : SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈 생성 → Procedure, User Defined Function, Trigger

 

저장 모듈 : PL/SQL 문장을 DB서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 SQL 컴포넌트 프로그램, 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램

 

DECLARE : 변수와 인수에 대한 정의 및 데이터 타입 선언부

BEGIN~END : SQL문과 여러 가지 비교문, 제어문을 이용 필용한 로직 처리

EXCEPTION : 에러가 발생하면 에러를 처리할지 정의하는 영역

 

T-SQL : 근본적으로 SQL Server를 제어하는 언어

 

Trigger : 특정 테이블에 insert, update, delete와 같은 DML문이 수행되었을 때 DB에서 자동으로 동작하도록 작성된 프로그램, 사용자 호출이 아닌 DB 자동 수행

 

프로시저 : BEGIN~END 절내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어 사용 가능

DB 트리거 : BEGIN~END절 내에 사용 불가

 

규칙기반 옵티마이저 : 우선순위를 가지고 실행계획 생성

비용기반 옵티마이저 : 비용이 가장 적은 실행계획 선택, 객체 정보, 시스템 통계정보 사용

 

인덱스 : 원하는 데이터를 쉽게 찾을 수 있도록 도와줌, 검색성능 최적화

 

인덱스 스캔 : 인덱스를 구성하는 컬럼의 값을 기반으로 데이터를 추출

인덱스 유일 스캔 : 유일 인덱스를 사용하여 단 하나의 데이터를 추출하는 방식(중복x, 구성 컬럼에 대해 모두 ‘=’로 값이 주어진 경우에만 가능)

인덱스 범위 스캔 : 인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식

인덱스 역순 범위 스캔 : 인덱스의 리프 블록의 양방향 링크를 이용하여 내림차순으로 데이터를 읽음

 

NL Join : 중첩된 반복문과 유사한 방식의 조인, 랜덤 액세스 방식

Sort Merge Join : 정렬하여 조인 수행

Hash Join : 해싱기법 사용, NL Join의 랜덤 액세스 문제와, Sort Merge Join의 정렬 작업 부담을 해결하기 위한 방법

 

sum(A) + sum(B) + sum(C) == null값을 제외한 숫자를 더한값 출력 

vs 

sum(A+B+C) == null값이 존재한다면 null 출력

null + 3 = null

any, some 공부

 

• Truncate(구조남음) vs DROP(구조도 삭제)
• Truncate(DDL) vs delete(DML)

→ rollback과 commit이랑 연관되어서 나옴

 

• DML(실습)

insert, update, delete → TCL(commit, rollback)과 연관되어서 나옴

marge → 실습

 

제약조건(중요) 

→ pk(unique + not null), 

→ unique

→ not null

 

DCL

정의 많이 물어봄, 문법보기

GRANT

REVOKE

 

• 그룹함수(중요) → 실습하깅

ex : 결과값 주고 뭘 적어야 하는지 나옴

roll up

cube

groupingsets

grouping

 

ex) : rollup(A,B)와 rollup(B,A)는 같은 결과x

ex) : cube(A,B) 와 cube(B,A)는 같은 결과

 

null 다 찾기

총합행이 존재하는지 찾기 

→ 있다 → rollup(한쪽 결과, 계층, 행의수가 적음), cube(양쪽 둘다 결과, 행의수가 많음)

→ 없다 → groupingsets

 

• 윈도우 함수(기출문제같은 문제풀기)

rows

range

→ 차이점 확인해보기

→ 같은값 존재하는지 유무 확인

 

• prior empno = mgr(실습)

level 1 king empno

level 2 james mgr

level3 scott

• 정규화(중요) → 예시 보고 기출문제 잘 확인

1차 원자성

2차 부분함수종속제거

3차 이행함수종속제거

bcnf 결정함수종속제거

 

• 엔터티

엔터티 도출

특징(암기)

분류(유형,개념,사건) → 유개사기중행

 

• 속성

1. scott 특성

instance

1. 분류 기설파 정의
2. 도메인

데이터 유형, 크기 제약조건, check, primary key

• 관계

IE 표기법 → PK~어쩌구

BARKEA표기법 → # O O O O O

 

식별 / 비식별자 관계, ERD확인

점선 : 비식별자

 

• rank 종류 공부

rank → 중복 건너뛴다.

dense.rank → 건너뛰기x

 

range 파티셔닝(가장 많이 쓰임, 관리 쉬움), hash파티셔닝(관리 힘듬) → 특징 공부, 관리방법

 

• 조인 수행 원리(중요)

NL → 랜덤 액세스, 대용량 sort 작업시 유리

Sort merge → 조인키를 기준으로 정렬, 등가, 비등가

Hash → 등가 only, 선행 테이블이 작다, Hash처리 별도 공간 필요

 

• 실행계획

순서(중요)

Hash Join 은 Non Equal Join 은 불가능함. Equal Join 만 가능함 

사전 Sorting 작업이 필요한 Join 알고리즘은 Sort Merge Join 임

기본속성 : 업무 분석을 통해 바로 정의한 속성

설계속성 : 업무를 규칙화하기 위해 새로 만들어지거나 변형된 속성

파생속성 : 다른 속성에 영향을 받아 발생, 일반적으로 계산된 값들

 

ANY : 어느 하나의 값이라도 만족하는 조건을의미, SOME과 동일

→ B.SALARY >= ANY A.SALARY

→ 상사의 연봉이 어떤 부하직원보다도 큰 결과를 뽑는 sql

 

ALL : 결과에 존재하는 모든 값을 만족하는 조건을 의미

→ A.DEPARTMENT_ID <= ALL(30, 50) → 30보다 작고 50보다 작아야함

 

• CHAR형의 경우 고정형 사이즈에 비해 작은 값이 들어올 경우 스페이스바로 채운다
• ORACLE의 경우 LENGTH 함수는 스페이스 하나를 1로 인식한다
• SQL Server의 경우 스페이스 하나와 ‘’를 0으로 인식한다
• SQL Server : SELECT LEN(‘’); → 0
• SQL Server : SELECT LEN(NULL) → NULL
• SQL Server : SELECT LEN(‘ ’) → 0
• ORACLE : SELECT LEN(‘’) → NULL;
• ORACLE : SELECT LEN(‘ ’) → 1;

 

• 하루에 2번 이상 주문하는 대상 조회
• SELECT 주문일자, 고객명, COUNT(*)
• FROM TAB1
• GROUP BY 주문일자, 고객명
• HAVING COUNT(*) >= 2 → GROUP로 주문일자, 고객명이 묶여 있으므로
• → HAVING COUNT(*) >= 2 로 하루에 2번이상 주문하는 대상을 조회할 수 있다.
• 주문일자 하나의 ROW와 고객명 모든 ROW를 그룹으로 묶음

 

• GROUP BY : 2개 이상의 컬럼을 지정할 경우
• A에 대한 소계행이 생기고, B에 대한 소계행이 다시 생김

 

• CUBE, ROLLUP, GROUPING SETS 전부 GROUP BY에 사용
• CUBE : 계산 가능한 모든 소계와 합계를 반환(합계를 맨위에 표시, 상세함)
• GROUP BY CUBE(DNAME, JOB) → 계산 가능한 모든 소계와 합계 반환
• DNAME에 있는 ROW 하나와 JOB의 모든 ROWS에 대한 소계와 합계 반환
• 즉 모든 경우의 수를 비교한다고 생각하면 될 듯, GROUP BY와 같은동작
• A,B로 묶이는 그룹의 값
• A로 묶이는 그룹의 소계
• 전체합계
• HAVING GROUPING_ID(job, deptno) = 1 → job소계만 추출
• HAVING GROUPING_ID(job, deptno) = 2 → deptno소계만 추출

 

• ROLL UP : 소계와 합계를 순서에 맞게 반환(합계를 마지막에 표시, 
• CUBE에 비해 간단)
• A,B로 묶이는 그룹의 값
• A로 묶이는 그룹의 소계
• 전체 합계

 

• GROUP BY DEPARTMENT_ID, JOB_ID

1. 각각 DEPARTMENT_ID의 연봉 합산값
2. 전체 연봉 합산 값
3. 각각 JOB_ID의 연봉 합산값

 

• GROUP BY CUBE(JOB_ID, DEPARTMENT_ID)

• 각각 DEPARTMENT_ID의 연봉 합산값
• 전체 연봉 합산 값
• 각각 JOB_ID의 연봉 합산값

 

• GROUP BY ROLLUP(DEPARTMENT_ID, JOB_ID)

• 각각 DEPARTMENT_ID의 연봉 합산값
• 전체 연봉 합산 값

 

ex) : rollup(A,B)와 rollup(B,A)는 같은 결과x

ex) : cube(A,B) 와 cube(B,A)는 같은 결과

 

• GROUP BY GROUPING SETS : 특정 항목에 대한 소계를 계산(특정 항목을 그룹한 다수의 SQL을 UNION한 것과 같음)
• 만약 전체값의 결과 즉 (전체 연봉 합산 값)만 보고 싶거나 (전체 연봉 합산 값)을 제외한 나머지를 보고 싶다거나 1,2,3번의 결과를 선택해서 출력하고 싶을 때 사용
• GROUPING SETS((DEPARTMENT_ID, JOB_ID), DEPARTMENT_ID, JOB_ID, ())
• → DEPARTMENT_ID만 넣었을 경우 : 1
• → JOB_ID까지 넣었을 경우 : 1, 3
• → DEPARTMENT_ID, JOB_ID, () 경우 : 1, 3, 2

 

• SUM(누적할 컬럼명) OVER (PARTITION BY 그룹화할 컬럼명 ORDER BY 정렬할 컬럼명)
• SUM(SAL) OVER (PARTITION BY JOB ORDER BY SAL DESC ROWS BETWEEN UNBOUNDED PRECEDING)
• PARTITION BY : 소그룹을 만들때 사용

UNBOUNDED PRECEDING(시작범위) 

AND 

CURRENT ROW(현재 행)

→ 처음행부터 현재행까지 계속 순차적으로 누적 합계를 구함

1. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 

- 윈도우의 범위: PARTITION BY 절에 명시된 그룹 전체 ROW 임. 

만약 PARTITION BY 가 명시되지 않았다면 전체 RESULTSET 을 모두 SUM 한다. 윈도절에 아무것도 쓰지 않으면 디폴트로 이 구문임.

 

2. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 

- 윈도우의 범위: 첫 번째 ROW 에서 현재 ROW 까지의 합을 구한다. 

 

3. ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 

- 윈도우의 범위: 현재 ROW 에서 마지막 ROW 까지의 합을 구한다. 

 

4. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 

- 윈도우의 범위: 현재 ROW 를 중심으로 이전 ROW 와 다음 ROW 까지의 합을 구한다. 

 

5. ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

- 윈도우의 범위: 이전 ROW 와 현재 ROW 의 합을 구한다. 

 

6. ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING 

- 윈도우의 범위: 현재 ROW 와 다음 ROW 의 합을 구한다. 

 

• UNBOUNDED PRECEDING : 윈도우의 시작 위치가 첫번째 row
• UNBOUNDED FOLLOWING : 윈도우의 마지막 위치가 마지막 row

• ROWS 10 PRECEDING → 현재 행에서 10행 앞쪽의 범위를 잡음
• RANGE 10 PRECEDING → 현재 컬럼값이 100이라면 90부터 범위를 정함

 

• Non Equi Join(비등가조인) : 동일 컬럼 없이(인덱스없이) 다른 조건(범위)를 사용하여 조인
• → 크거나 작거나 하는 경우의 ‘<’, ‘>’를 사용한다.
• Equi Join(등가조인) : 공통적으로 존재하는 컬럼의 값의 행이 일치하는 경우
• → =를 사용함

• Non Equi Join이 되지 못하는 Join은 Hash Join이다
• → 두개 테이블에 인덱스가 없음, Equal Join에서만 해당 조인방식 사용

 

• Oracle의 경우 DML 후 자동 COMMIT이 아니지만 DDL & DCL이 발생하면 자동으로 전체 트랜잭션이 COMMIT 된다.

 

• LEFT OUTER Join : A테이블에는 데이터가 있고 B테이블에 없는 경우 A테이블의 정보를 전부 출력
• RIGHT OUTER JOIN : LEFT JOIN의 반대
• FULL OUTER JOIN : 한쪽테이블에는 데이터가 있고 다른 테이블에 없는 경우 데이터가 있는 쪽 테이블의 내용을 전부 출력

 

• NVL(COL1, ‘X’) <> ‘A01’
• → NVL(COL1, ‘X’) → COL1이 NULL이라면 X로 치환, 아니라면 그대로 표시
• → col1은 조건을 잡을 컬럼, x는 임의의 수이다.
• → NULL값이 포함된 컬럼에 대해 조건을 잡을 경우 사용

‘<>’는 부등호(≠)연산자로서 ‘<>’좌우의 값들이 다르면 True를 돌려준다.

• → result : A01과 일치하는 컬럼이 아닌 것은 TRUE값이므로
• → A01(FALSE)를 제외한 COL1의 값이 그대로 출력됨 → NULL값은 NVL(COL1, ‘임의의 값’)을 사용했으므로 NULL도 출력

 

• NVL2(“값”, “지정값1”, “지정값2”)

 

WHERE 절에 조건절이 쓰이게 되면 암묵적으로 해당 컬럼에 대한 IS NOT NULL조건이 생성됨, 

 

• JOIN동작의 default는 INNER JOIN 이다
• NATURAL INNER JOIN은 시발 그냥 inner join이다. → cross join 수행
• 이 때 조건이 설정되지 않은 inner join은 cross join과 같은 연산을 수행

 

• inner join뒤에 on키워드를 사용하여 일치하는 속성을 기준으로 결합 가능
• SELECT * FROM instructor I JOIN teaches T ON I.id = T.id → Equi Join이라함

 

• Natural Join을 사용하면 공통된 속성을 찾아 같은 값을 갖는 항목끼리 결합함
• → SELECT * FROM instructor NATURAL JOIN teaches

 

• UNION 동작과정 → 쿼리문은 위에서 아래로 흐름

 

• 비용기반 옵티마이저 : 쿼리를 수행하는데 소요되는 일량 또는 시간을 기반으로 최적화를 수행

• PERCENT_RANK란? : 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로 제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구하는

 

• 프로그래밍에서 사용되는 중첩된 반복문과 유사한 방식으로 조인을 수행 : NESTED LOOP JOIN(NL JOIN) → 랜덤엑세스

 

• ROW_NUMBER : 각 PARTITION 내에서 ORDER BY절에 의해 정렬된 순서를 기준으로 고유한 값을 반환

• ROW_NUMER() OVER(PARTITION BY [그룹핑할 컬럼] ORDER BY [정렬할 컬럼])
• → 순위함수, PRATITION BY는 선택, ORDER BY는 필수
• → PARTITION BY를 미사용 할 경우 전체 컬럼에 대해 순서가 부여됨
• → PARTITION BY를 사용할 경우 그룹핑할 컬럼별로 순서가 부여됨

 

→ INTERSECT

 

UNION	여러 개의 SQL 문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 출력
UNION ALL	여러 개의 SQL 문의 결과에 대한 합집합으로 중복된 행도 그대로 결과로 표시
INTERSECT	여러 개의 SQL 문의 결과에 대한 교집합으로 중복된 행은 하나의 행으로 출력
EXCEPT	앞의 SQL 문의 결과에서 뒤의 SQL 문의 결과에 대한 차집합으로 중복된 행은 하나의 행으로 출력

 

• CONNECT BY → 다음에 전개될 자식 데이터를 지정하는 구문
• PRIOR → 상위행의 컬럼과 연결
• START WITH → 계층 구조 전개의 시작 위치를 지정함
• NOCYCLE : 동일한 데이터가 나타나는 경우가 사이클이며, NOCYCLE은 사이클이 발생한 이후의 데이터는 전개하지 않음
• ORDER SIBLINGS BY : 형제 노드 사이에서 정렬 수행
•  CONNECT_BY_ISCYCLE : 전개 과정에서 자식을 가질 때 해당 데이터가 조상으로서 존재하면 1, 아니라면 0
• SYS_CONNECT_BY_PATH : 루트 데이터부터 현재 전개할 데이터까지의 경로 표시
• CONNECT_BY_ROOT : 현재 전개할 데이터의 루트 데이터 표시

 

• 소문자 / 대문자로 있는 경우 두개의 문자열을 같게 하기 위해 UPPER or LOWER 사용해야함
• → WHERE () (A.V1) LIKE B.V1 
• → A.V1이 소문자라면 UPPER 적용
• → A.V1이 대문자라면 LOWER 적용
• 만약 A.V1테이블과 B.V1테이블의 대문자가 같고 && A.V1테이블에만 스페이스가 있을 경우
• → TRIM을 사용하여 제거함
• → WHERE ( TRIM ) (A.V1) LIKE B.V1

• NOT EXISTS의 OUTER JOIN 변형 문제??
• NOT EXISTS는 OUTER JOIN으로 변경 시 NOT NULL COLUMN에 대한 IS NULL 체크로 NOT EXISTS를 구현 가능하다.
• LEFT OUTER
• WHERE P.사원번호 IS NULL

COL1 → 1,2,3

COL2 → 1,2,4

 

SELECT *

FROM TAB1 A, TAB2 B

WHERE A.COL1 <> B.COL2;

 

→ COL1의 각각의 요소가 COL2의 요소를 각각 비교하여 자신의 값과 일치하지 않을경우 ROWS가 하나씩 SELECT 됨

 

EXISTS : 존재하면 출력해라 라는 뜻 IN과 같음, BUT 성능적인 면에서 뛰어남

NOT EXISTS : EXISTS와 반대 결과 출력