0. Intro ORACLE 社 Oracle, My SQL, Exadata IBM 社 DB2, Informix SAP 社 ASE, IQ, ASA Microsoft 社 SQL Server Teradata 社 Teradata 공통점은? Altibase 社 Altibase

Size: px

Start display at page:

Download "0. Intro ORACLE 社 Oracle, My SQL, Exadata IBM 社 DB2, Informix SAP 社 ASE, IQ, ASA Microsoft 社 SQL Server Teradata 社 Teradata 공통점은? Altibase 社 Altibase"

현화 연
5 years ago
Views:

1 목 차 SQL 기본과활용 삼성 S D S 정성철수석 ( D A / T A ) 1. RDBMS 2. SQL 3. SELECT 4. INDEX 5. MODELING 6. JOIN 7. DRIVING TABLE 8. SUBQUERY 9. OPTIMIZER 10. 과목2. ERD 11. 과목2. 목차

2 0. Intro ORACLE 社 Oracle, My SQL, Exadata IBM 社 DB2, Informix SAP 社 ASE, IQ, ASA Microsoft 社 SQL Server Teradata 社 Teradata 공통점은? Altibase 社 Altibase TMAX 社 Tibero, Nanobase NHN 社 Cubrid

3 1. RDBMS RDBMS (H N R) q 60 년대하이러키칼 / 계층형 DBMS, 70 년대네트워크 / 망형 DBMS 를거쳐 E.F.CODD 박사가 80 년대개념을제시한릴레이셔날 / 관계형데이터베이스는수학적개념을기반으로하여그편리성과유용성으로인해 90 년대폭발적인성장세를거쳐서지금까지데이터베이스의주류를이루고있다. q 앞으로 OODBMS 가주류? RDBMS 는없어진다??

4 1. RDBMS RDBMS (H N R) q 앞으로 OODBMS 가주류? No. RDBMS 는없어진다?? 없어지지않는다. 없어지면내손에장을지진다.^^. Why???

5 1. RDBMS RDBMS (H N R) q 앞으로 OODBMS 가주류? No. RDBMS 는없어진다?? 없어지지않는다. 없어지면내손에장을지진다.^^. Why??? q 기업의핵심데이터는대부분문자와숫자로만들어지고, 가로 x 세로테이블포맷으로만들어지기때문이다. 테이블구조를가장효율적으로사용할수있는것이관계형데이터베이스이다. q 기술적으로는객체기능을포함하지만, 핵심은관계형데이터베이스이다. ORDBMS (H N R or)

6 2. SQL ORDBMS (H N R or) SQL (DML/DDL/DCL, 실행순서 ) RDBMS 와빼놓을수없는것이바로 SQL!

7 2. SQL SQL 의중요성 1. 관계형데이터베이스를유일하게액세스할수있는랭귀지 2. DA나개발자로서사회에서처음접하는주요 IT 기술중하나 3. 모델링이실제로구현될때사용되는기술 4. 개발자로서실력평가의기준이되는지식 SQL 활용의목적 1. 원하는정확한결과를조회 / 입력 / 수정 / 삭제하는것 (DML)

8 2. SQL SQL 의특징 1. SQL 은 DML 4 개, DDL 4 개, DCL 2 개등 10 개의명령어만배우면누구나쉽게사용할수있는언어이다. 2. 그런데, 결과는똑같은데이터가나오는 SQL 문장인데, 개발자에따라서수행시간은 1 시간이소요될수도있고, 1 초가소요될수도있다.

