SQLD

SQLD 공부 중에 학습한 내용을 정리하였습니다.

 

1. DROP, TRUNCATE, DELETE의 차이

DROP, TRUNCATE 는 auto commit 이 됩니다. 

TRUNCATE는 용량이 줄어들며 인덱스도 삭제됩니다. 그러나, 테이블은 삭제되지 않고 데이터만 삭제됩니다.

DROP은 테이블 전체를 삭제하며 공간도 전체를 삭제합니다.

 

DELETE 는 사용자 commit 필요합니다. 

테이블의 용량은 줄어들지 않으며 데이터만 지울 수 있습니다.

삭제 후 복원 가능합니다.

2. Oracle 과 SQL Server 의 차이

2.1 Rollback

Oracle 은 Create table의 경우 transaction 종료 처리되어 rollback해도 테이블이 그대로 존재합니다.

SQL server 의 경우 Begin Transaction이 있는 곳 까지 rollback 됩니다.

 

2.2 ORDER BY - NULL

Oracle 에서는 NULL 값을 가장 큰 값으로 간주하여 오름차순 정렬시 가장 마지막에 위치합니다.

SQL Server의 경우 NULL 값을 가장 작은 값으로 간주하여 오름차순 정렬시 가장 먼저 위치합니다.

 

3. 함수

내장함수와 사용자정의 함수로 나눌 수 있습니다.

내장함수는 함수의 입력행수에 따라 단일행 함수와 다중행함수로 나눌수 있습니다.

단일행 함수는 TRUNC , LOWER, UPPER 등이 있습니다.

다중행함수는 집계함수 (Average Function), 그룹 함수(Group Function), 윈도우 함수(Window Function)으로 구분됩니다.

단일행 함수, 다중행 함수 모두 여러개의 인수가 입력되어도 단일값만을 반환합니다.

LENGHTH: 줄바꿈도 하나의 길이로 카운트

CHR: 줄바꿈을 문자로 -> REPLACE('A/nA',CHR(10)) --> 'AA'

 

4. DUAL

사용자 sys가 소유하며 모든 사용자가 엑세스 가능한 테이블입니다.

DUMMY 라는 VARCHAR2(1) 컬럼에는 X라는 값이 들어있는 행을 1건 포함하고 있습니다.

 

 

5. 단일행 NULL 관련 함수의 종류

일반형 함수	함수 설명
NVL(표현식1, 표현식2) ISNULL(표현식1, 표현식2)	표현식1의 결과값이 NULL이면 표현식2의 값을 출력한다
NULLIF(표현식1,표현식2)	표현식1이 표현식2와 같으면 NULL을 아니면 표현식1을 리턴한다
COALESCE(표현식1, 표현식2, ...)	임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다. 모든 표현식이 NULL이라면 NULL을 리턴한다

 

6. COUNT

COUNT(*): NULL 을 포함한 갯수를 return

COUNT(특정컬럼명): NULL은 카운트하지 않습니다.

 

7. SQL 문 실행 순서 

FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

 

8. 서브쿼리

인라인 뷰(View) : FROM 절에서 사용 

스칼라 서브쿼리(Scala Subquery): SELECT 문에서 사용

서브쿼리(subquery): WHERE 문에서 사용

 

9. ORDER BY 절

ORDER BY 절에는 SELECT 문에서 언급한 COLUMN을 정렬할 수 있습니다.

GROUP BY 절을 사용하는 경우 ORDER BY 절에 집계 함수를 사용할 수 있습니다.

SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 컬럼을 기준으로 정렬하는데 사용됩니다.

10. GROUP BY 절

SELECT 절과는 달리 ALIAS 명을 사용할 수 없습니다.

 

11. TOP, TOP WITH TIES

상위 N개의 데이터를 출력할 때 TOP N 이렇게 사용합니다.

TOP WITH TIES는 TOP과 동일하게 상위 N개 데이터를 출력하지만 동일 데이터가 존재하면 함께 출력합니다.

TOP WITH TIES는 ORDER BY 절과 함께 사용돼야합니다. 동일데이터의 기준이 해당 절의 컬럼이기 때문입니다.

 

12. 옵티마이저

