반응형
범주 명령 단축키
Worksheet SQL 워크시트(W) Alt-F10
Worksheet 계획 설명(E)... F10
Worksheet 내역(H) F8
Worksheet 롤백(R) F12
Worksheet 명령문 실행 F9
Worksheet 명령문 실행 Ctrl-Enter
Worksheet 비공유 SQL 워크시트 Ctrl-Shift-N
Worksheet 스크립트 실행 F5
Worksheet 자동 추적(A)... F6
Worksheet 지우기(C) Ctrl-D
Worksheet 커밋(O) F11
검색 다음 찾기(N) F3
검색 바꾸기...(R) Ctrl-R
검색 이전 찾기(P) Shift-F3
검색 증분 뒤로 찾기(B) Ctrl-Shift-E
검색 증분 앞으로 찾기(O) Ctrl-E
검색 찾기...(F) Ctrl-F
검색 커서 다음 단어 찾기 Ctrl-F3
검색 커서 앞 단어 찾기 Ctrl-Shift-F3
기타 SQL 내역: 다음 내역에서 추가 Ctrl-Shift-Down
기타 SQL 내역: 다음 내역으로 바꾸기 Ctrl-Down
기타 SQL 내역: 이전 내역에서 추가 Ctrl-Shift-Up
기타 SQL 내역: 이전 내역으로 바꾸기 Ctrl-Up
기타 고급 형식... Ctrl-Shift-F7
기타 구현 디버그 Shift-F9
기타 대문자/소문자/첫 자를 대문자로 Ctrl-Quote
기타 변경 사항 롤백 F12
기타 변경 사항 커밋 F11
기타 보기 고정 Ctrl-Shift-P
기타 새로 고침 Ctrl-R
기타 새로 만들기(N)... Ctrl-N
기타 테스트 실행 F9
기타 파일 실행 Ctrl-F11
기타 팝업 메뉴 Shift-F10
기타 팝업 메뉴 Context Menu
기타 팝업 설명 Shift-F4
기타 편집(E) Ctrl-L
기타 형식 Ctrl-F7
데이터 편집기 데이터 필터링 Ctrl-Alt-F
데이터 편집기 변경 사항 롤백 F12
데이터 편집기 변경 사항 커밋 F11
데이터 편집기 새로 고침 Ctrl-R
데이터 편집기 선택된 행 삭제 Ctrl-D
데이터 편집기 정렬... Ctrl-Alt-S
데이터 편집기 행 삽입 Ctrl-I
도구 공백 표시 Ctrl-Shift-W
도움말 뒤로 Ctrl-Alt-Left
도움말 문맥에 따른 도움말 F1
도움말 문맥에 따른 도움말 Shift-F1
도움말 앞으로 Ctrl-Alt-Right
디버그 감시...(W) Ctrl-F5
디버그 검사...(I) Ctrl-I
디버그 내부 이동 F7
디버그 외부 이동 Shift-F7
디버그 이동 계속(C) Shift-F8
디버그 재개 F9
디버그 전체 이동 F8
디버그 종료 Ctrl-F2
디버그 중단점 토글(T) F5
디버그 커서까지 실행(U) F4
디버그 프로젝트 디버그 Shift-F9
버전 지정 속성(버전 지정) Ctrl-Shift-O
보기 로그(L) Ctrl-Shift-L
보기 중단점(B) Ctrl-Shift-R
소스 모두 축소(A) Ctrl-Shift-Open Bracket
소스 모두 확장(L) Ctrl-Shift-Close Bracket
소스 재형식화 Ctrl-Alt-L
소스 재형식화 Alt-Shift-F
실행(R) 프로젝트 실행 F11
이동 0 책갈피 토글 Ctrl-Shift-0
이동 0 책갈피로 이동 Ctrl-0
이동 1 책갈피 토글 Ctrl-Shift-1
이동 1 책갈피로 이동 Ctrl-1
이동 2 책갈피 토글 Ctrl-Shift-2
이동 2 책갈피로 이동 Ctrl-2
이동 3 책갈피 토글 Ctrl-Shift-3
이동 3 책갈피로 이동 Ctrl-3
이동 4 책갈피 토글 Ctrl-Shift-4
이동 4 책갈피로 이동 Ctrl-4
이동 5 책갈피 토글 Ctrl-Shift-5
이동 5 책갈피로 이동 Ctrl-5
이동 6 책갈피 토글 Ctrl-Shift-6
이동 6 책갈피로 이동 Ctrl-6
이동 7 책갈피 토글 Ctrl-Shift-7
이동 7 책갈피로 이동 Ctrl-7
이동 8 책갈피 토글 Ctrl-Shift-8
이동 8 책갈피로 이동 Ctrl-8
이동 9 책갈피 토글 Ctrl-Shift-9
이동 9 책갈피로 이동 Ctrl-9
이동 Maximize Toggle Ctrl-Alt-M
이동 기호 문서 찾아보기... Alt-Shift-Minus
이동 기호 찾아보기...(M) Ctrl-Minus
이동 다음 메시지로 이동(X) Alt-F8
이동 다음 책갈피로 이동(O) Ctrl-Q
이동 뒤로(C) Alt-Left
이동 앞으로(W) Alt-Right
이동 이전 메시지로 이동(V) Alt-F7
이동 이전 책갈피로 이동(P) Ctrl-Shift-Q
이동 책갈피 토글(T) Ctrl-K
이동 책갈피로 이동...(B) Ctrl-Shift-K
이동 최근 파일로 이동(F)... Ctrl-Equals
이동 최근 편집으로 이동(E) Ctrl-Shift-Backspace
이동 행으로 이동...(G) Ctrl-G
1 문서 활성화 Alt-1
2 문서 활성화 Alt-2
3 문서 활성화 Alt-3
4 문서 활성화 Alt-4
5 문서 활성화 Alt-5
6 문서 활성화 Alt-6
7 문서 활성화 Alt-7
8 문서 활성화 Alt-8
9 문서 활성화 Alt-9
Alt+1에 지정 Alt-Shift-1
Alt+2에 지정 Alt-Shift-2
Alt+3에 지정 Alt-Shift-3
Alt+4에 지정 Alt-Shift-4
Alt+5에 지정 Alt-Shift-5
Alt+6에 지정 Alt-Shift-6
Alt+7에 지정 Alt-Shift-7
Alt+8에 지정 Alt-Shift-8
Alt+9에 지정 Alt-Shift-9
다음 창(X) F6
다음 파일(N) Ctrl-F6
다음 파일(N) Ctrl-Tab
오른쪽 편집기(G) Alt-Page Down
왼쪽 편집기(E) Alt-Page Up
이전 창(V) Shift-F6
이전 파일(P) Ctrl-Shift-F6
이전 파일(P) Ctrl-Shift-Tab
파일 목록(F) Alt-0
편집기 메뉴 표시(S) Alt-Minus
코드 편집기 다음 단어 시작 부분까지 삭제 Ctrl-Delete
코드 편집기 다음 단어 시작 부분까지 삭제 Ctrl-T
코드 편집기 다음 단어 시작 부분으로 이동 Ctrl-Right
코드 편집기 뒤로 이동 Left
코드 편집기 로컬 탭 크기를 2로 설정 Ctrl-2
코드 편집기 로컬 탭 크기를 4로 설정 Ctrl-4
코드 편집기 로컬 탭 크기를 8로 설정 Ctrl-8
코드 편집기 매개변수 인사이트(P) Ctrl-Shift-Space
코드 편집기 삽입 모드 토글 Insert
코드 편집기 새 행 삽입 Shift-Enter
코드 편집기 새 행 삽입 Enter
코드 편집기 선택 사항 뒤로 이동 Shift-Left
코드 편집기 선택 사항 복제 Ctrl-Shift-D
코드 편집기 선택 사항 아래로 이동 Shift-Down
코드 편집기 선택 사항 앞으로 이동 Shift-Right
코드 편집기 선택 사항 위로 이동 Shift-Up
코드 편집기 선택 사항을 다음 단어 시작 부분으로 이동 Ctrl-Shift-Right
코드 편집기 선택 사항을 이전 단어 시작 부분으로 이동 Ctrl-Shift-Left
코드 편집기 선택 사항을 파일 끝으로 이동 Ctrl-Shift-End
코드 편집기 선택 사항을 파일 시작 부분으로 이동 Ctrl-Shift-Home
코드 편집기 선택 사항을 페이지 아래로 이동 Shift-Page Down
코드 편집기 선택 사항을 페이지 위로 이동 Shift-Page Up
코드 편집기 선택 사항을 행 끝으로 이동 Shift-End
코드 편집기 선택 사항을 행 시작 부분으로 이동 Shift-Home
코드 편집기 선택 해제 Ctrl-Back Slash
코드 편집기 선행 공백을 탭으로 변환 Ctrl-Shift-T
코드 편집기 선행 탭을 공백으로 변환 Ctrl-Shift-U
코드 편집기 스마트 완성 인사이트(A) Ctrl-Alt-Space
코드 편집기 아래로 이동 Down
코드 편집기 앞으로 이동 Right
코드 편집기 역방향 탭 Shift-Tab
코드 편집기 완성 인사이트(C) Ctrl-Space
코드 편집기 위로 이동 Up
코드 편집기 이전 단어 시작 부분까지 삭제 Ctrl-Backspace
코드 편집기 이전 단어 시작 부분으로 이동 Ctrl-Left
코드 편집기 이전 문자 삭제 Shift-Backspace
코드 편집기 이전 문자 삭제 Backspace
코드 편집기 일치하는 중괄호까지 선택 Alt-Shift-Close Bracket
코드 편집기 일치하는 중괄호까지 선택 Alt-Shift-Open Bracket
코드 편집기 일치하는 중괄호로 이동 Alt-Open Bracket
코드 편집기 일치하는 중괄호로 이동 Alt-Close Bracket
코드 편집기 취소 Escape
코드 편집기 탭 삽입 Tab
코드 편집기 파일 끝으로 이동 Ctrl-End
코드 편집기 파일 시작 부분으로 이동 Ctrl-Home
코드 편집기 페이지 아래로 이동 Page Down
코드 편집기 페이지 위로 이동 Page Up
코드 편집기 행 끝까지 삭제 Ctrl-Shift-Y
코드 편집기 행 끝으로 이동 End
코드 편집기 행 시작 부분으로 이동 Home
코드 편집기 행 아래로 스크롤 Ctrl-Down
코드 편집기 행 위로 스크롤 Ctrl-Up
코드 편집기 행 조인 Ctrl-J
코드 편집기 행 주석 토글(T) Ctrl-Slash
코드 편집기 행 주석 토글(T) Ctrl-Shift-Slash
파일 닫기 Ctrl-F4
파일 닫기 Ctrl-W
파일 모두 닫기(E) Ctrl-Shift-F4
파일 열기...(O) Ctrl-O
파일 인쇄...(P) Ctrl-P
파일 저장(S) Ctrl-S
파일 종료(X) Alt-F4
편집 실행 취소 Alt-Backspace
편집 실행 취소 Ctrl-Z
편집 재실행 Alt-Shift-Backspace
편집 재실행 Ctrl-Shift-Z
편집 재실행 Ctrl-Y
편집 경로 복사(H) Ctrl-Shift-C
편집 모두 선택(A) Ctrl-A
편집 복사(C) Ctrl-C
편집 복사(C) Ctrl-Insert
편집 붙여넣기(P) Shift-Insert
편집 붙여넣기(P) Ctrl-V
편집 삭제(D) Delete
편집 잘라내기(T) Shift-Delete
편집 잘라내기(T) Ctrl-X
편집 확장된 붙여넣기...(E) Ctrl-Shift-V
 