9 2. SQL SQL 개발자의현주소 1. 테이블의구조와칼럼의데이터타입을모르고, SQL 을작성한다. 2. NULL 의미를모른다. 공집합의개념을모른다. NVL/ISNULL 함수를남발한다. 3. 불필요한 [ 스칼라 ] 서브쿼리, 함수를남발한다. ( 조인으로 ) 4. 인덱스의역할을이해하지못한다. 5. 인덱스사이즈가테이블보다몇배크다. 6. 성능을고려하지않고 Dynamic SQL 을작성한다. 7. UNION ALL 을고민하지않고, UNION 을사용한다. 8. Multi Column IN List, Correlated Subquery Update, Rollup/Cube 그룹함수, Rank/Dense_Rank 윈도우함수등을활용해짧고최적화된 SQL 을작성하지않고, SQL 문장을복잡하고비효율적이고이해하기힘들게작성한다. 9. 실행계획과옵티마이저의역할에관심이없다. ( 튜닝출발점 )

10 2. SQL SQL 튜닝의목적 1. 한정된자원을 ( 특히??? 를 ) 효율적으로활용하면서빠른응답속도를보장하는것 2. HW 자원증설에비해비용도적고효과도훨씬좋다.

11 2. SQL SQL 튜닝의목적 1. 한정된자원을 ( 특히??? 를 ) 효율적으로활용하면서빠른응답속도를보장하는것 SQL 튜닝이필요한이유 1. 데이터베이스가운영되는메모리와데이터를보관하는디스크의데이터액세스속도차이때문이다. 상대적으로빠른속도를가졌지만높은비용이소요되는메모리와저렴하지만느린속도를가진디스크에그원인이있는것임. 2. SQL 튜닝은대부분한정된메모리를효율적으로사용하기위한노력이라고할수있다. (IO, CPU, Lock 도포함됨 ) MMDBMS MRDBMS 1. 이론적으로는몇십배빠른성능을보여야하는메인메모리 DBMS 이지만, 장애를대비해서디스크에백업을받아야하는문제가있어아직은몇배빠른정도의성능을보이는한계를보이고있음

12 3. SELECT ORDBMS (H N R or) SQL (DML/DDL/DCL) SELECT ( 가장중요함 ) RDBMS 와빼놓을수없는것이바로 SQL! 10 개 SQL 중가장중요한 SQL 은바로 SELECT 문! Why?

13 3. SELECT Q. SELECT 문장실행순서?. SELECT column(s)?. FROM Table(s)?. [WHERE condition(s)]?. [GROUP BY column(s]?. [HAVING condition(s)]?. [ORDER BY column(s)];

14 3. SELECT Q. SELECT 문장실행순서 5. SELECT column(s) 1. FROM Table(s) 2. [WHERE condition(s)] 3. [GROUP BY column(s] 4. [HAVING condition(s)] 6. [ORDER BY column(s)];

15 3. SELECT Q. 결과는? 1. SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO, SAL; (?) 2. SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO ORDER BY SUM(COMM), MAX(EMPNO), MIN(MGR); (?) 3. SELECT ENAME, JOB, DEPTNO FROM EMP WHERE (JOB, DEPTNO) IN (SELECT DNAME, DEPTNO FROM DEPT); (?)

16 3. SELECT Q. 결과는? 1. SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO, SAL; SYNTAX ERROR! 2. SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO ORDER BY SUM(COMM), MAX(EMPNO), MIN(MGR); 맞는문장 3. SELECT ENAME, JOB, DEPTNO FROM EMP WHERE (JOB, DEPTNO) IN (SELECT DNAME, DEPTNO FROM DEPT); 맞는문장

17 3. SELECT Q. 결과는? 1. SELECT NVL(MGR,9999) FROM EMP WHERE ENAME = 'JSC'; (?) SELECT NVL(MGR,9999) FROM EMP WHERE 1 = 2 ; (?) 2. SELECT MAX(MGR) FROM EMP WHERE ENAME = 'JSC'; (?) 3. SELECT NVL(MAX(MGR),9999) FROM EMP WHERE ENAME = 'JSC'; (?)

18 3. SELECT Q. 결과는? 1. SELECT NVL(MGR,9999) FROM EMP WHERE ENAME = 'JSC'; SELECT NVL(MGR,9999) FROM EMP WHERE 1 = 2 ; 공집합 (' 데이터를찾을수없다.') 2. SELECT MAX(MGR) FROM EMP WHERE ENAME = 'JSC'; NULL Aggregate 함수와 Scalar Subquery 는 NULL 을리턴함 3. SELECT NVL(MAX(MGR),9999) FROM EMP WHERE ENAME = 'JSC'; 9999 ISNULL(MAX(MGR),9999)

19 3. SELECT Q. 가장효율적인표현식은? ( 결과는같음 ) 1. SELECT 1NVL(SUM(DECODE(YEAR, '2010', AMT )),0) AS Yr2010 2NVL(SUM(DECODE(YEAR, '2010', AMT,0)),0) AS Yr2010 3SUM(NVL(DECODE(YEAR, '2010', AMT ),0)) AS Yr2010 4SUM(NVL(DECODE(YEAR, '2010', AMT,0),0)) AS Yr2010 FROM SALES; 2. SELECT 1ISNULL(SUM(CASE YEAR WHEN '2010' TEHN AMT END),0) Yr2010 2ISNULL(SUM(CASE YEAR WHEN '2010' TEHN AMT ELSE 0 END),0) Yr2010 3SUM(ISNULL(CASE YEAR WHEN '2010' TEHN AMT END),0)) Yr2010 4SUM(ISNULL(CASE YEAR WHEN '2010' TEHN AMT ELSE 0 END),0)) Yr2010 FROM SALES;

