[오라클로 배우는 데이터베이스 개론과 실습]SQL 고급

티스토리 뷰

Database/Oracle

[오라클로 배우는 데이터베이스 개론과 실습]SQL 고급

retto9522 2024. 9. 11. 11:11

내장 함수

SQL에서는 함수의 개념을 사용합니다. 함수란 특정 값을 입력받아 그 값을 계산하여 결과 값을 돌려주는 것을 의미합니다.
함수는 DBMS가 제공하는 내장 함수(built-in function)와 사용자가 필요에 따라 직접 정의하는 사용자 정의 함수(user-defined function)로 나누어집니다.
SQL 내장 함수는 상수나 속성 이름을 입력 값으로 받아 단일 값을 결과로 반환합니다.
모든 내장 함수는 최초에 선언될 때 유효한 입력 값을 받아야 합니다.

숫자 함수

문자 함수

날짜 및 시간 함수

NULL 값 처리

NULL은 아직 지정되지 않은 값으로 0 또는 " (빈문자), ' ' (공백) 과는 다른 특별한 값입니다.
NULL 값은 비교 연산자로 비교가 불가능합니다.
NULL 값의 산술 연산을 수행하면 결과 역시 NULL 값으로 반환됩니다.
NULL + 숫자 연산의 결과는 NULL입니다.
집계 함수(AVG, SUM, COUNT, ...) 계산 시 NULL이 포함된 행은 집계에서 제외됩니다.
해당되는 행이 하나도 없을 경우 SUM, AVG 함수의 결과는 NULL이 되며, COUNT함수의 결과는 0입니다.

ROWNUM

내장 함수는 아니지만 자주 사용되는 문법입니다.
오라클에서 내부적으로 생성되는 가상 컬럼으로 SQL 조회 결과의 순번을 나타냅니다.
자료를 일부분만 확인하여 처리할 때 유용합니다.

부속질의

부속질의(Subquery)

SQL문 안에 다른 SQL문이 중첩된 질의를 말합니다.
다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공할 때 사용합니다.
데이터가 대량일 때 데이터를 모두 합쳐서 연산하는 조인보다 필요한 데이터만 찾아서 공급해 주는 부속 질의가 성능이 더 좋습니다.
주 질의(Main Query, 외부질의)와 부속 질의(Sub Query, 내부 질의)로 구성됩니다.

스칼라 부속 질의

SELECT 절에서 사용되는 부속질의로, 부속질의의 결과 값을 단일 행, 단일 열의 스칼라 값으로 반환합니다.
스칼라 부속질의는 원칙적으로 스칼라 값이 들어갈 수 있는 모든 곳에 사용 가능하며, 일반적으로 SELECT문과 UPDATE SET 절에 사용됩니다.
주 질의와 부속 질의와의 관계는 상관/비상관 모두 가능합니다.

스칼라 값
SQL에서 스칼라 값은 단일 값을 의미합니다. 즉, 하나의 데이터 항목을 나타내며, 숫자, 문자열, 날짜 등과 같은 기본 데이터 유형으로 구성됩니다. 스칼라 값은 테이블의 한 열에서 특정 행에 해당하는 값으로 볼 수 있습니다. 예를 들어,
SELECT name FROM users 쿼리에서 name 열의 각 값은 스칼라 값입니다.
반면에 여러 값을 포함하는 배열이나 테이블과 같은 복합 데이터 구조는 스칼라 값이 아닙니다.

인라인 뷰

FROM 절에 사용되는 부속질의입니다.
테이블 이름 대신 인라인 뷰 부속질의를 사용하면 보통의 테이블과 같은 형태로 사용할 수 있습니다.
부속질의 결과로 반환되는 데이터는 다중 행, 다중 열이어도 상관없습니다.
다만 가상의 테이블인 뷰 형태로 제공되어 상관 부속질의로 사용될 수는 없습니다.

중첩 질의

WHERE 절에 사용되는 부속질의입니다.
WHERE 절은 보통 데이터를 선택하는 조건 혹은 술어와 같이 사용됩니다. 그래서 중첩 질의를 술어 부속 질의라고도 합니다.
부속질의 결과로 반환되는 데이터는 다중 행, 다중 열이어도 상관없습니다.
다만 가상의 테이블은 뷰 형태로 제공되어 상관 부속질의로 사용될 수 없습니다.

뷰

뷰는 하나 이상의 테이블을 합하여 만든 가상의 테이블
편리성: 미리 정의된 뷰를 일반 테이블처럼 사용할 수 있기 때문에 편리합니다. 또, 사용자가 필요한 정보만 요구에 맞게 가공하여 뷰로 만들어 쓸 수 있습니다.
재사용성: 자주 사용되는 질의를 뷰로 미리 정의해 놓을 수 있습니다.
보안성: 각 사용자 별로 필요한 데이터만 선별하여 보여줄 수 있습니다. 중요한 질의의 경우 질의 내용을 암호화할 수 있습니다.

