오라클로 배우는 데이터베이스 개론과 실습 2판 (박우창, 남송휘, 이현룡 지음, 한빛 아카데미)를 읽고 공부하며 기록한 내용입니다.오류가 있을 수 있습니다.
ROWNUM
WHERE문을 ORDER BY보다 먼저 처리하기 때문에 부속질의로 처리해 주어야 함
📍02. 부속질의
SQL문 안에 다른 SQL문이 중첩된 질의, 내부 질의라고도 한다.(⭤주 질의, 외부 질의)
EX) 박지성 고객의 주문 내역을 확인하려면?
- 조인을 사용할 경우 : Customer 테이블과 Orders 테이블의 고객번호로 조인한 후 필요한 데이터 추출
- 부속질의를 사용할 경우 : Customer 테이블에서 박지성 고객의 고객번호를 찾고, 찾은 고객번호를 바탕으로 Orders 테이블에서 확인한다.
부속질의는 위치와 역할에 따라 3가지로 구분된다.
- 중첩 질의: WHERE절, 일반적인 부속 질의, 보통 술어,조건과 함께 사용되므로 술어 부속질의라고도 한다.
- 스칼라 부속질의: SELECT절, 부속질의의 결과 값을 단일 행, 단일 열의 스칼라 값으로 반환한다.
- 인라인 뷰: FROM절
+ 주 질의 값을 참조하느냐에 따라 상관 부속질의, 비상관 부속질의(일반 부속질의)
+ 반환하는 행에 따라 단일 행 부속질의, 다중 행 부속질의로도 구분이 가능함
- ALL, SOME(ANY) 연산자 활용하기
ex) 금액 > SOME(SELECT 단가 FROM 상품) ⭢ 상품 테이블에 있는 어떠한(최소한 하나) 단가보다 큰 경우 참
금액 > ALL(SELECT 단가 FROM 상품) ⭢ 상품 테이블에 있는 모든 단가보다 큰 경우 참
인라인 뷰 예제 질의 4-19
-- 고객번호가 2이하인 고객의 판매액을 보이시오
//교재 풀이
select cs.name, SUM(od.saleprice) "total"
from (select custid, name from Customer WHERE custid <= 2) cs, Orders od
-- 처음에 테이블을 가져올 때, Customer 테이블 전체를 가져오는 것이 아니라, 고객번호가 2이하인 행만 선택하여 cs라는 이름의 테이블로 가져옴
where cs.custid = od.custid
group by cs.name
// 내 풀이 (인라인 뷰(FROM절의 부속질의)를 활용하지 않음)
select name, sum(saleprice) from Customer, Orders where Customer.custid <=2 and Customer.custid = Orders.custid group by name
부속 질의를 사용하지 않고 이렇게 동등 조인 방식으로 풀게 되면 조인 결과 테이블에서 필요없는 데이터를 제거해야 하므로 성능저하가 발생한다. 인라인 뷰를 사용하면 조인에 참여하기 직전 Customer 테이블에서 필요한 데이터만 뽑아 조인시킬 수 있으므로 처리 성능을 높일 수 있다.
📍03. 뷰
- 하나 이상의 테이블을 합하여 만든 가상의 테이블
- 우리가 지금까지 사용해 온 테이블은 물리적인 디스크에 데이터를 실제로 저장한다. 하지만 뷰는 가상의 테이블이므로 실제 데이터를 디스크에 저장하지 않고, 단지 뷰를 생성할 때 사용한 SELECT문의 정의를 DBMS가 저장한다.
- 뷰의 장점: 편리성, 보안성, 논리적 데이터 독립성
- SELECT문을 제외한 일부 물리적인 테이블의 갱신작업을 수행하는 데에는 제약이 있다. 뷰에서 수정 요청을 하더라도, 가상의 테이블 뷰가 아닌 실제 원본 테이블에서 작업이 수행된다.
- 뷰의 생성
CREATE VIEW 뷰이름 [(열이름 [...,n])]
AS <SELECT 문> - 뷰의 수정
CREATE OR REPLACE VIEW 뷰이름 [(열이름[,...n])]
AS SELECT문 - 뷰의 삭제
DROP VIEW 뷰이름 [,...n];
cf) 시스템 뷰: 모든 DBMS에서는 데이터베이스 개체나 시스템의 통계 정보 등을 사용자가 직접 확인할 수 있도록 시스템 뷰를 만들어 제공한다.
📍04. 인덱스
DBMS는 운영체제에서 실행되는 응용 프로그램의 일종이다. SQL 작업 도구(SQL Developer, SQL Plus)를 통해 SQL 문을 작성하여 실행하면 DBMS에 의해 처리 방법이 결정되고, 운영체제를 통해 각 장치에 명령이 내려져 작업이 처리된다.
실제 데이터가 저장되는 곳은 하드디스크, SDD, USB 메모리 같은 보조기억장치이다. 하드디스크는 원형의 플레이트로 구성되어 있고, 이 플레이트는 여러 트랙으로, 트랙은 몇 개의 섹터로 나뉜다. 원형의 플레이트는 초당 빠른 속도로 회전하고, 회전하는 플레이트를 하드디스크의 액세스 암과 헤더가 접근하여 원하는 섹터에서 데이터를 가져온다. 디스크의 입출력 시간을 액세스 시간이라고 한다.
액세스 시간 = 탐색시간(액세스 헤드 -> 트랙) + 회전 지연시간(섹터 -> 엑세스 헤드) + 데이터 전송시간(데이터를 주기억장치로 읽어옴)
DB버퍼캐시
DBMS는 데이터베이스별로 하나 이상의 데이터 파일을 생성한다. 테이블은 생성 시 정의된 내용에 따라 논리적으로 구분 지어 각각의 데이터 파일에 저장한다.
- 데이터 파일
- 온라인 리두 로그
- 컨트롤 파일
인덱스란 자료를 쉽고 빠르게 찾을 수 있도록 만든 데이터 구조이다. 인덱스의 특징은 아래와 같다.
- 인덱스는 테이블에서 한 개 이상의 속성을 이용하여 생성한다.
- 빠른 검색과 함께 효율적인 레코드 접근이 가능하다.
- 순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간을 차지한다.
- 저장된 값들은 테이블의 부분집합이 된다.
- 일반적으로 B-tree 형태의 구조를 가진다.
- 데이터의 수정, 삭제 등 변경이 발생하면 인덱스의 재구성이 필요하다.
오라클 B-tree는 리프 노드 블록에 연속된 키 값의 레코드에 대한 rowid를 지정한다. rowid는 실제 데이터 값이 아닌 테이블 상의 데이터 위치를 지정하며 <Block번호 - Block 내의 Row가 위치한 순번>의 형태로 구성되어 있다.
오라클 인덱스의 종류
- B-tree 인덱스
- IOT(Index Organized Table, 인덱스 구조 테이블)
- Bitmap Index(비트맵 인덱스)
- Function-Base Index(함수 기반 인덱스)
인덱스 생성의 고려사항
아무 의미 없이 인덱스를 생성하면 검색이 더 느려지고 저장공간만 낭비하게 된다. 데이터 양이 별로 없거나 데이터 값이 몇 종류 안되어 선택도가 높으면 인덱스가 없는 게 더 빠를 수도 있다.
- 인덱스는 WHERE절에 자주 사용되는 속성이어야 한다
- 인덱스는 조인에 자주 사용되는 속성이어야 한다
- 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다(테이블 당 4~5개 정도 권장)
- 속성이 가공되는 경우 사용하지 않는다
- 속성의 선택도가 낮을 때 유리하다(속성의 모든 값이 다를 경우)
선택도란? 1/서로 다른 값의 개수
ex) 100개의 행을 가진 테이블에 값이 남,여 두 가지라면-> 선택도가 높다 1/2
선택도가 낮을 때, 값이 많아서 데이터를 단순히 검색해서 찾기 어려울 때 인덱스를 통해서 시간을 줄일 수 있다.
즉 선택도가 낮을 때 유리하다!
- 인덱스 생성
CREATE [REVERSE] [UNIQUE] INDEX [인덱스이름]
ON 테이블이름 (컬럼 [ASC|DESC] [{, 컬럼 [ASC|DESC]} ...])[;]
- 인덱스 재구성
ALTER [REVERSE] [UNIQUE] INDEX 인덱스 이름
[ON {ONLY} 테이블이름 (컬럼이름 [{, 컬럼이름}...)] REBUILD[;]
- 인덱스 삭제
DROP INDEX ix_book;
예제의 내용뿐으로는 뷰의 생성과 수정이 너무 비슷해보인다. 수정을 할 때도 거의 다 다시 써주는데.. 구분되어 있는 이유가 있겠지