20 3. SELECT Q. 가장효율적인표현식은? ( 결과는같음 ) 1. SELECT 1NVL(SUM(DECODE(YEAR, '2010', AMT )),0) AS Yr2010 2NVL(SUM(DECODE(YEAR, '2010', AMT,0)),0) AS Yr2010 3SUM(NVL(DECODE(YEAR, '2010', AMT ),0)) AS Yr2010 4SUM(NVL(DECODE(YEAR, '2010', AMT,0),0)) AS Yr2010 FROM SALES; 2. SELECT 1ISNULL(SUM(CASE YEAR WHEN '2010' TEHN AMT END),0) Yr2010 2ISNULL(SUM(CASE YEAR WHEN '2010' TEHN AMT ELSE 0 END),0) Yr2010 3SUM(ISNULL(CASE YEAR WHEN '2010' TEHN AMT END),0)) Yr2010 4SUM(ISNULL(CASE YEAR WHEN '2010' TEHN AMT ELSE 0 END),0)) Yr2010 FROM SALES;

21 4. INDEX ORDBMS (H N R or) SQL (DML/DDL/DCL) SELECT ( 가장중요함 ) RDBMS 와빼놓을수없는것이바로 SQL! 10 개 SQL 중가장중요한 SQL 은바로 SELECT 문! INDEX (B_TREE,BITMAP, 결합 INDEX) RDBMS 의성능핵심은바로 INDEX! 가장중요한 INDEX 는 B Tree 인덱스 No2. 등장! Bitmap Index (Not, Null, Or 조건및압축에장점 ) ( 데이터변경이적은경우에만효과적임 )

22 5. MODELING ORDBMS (H N R or) MODELING ( 정규화,M:M 분해 ) SQL (DML/DDL/DCL) SELECT ( 가장중요함 ) INDEX (B_TREE,BITMAP, 결합 INDEX) 데이터베이스에서데이터모델링은필수! 특히관계형데이터베이스에서정규화는데이터의정합성과저장공간절약을위해필수적임 정규화는기술적으로는하나의엔티티를여러개의엔티티로나누는작업이다.