뷰의 생성

뷰의 수정

뷰의 삭제

인덱스(Index)

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

데이터가 저장되는 저장 장치: 하드 디스크, SSD, USB 메모리
하드 디스크의 3가지 특징
- 원형의 플레이트로 구성되어 있으며, 이 플레이트는 논리적으로 트랙으로 나뉘며 트랙은 다시 몇 개의 섹터로 나뉩니다.
- 원형의 플레이트는 초당 빠른 속도로 회전 하며, 회전하는 플레이트를 하드 디스크의 액세스 암과 헤더가 접근하여 원하는 섹터에서 데이터를 가져옵니다.
- 하드 디스크에 저장된 데이터를 읽어오는 데 걸리는 시간은 모터에 의해서 분당 회전하는 속도(RPM, Revolution Per Minute), 데이터를 읽을 때 액세스 암이 이동하는 시간(Latency Time), 주 기억 장치로 읽어오는 시간(Transfer Time)에 영향을 받습니다.

인덱스(Index, 색인)

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

위 그림과 같이 하나의 루트 노드부터 시작하여 인덱스를 이용하여 데이터가 있는 테이블을 참조합니다.

인덱스가 데이터 테이블레 도달하기 전까지 인덱스를 점점 구체화 시켜 가며 최하위의 데이터 테이블에 도착하면 데이터를 참조합니다. B-Tree에서 데이터를 찾아가는 과정은 아래에서 자세히 다루도록 하겠습니다.

인덱스의 특징

인덱스는 테이블에서 한 개 이상의 속성을 이용하여 생성합니다.
빠른 검색과 함께 효율적인 레코드 접근이 가능합니다.
순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간을 차지합니다.
저장된 값들은 테이블의 부분 집합이 됩니다.
일반적으로 B-Tree의 구조를 가집니다.
데이터의 수정, 삭제 등의 변경이 발생하면 인덱스의 재구성이 필요합니다.

자료구조 중에서 원하는 값을 최고로 빠르게 가져올 수 있는 구조가 트리구조입니다. (시간 복잡도가 O(log N)이기 때문에 비교적 적은 시간이 소모됩니다.) 그래서 데이터베이스의 인덱스에서도 트리 구조를 활용하는데, 속성을 인덱스로 활용하여 높은 효율성을 보여줍니다.

어떤 값을 삭제 또는 변경하는 경우에 트리의 구조가 무너져서 순서대로 정렬되지 않은 트리 구조로 변질될 수 있으므로 수정, 삭제와 같은 연산을 수행하는 경우에는 인덱스를 재구성해주어야 합니다.

인덱스의 생성

인덱스는 WHERE절에 자주 사용되는 속성이어야 합니다.
인덱스는 JOIN에 자주 사용되는 속성이어야 합니다.
단일 테이블에 인덱스가 많으면 속도가 느려질 수 있습니다.
속성이 가공되는 경우에 사용하지 않습니다.
속성의 선택도가 낮을 때 유리합니다.

인덱스가 WHERE절에서 자주 사용되는 속성이어야 하는 이유는 자명합니다. WHERE절에 자주 사용되는 속성일수록 그 속성을 이용하여 접근을 많이 한다는 것이고 접근의 효율을 높이기 위해서는 그 속성을 인덱스로 사용하여 B-Tree에 접근하는 것이 최적이기 때문입니다. JOIN도 마찬가지입니다.

하지만, 무분별하게 인덱스를 많이 생성하면 효율이 떨어질 수 있으므로 한 테이블에 4~5개를 권장합니다.

또한, 속성이 가공된다는 것은 변경 또는 삭제의 연산을 의미하는데 변경 또는 삭제 연산을 수행하는 경우에는 인덱스의 재구성을 필수적으로 해주어야 하므로 가공되지 않는 속성의 경우에 유리합니다.

또한, 속성의 선택도가 낮을 때 즉, 속성의 모든 값이 다른 경우에 하나의 인덱스만을 사용하여 접근하므로 효율이 좋아집니다. 그러므로 선택도가 낮은 속성을 인덱스로 만드는 것이 유리합니다.

인덱스의 재구성과 삭제

ALTER INDEX 명령을 통해 인덱스를 재구성할 수 있습니다.

위에서 설명했던 내장 함수에 대한 내용이 좀 많아, 따로 포스팅에 자세히 정리해 놓았습니다.

[DB]내장 함수