[Database] 4. SQL 고급

[ 본 사진은 쉽게 배우는 오라클로 배우는 데이터베이스 개론과 실습 ppt에서 캡처했습니다. ]

1. 내장함수

---

[ 내장 함수 ]

• SQL에서는 함수의 개념을 사용하는데, 수학의 함수와 마찬가지로 특정 값이나 열의 값을 입력 받아 그 값을 계산하여 결과 값을 돌려줌.

• SQL의 함수는 DBMS가 제공하는 내장 함수(built-in function)와 사용자가 필요에 따라 직접 만드는 사용자 정의 함수(user-defined function)로 나뉨.

• SQL내장함수는 상수나 속성 이름을 입력 값으로 받아 단일 값을 결과로 반환함.

• 모든 내장 함수는 최초에 선언될 때 유효한 입력 값을 받아야 함.

[ 숫자 함수 ]

[ 문자함수 ]

[ 날짜 및 시간 함수 ]

[ NULL 값 처리 ]

• NULL은 아직 지정되지 않은 값으로 0 또는 ''(빈문자) 그리고 ' '(공백) 과는 다른 특별한 값이다.

• NULL값은 비교 연산자로 비교가 불가능함.

• NULL값의 산술 연산을 수행하면 결과 역시 NULL 값으로 반횐됨.

• NULL+숫자 연산의 결과는 NULL

• 집계 함수 계산 시 NULL이 포함된 행은 집계에서 빠짐.

• 해당되는 행이 하나도 없을 경우 SUM, AVG 함수의 결과는 NULL이 되며, COUNT함수의 결과는 0.

[ ROWNUM ]

• 내장 함수는 아니지만 자주 사용되는 문법임.

• 오라클에서 내부적으로 생성되는 가상 컬럼으로 SQL 조회 결과의 순번을 나타냄.

• 자료를 일부분만 확인하여 처리할 때 유용함.

2. 부속질의

---

[ 부속질의(Subquery) ]

• 하나의 SQL문 안에 다른 SQL 문이 중첩된 질의를 말함.

• 다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공할 때 사용함.

• 보통 데이터가 대량일 대 데이터를 모두 합쳐서 연산하는 조인보다 필요한 데이터만 찾아서 공급해주는 부속질의가 성능이 더 좋음.

• 주질의(Main Query, 외부질의)와 부속질의(Sub Query, 내부 질의)로 구성됨.

[ 스칼라 부속질의(Scalar Subquery) - SELECT 부속질의 ]

• SELECT 절에서 사용되는 부속질의로, 부속질의의 결과 값을 단일 행, 단일 열의 스칼라 값으로 반환함.

• 스칼라 부속질의는 원칙적으로 스칼라 값이 들어갈 수 있는 모든 곳에 사용 가능하며, 일반적으로 SELECT문과 UPDATE SET 절에 사용됨.

• 주질의와 부속질의와의 관계는 상관/비상관 모두 가능함.

[ 인라인 뷰(Inline View) - FROM 부속질의 ]

• From 절에서 사용되는 부속질의.

• 테이블 이름 대신 인라인 뷰 부속질의를 사용하면 보통의 테이블과 같은 형태로 사용할 수 있음.

• 부속질의 결과 반환되는 데이터는 다중 행, 다중 열이어도 상관없음.

• 다만 가상의 테이블인 뷰 형태로 제공되어 상관 부속질의로 사용될 수는 없음.

[ 중첩질의(Nested Subquery) - WHERE 부속질의 ]

• WHERE 절에서 사용되는 부속질의.

• WHERE 절은 보통 데이터를 선택하는 조건 혹은 술어(predicate)와 같이 사용됨. 그래서 중첩질의를 술어 부속질의( Predicate subquery)라고도 함.

• 부속질의 결과 반환되는 데이터는 다중 행, 다중 열이어도 상관없음.

• 다만 가상의 테이블인 뷰 형태로 제공되어 상관 부속질의로 사용될 수는 없음.

3. 뷰

---

[ 뷰(View) ]

• 뷰(View)는 하나 이상의 테이블을 합하여 만든 가상의 테이블.

• 편리성: 미리 정의된 뷰를 일반 테이블처럼 사용할 수 있기 때문에 편리함. 또 사용자가 필요한 정보만 요구에 맞게 가공하여 뷰로 만들어 쓸 수 있음.

• 재사용성: 자주 사용되는 질의를 뷰로 미리 정의해 놓을 수 있음.

• 보안성: 각 사용자별로 필요한 데이터만 선별하여 보여줄 수 있음. 중요한 질의의 경우 질의 내용을 암호화할 수 있음.

[ 뷰의 생성 ]