원래 sqldbx를 즐겨 사용하고 Toad도 사용하지만 현재 근무하는 회사 정책상
프리웨어를 사용할수 밖에 없게 되었다 하여 SQL Developer 를 사용하게 되었다.
음 그닥 불편하지는 않지만 아직 익숙하지 않기에 우선 단축키부터 찾아보았다.
반응형

 
  1. 특정 데이터베이스의 DEFAULT CHARACTER SET 확인 방법
    • SHOW CREATE DATABASE db_name;
    • 이렇게 하면, 해당 데이터베이스를 생성하는데 사용할 수 있는 DDL 문장을 보여주는데, 거기에 기본 문자셋에 대한 정보도 포함되어 있음.
  2. 특정 테이블의 DEFAULT CHARACTER SET 확인 방법
    • SHOW CREATE TABLE table_name;
    • 이렇게 하면, 데이터베이스와 동일하게 해당 테이블을 생성하는데 사용할 수 있는 DDL 문장을 보여주는데, 거기에 기본 문자셋에 대한 정보도 포함되어 있음.
  3. 특정 데이터베이스의 DEFAULT CHARACTER SET 수정하는 방법
    • ALTER DATABASE db_name [DEFAULT] CHARACTER SET = charset_name;
    • 참고로, 이 방법은 현재 이 데이터베이스에 속해있는 테이블들의 문자셋을 변경하는 것은 아님. 다만, 향후에 CREATE TABLE 문장으로 테이블(들)을 새로이 추가하려고 할 때 (해당 테이블들에 명시적으로 문자셋이 지정되지 않은 경우) 해당 테이블들이 기본적으로 가지게 될 DEFAULT 문자셋을 지정하는 것에 불과함.
  4. 특정 데이터베이스의 DEFAULT COLLATION 수정하는 방법
    • ALTER DATABASE db_name [DEFAULT] COLLATE = collation_name;
    • 참고로, 이 방법은 현재 이 데이터베이스에 속해있는 테이블들의 collation을 변경하는 것은 아님. 다만, 향후에 CREATE TABLE 문장으로 테이블(들)을 새로이 추가하려고 할 때 (해당 테이블들에 명시적으로 collation이 지정되지 않은 경우) 해당 테이블들이 기본적으로 가지게 될 DEFAULT collation을 지정하는 것에 불과함.
  5. 특정 테이블의 DEFAULT CHARACTER SET 수정하는 방법
    • ALTER TABLE table_name [DEFAULT] CHARACTER SET = charset_name;
    • 참고로, 이 방법은 현재 테이블에 들어가있는 칼럼들의 문자셋이나 collation을 변경하지는 않는다. 다만, 향후에 ALTER TABLE 문장으로 칼럼(들)을 새로이 추가하려고 할 때 (해당 칼럼들에 명시적으로 문자셋이나 collation이 지정되지 않은 경우) 해당 칼럼들이 기본적으로 가지게 될 DEFAULT 문자셋을 지정하는 것에 불과함.
    • collation도 함께 수정하려고 할 때는 다음과 같이 한다.
    • ALTER TABLE table_name [DEFAULT] CHARACTER SET = charset_name COLLATE = collation_name;
    • 만약, 이미 테이블에 포함되어있는 칼럼들에 지정되어있는 문자셋이나 collation을 수정하고 싶은 경우에는 다음과 같이 'CONVERT TO'를 사용해야 한다.
      • ALTER TABLE table_name CONVERT TO CHARACTER SET = charset_name [COLLATE =collation_name];
      • 이렇게 하면 현재 테이블에 포함되어있던 각 컬럼들에 지정되어있던 문자셋이 모두 동일하게 새로운 문자셋과 collation으로 변경되게 됨.
      • 그런데, 이렇게 'CONVERT TO'를 사용하면 새로운 문자셋이 기존의 문자셋보다 Max_len 이 큰 경우 각 컬럼들에 지정되어있던 데이터 타입의 크기 역시 함께 변경이 된다. (예를 들어, TEXT 타입이었다면 MEDIUMTEXT 타입으로 변경되기도 함) 또한 각 컬럼들에 저장되어있는 실제 '값'도 역시 새로운 문자셋으로 변경이 일어난다(매뉴얼에서는 이렇게 이야기하고 있는데, 직접 확인 필요)
      • 만약, 이렇게 데이터 타입의 크기나 '값'의 변화를 원하지 않는 경우에는 'CONVERT TO'를 사용하면 안되고 각 컬럼마다 'MODIFY' 명령어를 통해 각각 수정해주어야 함.
      • ALTER TABLE table_name MODIFY column_name VARCHAR(10) CHARACTER SET charset_name [COLLATE collation_name];
      • 좀 더 자세한 사항은 직접 매뉴얼을 참고할 것.
  6. [참고] 현재 시스템에서 지원하는 문자셋들의 목록을 보고 싶을 때
    • SHOW CHARACTER SET;
      • 이렇게 하면 지원하는 문자셋 목록을 모두 다 보여줌. 문자셋 이외에도 해당 문자셋이 선택되었을때 (별도로 명시적으로 collation을 지정하지 않았을때 사용되는) Default collation도 보여주고, 해당 문자셋이 최대 몇 바이트를 차지하는지에 대한 Maxlen 정보도 함께 보여줌.(고정폭 인코딩을 사용하는 문자셋이 아니라 가변폭 인코딩을 사용하는 문자셋도 있으므로 그야말로 Maxlen임)
    • 만약, 전체 목록이 아니라 필터를 사용해서 일부 목록만 보고 싶을 때는 다음과 같이 하면 된다.
    • SHOW CHARACTER SET LIKE '%euc%';
  7. [참고] 현재 시스템에서 지원하는 collation 목록을 보고 싶을 때
    • SHOW COLLATION;
      • 이렇게 하면 지원하는 collation 목록을 모두 다 보여줌. 해당 collation이 속해있는 문자셋 정보, 해당 collation이 Default collation인지 여부 등의 정보도 함께 보여줌.
    • 만약, 전체 목록이 아니라 필터를 사용해서 일부 목록만 보고 싶을 때는 다음과 같이 하면 된다.
    • SHOW COLLATION LIKE '%euc%';
    • 또한, 각 문자셋별로 Default collation만을 걸러서 보고 싶을 때는 다음과 같이 한다.
    • SHOW COLLATION WHERE 'Default' = 'Yes';

 