옵티마이저는 SQL을 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부 핵심엔진입니다.

사용자가 던진 쿼리 수행을 위해 후보군이 될만한 실행계획을 찾습니다.

데이터 딕셔너리에 미리 수집해둔 오브젝트 통계 및 시스템 통계 정보를 이용해 실행 계획의 예상비용을 산정합니다.

각 실행계획을 비교해 최저비용을 갖는 하나를 선택합니다.

 

13. 순수 관계 연산자

RDB에 적용할 수 있도록 개발한 관계 연산자를 의미합니다.

SELECT, PROJECT, JOIN, DIVISION이 있습니다.

 

14. MERGE

INSERT와 UPDATE를 동시에 처리할 수 있는 SQL 구문입니다.

 

15. INNER JOIN

FROM 절에 사용되며 'A 테이블 INNER JOIN B 테이블 ON 조인조건' 형식으로 사용합니다. (?)

 

16. 모델링의 단계

데이터모델링을 할 때엔 중복, 비유연성, 비일관성에 유의하여야합니다.

중복: 여러 장소의 데이터베이스에 같은 정보를 저장하지 않도록 하여 중복성을 최소화합니다.

비유연성: 데이터의 정의를 데이터의 사용 프로세스와 분리하여 유연성을 높입니다.

비일관성: 데이터간의 상호 연관관계를 명확하게 정의하여 일광성 있게 데이터가 유지되도록 합니다.

 

데이터 모델링의 단계는 4단계로 나눠집니다.

1) 요구사항 분석

2) 개념 모델

 - 고객과의 합의를 도출해야합니다.

 - 기술적 요소는 배제되고 철저히 업무적인 관점에서 진행돼야합니다.

3) 논리 모델

 - 중복된 데이터가 전혀 없는 정규화된 상태로 모든 구성 요소가 정의된 상태여야합니다.

4) 물리 모델

 - 성능 목표를 달성하기 위한 구현 가능한 물리 구조 지원이 목표

 

17. 스키마

스키마란, 데이터베이스의 구조와 제약조건에 관해 전반적인 명세를 기술한 것입니다.

Attribute, Entity, Relation, 제약조건을 기술한 것으로 DB내에 어떤 구조로 데이터가 저장되는가를 나타내는 DB 구조를 스키마라고 합니다.

 

스키마는 사용자의 관에따라 외부, 개념, 내부 스키마로 분리됩니다.

1) 외부스키마 

 - 서브스키마, 사용자 뷰

 - 사용자나 개발자가 개인의 입장에서 필요한 데이터베이스의 논리적 구조를 정의한 것입니다.

 

2) 개념스키마

 - 모든 사용자 관점을 통합한 조직 전체 관점

 - 조직의 모든 응용시스템에서 필요로하는 개체 관계, 제약조건들을 포함

 - DB를 효율적으로 관리하는데 필요한 접근권한, 보안정책, 무결성 규칙에 관한 사항들도 추가로 포함

 - 외부 스키마가 각 사용자의 관점이라면 개념 스키마는 모든 사용자의 관점

 

3) 내부스키마

 - 물리적 저장장치 입장에서 DB가 저장되는 방법을 기술

 - 내부 레코드 형식, 내부 레코드의 물리적 순서, 인덱스 유/무 등에 관한 내용

 - 내부 스키마에 기술한 내용에 따라 운영체제의 파일 시스템에 의해 물리적 저장장치에 기록

 

18. ERD(Entity Relationship Diagram)

ERD는 요구사항을 그림으로 그려내어 관계를 도출하는 것입니다.

 - 작성 방법: 엔터티 도출 > 엔터티 배치 > 관계 설정 > 관계명 기술

 

19. 엔터티의 특징

1) 해당 업무에서 필요하고 관리하고자 하는 정보여야 함

2) 유일한 식별자에 의해 식별이 가능해야 함

3) 영속적으로 존재하는 두개 이상의 인스턴스의 집합

4) 업무 프로세스에 의해 이용되어야 함

5) 엔터티는 반드시 속성이 있어야 함

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

7) 사람, 장소, 물건, 사건, 개념 등의 명사에 해당

 