[ 뷰의 수정 ]

[ 뷰의 삭제 ]

4. 인덱스(Index)

---

[ 데이터베이스의 물리적 저장 ]

• 데이터가 저장되는 곳: 하드디스크, SSD, USB 메모리

• 하드디스크의 3가지 특징

• 원형의 플레이트(Plate)로 구성되어 있고, 이 플레이트는 논리적으로 트랙으로 나뉘며 트랙은 다시 몇개의 섹터로 나뉨.

• 원형의 플레이트는 초당 빠른 속도로 회전하고, 회전하는 플레이트를 하드디스크의 액세스 암(arm)과 헤더가 접근하여 원하는 섹터에서 데이터를 가져옴.

• 하드디스크에 저장된 데이터를 읽어 오는 데 걸리는 시간은 모터(Motor)에 의해서 분당 회전하는 속도(RPM, Revolutions Per Minute), 데이터를 읽을 대 액세스 암이 이동하는 시간(Latency time), 주기억 장치로 읽어오는 시간(Transfer Time)에 영향을 받음. 

[ 인덱스(index, 색인) ]

• 인덱스: 도서의 색인이나 사전과 같이 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터 구조

위의 그림과 같이 하나의 루트노드로부터 시작하여 인덱스들을 이용하여 데이터가 있는 테이블을 참조합니다. 인덱스가 데이터 테이블에 도달하기 전까지 인덱스를 점점 구체화시켜가며 최하위의 데이터 테이블에 도착하면 데이터를 참조합니다. B-Tree에서 데이터를 찾아가는 과정에 대해서는 아래에서 자세히 다루도록 하겠습니다!

[ 인덱스의 특징 ]

• 인덱스는 테이블에서 한 개 이상의 속성을 이용하여 생성함.

• 빠른 검색과 함께 효율적인 레코드 접근이 가능함.

• 순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간을 차지함.

• 저장된 값들은 테이블의 부분집합이 됨.

• 일반적으로 B-tree 형태의 구조를 가짐

• 데이터의 수정, 삭제 등의 변경이 발생하면 인덱스의 재구성이 필요함.

자료구조 중에서 원하는 값을 최고로 빠르게 가져올 수 있는 구조가 트리구조라고 합니다. 그래서 데이터베이스의 인덱스에서도 트리구조를 활용하는데, 속성을 인덱스로 활용하여 높은 효율성을 보여줍니다. 어떤 값을 삭제 또는 변경하는 경우에 트리의 구조가 무너져서 순서대로 정렬되지 않은 트리구조로 변질될 수 있으므로 수정, 삭제와 같은 연산을 수행하는 경우에는 인덱스를 재구성해주어야 합니다.

[ 인덱스의 생성 ]

• 인덱스는 WHERE절에 자주 사용되는 속성이어야 함.

• 인덱스는 Join(조인)에 자주 사용되는 속성이어야 함

• 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있음

• 속성이 가공되는 경우에 사용하지 않음

• 속성의 선택도가 낮을 때 유리함

인덱스가 WHERE절에서 자주 사용되는 속성이어야 하는 이유는 자명합니다. WHERE절에 자주 사용되는 속성일수록 그 속성을 이용하여 접근을 많이 한다는 것이고 접근의 효율을 높이기 위해서는 그 속성을 인덱스로 사용하여 B-TREE에 접근하는 것이 최적이기 때문입니다. 조인도 마찬가지입니다. 하지만 무분별하게 인덱스를 많이 생성하면 효율이 느려질 수 있으므로 한 테이블에 4~5개를 권장합니다. 또한 속성이 가공된다는 것은 변경 또는 삭제의 연산을 의미하는데, 변경 또는 삭제 연산을 수행하는 경우에는 인덱스의 재구성을 필수적으로 해주어야 하므로 가공되지 않는 속성의 경우에 유리합니다. 또한 속성의 선택도가 낮을 때 즉, 속성의 모든 값이 다른 경우에, 하나의 인덱스 만을 사용하여 접근하므로 효율이 좋아집니다. 그러므로 선택도가 낮은 속성을 인덱스로 만드는 것이 유리합니다.

[ 인덱스의 재구성과 삭제 ]

• 인덱스의 재구성은 ALTER INDEX 명령을 사용한다.

관련 포스팅

1. 데이트베이스 시스템 (1/8)

2. 관계데이터 모델 (2/8)

3. SQL 기초 (3/8)

4. SQL 고급 (4/8)

5. 데이터베이스 프로그래밍 (5/8)

6. 데이터 모델링(Data Modeling) (6/8)

7. 정규화(Normalization) (7/8)

8. 트랜잭션, 동시성 제어, 회복 (8/8)