다시한번 강조하지만, 여기에서 정리한 것들은 이미 값이 들어가있는 테이블이나 칼럼 등에 영향을 주는 것이 아니라는 점... 헷갈리지 마시길!!! 
<출처: http://blog.naver.com/kumimo?Redirect=Log&logNo=80090608750 >


각 데이터베이스 별로 인코딩을 설정하기
1. 생성시
CREATE DATABASE [DB_NAME]  DEFAULT CHARACTER SET [char_set] COLLATE [coll_name];
ex> CREATE DATABASE db_name CHARACTER SET euckr COLLATE euckr_korean_ci;

2. 생성된 데이터베이스 수정시
ALTER DATABASE [DB_NAME] CHARACTER SET  [char_set] COLLATE  [coll_name];
ex> ALTER DATABASE db_name DEFAULT CHARACTER SET  euckr DEFAULT COLLATE  euckr_korean_ci;

3. 테이블의 텍스트 값과 기본 인코딩 변경하기
ALTER TABLE [TABLE_NAME] CONVERT TO CHARACTER SET [char_set];

위 명령은 컬럼들의 값을 바꾸는데, 값이 바뀌는 것을 원하지 않는 경우(예를 들어, 테이블은 latin1을 사용하는데 저장된 텍스트들은 utf8인 경우, 테이블의 캐릭터 셋만 바꾸면 되므로)엔 다음과 같이 칼럼별로 수행한다.

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
ALTER TABLE t1 DEFAULT CHARACTER SET utf8;

반응형

<출처: http://rayfeel.co.kr/63 >

※ 인덱스란?
  인덱스는 테이블이나 클러스트에서 쓰여지는 선택적인 객체로서, 오라클 데이터베이스 테이블내의
원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조
입니다.  

자동 인덱스 : 프라이머리 키 또는 uinque 제한 규칙에 의해 자동적으로 생성되는 인덱스 입니다.
수동 인덱스 : CREATE INDEX 명령을 실행해서 만드는 인덱스들 입니다.  

※  Index를 생성하는 것이 좋은 Column
① WHERE절이나 join조건 안에서 자주 사용되는 컬럼
② null 값이 많이 포함되어 있는 컬럼
③ WHERE절이나 join조건에서 자주 사용되는 두 개이상의 컬럼들
※  다음과 같은 경우에는 index 생성이 불필요 합니다.

① table이 작을 때
③ 테이블이 자주 갱신될 때

※  오라클 인덱스는 B-tree(binary search tree)에 대한 원리를 기반으로 하고 있습니다.
B-tree인덱스는 컬럼안에 독특한 데이터가 많을 때 가장 좋은 효과를 냅니다.
이 알고리즘 원리는
 ① 주어진 값을 리스트의 중간점에 있는 값과 비교합니다.     
     만약 그 값이 더 크면 리스트의 아래쪽 반을 버립니다.
     만약 그 값이 더 작다면 위쪽 반을 버립니다.
 ② 하나의 값이 발견될 때 까지 또는 리스트가 끝날 때까지 그와 같은 작업을 다른 반쪽에도
     반복합니다.

※  인덱스는 B-tree 구조를 가지며 크게 다음 네 가지로 분류될수 있습니다.
Bitmap 인덱스
  비트맵 인덱스는 각 컬럼에 대해 적은 개수의 독특한 값이 있을 경우에 가장 잘 작동합니다.
  그러므로 비트맵 인덱스는 B-tree 인덱스가 사용되지 않을 경우에서 성능을 향상 시킵니다.
  테이블이 매우 크거나 수정/변경이 잘 일어나지 않는 경우에 사용할수 있습니다.

SQL>CREATE BITMAP INDEX emp_deptno_indx
ON emp(deptno);

Unique 인덱스
  Unique 인덱스는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있습니다.
프라이머리키 와 Unique 제약 조건시 생성되는 인덱스는 Unique 인덱스입니다.

SQL>CREATE UNIQUE INDEX emp_ename_indx
ON  emp(ename);

③ Non-Unique 인덱스
   Non-Unique 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수 있습니다.