20. 엔터티의 분류

 - 엔터티 자신의 성격 / 발생시점에 따라 분류할 수 있습니다.

1) 유형/ 무형에 따른 분류

 - 유/무형에 따라 유형 엔터티, 개념 엔터티, 사건 엔터티로 구분됩니다.

 

 - 유형 엔터티 : 물리적 형태가 존재하며 업무로부터 엔터티를 구분하기 가장 용이 합니다. 

                      eg. 사원, 물품, 강사

 

 - 개념 엔터티 : 물리적 형태는 존재하지 않고 관리해야 할 개념적 정보로 구분되는 엔터티

                      eg. 조직, 보험상품

 

 - 사건 엔터티: 업무를 수행함에 따라 발생되는 엔터티. 비교적 발생량이 많아 각종 통계 자료에 이용될 수 있습니다.

                      eg. 주문, 청구, 미납

 

2) 발생 시점에 따른 분류

 - 엔터티의 발생시점에 다라 기본 엔터티(키엔터티), 중심엔터티, 행위 엔터티로 구분할 수 있습니다.

 

 - 기본 엔터티 : 그 업무에 원래 존재하는 정보로서 다른 엔터티와의 관계에 의해 생성되지 않고 독립적으로 생성 가능하고 타 엔터티의 부모 역할을 하게 됩니다.

   eg. 사원, 부서, 고객, 상품, 자재

 

 - 중심 엔터티 : 기본엔터티로부터 발생되고 업무의 중심적인 역할을 합니다. 데이터 양이 많이 발생되며 다른 엔터티와의 관계를 통해 많은 행위 엔터티를 생성합니다. 

  eg. 계약, 청구, 주문, 매출

 

 - 행위 엔터티 : 두개 이상의 부모 엔터티로부터 발생되고 자주 내용이 바뀌거나 데이터량이 증가됩니다. 분석초기 단계에서는 잘 나타나지 않으며 상세 설계 단계나 프로세스와 상관 모델링을 진행하며 도출될 수 있습니다.

  eg. 주문목록, 사원변경이력

 

21, 속성의 분류

 - 기본 속성: 기본 업무로부터 추출한 모든 속성. 

 - 설계 속성: 업무상 필요한 데이터 이외에 데이터 모델링을 위해/ 업무 규칙화를 위해 새로 만들거나 변형하여 정의한 속성. eg. 약품용기코드 (001-식품용기, 002 -약품용기)

 - 파생 속성(Derived Attribute): 데이터를 조회할 때 빠른 성능을 낼 수 있도록 하기 위해 원래 속성의 값을 계산하여 저장할 수 있도록 만든 속성. 가급적 파생속성을 적게 정의하는 것이 좋습니다.

 

22. 관계

 - 관계의 표기법은 관계명, 관계 차수, 선택성(선택사양)의 3가지 개념으로 표현합니다.

 - UML(Unified Modeling Language)에는 클래스 다이어그램의 관계 중 연관관계(Association)와 의존관계(Dependency)가 있고 이것은 실선과 점선의 표기법으로 다르게 표현이 됩니다.

 - 관계는 존재에 의한 관계와 행위에 의한 관계로 구분될 수 있고 ERD에서는 관계를 연결할 때, 존재와 행위를 구분하지 않고 단일화된 표기법을 사용합니다.

 

 

23. 식별자의 특성

대표성의 여부에 따라 주식별자, 보조식별자로 구분

엔터티내에서 스스로 생성되었는지 여부에 따라 내부 식별자와 외부 식별자로 구분

단일 속성으로 식별되는가에 따라 단말식별자와 외부 식별자로 구분

원래 업무적으로 의미가 있던 식별자인가에 따라 본질식별자, 인조식별자로 구분. 인조식별자는 일련번호와 같이 본래의 속성을 대체하여 새롭게 만든 식별자

 

 

24. 식별자/비식별자 관계

자식 엔터티가 부모엔터티로 부터 받은 외부식별자(FK)를 자신의 주식별자로 사용할 것인지 결정해야합니다.

1) 식별자 관계

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

 

2) 비식별자 관계