23 6. JOIN ORDBMS (H N R or) SQL (DML/DDL/DCL) SELECT ( 가장중요함 ) DBMS 에서데이터모델링은필수! 특히관계형데이터베이스에서정규화는데이터의정합성과저장공간절약을위해필수적임 MODELING ( 정규화,M:M 분해 ) INDEX (B_TREE,BITMAP, 결합 INDEX) 정규화등모델링작업을거쳐서나누어진엔티티간의연결필수! RDBMS 의꽃은바로 JOIN! JOIN (NLJ,HJ,SMJ) 가장중요한조인은 NLJ! Why?

24 6. JOIN ORDBMS (H N R or) SQL (DML/DDL/DCL) SELECT ( 가장중요함 ) DBMS 에서데이터모델링은필수! 특히관계형데이터베이스에서정규화는데이터의정합성과저장공간절약을위해필수적임 MODELING ( 정규화,M:M 분해 ) INDEX (B_TREE,BITMAP, 결합 INDEX) 정규화등모델링작업을거쳐서나누어진엔티티간의연결필수! RDBMS 의꽃은바로 JOIN! JOIN (NLJ,HJ,SMJ) 가장중요한조인은 NLJ! Why? 새로운 No2. 등장! Hash JOIN ( 정렬및조인인덱스필요없음, HW 자원비례 )

25 7. DRIVING TABLE ORDBMS (H N R or) SQL (DML/DDL/DCL) SELECT ( 가장중요함 ) DBMS 에서데이터모델링은필수! 정규화등모델링을거쳐서나누어진엔티티간의연결필요! MODELING ( 정규화,M:M 분해 ) INDEX (B_TREE,BITMAP, 결합 INDEX) RDBMS 의꽃은바로 JOIN! 조인에서중요한것은드라이빙테이블! JOIN DRIVINGTABLE (NLJ,HJ,SMJ) (INDEX 매칭율, 통계정보 )

26 7. DRIVING TABLE 실전 SQL 튜닝에서는.. No0. 과다한 Dynamic SQL 파싱발생 ( 장애발생가능성 ) No1. INDEX 튜닝 ( 인덱스생성 / 통합 / 삭제, 인덱스관련조건절튜닝포함 ) No2. NLJ, HJ 의드라이빙테이블선정 인라인뷰의사용이많아지면서여러집합의조인이일어날수있는데, 가장효율적인검색이될수있도록테이블간의순서를가이드할수있을정도의 SQL 튜닝실력이필요하다. 고급 SQL 튜닝을하기위해서는옵티마이저와실행계획, 인덱스를이해할수있어야한다. ( 과목 2. 3 장. SQL 최적화기본원리 에서기초를파악하고, 과목 3. SQL 고급활용및튜닝 에서심화학습함 )

27 7. DRIVING TABLE 드라이빙테이블의중요성 (NLJ 사례 ) 관계는 1:1:1 로가정함, 양쪽결과는같음 TABLE1 TABLE2 TABLE3 TABLE3 TABLE2 TABLE1 1 A 2 C 3 D 4 K 5 M 6 F 7 E 8 M A 가 P 나 C 라 H 사... E 마 (1000 row) 라 10 마 20 (2 row) vs 라 10 마 20 (2 row) A 가 P 나 C 라 S 바... E 마 (2 row) 1 A 2 C 3 D 4 K 5 M 6 F 7 E 8 M (10000 row) (2 row) 최소 11,002 회이상 ACCESS 최대 6 회이하 ACCESS

28 8. SUBQUERY ORDBMS (H N R or) SQL (DML/DDL/DCL) SELECT ( 가장중요함 ) DBMS 에서데이터모델링은필수! 정규화등모델링을거쳐서나누어진엔티티간의연결필요! MODELING ( 정규화,M:M 분해 ) INDEX (B_TREE,BITMAP, 결합 INDEX) RDBMS 의꽃은바로 JOIN! 조인에서중요한것은라이빙테이블! 드 JOIN (NLJ,HJ,SMJ) DRIVINGTABLE (INDEX 매칭율, 통계정보 ) SUBQUERY (NESTED SUBQUERY, INLINE VIEW, SCALAR SUBQUERY) 서브쿼리고민하고사용하자!