SQL>CREATE INDEX  dept_dname_indx
ON  dept(dname);

④ 결합 (Concatenated(=Composite)) 인덱스
   복수개의 컬럼에 생성할 수 있으며 복수키 인덱스가 가질수 있는 최대 컬럼값은 16개입니다

SQL>CREATE UNIQUE INDEX emp_empno_ename_indx
ON  emp(empno, ename);

※  인덱스의 삭제
 - 인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는 테이블의 데이터에는 아무런 영향도 미치지
않습니다.
 - 인덱스를 삭제하려면 INDEX의 소유자이거나 DROP ANY INDEX권한을 가지고 있어야 합니다.
 - INDEX는 ALTER를 할 수 없습니다.
SQL>DROP INDEX emp_empno_ename_indx ;

※  인덱스에 대한 정보는 USER_INDEXES 뷰 또는 USER_IND_COLUMNS뷰를 통해 검색할 수
      있습니다.


반응형
토드에서 쿼리를 실행한 후에 결과를 엑셀 파일로 저장하려면

결과 출력 부분에서 Script Output에서 Grid 를 선택하면 아래와 같이 빨간 네모 박스 부분이 활성화 된다. 


위에 빨간 박스 모양을 클릭하면 원하는 형태의 파일로 출력할수가 있다.

그리고 엑셀로 출력시 한글 깨짐 현상이 발생하는데 그에 대한 해결은 설정 옵션을 추가 하면 된다.
출력전에 Write Wide String 옵션에 체크를 하고 해보자.. 그럼 정상적으로 출력 되는걸 확인 할수 있다.

Excel 파일로 Export 된 데이터를 Import 하려면 메뉴에서 Database -> Import -> Import Table Data

Import 하는 방법을 스샷으로 간단하게 훑어 보자.

반응형
DB툴로 sqldbx를 주로 사용한다. 가벼운것은 물론이거니와 여러 디비에 멀티 커넥팅도 되고
가장 편하게 사용하는 것은 물론 인텔리전스 기능이다. (자동완성)

그런데 업무를 처리하던중 데이터의 export 와 import 과정에서 난관에 봉착했다.
도대체 어떻게 하는건지 ㅠㅠ 이대로 sqldbx를 포기해야 하나.. ㅠㅠ
설마 다른 디비툴도 지원하는걸 sqldbx 가 지원하지 않을까 하고 자료를 찾아봤지만.. 못찾았다.
결론은 excel 을 이용한 방법은 찾았다.

도움말에는
 

Export to File

Select Export To->File menu item from table popup menu. Saved file can be later opened as script file and executed directly.

 

Import from File

You can import previously exported data by selecting Import From->File menu item from table popup menu. If table name in the file different from selected table it will be automatically replaced. Also file can be opened as ordinary file in Script Editor.

 

Export to Excel

Select Export To->Excel menu item from table popup menu to copy all table data to Excel

 

Import from Excel

You can import table data from Excel by first selecting range in Excel which contains table data with table column names in the first row. Order of columns is not important. Some column can be omitted too. To import data from Excel select table into which you want to import data. Right click and select Import From menu item. Select menu item with Excel session in which you previously selected data. Data from Excel will appear in a new Script Editor window as a series of INSERT statements. Now you can edit or execute script to insert data into the table.


지금은 회사업무로 인하여 데이터 스샷을 찍을순 없어서 스샷을 남길수 없다.
향후 테스트 디비를 생성하여 스샷을 남기도록 하겟다.

일단 Export 과정은..
해당 테이블을 선택하고 오른쪽 마우스 클릭하면
엑셀 파일로 Export


확장자가 .sql 이나 .qry 로 insert문으로 작성된 Export 파일 생성


File(as Inserts).. 선택시 뜨는 탐색창...

생성된 파일(Export1.sql)을 열었을때 각 데이터들이 Insert 문으로 생성된것을 확인할수 있다.


위 방법은 선택한 테이블의 전체 데이터를 Export 하는 방법이였고 아래 방법은 Select 쿼리를 날려
조회된 데이터를 Export 하는 방법이다.
쿼리를 날려 조회덴 Result Grid 화면이다.
조회된 데이터를 선택한후 오른쪽 클릭 팝업메뉴.. Excel 로 Export 방법과
File... 방법이 있다.File에서는 Csv,tab,txt 등으로 Export 할수 있다.

이제 Export 된 데이터를 Import 하는 방법이다.
우선 Export 됬던 Excel 파일을 열고 Import 할 데이터를 선택한다.
Excel로 Export한 데이터중 Import 할 데이터를 선택한다.

그리고 Import 할 테이블을 선택한후 오른쪽 클릭...
Import Data From 선택하면 Excel 창이 열려 있으면 자동으로 인식한다.
Excel파일로 Import 를 시작하면 Script 창에 쿼리가 자동으로 생성된다.

쿼리가 생성된 화면이다. 쿼리를 실행하면 Import 작업도 끝이다.

아직 csv 파일이나 txt 파일의 Import 방법은 찾아보지 못했다. Export 나 Import 작업에 불편함이 없지 않다.
누가 제대로 된 방법을 알려주었으면 한다. sqldbx 사용하시는분  자세히 아시면 조언좀~~
스샷이 조금 지저분하고 내용이 뒤죽박죽이드래도.. 잘 보시면 쉽게 알수 있을것이다.^^
반응형
기존 테이블에 컬럼 추가,변경,삭제 및 테이블명 변경
1. 테이블에 새로운 컬럼 추가하기
alter table [테이블명] add column [추가할 컬럼명] [추가할 컬럼타입]
2. 테이블에 컬럼타입 변경하기
alter table [테이블명] modify column [변경할 컬럼명] [변경할 컬럼타입]
3. 테이블에 컬럼이름 변경하기
alter table [테이블명] change column [기존 컬럼명] [변경할 컬럼명] [변경할 컬럼타입]
4. 테이블에 컬럼 삭제하기
alter table [테이블명] drop column [삭제할 컬럼명]
5. 테이블명 바꾸기
alter table [테이블명] rename 새로운테이블명


 
기존 테이블에 인덱스 추가,삭제 및 고유키 추가,삭제
1. 테이블컬럼에 인덱스 주기 (인덱스 다수 입력가능)
alter table [테이블명] add index 인덱스명(컬럼1 , 컬럼2 , 컬럼3)
2. 테이블컬럼에 인덱스 삭제하기
alter table [테이블명] drop index 인덱스명;
3. 테이블에 Primary Key 만들기
alter table [테이블명] add primary key (컬럼1 , 컬럼2, 컬럼3)
4. 테이블에 Primary Key 삭제하기
alter table [테이블명] drop primary key;



반응형

F1

Toad 도움말 파일의 SQL Editor 부분이 표시됩니다.

F2

전체 화면 Editor Editor/Results 패널 표시 장치 사이를 전환합니다.

<SHIFT>F2

전체 화면 그리드를 전환합니다.

F3

다음으로 일치하는 것을 찾습니다.

<SHIFT>F3

이전에 일치하는 것을 찾습니다.

F4

팝업 창의 테이블, , 프로시저, 함수, 또는 패키지를 설명합니다.

F5

스크립트로 실행합니다.

F6

커서를 Editor Results 패널 사이로 전환합니다.

F7

모든 텍스트를 지웁니다.

F8

이전 SQL 문을 재호출합니다(SQL Statement Recall 창을 불러옵니다).