부모엔터티로부터 속성을 받았지만 자식엔터티의 주식별자로 사용하지 않고 일반 속성으로 사용하는 관계입니다.

 

 

25. INNER JOIN ~ USING

USING을 사용할 때에는 ALIAS를 사용할 수 없습니다.

 

26. CROSS JOIN

N*M건의 데이터 조합을 발생하고자 할 때 사용합니다. 모든 데이터의 조합을 볼 수 있습니다.

 

27. UNION

 

28. 3층 스키마

3층 스키마는 데이터베이스 전체구조를 기술한 것으로 외부스키마, 개념 스키마, 내부 스키마로 분류됩니다.

이중에서 개념 스키마는 데이터 베이스 전체 구조를 통합한 데이터베이스를 기술한 것입니다.

 

 

29. 다중값

다중 값은 엔터티에 있는 속성 중에서 여러 개의 튜플을 가지고 있는 속성입니다.

별도의 엔터티로 분리되어 있어야합니다.

 

30. 데이터 모델링

성능 데이터 모델링할때 '정규화 수행'을 가장 먼저 고려해야합니다.

 

 

31. 반정규화 절차

반정규화 절차	설명
대상 조사 및 검토	데이터 처리범위, 통계성 등을 확인해서 반정규화 대상 조사
다른 방법 검토	반정규화 수행 전 다른 방법 검토 클러스터링, 뷰, 인덱스 튜닝, 응용프로그램, 파티션 등을 검토
반정규화 수행	테이블, 속성, 관계 등을 반정규화

* 클러스터링: 인덱스 정보 저장 시 물리적으로 정렬해서 저장하는 방법

 

32. 파티션 기법

-Ranage Partition: 데이터값의 범위를 기준으로 파티션 수행

-List Partition: 특정 값을 지정하여 파티션 수행

-Hash Partition: 해시함수를 적용하여 파티션 수행

-Composite Partition; 범위와 해시를 복합 적용하여 파티션 수행

 

33. Super type, Sub type

고객 엔터티는 슈퍼타입이고 개인고객, 법인고객은 서브타입입니다. 즉, 부모-자식 관계입니ㅏㄷ.

변환방법

변환방법	설명
OneToOne Type	슈퍼 타입과 서브 타입을 개별 테이블로 도출 테이블 수가 많아서 조인이 많이 발생하여 관리가 어려움
PlusType	슈퍼타입과 서브타입 테이블로 도출 조인이 발생하고 관리가 어려움
Sing Type	슈퍼타입, 서브타입을 하나의 테이블로 도출 조인성능이 좋으며 관리가 편하지만, IO 성능이 나쁨

 

34. ROWID

실제 데이터를 보관하고 있는 데이터 블록의 주소를 의미합니다.

 

35. Join 방식

Sort Merge 방식이 Nested Loop 방식 조인보다 효율적으로 판단될때는 기본키와 외래키 관계에서 외래키에 인덱스가 없을 때입니다.

 

36. Index-Organized Table

테이블을 참조하지 않고 인덱스를 생성하는 것

즉, 인덱스의 key가 Fetch 하는 추출의 칼럼으로 이뤄진 인덱스

 

37. Parsing

SQL Parsing은 소프트파싱, 하드파싱이 있습니다.

소프트 파싱은 SQL 파싱 정보를 저장하고 있는 라이브러리 캐시에서 파싱된 정보를 찾을 수 있는 경우입니다.

하드 파싱은 라이브러리 캐시에서 해당 SQL문 파싱 정보를 찾을 수가 없을 때 파싱을 수행하고 실행합니다.

SQL문은 파싱 > 실행 > 인출(Fetch) 단계로 실행되는데, 소프트 파싱은 파싱 단계를 생략하고 실행단계로 넘어값니다.

 

38. Database call

사용자가 select문을 실행하면 parsing > execute > fetch 순으로 진행됩니다.

Insert, update문장에서는 절대로 fetch call이 발생하지 않습니다.

Database call이 적을수록 성능에 유리합니다.

 

39. Dynamic SQL

프로그램언어의 String 변수에 SQL문을 문자열 형태로 저장하고 실행하는 SQL문입니다.

동적 SQL문이라고 하드파싱을 반복하진 않습니다.