29 8. SUBQUERY 네스티드서브쿼리 1. 서브쿼리를문법적으로구분하는가장쉬운방법은괄호 (~~) 로묶여져있는 SQL 문장은서브쿼리이다. 2. 조인과서브쿼리를논리적으로구분하는가장좋은방법은두개의테이블위치를바꾸어보는것이다. 3. 조인은두개의테이블위치를바꾸어보더라도같은결과가나오며, 서브쿼리의경우는주종의관계이므로일반적으로다른결과가나오게된다. 4. 만일서브쿼리에서두개의테이블위치를바꾸었는데도같은결과가나온다면, 조인으로바꿀수있다는얘기가되므로가능하면서브쿼리를집합적개념을사용할수있는조인으로바꾸어야한다.

30 8. SUBQUERY 인라인뷰 1. Dynamic Table 로서브쿼리의칼럼을조인처럼메인쿼리에서도사용할수있다. 2. 비절차성 SQL 에일종의절차성을부여한매우중요한기능으로, SQL 이더많은역할을할수있도록기여하였다. ( 뷰병합이없다면일반적으로인라인뷰가먼저실행됨 ) 3. 단일 SQL 문장이너무길어질수있으므로유지보수를위해주석문필요함 스칼라서브쿼리 1. 스칼라서브쿼리의경우하나의 SQL 문이지만, 실질적으로는하나의함수 (Function) 와같은특성 ( 입력 M : 출력 1, 행마다수행 ) 을가진다. 2. 대량의데이터처리시무분별하게함수나스칼라서브쿼리를남발하는경우는성능저하의원인이되며, 집합적개념을적용하기힘들어지게되므로, 같은결과가나올수있다면조인으로대체하는것이필요하다. ( 캐싱기능으로스칼라서브쿼리가유용한경우도있음 )

31 9. OPTIMIZER ORDBMS (H N R or) SQL (DML/DDL/DCL) SELECT ( 가장중요함 ) RDBMS 의핵심엔진은옵티마이저! 다양한실행계획중최적의실행계획을예측함 MODELING OPTIMIZER INDEX ( 정규화,M:M 분해 ) ( 실행계획,CBO, RBO,HINT) (B_TREE,BITMAP, 결합 INDEX) JOIN (NLJ,HJ,SMJ) DRIVINGTABLE (INDEX 매칭율, 통계정보 ) SUBQUERY (NESTED SUBQUERY, INLINE VIEW, SCALAR SUBQUERY)

32 9. OPTIMIZER 옵티마이저 1. CBO 자동카메라 2. RBO 수동카메라 3. 대세는 CBO 가주류, 대용량데이터베이스의경우 RBO 부적합가능성 4. RBO 를중시하던오라클마저 10g 버전부터 RBO 모드를지원하지않음 (RULE HINT 유지및다양한 HINT 추가제공 ) 5. 사진전문가들은수동카메라를선호 실력이뛰어난 DA 들은 RBO 선호 6. CBO 를기반으로하되, 중요 SQL 문의경우힌트를이용해최적의실행계획을고정시킴

33 10. 과목 2. ERD (K- 리그 )

34 10. 과목 2. ERD (K- 리그 )

35 10. 과목 2. ERD ( 부서 - 사원 ) DEPT( 부서 ) DEPTNO DNAME LOC (4 건 ) IE( 정보공학 ) 표기법실선 : 식별관계점선 : 비식별관계 < EMP( 사원 ) EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO (FK) (14 건 )