F9

실행문을 실행합니다.

<CTRL>F9

실행(구문 분석) 없이 실행문을 검사합니다.

<SHIFT>F9

커서 위치에서 현재 실행문을 실행합니다.

F10

오른쪽 클릭 메뉴를 표시합니다.

F11

Script 같은 실행(=F5)

F12

편집기 내용을 지정된 외부 편집기로 전달합니다.

<CTRL>A

모든 텍스트를 선택합니다.

<CTRL>C

복사

<CTRL>D

프로시저 인수를 표시합니다.

<CTRL>E

현재 실행문에서 Explain Plan 실행합니다.

<CTRL>F

텍스트를 찾습니다(Find Text 창을 불러옵니다).

<CTRL>G

라인으로 이동합니다(Goto Line 창을 불러옵니다).

<CTRL>L

텍스트를 소문자로 변환합니다.

<CTRL>M

Make Code Statement

<CTRL>N

이름이 지정된 SQL 문을 재호출합니다(SQL Statement Recall 창을 불러옵니다).

<CTRL>O

텍스트 파일을 엽니다.

<CTRL>P

Strip Code Statement

<CTRL>R

검색 바꾸기(Find and Replace Text 창을 불러옵니다)

<CTRL>S

파일을 저장합니다.

<SHIFT><CTRL>S

파일을 다른 이름으로 저장합니다.

<CTRL>T

드롭다운을 표시합니다.

<CTRL>U

텍스트를 대문자로 변환합니다.

<CTRL>V

붙여넣기

<CTRL>X

잘라내기

<SHIFT><CTRL>Z

마지막으로 취소한 작업을 재실행합니다.

<ALT><UP>

이전 실행문을 표시합니다.

<ALT><DOWN>

다음 실행문을 표시합니다(<ALT><UP> 사용한 사용)

<ALT><PgUp>

이전 탭으로 이동

<ALT><PgDn>

다음 탭으로 이동

<CTRL><ALT><PgUp>

이전 결과 패널 탭으로 이동

<CTRL><ALT><PgDn>

다음 결과 패널 탭으로 이동

<CTRL><HOME>

데이터 그리드에서는 위의 레코드셋으로 이동하며, 결과 그리드에서는 커서가 위치한 행의 번째 열로 이동하고, 편집기에서는 텍스트의 번째 열과 번째 행으로 이동합니다.

<CTRL><END>

데이터 그리드에서는 레코드셋의 끝으로 이동하며, 편집기에서는 텍스트의 마지막 열과 마지막 행으로 이동합니다. 단원의 "주의" 참조하십시오.

<CTRL><SPACE>

코드 완성 템플릿을 활성화합니다.

<CTRL><TAB>

MDI Child 창의 콜렉션을 순환합니다.

<CTRL><ENTER>

커서 이치에서 현재 SQL 문을 실행합니다.

<CTRL>. (마침표)

테이블 이름을 자동으로 완성합니다.


아래 설명한 것중, F4, Ctrl+T, Alt+Up, Alt+Down 등은 아주 유용하게 쓰일 수 있을 것이다.

SQL EDITOR상에서의 편리한 단축키

ESC: 열린 윈도우 닫기
F1: 모든 단축키를 보여준다.
F2: FULL SCREEN
F4: Table, View, Proc, Funct, Package를 DESC(테이블명에 커서를 둔 상태에서 해야함)
F6: SQL Editor와 결과창간의 전환
F7: 화면을 모두 CLEAR
F8: 실행했던 SQL들의 HISTORY
F9: 모든 쿼리 실행(Ctrl+Enter는 현재 쿼리만 실행)
Ctrl+F9: 실제 쿼리를 실행하지 않고 validate함
Ctrl+. : table Completion                (매칭되는 테이블목록 출력)
Ctrl+T : Columns Dropdown                (해당테이블의 컬럼목록 표시)
Alt+Up  : History Up                        (리눅스 bash의 UP키와 같다.)
Alt+Down: History Down                (리눅스 bash의 DOWN키와 같다.)
Ctrl+Tab: MDI Child윈도간 전환

그냥 'desc 테이블명' 을 치면, 팝업윈도로 해당 테이블의 컬럼목록윈도가 표시된다.
이때, 해당 컬럼을 double-click하면 SQL Editor로 반영된다.

"테이블명."까지 입력하고 좀 있으면, 해당 테이블의 컬럼목록이 표시된다.
마치 프로그래밍툴에서 클래스내 멤버들의 목록을 표시하듯이..
Ctrl+T는 출력된 목록을 취소했을 경우, 다시 불러낼때 쓰인다.

test, member 2개의 테이블이 있다고 하자.
select * from t까지 입력하고 Ctrl+.를 치면 select * from test로 테이블명을 완성시켜준다.
system계정에서는 안되더군.. 일반계정만 가능했다.

SQL문을 실행시

Ctrl+Enter
현재 커서의 SQL문 실행
여러개의 SQL문이 있을때, SQL문들을 개행문자로 분리해주면 된다.
해당 커서에서 개행문자가 있는 곳까지만 하나의 SQL문으로 간주한다.

F9
SQL Editor의 SQL문 실행하여 Grid에 출력.
여러 SQL문들을 개행문자로 구분하지 않는다는 것을 제외하면, 위의 Ctrl+Enter와 동일하다.
에디터내의 모든 SQL문은 하나로 취급하므로, 여러 개의 SQL문이 있으면 실행할 SQL문만 Selection하여 실행(F9)해준다.

F5
SQL Editor내의 모든 SQL문 실행. 여러개의 SQL문들을 모두 실행하여 SQL*PLUS와 같이 출력해준다.
이것은 출력이 Grid로 표시되지 않고 Editor윈도에 표시되므로, SQL*PLUS의 출
력과 같이, 다른 편집기로의 복사/붙여넣기 할때 사용하면 유용하다.
반응형

1. SELECT 등과 같은 키워드를 자동으로 대문자로 변경하게 하는 설정
    메뉴에서 View -> Editor -> Behavior  선택하면 오른쪽 편에 Languages 가 보인다.
    PL/SQL 선택하고 Edit 버튼 클릭  Highlighting 탭을 선택
   Styles -> Reserved word 를 선택하면 바로 오른쪽에 Capitalization effect: 항목이 보인다.
   이부분을 Uppercase 로 선택하고 하면 자동으로 변경된다.

2. 쿼리 작성후 자동 포맷팅 기능 사용 Ctrl + Shift + F   사용시 위에 주석부분 출력안되게 하려면
   View -> Formatting Options -> Formatter Options -> General Layout -> Header
    Tagline 을 Disable 로 설정해주면 된다.


3.GRID에서 null인값 노란색 및 {null}으로 표시하게 하기
   VIEW-Toad Options-Data Grids-Visua Null columns 값을 원하는값으로 변경


4. SELECT 질의를 날려 얻어진 결과를 그리드에서 직접 데이터를 수정하고 적용하는 방법

컨텍스트 메뉴를 보면 열 복제 기능도 있기 때문에 간단한 INSERT도 가능하다.
방법은 다음과 같이 ROWID라는 의사열(Pseudo Column)를 추가하여 쿼리하면 된다.
질의 결과 그리드에서 데이터를 바로 수정한 후에 위쪽에 아이콘을 이용해 COMMIT 해주면
 데이터가 반영된다. 단 변형된 컬럼일 경우에는 수정할 수 없다.
