1. 프로젝트 생성 후 pom.xml의 수정 -> 데이터베이스 관련 처리 -> 스프링 MVC 처리와 같은 처리
2. 시퀀스를 생성할 때는 데이터베이스의 다른 오브젝트를 구분하기 위해서 'seq_'와 같이 시작하는 것이 일반적입니다.
테이블을 생성할 때는 'tbl_' 로 시작 하거나 't_'와 같이 구분이 가능한 단어를 앞에 붙여주는 것이 좋습니다.
tbl_board 테이블은 고유의 번호를 가지기 위해서 bno 칼럼을 지정했고, 제목(title),내용(content), 작성자(writer)를 칼럼으로 지정합니다.
테이블을 설계할 때는 가능하면 레코드의 생성 시간과 최종 수정 시간을 같이 기록하는 것이 좋기 때문에 생성 시간(regdate)과 레코드의 최종 수정 시간(updatedate)칼럼을 작성 합니다. 이때 기본 값으로 sysdate를 지정해서 레코드가 생성된 시간은 자동으로 기록될 수 있게 합니다.
테이블 생성 이후에는 'alter table..'을 이용해서 테이블에 Primary Key(이하 PK)를 지정해 주었습니다. PK를 지정할때 'pk_board'라는 이름을 부여하는데, 이 이름은 뒤에서 중요하게 사용되므로 반드시 의미를 구분할 수 있게 생성해 주는 것이 좋습니다.
테이블을 생성하고 나면 여러개의 데이터를 추가해주는데 이런 의미없는 데이터를 더미 데이터라고 합니다.
insert into tbl_board(bno,title,content,writer)
values(seq_board.nextval, '테스트 제목', '테스트 내용', 'user00');
3. 실행계획과 order by 그 상위단 내용은 오라클 데이터베이스의 페이징 처리(여기가 왜 페이징 처리인지 물어보기)
오라클의 페이징 처리를 제대로 이해하기 위해서 반드시 알아두어야 하는 것이 실행 계획 입니다.(execution plan)입니다. 실행계획은 말 그대로 'SQL을 데이터베이스에서 어떻게 처리할 것인가?'에 대한 것 입니다. SQL이 데이터베이스에 전달되면 데이터베이스는 여러 단계를 거쳐서 해당 SQL을 어떤 순서와 방식으로 처리할 것인지 계획을 세우게 됩니다.
데이터베이스에 전달된 SQL문은 아래와 같은 과정을 거쳐서 처리됩니다.
SQL파싱 -> SQL최적화 -> SQL실행
SQL파싱 단계에서는 SQL구문에 오류가 있는지 SQL을 실행해야 하는 대상 객체( 테이블, 제약조건, 권한 등) 가 존재하는지를 검사합니다. SQL최적화 단계에서는 SQL이 실행되는데 필요한 비용(cost)를 계산하게 됩니다. 이 계산된 값을 기초로 해서 어떤 방식으로 실행하는 것이 가장 좋다는 것을 판단하는 '실행계획(execution plan)'을 세우게 됩니다.
SQL실행 단계에서는 세워진 실행계획을 통해서 메모리상에서 데이터를 읽거나 물리적인 공간에서 데이터를 로딩하는 등의 작업을 하게 됩니다.
개발자들은 도구를 이용하거나 SQL Plus등을 이용해서 특정한 SQL에 대한 실행 계획을 알아볼 수 있습니다. SQL Developer에서는 간단히 버튼을 클릭해서 실행 계획을 확인할 수 있습니다.
예를들어, '게시물 번호의 역순으로 출력하라' 는 처리를 한다면 SQL Developer에서 다음과 같이 처리가 가능합니다.
상단 버튼 중에서 SQL에 대해서 실행 계획을 쉽게 볼 수 있도록 버튼이 제공됩니다.
실행계획을 보면 트리 구조로 방금 전 실행한 SQL이 어떻게 처리된 것인지를 알려줍니다. 흔히 SQL튜닝이라고 하는 작업은 이를 보고 어떤 방식이 더 효과적인지를 판단해서 수정하게 됩니다.
이 책에서는 실행 계획에 대해서 많은 내용을 설명할 수 없지만, 가장 간단하게 실행계획을 보는 방법은 '안쪽에서 바깥쪽으로, 위에서 아래로'봐주면 됩니다.
위 그림의 내용을 해석하자면 'TBL_BOARD' 테이블을 'FULL'로 접근하고 정렬했다는 것을 의미합니다. 'FULL'이라는 것의 의미는 테이블 내의 모든 데이터를 스캔( scan) 했다는 의미입니다. 실행 계획을 세우는 것은 데이터베이스에서 하는 역할이기 때문에 데이터의 양이나 제약조건 등 여러 상황에 따라서 데이터베이스는 실행계획을 다르게 작성합니다.
테스트를 위해서 데이터가 좀 많아지도록 아래의 SQL을 여러 번 실행해서 데이터를 수백만 개로 만든 후에 커밋을 합니다.
~~~내용이 있고 수백만개의 데이터를 insert 한다는~~~~~
실행계획을 잠깐 살펴 보면 TBL_BOARD를 'FULL'로 조사(스캔)했고, 바깥쪽으로 가면서 'SORT'가 일어난 것을 볼 수 있습니다. 이때 가장 많은 시간을 소모하는 작업은 정렬 작업입니다.
위의 SQL에서 ' order by bno + 1 desc'라는 조건에서 +1 을 하는 것은 정렬에 아무런 도움을 주지 않으므로 아래와 같이 수정합니다.
select * from tbl_board order by bno desc;
기존의 SQL이 TBL_BOARD 테이블 전체를 스캔했지만, 이번에는 PK_BOARD라는 것을 이용해서 접근하고 기존과 달리 맨 위의 SORT과정이 없는 것을 볼 수 있습ㄴㅣ다.
이것을 이해하려면 데이터베이스의 인덱스(index)에 대해서 조금 알아둘 필요가 있습니다.
4. order by 보다는 인덱스
-> 데이터가 많은 상태에서는 정렬 작업이 문제가 됩니다. 이를 어떻게 해결해야 할까요? 가장 일반적인 해결책인 '인덱스(index)를 이용해서 정렬을 생략하는 방법입니다.
결론 부터 말하자면 '인덱스'라는 존재가 이미 정렬된 구조이므로 이를 이용해서 별도로 정렬을 하지 않습니다.
SQL의 실행 계획에서 주의해서 봐야 하는 부분은 1) SORT를 하지 않았다는 점, 2) TBL_BOARD를 바로 접근하는 것이 아니라 PK_BOARD를 이용해서 접근한 점, 3) RANGE SCAN DESCENDING, BY INDEX ROWID로 접근했다는 점입니다.
4.1 PK_BOARD라는 인덱스
tbl_board테이블을 생성했을때의 sql
create table tbl_board(
bno number(10,0),
title varchar2(200) not null,
content varchar2(2000) not null,
writer varchar2(50) not null,
regdate date default sysdate,
updatedate date default sysdate
);
alter table tbl_board add constraint pk_board
primary key(bno);
테이블을 생성할 때 제약조건으로 PK를 지정하고, PK의 이름이 'pk_board'라고 지정하였습니다. 데이터베이스에서 PK는 상당히 중요한 의미를 가지는데
흔히, 식별자의 의미와 인덱스의 의미를 가집니다.
인덱스는 말그대로 색인입니다. 우리가 흔히 접하는 인덱스는 도서 뒤쪽에 정리되어 있는 색인입니다. 색인을 이용하면 사용자들은 책 전체를 살펴볼 필요 없이 색인을 통해서 자신이 원하는 내용이 책의 어디에 있는지 알 수 있습니다. 데이터베이스에서 인덱스를 이해하는 가장 쉬운 방법은 데이터베이스의 테이블을 하나의 책이라고 생각하고 어떻게 데이터를 찾거나 정렬하는지를 생각하는 것입니다. 색인은 사람들이 쉽게 찾아볼 수 있게 알파벳 순서나 한글 순서로 정렬합니다. 이를 통해서 원하는 내용을 위에서 부터 혹은 반대로 찾아나가는데 이를 '스캔(scan)'이라고 합니다.
데이터베이스에 테이블을 만들때 PK를 부여하면 지금까지 얘기한 '인덱스'라는 것이 만들어집니다.
데이터베이스를 만들 떄 PK를 지정하는 이유는 '식별'이라는 의미가 있지만, 구조상으로는 '인덱스'라는 존재(객체)가 만들어지는 것을 의미합니다.
tbl_board 테이블은 bno라는 칼럼을 기준으로 인덱스를 생성하게 됩니다 .인덱스에는 순서가 있기 때문에 그림으로 표현하자면
인덱스를 역순으로 찾기 때문에 가장 먼저 찾은 bno 값은 가장 큰 값을 가진 데이터가 됩니다. 이후에는 테이블에 접근해서 데이터를 가져오게 되는데, 이런 과정이 반복되면 정렬을 하지 않아도 동일하게 정렬된 결과를 볼 수 있게 됩니다.
하나의 예를 더 보자면, 만일 사용자가 'bno의 순서로 정렬해 달라'고 요구하는 상황이라면 PK_BOARD 인덱스가 앞에서부터 찾아서 내려가는 구조를 이용하는 것이 효율적입니다. SQL Developer를 이용해서 실행해 보면 아래와 같은 실행 계획이 수립되는 것을 볼 수 있습니다.
실행 계획상으로 PK_BOARD 인덱스를 먼저 접근하고, TBL_BOARD를 이용하는 것을 볼 수 있습니다. SORT가 없기 때문에 0초에 가까운 성능을 보여줍니다. 실무에서도 데이터의 양이 많고 정렬이 필요한 상황이라면 우선적으로 생각하는 것이 '인덱스'를 작성하느나 것입니다. 데이터의 양이 수천,수만 개 정도의 정렬은 그다지 부하가 걸리지 않지만 그 이상의 데이터를 처리해야 하는 상황이라면 정렬을 안할 수 있는 방법에 대해서 고민해야만 합니다.
오라클에는 hint가 있지만 mysql에서는 hint라는 문법을 사용하지 않는다.
오라클에서 ROWNUM이라는 특별한 키워드를 사용해서 데이터에 순번을 붙여 사용합니다.