36 11. 과목 2. 목차 1 장. SQL 기본 1. 관계형데이터베이스개요 ü 데이터베이스, SQL, TABLE, ERD 2. DDL ü 데이터유형, CREATE TABLE, ALTER TABLE, RENAME TABLE, DROP TABLE, TRUNCATE TABLE 3. DML ü INSERT, UPDATE, DELETE, SELECT, ALIAS, 산술연산자와합성연산자 4. TCL ü 트랜잭션개요, COMMIT, ROLLBACK, SAVEPOINT 5. WHERE 조건절 ü 개요, 연산자의종류, 비교연산자, SQL 연산자, 논리연산자, 부정연산자, ROWNUM/TOP 사용 6. FUNCTION ü 내장함수 (BUILT-IN FUNCTION) 개요, 문자형함수, 숫자형함수, 날짜형함수, 변환형함수, CASE 표현, NULL 관련함수 (NVL/ISNULL, 공집합, NULLIF, COALESCE) 7. GROUP BY, HAVING 절 ü 집계함수, GROUP BY 절, HAVING 절, SUM(CASE/DECODE) 월별집계, 집계함수와 NULL 처리 8. ORDER BY 절 ü ORDER BY 정렬, SELECT 문장실행순서, TOP N 쿼리 9. JOIN ü 개요, EQUI JOIN, NON-EQUI JOIN, 3 개이상 TABLE JOIN

37 11. 과목 2. 목차 2 장. SQL 활용 1. STANDARD JOIN ü 개요, FROM 절 JOIN 형태, INNER JOIN, NATURAL JOIN, USING 조건절, ON 조건절, CROSS JOIN, OUTER JOIN, INNER VS OUTER VS CROSS JOIN 비교 2. 집합연산자 ü 개요, UNION, UNION ALL, INTERSECT, MINUS/EXCEPT 3. 계층형질의 ü 계층형질의, SELF JOIN 4. 서브쿼리 ü 개요, 단일행서브쿼리, 다중행서브쿼리, 다중칼럼서브쿼리, 연관서브쿼리, SCALAR SUBQUERY, INLINE VIEW, 뷰 5. GROUP 함수 ü 데이터분석개요, ROLLUP 함수, CUBE 함수, GROUPING SETS 함수 6. WINDOW 함수 ü 개요, 그룹내순위함수, 일반집계함수, 그룹내행순서함수, 그룹내비율함수 7. DCL ü 개요, 유저와권한, ROLE 을이용한권한부여 8. STORED MODULE ü 개요, PL/SQL 개요, PROCEDURE 의생성과활용, USER DEFINED FUNCTION 의생성과활용, TRIGGER 의생성과활용, 프로시저와트리거의차이점

38 11. 과목 2. 목차 3 장. SQL 최적화기본원리 ( 과목 3. 의연결고리 ) 1. 옵티마이저와실행계획 ü 옵티마이저, 실행계획, SQL 처리흐름도 2. 인덱스기본 ü 특징과종류, 전체테이블스캔과인덱스스캔, 조인수행원리 3. 조인수행원리 ü NL 조인, SM 조인, HASH 조인

목차 BUG 문법에맞지않는질의문수행시, 에러메시지에질의문의일부만보여주는문제를수정합니다... 3 BUG ROUND, TRUNC 함수에서 DATE 포맷 IW 를추가지원합니다... 5 BUG ROLLUP/CUBE 절을포함하는질의는 SUBQUE

목차 BUG 문법에맞지않는질의문수행시, 에러메시지에질의문의일부만보여주는문제를수정합니다... 3 BUG ROUND, TRUNC 함수에서 DATE 포맷 IW 를추가지원합니다... 5 BUG ROLLUP/CUBE 절을포함하는질의는 SUBQUE ALTIBASE HDB 6.3.1.10.1 Patch Notes 목차 BUG-45710 문법에맞지않는질의문수행시, 에러메시지에질의문의일부만보여주는문제를수정합니다... 3 BUG-45730 ROUND, TRUNC 함수에서 DATE 포맷 IW 를추가지원합니다... 5 BUG-45760 ROLLUP/CUBE 절을포함하는질의는 SUBQUERY REMOVAL 변환을수행하지않도록수정합니다....