UPDATE가 가능한 VIEW와 같은 방식으로 이해하면 된다.

5. 오라클의 함수나 숫자/날짜 변환 형식, 옵티마이저 힌트등에 관한 코드 템플릿과 설명을 보는 방법
View 메뉴에서 Code Snippets를 선택하면 다음과 같은
템플릿 창을 볼 수 있다. 맨 위에 카테고리를 정하고 다음 리스트에서
항목을 선택하면 하단에 도움말이 표시된다.
항목을 더블 클릭하거나 [Ctrl+C]를 눌러 코드를 복사할 수 있다.
(이와 비슷한 Object Palette도 제공한다. 테이블이나 컬럼등을
볼 수 있는 창이다.)


거의 모든 코드 도움말을 얻을 수 있어, 일일이 웹이나 책을 뒤져보는 수고를 덜어준다.
다음은 제공해 주는 카테고리 목록이다.

  • Single Row Character Functions (문자열 관련 함수)
  • Single Row Number Functions (숫자 관련 함수)
  • Group Functions (그룹핑 관련 함수)
  • Date Functions (날짜 관련 함수)
  • Date Format Options (날짜 포메팅 형식)
  • Data Conversion Functions (데이터 타입 변환 함수)
  • Oter Misc. Functions (기타 자주 쓰이는 함수)
  • Number Format Options (숫자 포메팅 형식)
  • Oracle Pseudo Columns (오라클 의사열)
  • Sql Optimizer hints (옵티마이저 힌트)
  • Defined Exceptions (예외 처리 관련)
  • User provided Function List (사용자 정의 함수)

단축키 목록

테이블 정보 상세보기

F4 : Table, View, Proc, Funct, Package DESC(테이블명 위에 커서를 두고 F4)

 

자동완성

Ctrl+. : Table Completion (매칭되는 테이블목록 출력)

Ctrl+T : Columns Dropdown (해당테이블의 컬럼목록 표시)

 

SQL문 실행

F5 : SQL Editor내의 모든 SQL문 실행

Ctrl+Enter : 현재 커서의 SQL문 실행

F9 : SQL문 실행 후 Grid에 출력

 

히스토리(과거 수행SQL문 조회)

F8 : 과거에 실행한SQL HISTORY 목록

Alt+Up : History UP

Alt+Down : History DOWN

 

텍스트 대/소문자 변환

CTRL+L : 텍스트를 소문자로

CTRL+U : 텍스트를 대문자로

 

주석처리

Ctrl+B : 주석처리

Ctrl+Shift+B : 주석해제

 

편집 창 전환(이동)

F6 : SQL Editor와 결과창간의 이동

F2 : SQL Editor창 전체화면 전환

Shift+F2 : Grid Output창 전체화면 전환

 

기타 단축키

F7 : 화면을 모두 CLEAR

Ctrl+Shift+F : 쿼리문을 보기좋게 정렬

Ctrl+F9 : SQL Validate (SQL문을 수행하지 않음)

반응형

Oracle 에서 테이블의 컬룸의 값들이 자동으로 증가하도록 테이블을 구성하는 방법

우선 원하는 테이블을 생성한다

CREATE TABLE ajaxchat (
  id int NOT NULL ,
  user varchar(255) NOT NULL,
  msg varchar(255) NOT NULL,
  time int NOT NULL,
  PRIMARY KEY  (id)
);

그후 Sequence 테이블을 작성 한다 .

SQL>CREATE SEQUENCE id
        START WITH 1
        INCREMENT BY 1
        MAXVALUE 100000 ;

 

 시작 값이 1일고 1씩 증가하고, 최대값이 100000만이 되는 시퀀스를 생성했습니다.

그리고 값을 저장 할 때 이런식으로 insert 시킨다


 SQL>INSERT INTO ajaxchat(id, user, msg,time ) VALUES(id.NEXTVAL, ’julia’ , sysdate,1);

    empno는 컬럼값을 입력할 때 일일이 다음 값을 기억하지 않아도 NEXTVAL을 사용하여
    자동으로 입력할 수 있습니다.




 시퀀스란?


유일(UNIQUE)한 값을 생성해주는 오라클 객체입니다.

◈ 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성할수 있습니다.

◈ 보통 primary key 값을 생성하기 위해 사용합니다.

◈ 메모리에 Cache되었을 때 Sequence 값의 액세스 효율이 증가 합니다.

◈ Sequence는 테이블과는 독립적으로 저장되고 생성됩니다. 따라서 하나의 sequence를
    여러 테이블에서 쓸 수 있습니다.
 



시퀀스 생성





START WITH : 시퀀스의 시작 값을 지정합니다. n을 1로 지정하면 1부터 순차적으로
                     시퀀스번호가 증가 합니다.

INCREMENT BY : 시퀀스의 증가 값을 말합니다. n을 2로 하면 2씩 증가합니다.
                        START WITH를 1로 하고 INCREMENT BY를 2으로 하면 1, 3, 5,7,.. 이렇게
                       시퀀스  번호가 증가하게 됩니다.

MAXVALUE n | NOMAXVALUE : MAXVALUE는 시퀀스가 증가할수 있는 최대값을 말합니다.                                             NOMAXVALUE는 시퀀스의 값을 무한대로 지정합니다.

MINVALUE n | NOMINVALUE : MINVALUE는 시퀀스의 최소값을 지정 합니다.
                                      기본값은 1이며, NOMINVALUE를 지정할 경우 최소값은 무한대가 됩니다

 


 SQL>CREATE SEQUENCE emp_seq
        START WITH 1
        INCREMENT BY 1
        MAXVALUE 100000 ;

      sequence created.

     시작 값이 1일고 1씩 증가하고, 최대값이 100000만이 되는 시퀀스를 생성했습니다.


 SQL>INSERT INTO emp(empno, ename, hiredate ) VALUES(emp_seq.NEXTVAL, 'julia' , sysdate);

    empno는 컬럼값을 입력할 때 일일이 다음 값을 기억하지 않아도 NEXTVAL을 사용하여
    자동으로 입력할 수 있습니다.

  CURRVAL : 현재 값을 반환 합니다. .
   NEXTVAL : 현재 시퀀스값의 다음 값을 반환 합니다.


 SQL>SELECT emp_seq.CURRVAL FROM DUAL ;

        CURRVAL
        ---------
                  1

 SQL>SELECT emp_seq.NEXTVAL FROM DUAL ;

       NEXTVAL
     ---------
                 2
 



사용규칙 


  ◈ NEXTVAL, CURRVAL을 사용할 수 있는 경우
    - subquery가 아닌 select문
    - insert문의 select절
    - insert문의 value절
    - update문의 set절


  ◈ NEXTVAL, CURRVAL을 사용할 수 없는 경우
    - view의 select절
    - distinct 키워드가 있는 select문
    - group by, having, order by절이 있는 select문
    - select, delete, update의 subquery
    - create table, alter table 명령의 default값


시퀀스의 수정 및 삭제





START WITH는 수정할수 없습니다.
START WITH 절이 없다는 점을 빼고는 CREATE SEQUENCE와 같습니다.
 


 SQL>ALTER SEQUENCE emp_seq
         INCREMENT BY 2
         CYCLE;

      sequence altered.

      2씩 증가하고, 최대값을 넘으면 다시 처음부터 순환하도록 수정하였습니다.


      DROP 문을로 필요하지 않은 시퀀스는 삭제 할수 있습니다.

 SQL>DROP SEQUENCE PRD_SEQ;
        sequence dropped.
 

  ================================================
    * 오라클 정보공유 커뮤니티 oracleclub.com
    * http://www.oracleclub.com
    * http://www.oramaster.net
    * 운영자 : 김정식 (oramaster _at_ naver.com)
  ================================================
