내 남자의 길~!

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 >

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.

 지난회에는 인덱스를 생성했으나 컬럼의 가공, 내부적 변형, 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가 여러분의 심부름꾼이 될수 있기때문이다.