[SQLD] 7. SQL 기본 3 - 데이터베이스 로그, TRUNCATE, Order by와 index 힌트, like문 underscore

 

1. 데이터베이스 로그

 

데이터 베이스는 트랜잭션이 발생하면 데이터베이스의 무결성을 확보하기 위해서 로그 파일을 남긴다. 트랜잭션이 일어나는 도중에 정전이나 예기치 못한 특별한 상황이 발생하여 시스템이 다운될수도 있다. 따라서 데이터베이스와 관련된 로그파일을 남겨두어 트랜잭션의 원자성이 깨진 부분 등을 복구할 수 있도록 한다. 다만 로그는 모든 정보를 남기는 것이 아니라, insert나 update 등 데이터의 변화가 생길 때만 기록을 남긴다. select 처럼 조회하는 부분은 로그 파일에 기록하지 않는다.

 

MSSQL의 로그파일 관리(참조1)

MSSQL은 3가지 타입의 로그파일을 남긴다.

 

• Primary Data File : 주 데이터 파일이며 데이터베이스의 나머지 파일들을 가리키고 있는 파일이다. 모든 데이터베이스에는 한 개의 주 데이터 파일이 있다. .mdf의 파일 확장자를 갖는다.
• Secondary Data File : 보조 데이터 파일이며 주 데이터 파일이 아닌 모든 데이터 파일을 구성한다. 데이터베이스에 따라 없을 수도 있고, 여러 개가 있을 수도 있다. .ndf 파일 확장자를 갖는다.
• Log File : 데이터베이스를 복구하는데 필요한 모든 정보를 담고 있다. 데이터베이스마다 적어도 1개 이상의 로그파일이 있어야 한다. .ldf 파일 확장자를 갖는다.

 

일반적으로 많이 사용하는 테이블에 대한 데이터베이스 파일은 주 데이터 파일 그룹으로 생성하여 관리하고, 사용률이 적은 파일들은 보조 데이터 파일 그룹으로 생성하여 관리한다. 주 데이터 파일 그룹은 .mdf, .ndf로 구성되며 보조 데이터 파일 그룹은 .ndf 파일들로만 구성한다. 이렇게 사용률에 따라 데이터베이스 파일들을 나누어 관리하면 물리적인 접근 경쟁을 분산하여 데이터베이스의 성능을 향상시킬 수 있는 효과가 있다.

 

 

NoLogging 옵션 사용

 

Nologging 옵션은 로그파일의 기록을 최소화하여 입력 시 성능을 향상시키는 방법이다. 원래 Buffer Cache 영역에 로그를 기록하면서 트랜잭션을 진행하는데, Nologging 옵션을 적용하면 이를 생략한다.

 

ALTER TABLE DEPT NOLOGGING;

 

 

 

---

 

2. TRUNCATE TABLE : 테이블 용량 초기화

 

테이블 용량 초과

Oracle 데이터베이스는 Extent 라는 단위로 테이블의 용량을 관리한다. 만약 Extent의 크기가 MAX_EXTENTS를 넘어서면 용량 초과 오류가 발생한다. 

 

MAX_EXTENTS 조회

SELECT TABLE_NAME, MAX EXTENTS
FROM USER_TABLES;

 

H2 Database에서 테이블 사용 용량 조회 문법 : CALL DISK_SPACE_USED('테이블명');

 

 

테이블의 용량 초기화

 

DELETE 문은 테이블의 데이터를 삭제하지만, 테이블의 용량을 감소시키지는 않는다. 

 

테이블의 모든 데이터를 삭제하고 용량까지 초기화할려면 TRUNCATE TABLE 테이블명; 문법을 사용해야한다.

 

 

 

 

---

 

3. Order by

 

Order by는 SELECT문의 모든 실행이 끝난 후, 출력 바로 직전에 수행된다.

 

정렬은 데이터베이스 메모리를 사용하기 때문에, 대량의 데이터를 정렬하면 성능 저하가 발생할 수 있다. Oracle 데이터베이스는 메모리 내부에 할당된 SORT_AREA_SIZE를 사용한다. 만약 SORT_AREA_SIZE가 너무 작으면 성능 저하가 발생한다.

 

일반적으로 데이터베이스는 Order by를 지정하지 않아도, 기본키를 기준으로하여 오름차순으로 정렬한다.

 

Index를 이용한 정렬 회피

Oracle에서는 Order by외에 Index를 통한 정렬을 할 수 있도록 힌트를 제공한다. 이러면 Order by 없이 index를 사용하므로 정렬에 의한 메모리 부하를 줄일 수 있다.

 

SELECT /*+ INDEX_DESC(A) */
FROM EMP A;

 

 

---

 

4. Like 문 Underscore

 

underscore는 글자수를 의미한다. 아래 예제에서 '이__' (underscore 2개) 를 입력하여 '이' 뒤에 두 글자가 더 있는 데이터를 조회하였다.

 

 

 

---

참조

 

0. [도서] 영진닷컴 - SQL 개발자 이론서 + 기출문제

 

1. 공대인들이 직접쓰는 컴퓨터 공부방 - [ MSSQL ] 데이터 파일(.mdf/.ndf)과 로그파일(.ldf)

https://hackersstudy.tistory.com/60