※ oracleclub 강좌를 다른 홈페이지에 퍼가실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

 

반응형
출처: http://www.devpia.com/DevStudy/Lecture/OffLineDetail.aspx?nSemiID=1431&lectype=evt

데브피아에 DB 튜닝관련 컬럼 연재가 2회차가 올라왔습니다. ^^

 지난회에는 인덱스를 생성했으나 컬럼의 가공, 내부적 변형, null과의 비교, 부정형 조건등으로 인하여 인덱스를 사용하지 못하는 경우를 보았다.
그럼 과연 인덱스를 타기만 하면 무조건 빠를까?
불행하게도 그렇지 않다. 대부분의 경우는 빠르겠지만 경우에 따라서는 인덱스를 타기 때문에 느려지는 경우가 많이 발생한다.

EMPLOYEE에 성별 컬럼을 추가하고 절반정도 되게 남성과 여성을아래와 같은 분포도로 넣었다.

SELECT GENDER , COUNT(*) CNT, ROUND(COUNT(*) / 15132,3)*100 RATIO FROM EMPLOYEES
GROUP BY GENDER;

G
----
F
M
CNT
--------
7590
7542
RATIO
---------
50.2
49.8
 
그리고 아래와 같은 INDEX를 생성하였다.
CREATE INDEX IDX_GENDER ON EMPLOYEES(GENDER);
그러면 이제 2개의 SQL의 수행 결과를 보자.
첫번째 경우는 INDEX를 탄경우다.
아래와 같이 HINT를 주어서 OPTIMIZER의 PLAN을 고정하였다.
/*+ INDEX(E IDX_GENDER) */ 는 E라는 별명의 테이블에 IDX_GENDER이라는 INDEX를 이용하여 테이블에 데이터를 가져오라는 뜻이다.
SELECT /*+ INDEX( E IDX_GENDER) */ GENDER, COUNT(*), AVG(SALARY)
FROM EMPLOYEES E
WHERE GENDER = 'M'
GROUP BY GENDER

Call
----------
Parse
Execute
Fetch
----------
Total

Count
----------
1
1
2
----------
4
CPU Time
----------
0.000
0.000
0.030
----------
0.030
Elapsed Time
-------------
0.000
0.000
0.026
-------------
0.026
Disk
----------
0
0
0
----------
0
Query
----------
0
0
127
----------
127
Current
----------
0
0
0
----------
0
Rows
----------
0
0
1
----------
1
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user: SCOTT (ID=54)

Rows
----------
0
1
7542
7542
Row Source Operation
---------------------------------------------------
STATEMENT
SORT GROUP BY NOSORT (cr=127 pr=0 pw=0 time=25567 us)
TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=127 pr=0 pw=0 time=15176 us)
INDEX RANGE SCAN IDX_GENDER (cr=16 pr=0 pw=0 time=61 us)OF IDX_GENDER (NONUNIQUE)
두번째 경우는 INDEX를 타지 않은 경우다.
아래와 같이 HINT를 주어서 OPTIMIZER의 PLAN을 고정하였다.
/*+ FULL(E) */ 는 E라는 별명의 테이블을 할 때 테이블 전체를 다 읽어서 처리(FULL TABLE SCAN)하라는 뜻이다.
SELECT /*+ FULL(E) */ GENDER, COUNT(*), AVG(SALARY)
FROM EMPLOYEES E
WHERE GENDER = 'M'
GROUP BY GENDER

Call
----------
Parse
Execute
Fetch
----------
Total

Count
----------
1
1
2
----------
4
CPU Time
----------
0.000
0.000
0.010
----------
0.010
Elapsed Time
-------------
0.000
0.000
0.014
-------------
0.015
Disk
----------
0
0
0
----------
0
Query
----------
0
0
115
----------
115
Current
----------
0
0
0
----------
0
Rows
----------
0
0
1
----------
1
Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user: SCOTT (ID=54)

Rows
----------
0
1
7542
Row Source Operation
---------------------------------------------------
STATEMENT
SORT GROUP BY NOSORT (cr=115 pr=0 pw=0 time=14410 us)
TABLE ACCESS FULL EMPLOYEES (cr=115 pr=0 pw=0 time=181 us)
인덱스를 탄 경우는 0.03초가 걸렸고 인덱스를 타지 않은경우는 0.01초가 걸렸다.
인덱스를 타서 3배나 더 느려졌다!. 이것이 가능한가? 그러면 왜 인덱스를 탔는데도 시간이 더 걸리는 것인가?
이유는 Disk io에 있다. 일반적으로 Full table scan을 할때는 한번에 1개의 block씩 i/o를 하지 않고 muti block를 한번에 요구한다. 그 이유는 읽을 양이 많다고 미리 가정하기 때문이다. 따라서 Oracle의 경우 db_file_multiblock_read_count라는 파라미터가 있고 일반적으로 8또는 16을 설정한다. 만약 16이라면 한번 I/O에 16개의 BLOCK을 읽어오게 되는것이다.
따라서 EMPLOYEE 115BLOCK을 한번에 16개씩 읽으면 약 8번의 IO 요청으로 완료가 된다.
그러나 Index를 사용할 경우 index를 사용하면 기본적으로 대량의 io가 발생할 것이라고 가정하지 않기 때문에 1개의 block씩 i/o를 하게 된다.
따라서 16개의 index block과 115개의 block의 물리적 i/o가 발생한다 16+115를 하면 총 131번의 물리적 io가 발생하게 되는것이다. 논리적으로는 인덱스 1개보고 테이블 1개 블락을 읽고를 7542번+1번을 하게되는 것이다. 마지막 1번은 다음에 더 이상 ‘M’이 없는지 확인하기위해서 1번 더 읽는다. 어째든 인덱스를 사용되는 것이 더욱 느리다는 것이다. 현지 EMPLOYEES 테이블은 15132건이다. 만약 이 데이터가 많아진다면 차이는 점점 더 많이 날것이다.

그러면 어느정도은 INDEX를 타고 어느 정도는 Full Table Scan이 오히려 더 좋은가?
시스템의 성능또는 데이터의 양에 따라 차이가 조금씩있으나 일반적인 기준은 있다.
아래 TYPE이라는 컬럼에 A-F까지 값을 가지고 있으며 가각 49.8%부터 0.9%까지의 분포를 가지는 값들을 가지고 있다.
SELECT TYPE , COUNT(*) CNT, ROUND(COUNT(*) / 15132,3)*100 RATIO FROM EMPLOYEES
GROUP BY TYPE;

T
--------
A
B
C
D
E
F
CNT
--------
7542
4533
1515
799
603
140
RATIO
---------
49.8
30
10
5.3
4
0.9
아래는 예제로 사용되었던 SQL과 그에 따른 응답시간을 비교한 표이다.

FTS(Full Table Scan) SQL
SELECT /*+ FULL(E) */ GENDER, COUNT(*), AVG(SALARY)
FROM EMPLOYEES E
WHERE TYPE = 'F'
GROUP BY GENDER;

Rows
--------
0
1
140
Row Source Operation
---------------------------------------------------
STATEMENT
1 HASH GROUP BY (cr=115 pr=0 pw=0 time=6925 us)
140 TABLE ACCESS FULL EMPLOYEES (cr=115 pr=0 pw=0 time=10323 us)
INDEX(Index Scan) SQL
SELECT /*+ INDEX(E IDX_TYPE) */ GENDER, COUNT(*), AVG(SALARY)
FROM EMPLOYEES E
WHERE TYPE = 'F'
GROUP BY GENDER;

Rows
--------
0
1
140
140
Row Source Operation
---------------------------------------------------
STATEMENT
HASH GROUP BY (cr=87 pr=0 pw=0 time=3227 us) 140
TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=87 pr=0 pw=0 time=2956 us)
INDEX RANGE SCAN IDX_TYPE (cr=2 pr=0 pw=0 time=539 us)OF IDX_TYPE (NONUNIQUE)
 
결과를 보자 약 15132건의 테이블을 ACCESS하는데 10%이상이 되는 경우는 FTS이 더빠르고 10%이하인 경우는 INDEX를 타는 경우가 더 빠르다.
10% 미만일때는 INDEX를 타고 10%가 넘으면 인덱스를 안타게 할수 있는가?
결론적으로 가능하다.
아래 SQL을 보자 2개의 SQL을 UNION ALL로 결합하고 비교조건을(굵은색)을 줌으로서 논리적 비교를 통해서 실제로 FTS의 조건은 타지 않고 INDEX쪽만 수행하도록 하였다.
아래 수행결과를 보면 INDEX를 타는 곳에서만 ROWS가 나온 것을 알수있다.
SELECT /*+ FULL(E) */ GENDER, COUNT(*), AVG(SALARY)
FROM EMPLOYEES E
WHERE TYPE = 'F'
   AND TYPE IN ( 'A','B','C')
GROUP BY GENDER
UNION ALL
SELECT /*+ INDEX(E IDX_TYPE) */ GENDER, COUNT(*), AVG(SALARY)
FROM EMPLOYEES E
WHERE TYPE = 'F'
   AND TYPE IN ( 'D','E','F')
GROUP BY GENDER;

Rows
--------
0
1
0
0
0
1
140
140
Row Source Operation
---------------------------------------------------
STATEMENT
UNION-ALL (cr=87 pr=0 pw=0 time=2743 us)
HASH GROUP BY (cr=0 pr=0 pw=0 time=235 us)
FILTER (cr=0 pr=0 pw=0 time=8 us)
TABLE ACCESS FULL EMPLOYEES (cr=0 pr=0 pw=0 time=0 us)
HASH GROUP BY (cr=87 pr=0 pw=0 time=2477 us)
TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=87 pr=0 pw=0 time=2328 us)
INDEX RANGE SCAN IDX_TYPE (cr=2 pr=0 pw=0 time=205 us)OF IDX_TYPE (NONUNIQUE)
그렇나 이렇게 프로그램을 한다면 프로그램이 힘들어 질것이다. 따라서 현재 Optimizer들은 실제 값에 따라서 FTS이 유리한지 아니면 INDEX SCAN이 유지한지 값을 보고 PLAN이 바뀌도록 되어있다. 물론 이를 위해서는 컬럼에 대한 분포도 정보를 DB가 가지고 있어야 한다. 이는 ANALYZER를 통해서 DB가 취득하게 된다.

그럼 이제 간단하다 10%이상에 데이터를 INDEX를 타면 속도가 오히려 느려지므로 10%이하의 데이터를 찾고자 할 때만 INDEX를 생성하면 간단하게 해결될것이다!
그러나 과연 그럴까?
INDEX를 생성하면 일반적으로 SELECT의 속도는 향상을 보지만 반대로 INSERT,UPDATE,DELETE는 저하되게 된다.
위에 도표를 보면 INDEX의 숫자가 증가함에 따라서 속도가 느려지는 것을 알수 있다. 즉, 인덱스의 생성으로 SELECT는 빨라질수도 있고 느려질수도 있다. 그러나 DML(INSERT,UPDATE,DELETE)는 항상 느려진다. 따라서 INDEX를 무작정 다는 것은 DML 성능을 느리게 한다.
그럼 어떤 기준으로 인덱스를 생성할지 말지를 결정할 것인가?
아래 2가지 시간을 고령하자.
이익시간 = INDEX생성으로 빨라진시간 * 수행 QUERY수
비용시간 = INDEX생성으로 느려진 INSERT시간 * INSERT수행횟수
                + INDEX생성으로 느려진 UPDATE시간 * UPDATE수행횟수
                + INDEX생성으로 느려진 DELETE시간 * DELETE수행횟수

이익시간이 비용시간 보다 크다면 인덱스를 생성하는 것이 좋을 것이다. 반대로 이익시간 < 비용시간 보다 작다면 인덱스를 만드는 것이 손해보는 경우다.
이런 경우라면 인덱스를 만들면 안되는 것이 유리하다 할수 있다. 그러나 반드시 그런 것은 아니다. 그것은 수행 시간을 고려해야한다. 낮에일반적으로 QUERY가 빠르게 수행되고 주로 밤에 BATCH에서 DML이 수행되고 있다고 가정할 때 DML이 더 느려지는 것이 그렇게 문제가 되지 않는다면 INDEX를 생성할 수도 있는것이다. 어디까지나 Application 사용의 관점에서 효율적인 것을 찾는 것이 중요하다.
인덱스를 사용하여 손해보는 경우는 아래와 같다.

  • 같은 값이 많은 컬럼
    • INDEX를 타면 10%이상 선택하는 경우
    • 예) 남녀성별등..
  • 조회보다 DML의 부담이 큰 경우
    • 이익시간 < 비용시간 경우
    • 그러나 이때도 사용환경을 고려하여 인덱스를 생성할 수 있다.
  • 데이터가 적은 테이블
    • 일반적으로 db_file_multiblock_read_count보다 적은 수의 BLOCK을 가진테이블은 INDEX를 타지 않는 것이 빠르다.
    • 그러나 integrity를 위해서 PK와 FK는 달아야 한다.

이번 회에는 INDEX를 타서 오히려 손해를 보는 경우와 그를 방지하는 방법을 보았다.
인덱스를 생성한다고 인덱스를 반드시 타는 것도 아니며 또한 인덱스를 탄다고 반드시 빠른 것도 아니다. 따라서 INDEX의 생성과사용 전략은 그렇게 쉬운 문제가 아니다. 빠른 시스템을 위해서는 고려할 점이 많다는 것이다. 물론 필자가 다룬 것은 테이블중에 일반테이블과 일반 INDEX에 대해서만 다루었기때문에 PARTITION이나 BITMAP같은 다른 구조의 인덱스에서는 다른 특성을 가진다. 그러나 이러한 것은 대용량이나 DW의 특수한 용도에 사용되므로 대부분의 경우에는 고려하지 않아도 크게 문제되지 않을 것이다.

못조록 필자의 글이 독자들에게 도움이되는 길이기를 바라면서 이글을 마무리하고자 한다.
마지막으로 당부 드리고 싶은 말은 SQL을 작성하시고 항상 PLAN을 확인하시기 바랍니다.
PLAN에 익숙해지고 OPTIMIZER를 이해할 때 비로소 OPTIMIZER가 여러분의 심부름꾼이 될수 있기때문이다.



+ Recent posts