개발및운영 Tibero SQL 가이드

Tibero SQL 가이드 2014. 05. 30.

목차 1. SQL... 4 1.1. SQL 개요... 4 1.2. SQL 문장분류... 4 1.3. SQL 작성시요구사항... 4 2. SQL 문장의구성요소... 5 2.1. 데이터타입... 5 2.1.1. 문자형... 6 2.1.2. 숫자형... 6 2.1.3. 날짜형... 6 2.1.4. 간격형... 6 2.1.5. 대용량객체형... 6 2.1.6. 내재형... 6 2.2. 리터럴... 7 2.3. 형식문자열... 7 2.4. 의사컬럼... 10 2.4.1. ROWID... 11 2.4.2. ROWNUM... 12 2.4.3. LEVEL... 13 2.4.4. CONNECT_BY_ISLEAF... 13 2.5. NULL... 13 2.6. 주석... 13 2.7. 힌트... 14 2.7.1. 힌트의종류... 14 2.7.2. 접근방법... 16 2.7.3. 최적화방법... 16 2.7.4. 접근방법... 16 2.7.5. 조인순서... 17 2.7.6. 조인방법... 18 2.7.7. 병렬처리... 19 2.7.8. 실체화뷰... 19 2.7.9. 기타... 19 2.8. 스키마객체... 19 3. SQL 연산자... 21 4. SQL 함수... 25 2

4.1. 단일행함수종류... 25 4.2. 그룹행함수종류... 32 5. JOIN... 35 5.1. JOIN 유형... 35 5.2. JOIN 방식... 38 6. SUBQUERY... 41 6.1. SUBQUERY 유형... 41 7. DML... 44 7.1. DML 유형... 44 7.1.1. INSERT... 44 7.1.2. UPDATE... 46 7.1.3. MERGE... 46 7.1.4. DELETE... 46 8. TRANSATION... 47 8.1. TRANSACTION... 47 8.1.1. COMMIT... 47 8.1.2. SAVEPOINT... 47 8.1.3. ROLLBACK / ROLLBACK TO SAVEPOINT_NAME... 48 9. SQL 활용가이드... 49 9.1. SQL 구문활용가이드... 49 9.2. SQL 함수, 키워드및조건절활용가이드... 49 9.2.1. Distinct, Decode, 그룹함수 (SUM, AVG)... 49 9.2.2. OR 및 WHERE 절구문... 50 9.2.3. IN... 50 9.2.4. EXISTS... 51 9.3. Join 활용가이드... 51 9.3.1. Outer Join... 51 9.4. Index 활용가이드... 51 9.4.1. Index 를사용할수없는경우... 51 9.4.2. 유형별해결방안 - Index 구성컬럼의외부적변형... 52 9.4.3. 유형별해결방안 - Index 구성컬럼의내부적변형... 52 9.4.4. 유형별해결방안 - 부정형비교... 53 9.4.5. 유형별해결방안 - NULL 값비교... 53 3

Tibero SQL 가이드 1. SQL 1.1. SQL 개요 SQL(Structure Query Language) 은 RDBMS 를사용하기위해 ANSI 에서채택한표준언어로 Tibero 서버와통신하기위 한명령언어이다. 1.2. SQL 문장분류 구분내용명령어설명 DML (Data Manipulation Language) 데이터베이스객체내의데이터조회및삽입, 삭제, 변경을위한문장 SELECT INSERT UPDATE DELETE 데이터조회 데이터삽입데이터변경 데이터삭제 DDL (Data Definition Language) 데이터베이스객체를생성, 변경, 제거하기위한문장 CREATE ALTER DROP RENAME TRUNCATE 객체생성객체변경객체제거객체이름변경 객체데이터삭제및사용한 저장공간모두해제 TCL (Transaction Control Language) Transaction 종료및취소하기위한문장 COMMIT ROLLBACK SAVEPOINT 트랜잭션완료트랜잭션취소 트랜잭션특정시점저장 DCL (Data Control Language) 데이터및객체들의작업에대한권한을부여하고취소하기위한문장 GRANT REVOKE 권한부여 권한회수 1.3. SQL 작성시요구사항 SQL문은대소문자를구별하지는않지만프로젝트의가이드에맞추도록한다. SQL문은하나이상의줄에입력할수있지만, 키워드는여러줄에입력할수는없다. 절은일반적으로읽기쉽고편집하기쉽도록다른줄에쓴다. 들여쓰기를사용하면좀더읽기쉬운 SQL문을작성할수있다. 일반적으로키워드는대문자로입력하고테이블이름, 열등다른단어는모두소문자로입력한다. TAB 과들여쓰기를사용하여좀더읽기쉬운 SQL로작성하도록한다. 4

2. SQL 문장의구성요소 구분 데이터타입 설명 Tibero RDBMS 에서는 SQL 표준에기반한여러가지데이터타입을제공한다 리터럴상수값을의미한다. 형식문자열 NUMBER 타입과날짜형타입의값을문자열로변환하기위한형식을정의한 것이다. 의사컬럼시간간격을표현하는데이터타입이다. NULL 한로우에서어떤컬럼에값이없을때그컬럼을 NULL 이라고한다. 주석 힌트 스키마객체 책이나문서에서주석이낱말이나문장의뜻을쉽게풀이하는역할을하듯 SQL 문장에도주석을활용하여해당문장의부연설명을삽입할수있다. SQL 문장에힌트를추가하여 Tibero RDBMS의질의최적화기 (Optimizer) 에특정행동을지시하거나질의최적화기의실행계획을변경한다. 한사용자가하나의스키마만을정의할수있고, 스키마의이름은항상사용자의이름과동일하다. 이러한스키마에포함된객체를스키마객체라한다. 2.1. 데이터타입 구분데이터타입설명 문자형 CHAR, VARCHAR, VARCHAR2, NCHAR, NVARCHAR, NVARCHAR2, RAW, LONG, LONG RAW 문자열을표현하는데이터타입 숫자형 NUMBER, INTEGER, FLOAT 정수나실수의숫자를저장하는데이터타입 날짜형 간격형 DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND 시간이나날짜를저장하는데이터타입 시간이나날짜사이의간격을저장하는데이터 타입 대용량객체형 CLOB, BLOB, XMLTYPE 대용량의객체를저장하기위해 Tibero에서제공하는가장큰데이터타입 내재형 ROWID 사용자가명시적으로선언하지않아도Tibero가삽입되는로우마다자동으로부여하는데이터 타입 5

2.1.1. 문자형 CHAR (n) - 문자열을저장하는데이터타입이다. 항상고정된문자열길이를갖는다. VARCHAR(n) - 가변길이문자데이터. 최대 4000byte 까지선언 NCHAR - 유니코드문자열을저장하기위한타입.( 고정길이 ) 최대 2000byte 까지선언 NVARCHAR - 유니코드문자열을저장하기위한타입.( 가변길이 ) 최대 4000Byte 까지선언 NVARCHAR2 - NVARCHAR 타입과완전히동일 RAW - 임의의바이너리데이터를저장하기위한타입. 최대 2000byte 까지선언 LONG - 최대 2G까지선언. 일반문자열저장 LONG RAW - 최대 2G까지선언. 바이너리데이터저장 2.1.2. 숫자형 NUMBER (p,s) - 가변길이숫자데이터. 전체 p 자리중소수점이하 s 자리. 2.1.3. 날짜형 DATE - 날짜및초단위까지의시간을선언. TIME - 초단위소수점 9자리까지시간을선언. TIMESTAMP - 날짜와초단위소수점 9자리까지의시간을선언. TIMESTAMP WITH TIME ZONE - TIMESTAMP 타입을확장하여시간대까지표현 TIMESTAMP WITH LOCAL TIME ZONE - 특정세션의시간대에따라다르게시간정보를표현 2.1.4. 간격형 INTERVAL YEAR TO MONTH - 연도와월을이용하여시간간격을표현 INTERVAL DAY TO SECOND - 일, 시, 분, 초를이용하여시간간격을표현 2.1.5. 대용량객체형 BLOB - 최대 4G까지선언. 바이너리저장 (LONG RAW을확장한데이터타입 ) CLOB - 최대 4G까지선언. 일반문자열저장 (LONG을확장한데이터타입 XMLTYPE - XML(Extensible Markup Language) 은구조화되거나그렇지않은모든데이터를표현하기위해 W3C (World Wide Web Consortium) 에의해표준으로제정된형식 - Tibero에서 XML 데이터를저장하기위해 XMLTYPE 타입을제공하고, 내부적으로 CLOB 형식으로저장 2.1.6. 내재형 ROWID - 사용자가명시적으로선언하지않아도 Tibero RDBMS 가삽입되는 ROW 마다자동으로부여 하는데이터타입 6

2.2. 리터럴 구분문자열리터럴숫자리터럴날짜형리터럴간격리터럴 설명문자열리터럴문자열을표현할때사용하는리터럴숫자리터럴정수또는실수를표현할때사용하는리터럴날짜형리터럴날짜와시간정보를표현하는리터럴간격리터럴특정시간과시간사이의간격을표현하는리터럴 문자열리터럴 'Tibero' 'Database' '2009/11/11' 숫자리터럴 123 +1.23 0.123 123e-123-123 날짜형리터럴 TO_DATE('2005/01/01 12:38:20', 'YY/MM/DD HH24:MI:SS') TO_TIME('12:38:20.123456789', 'HH24:MI:SSXFF') TO_TIMESTAMP('09-Aug-01 12:07:15.50', 'DD-Mon-RR HH24:MI:SS.FF') 간격리터럴 INTERVAL '12-3' YEAR TO MONTH INTERVAL '123' YEAR(3) INTERVAL '123' MONTH INTERVAL '1' YEAR INTERVAL '1234' MONTH(3) 2.3. 형식문자열 NUMBER 타입과날짜형타입의값을문자열로변환하기위한형식을정의한것이다. NUMBER 타입 형식요소예제결과설명,( 쉼표 ) or D 9,999 9D999 1,234 해당위치에쉼표를표기한다..( 마침표 )or G 9,999.99 1,234.00 해당위치에소수점을표기하고지정된자릿수에데이터가 7

없으면 0 을표기한다. $ $9,999 $1,234 숫자의시작에달러문자를표기한다. 0 00,999 01,234 해당되는위치에값이없을경우 0 을표기한다. MI 9999MI -1234 1234- 음수부호를표기하는형식요소문자를숫자로변환시엔음수기호를앞에표기해주고, 숫자를문자로변환시엔음수기호를뒤에표기한다. PR 9999PR <1234> 음수에대해서숫자를문자로변환할때만음수표시 S S9999 9999S +1234 1234+ 양수 / 음수부호를해당위치에표기한다. NUMBER 타입 -- 9,999 SELECT to_char('1234','9,999')from dual; TO_CHAR('1234','9,999') ----------------------- 1,234 -- 9999MI SELECT to_number('-1234','9999mi') FROM dual; TO_NUMBER('-1234','9999MI') --------------------------- -1234 -- 9999MI SQL> SELECT to_char('-1234','9999mi') FROM dual; TO_CHAR('-1234','9999MI') ------------------------- 1234- DATE 타입형식요소 예제 결과 설명 -,.;:/ text YYYY-MM-DD 2007-04-18 결과값의해당하는위치에그대로출력된다. D D 3 일주일중몇번째날 (1~7) DAY DAY WEDNESDAY 요일이름을표기한다. DD DD 18 일자표기 (1~31) DDD DDD 108 일년중몇번째날 (1~366) 8

DY DY WED 축약한요일이름표기 MM MM 04 달을표기 (1~12) MON MON APR 축약한달이름표기 MONTH MONTH APRIL 달이름표기 Q Q 2 일년중몇번째분기 (1~4) YYYY YYYY 2007 연도 4 자리수표기 YEAR YEAR TWO THOUSAND SEVEN 연도를말로풀어서표기 DATE 타입 SQL> SELECT sysdate FROM dual; SYSDATE -------------------------------- 2014/05/19 -- YYYY-MM-DD SQL> SELECT to_char(sysdate, 'YYYY/MM/DD') FROM dual; TO_CHAR(SYSDATE,'YYYY/MM/DD') ----------------------------- 2014/05/19 -- DAY SQL> SELECT to_char(sysdate,'day') FROM dual; TO_CHAR(SYSDATE,'DAY') ---------------------- 월요일 -- Q SQL> SELECT to_char(sysdate, 'Q') FROM dual; TO_CHAR(SYSDATE,'Q') -------------------- 2 TIME 타입 형식요소예제결과설명 9

FF[1..9] HH:MI:SS.FF3 03:49:14.588 HH HH12 HH:MI:SS HH12:MI:SS 03:57:47 시간표기 (1~12) HH24 HH24:MI:SS 15:58:20 시간표기 (0 ~ 23) 소수점이하자리의초를표시. FF 뒤에명시한숫자의개수만큼소수점이하자릿수가출력된다. MI HH:MI:SS 03:58:40 시간중분을표기 (0~59) SS HH:MI:SS 03:59:04 시간중초를표기 (0~59) SSSSS SSSSS 14801 자정부터현재몇초 (0~86399) TIME 타입 SQL> SELECT systime FROM dual; SYSTIME -------------------------------- 14:25:40.313192 -- HH:MI:SS.FF3 SQL> SELECT to_char(systime, 'HH:MI:SS.FF3') FROM dual; TO_CHAR(SYSTIME,'HH:MI:SS.FF3') ------------------------------- 02:25:47.194 -- 'HH24:MI:SS.FF3 SQL> SELECT to_char(systime, 'HH24:MI:SS.FF3') FROM dual; TO_CHAR(SYSTIME,'HH24:MI:SS.FF3') --------------------------------- 14:25:55.821 -- SSSSS SQL> SELECT to_char(systime, 'SSSSS') FROM dual; TO_CHAR(SYSTIME,'SSSSS') ------------------------ 51962 2.4. 의사컬럼 사용자가명시적으로선언하지않아도, Tibero 시스템이자동으로모든테이블에포함하는컬럼을말한다. 10

의사컬럼종류 ROWID ROWNUM LEVEL CONNECT_BY_ISLEAF 설명 ROWID 는전체데이터베이스내의하나의로우를유일하게참조하는식별자 ROWID 는그로우의디스크상의물리적인위치를가리키고있으며, 그로우가삭제될때까지변화되지않는다. SELECT 문장의실행결과로우들에대하여순서대로번호를부여한것이다. 질의결과반환되는첫번째로우는 ROWNUM=1 이며, 두번째로우는 ROWNUM=2 값을갖는다. 티베로에서질의를수행하여결과로우를생성하여반환하기직전에그로우에 ROWNUM 을할당한다. 계층질의를실행한결과로우들에대하여트리내의계층을출력하기위한컬럼타입이다. 최상위로우의 LEVEL 값은 1 이며, 하위로우로내려가며 1 씩증가한다. 현재 row 가 CONNECT BY 조건에의해정의된 tree 의 leaf 이면 1 을반환하고그렇지않을경우에는 0 을반환한다. ( 이정보는해당 row 가 hierarchy 를더보여주기위해확장될수있는지를의미 ) 2.4.1. ROWID ROWID 구조 Segment# Data File# Data Block# Row# 4byte 2byte 4byte 2byte 전체데이터베이스내의하나의로우를유일하게참조하는식별자이다. ROWID 는그로우의디스크의물리적인위치를가리키고있으며, 그로우가삭제될때까지변화되지않는다. ROWID 값을표현하기위한포맷으로는 BASE64 인코딩을이용한다. BASE64 인코딩은 6bit에포함된숫자를 8bit 문자로나타내는방식으로, 0 ~ 63까지의숫자를 A ~ Z, a ~ z, 0 ~ 9, +, / 로대치한다. ROWID 인코딩 (BASE64 변환 ) Segment# Data File# Data Block# Row# 6byte 3byte 6byte 3byte ROWID 를 BASE64 인코딩으로변환하면 Segment#, Data File#, Data Block#, Row# 가각각 6, 3, 6, 3byte 되고, 'SSSSSSFFFBBBBBBRRR' 의형태를갖는다. 예를들어, Segment# = 100, Data File# = 20, Data Block# = 250, Row# = 0 인 ROWID 는 'AAAABkAAUAAAAD6AAA' 로나타낸다. ROWID 의조회결과 SQL> SELECT rowid, emp_no FROM employee; ROWID EMP_NO ------------------ -------- AAAAbNAAAAAABoSAAA 20081120 AAAAbNAAAAAABoSAAB 20083311 AAAAbNAAAAAABoSAAC 20083313 11

3 rows selected. 2.4.2. ROWNUM SELECT 문장의실행결과로나타나는로우에대하여순서대로번호를부여한다. ROWNUM 이할당되는순서 1 질의를수행한다. 2 질의결과로로우가생성된다. 3 로우를반환하기직전에그로우에 ROWNUM 이할당된다. Tibero RDBMS 는내부적으로 ROWNUM 카운터를가지고있으며, 카운터값을질의결과의로우에할당한다. 4 ROWNUM 을할당받은로우에 ROWNUM 에대한조건식을적용한다. 5 조건식을만족하면할당된 ROWNUM 이확정되고, 내부의 ROWNUM 카운터의값이 1로증가한다. 6 조건식을만족하지않으면그로우는버려지고, 내부의 ROWNUM 카운터의값은증가하지않는다. ROWNUM 의조회결과 SQL> SELECT rownum, emp_no FROM employee; ROWNUM EMP_NO ---------- -------- 1 20081120 2 20083311 3 20083313 3 rows selected. 아래의 SQL 문장은 10 개의로우만을반환하는예제이다. FROM EMP WHERE ROWNUM <= 10; 아래의 SQL 문장은실행할때마다다른결과를얻는다. FROM EMP WHERE ROWNUM <= 10 ORDER BY EMPNO; - 위의질의를다음과같이변환하면 ORDER BY 절을먼저처리하게되므로항상같은결과를얻을수있다. FROM ( FROM EMP ORDER BY EMPNO) WHERE ROWNUM <= 10; 아래의 SQL 문장은하나의로우도반환하지않는다. FROM EMP WHERE ROWNUM > 1; - 이유는 ROWNUM 값이확정되기전에 ROWNUM 에대한조건식이수행되기때문이다. 위의 SELECT 문의결과는첫번째로우가 ROWNUM = 1이기때문에조건식을만족하지않는다. 조건식을만족하지않으면 ROWNUM 카운터의값은변하지않는다. 따라서두번째결과로우도 ROWNUM = 1이므로반환되지않는다. 12

2.4.3. LEVEL - 계층질의를실행한결과에각로우의트리내계층을출력하기위한컬럼타입이다. 최상위로우의 LEVEL 값은 1 이며, 하위로우로갈수록 1 씩증가한다. 2.4.4. CONNECT_BY_ISLEAF - 현재로우가 CONNECT BY 조건에의해정의된트리 (Tree) 의리프 (Leaf) 이면 1 을반환하고그렇지않을경우에는 0 을 반환한다. 이정보는해당로우가계층구조 (Hierarchy) 를보여주기위해확장될수있는지없는지를나타낸다. 2.5. NULL - 한로우에서어떤컬럼에값이없을때그컬럼을 NULL이라고하거나 NULL 값을가진다고한다. - NULL은 NOT NULL 제약과 PRIMARY KEY 제약이걸리지않은모든데이터타입의칼럼에포함될수있다. - 실제값을모르거나아무런의미없는값이필요할때사용할수있다. NULL과 0은다르기때문에 NULL을 0으로나타내면안된다. 다만문자타입의컬럼에빈문자열 ('') 이들어가면 NULL로처리된다. NULL 을포함하는모든연산의결과도 NULL 이다. NULL + 1 = NULL 모든상수함수는함수의파라미터가 NULL 일경우반환값은 NULL 이다. DATA = {1000, 500, NULL, NULL, 1500} AVG(DATA) = (1000 + 500 + 1500) /3 = 1000 NULL 의비교조건은 IS NULL 과 IS NOT NULL 만가능하다. DECODE 함수를통해 NULL 이서로비교되었으며, 그결 과로서로같다는의미인 '1' 이반환되었음을알수있다. SQL> SELECT DECODE(NULL, NULL, 1) FROM DUAL; DECODE(NULL,NULL,1) ------------------- 1 2.6. 주석 - SQL 문장과스키마객체에는주석을삽입할수있다. - 시작기호 (/*) 로주석의시작을나타내고마침기호 (*/) 로주석을끝낸다. 주석의내용을여러줄에걸쳐삽입할수있다. 시작기호 (/*) 와마침기호 (*/) 를내용과구분하기위해공백이나줄바꿈을사용할필요는없다. - '--' 로주석의시작을나타내고바로뒤에주석의내용을적는다. - 스키마객체에도주석을삽입할수있다. 즉 COMMENT 명령을사용하여스키마객체인테이블, 뷰, 컬럼에주석을삽입할수있다. 스키마객체에삽입된주석은데이터사전에저장된다. 13

SQL 문장에주석을삽입한예제 SELECT emp_id, emp_name, e.dept_id /* 부서가총무과인직원의명단을출력한다. */ /* 테이블 */ FROM emp e, dept d WHERE e.dept_id = d.dept_id AND d.dept_name = ' 총무과 ' AND e.status!= 1; -- 퇴사한사람제외 2.7. 힌트 - 일종의지시문이다. SQL 문장에힌트를추가하여 Tibero 의질의최적화기 (Optimizer) 에특정행동을지시하거나 질의최적화기의실행계획을변경한다. 힌트를사용할때주의할점 - 힌트는반드시 DELETE, INSERT, SELECT, UPDATE 절뒤에만올수있다. - '+' 기호는반드시주석구분자 ('/*' 또는 '--') 바로뒤에공백없이붙여써야한다. - 힌트와 '+' 기호사이에공백은있어도되고, 없어도된다. - 문법에맞지않는힌트는주석으로취급되며, 에러는발생하지않는다. 힌트사용한예제 (DELETE INSERT SELECT UPDATE) /*+ hint [hint]... */ 또는 (DELETE INSERT SELECT UPDATE) --+ hint [hint]...from emp e, dept d 2.7.1. 힌트의종류 구성요소힌트설명 질의변형 NO_MERGE 질의변형기에게특정뷰에대한뷰병합 (View Merging) 을 하지않도록지시한다. UNNEST NO_UNNEST 질의변형기에게특정부질의를언네스팅 (Unnesting) 하도록지시한다. 질의변형기에게특정부질의에대해언네스팅을수행하지않도록지시한다. 최적화방법 ALL_ROWS 전체결과에대한처리량이가장많도록처리과정의최적 화를선택한다. FIRST_ROWS 결과를가장빠르게보여줄수있도록결과표시의최적화 를선택한다. 접근방법 FULL 전체테이블을스캔하도록지시한다. INDEX 명시한인덱스를사용한인덱스스캔을하도록지시한다. 14

NO_INDEX INDEX_ASC INDEX_DESC INDEX_FFS NO_INDEX_FFS 명시한인덱스를사용한인덱스스캔을하지않도록지시한다. 명시한인덱스를사용한인덱스스캔을오름차순으로하도록지시한다. 명시한인덱스를사용한인덱스스캔을내림차순으로하도록지시한다. 명시한인덱스를사용한인덱스를사용해빠른전체인덱스스캔 (Fast Full Index Scan) 을하도록지시한다. 명시한인덱스를사용한빠른전체인덱스스캔을하지않도록지시한다. 조인순서 LEADING 먼저조인되어야할테이블의집합을명시한다. ORDERED 테이블을 FROM 절에명시된순서대로조인하도록지시한 다. 조인방법 USE_NL 중첩루프조인을사용하도록지시한다. NO_USE_NL 중첩루프조인을사용하지않도록지시한다. USE_NL_WITH_INDEX 명시한인덱스와두테이블에대한조인조건을이용해중 첩루프조인을사용하도록지시한다. USE_MERGE 합병조인을사용하도록지시한다. NO_USE_MERGE 합병조인을사용하지않도록지시한다. USE_HASH 해시조인을사용하도록지시한다. NO_USE_HASH 해시조인을사용하지않도록지시한다. 병렬처리 PARALLEL 지정한개수의스레드를사용해질의의수행을병렬로진행하도록지시한다. NO_PARALLEL 질의의수행을병렬로진행하지않도록지시한다. PQ_DISTRIBUTE 조인을포함한질의의병렬처리에서로우의분산방법을 지시한다. 실체화뷰 REWRITE 비용의비교없이실체화뷰 (Materialized View) 를사용하여질의의다시쓰기를지시한다. NO_REWRITE 질의의다시쓰기를하지않도록지시한다. 기타 APPEND DML 문장에서직접데이터파일에추가하는삽입방법즉 Direct-Path 방식을수행하도록지시한다. NOAPPEND DML 문장에서 Direct-Path 방식을수행하지않도록지시한 다. 15

2.7.2. 접근방법 NO_MERGE - 질의변형기 (Query Transformer) 가특정뷰에대해뷰병합을하지않도록지시하는힌트이다. - Tibero 에서는뷰병합이디폴트로수행되며, 뷰가병합이가능할경우상위의질의블록과결합해하나의질의블록을형성한다. NO_MERGE 힌트를사용하면이렇게디폴트로수행되는뷰의병합을막을수있다. NO_MERGE 힌트를사용한예제 FROM T1, (SELECT /*+ NO_MERGE */ * FROM T2, T3 WHERE T2.A = T3.B) V WHERE T1.C = V.D - 위의예제처럼 NO_MERGE 힌트는병합되기를원하지않는뷰의질의블록에명시한다. 힌트가없었다면뷰가병합 되어질의최적화기에서테이블 T1, T2, T3 에대한조인순서와조인방법을고려하게되지만, 위와같이힌트가있을 경우는뷰가병합되지못하기때문에 T2 와 T3 가먼저조인되고, 그이후에 T1 이조인된다. UNNEST - 질의변형기가특정부질의 (Subquery) 를언네스팅하도록지시하는힌트이다. - Tibero 는부질의언네스팅을디폴트로수행하지만, 특정쿼리만언네스팅을하려면초기화파라미터에서언네스팅을해제하면된다. 그러면 UNNEST 힌트를이용할수있다. UNNEST 힌트는부질의블록에명시한다. NO_UNNEST - NO_UNNEST 는질의변형기가특정부질의에대해언네스팅을수행하지않도록지시하는힌트이다. - Tibero 는부질의언네스팅을디폴트로수행하며언네스팅이가능한경우부질의를조인으로변환한다. 이때 NO_UNNEST 힌트를사용해서언네스팅을막을수있다. NO_UNNEST 힌트는부질의블록에명시한다. 2.7.3. 최적화방법 최적화방법이적용된힌트를사용하여처리과정과결과표시를최적화할수있다. 만약최적화방법이적용된힌트가 사용된질의가있다면해당질의에대해서는통계정보와초기화파라미터의최적화방법 (OPTIMIZER MODE) 의값이 없는것처럼처리된다. ALL_ROWS - 최소한의리소스를사용하여전체결과에대한처리량이가장많도록처리과정의최적화방법을선택하는힌트 이다. FIRST_ROWS - FIRST_ROWS 는첫로우부터파라미터로입력된번호의로우까지가장빠르게보여줄수있도록결과표시의 최적화방법을선택하는힌트이다 2.7.4. 접근방법 - 접근방법이적용된힌트는질의최적화기가특정접근방법의사용이가능한경우그방법을사용하도록명시한다. 만일힌트에서명시한방법을사용할수없는경우에는질의최적화기는그힌트를무시한다. 16

- 힌트에명시하는테이블명은 SQL 문에서사용하는이름과동일해야한다. 즉, 테이블이름에대한별칭을사용하였 다면, 테이블이름대신에별칭을사용하여야한다. - SQL 문에서테이블이름에스키마이름을포함하여명시하였더라도힌트에서는테이블이름만을명시하여야한다. FULL - FULL 은명시한테이블을스캔할때전체테이블을스캔하도록지시하는힌트이다. WHERE 절에명시된조건식에 맞는인덱스가있더라도전체테이블스캔을사용한다. INDEX - INDEX 는명시한테이블을스캔할때명시한인덱스를사용하여인덱스스캔을하도록지시하는힌트이다. NO_INDEX - NO_INDEX 는명시한테이블을스캔할때명시한인덱스를사용하는인덱스스캔을하지않도록지시하는힌트이다. NO_INDEX 힌트와 INDEX 또는 INDEX_ASC, INDEX_DESC 힌트가동일한인덱스를명시한다면질의최적화기는이두힌트를모두무시한다. INDEX_ASC - INDEX_ASC 는명시한테이블을스캔할때명시한인덱스를사용하여인덱스스캔을하도록지시하는힌트이다. 만일인덱스범위스캔을사용하는경우에는인덱스를오름차순으로스캔하도록한다. 현재 Tibero 의인덱스스캔의기본동작이오름차순이기때문에 INDEX_ASC 는 INDEX 와동일한작업을수행한다. 분할된인덱스의경우분할된각영역내에서오름차순으로스캔한다. INDEX_DESC - INDEX_DESC 는명시한테이블을스캔할때명시한인덱스를사용하여인덱스스캔을하도록지시하는힌트이다. 만일인덱스범위스캔을사용하는경우에는인덱스를내림차순으로스캔하도록한다. 분할된인덱스의경우분할된각영역내에서내림차순으로스캔한다. INDEX_FFS - INDEX_FFS 는명시한테이블에대해명시한인덱스를사용하여빠른전체인덱스스캔 (Fast Full Index Scan) 을사 용하도록지시하는힌트이다. NO_INDEX_FFS - NO_INDEX_FFS 는명시한테이블에대해명시한인덱스를사용하는빠른전체인덱스스캔을사용하지않도록 지시하는힌트이다. 2.7.5. 조인순서 - LEADING, ORDERED 는조인순서를결정하는힌트이다. - LEADING 힌트가 ORDERED 보다질의최적화기를선택할수있는폭이넓어서 LEADING 을사용하는것이좋다. LEADING - LEADING 은조인에서먼저조인되어야할테이블의집합을명시하는힌트이다. 17

- LEADING 힌트가먼저조인될수없는테이블을포함하는경우무시된다..- LEADING 힌트끼리충돌하는경우 LEADING, ORDERED 힌트가모두무시된다. 만일 ORDERED 힌트가사용되는 경우에는 LEADING 힌트는모두무시된다. ORDERED - ORDERED는테이블을 FROM 절에명시된순서대로조인하도록지시하는힌트이다. - 질의최적화기는조인의결과집합의크기에대한정보를추가로알고있다. - 사용자가그정보를통해질의최적화기의조인순서를명확히알고있을경우에만 ORDERED 힌트를사용하는것이좋다. 2.7.6. 조인방법 - 조인방법이적용된힌트는한테이블에대해서만조인방법을지시한다. - 조인방법이적용된힌트는명시한테이블이조인의내부테이블로사용될경우에만참조된다. 명시한테이블을 외부테이블로사용하는경우에는조인방법이적용된힌트는무시된다. USE_NL - USE_NL 은명시한테이블을다른테이블과조인하는경우중첩루프조인을사용하도록지시하는힌트이다. NO_USE_NL - NO_USE_NL 은명시한테이블을다른테이블과조인하는경우중첩루프조인을사용하지않도록지시하는힌트이다. 하지만, 특수한경우에는이힌트가주어졌더라도질의최적화기에서중첩루프조인을사용하는플랜을생성할수있다. USE_NL_WITH_INDEX - USE_NL_WITH_INDEX 는명시한테이블을다른테이블과조인하는경우중첩루프조인을사용하도록지시하는힌트이다. 이때명시한테이블에대한접근은명시한인덱스와두테이블에대한조인조건을이용하여이루어져야한다. 만일인덱스를사용할수없는경우이면힌트는무시된다. USE_MERGE - USE_MERGE 는명시한테이블을다른테이블과조인하는경우합병조인을사용하도록지시하는힌트이다. NO_USE_MERGE - NO_USE_MERGE 는명시한테이블을다른테이블과조인하는경우합병조인을사용하지않도록지시하는힌트 이다. USE_HASH - USE_HASH 는명시한테이블을다른테이블과조인하는경우해시조인을사용하도록지시하는힌트이다. NO_USE_HASH - NO_USE_HASH 는명시한테이블을다른테이블과조인하는경우해시조인을사용하지않도록지시하는힌트 이다. 18

2.7.7. 병렬처리 PARALLEL - PARALLEL 은지정한개수의스레드를사용해질의의수행을병렬로진행하도록지시하는힌트이다. NO_PARALLEL - NO_PARALLEL 은질의의수행을병렬로진행하지않도록지시하는힌트이다. PQ_DISTRIBUTE - PQ_DISTRIBUTE 는조인을포함한질의의병렬처리에서조인될로우의분산방법을지시하는힌트이다. - 분산방법으로는 HASH-HASH, BROADCAST-NONE, NONE-BROADCAST, NONE-NONE 이있으며특정한분산방법을선택함으로써병렬처리에서조인의성능을향상시킬수있다. 2.7.8. 실체화뷰 REWRITE - REWRITE 는해당질의블록에서비용의비교없이실체화뷰를사용하여질의의다시쓰기를하도록지시하는힌트이다. 따라서최종으로는 REWRITE 힌트가사용된질의블록만다시쓰기를한결과와모든블록에서다시쓰기를한결과의비용을비교해서더좋은쪽을질의최적화기가선택하게된다. 그리고실체화뷰의목록이명시된경우에는목록에있는실체화뷰만사용하여질의의다시쓰기를시도한다. NO_REWRITE - NO_REWRITE 는해당질의블록에서는질의의다시쓰기를하지않도록지시하는힌트이다. 2.7.9. 기타 APPEND - APPEND 는 DML 문장에서직접데이터파일에추가하는삽입방법즉 Direct-Path 방식을수행하도록지시하는힌트이다. Direct-Path 방식은일반적인삽입방법과달리항상새로운데이터블록을할당받아서데이터삽입을수행하며, 버퍼캐시를이용하지않고직접데이터파일을추가하기때문에성능향상에많은이점이있다. NOAPPEND - NOAPPEND 는 DML 문장에서 Direct-Path 방식을수행하지않도록지시하는힌트이다. 2.8. 스키마객체 데이터베이스는여러객체로구성된다. 각객체는 ' 데이터베이스 > 사용자 > 스키마 > 스키마객체 ' 의순으로포함 관계를갖는다. 구분 테이블 (Table) 설명 관계형데이터베이스의기본저장단위이다. 다른모든스키마객체는테이블을 중심으로정의된다. 19

인덱스 (Index) 뷰 (View) 테이블과별도의저장공간을이용하여그테이블의특정컬럼을빠르게검색할수있도록해주는데이터구조이다. SQL 문장에이름을붙인것으로, 빈번히수행되는질의의결과를테이블형태로이용할수있도록정의한것이다. 시퀀스 (Sequence) 유일한연속적인값을생성해낼수있는스키마객체이다. 동의어 (Synonym) 특정스키마객체에정의하는일종의별칭 (Alias) 이다. 스키마객체예제 -- 테이블생성 SQL> create table test ( deptno number(2), dname varchar2(14), loc varchar2(13), create_date date default sysdate ); Table 'TEST' created. SQL> describe test; COLUMN_NAME TYPE CONSTRAINT ---------------------------------------- ------------------ ------------------- DEPTNO NUMBER(2) DNAME VARCHAR(14) LOC VARCHAR(13) CREATE_DATE DATE -- Subquery 이용테이블생성 SQL> create table copy_emp as select * from employee; Table 'COPY_EMP' created. -- Subquery 이용테이블생성 ( 구조만복제할경우 ) SQL> create table copy_emp1 as select * from employee where 1=2; Table 'COPY_EMP1' created. -- 인덱스생성 SQL> create index test_deptno_idx on test(deptno); Index 'TEST_DEPTNO_IDX' created. -- 뷰생성 20

SQL> create view employee_2008 as select * from employee where emp_no like '2008%'; View 'EMPLOYEE_2008' created. SQL> select * from employee_2008; EMP_NO EMP_NAME HIREDATE SALARY BONUS DEPT_CD MANAGER -------- ----------------- ------------ ---------- ---------- ------- -------- 20081120 Lolly 2008/06/20 3000 2000 1000 20032813 20083311 Angel 2008/07/13 5000 2500 1000 20032813 3 rows selected. -- Synonym 생성 SQL> Create public synonym syn_emp for sys.employee; Synonym 'SYN_EMP' created. SQL> select * from syn_emp; EMP_NO EMP_NAME HIREDATE SALARY BONUS DEPT_CD MANAGER -------- ----------------- ------------ ---------- ---------- ------- -------- 20081120 Lolly 2008/06/20 3000 2000 1000 20032813 20083311 Angel 2008/07/13 5000 2500 1000 20032813 3 rows selected. 3. SQL 연산자 산술연산자 연산자종류설명 + - * / 덧셈뺄셈곱셈나누기 문자열연산자 문자열하나로연결 집합연산자 UNION UNION ALL INTERSECT MINUS 단일행연산자 ( 결과값이 1 개일경우 ) = 같을때 각결과의합 ( 중복허용안함 ) ( 합집합 ) 각결과의합 ( 중복허용 ) 각결과의중복되는부분만출력 ( 교집합 ) 첫번째결과에서두번째결과를뺌 ( 차집합 ) 비교연산자!= 또는 <> 같지않을때 < 왼쪽값이오른쪽값보다작을때 > 왼쪽값이오른쪽값보다클때 21

<= 왼쪽값이오른쪽값보다같거나작을때 >= 왼쪽값이오른쪽값보다같거나클때 <> 같지않을때 복수행연산자 ( 결과값이 2개이상일경우 ) 기타연산자 BETWEEN IN ALL ANY EXISTS LIKE IS NULL 왼쪽의값이오른쪽의두값사이에존재하는지비교결과값과일치하는값이있는지비교 Return된목록의각각의값과비교 Return된목록의모든값과비교결과값으로적어도 1개의행이있는지비교 LIKE 다음비교할조건에문자또는날짜등의패턴을비교해당컬럼의값이널인지아닌지만을비교 산술연산자 SQL> SELECT employee_id, first_name last_name employee_name, salary, salary + (salary * 0.01) as salary_01 FROM employees; EMPLOYEE_ID EMPLOYEE_NAME SALARY SALARY_01 ----------- ----------------- ---------- ---------- 100 StevenKing 24000 24240 101 NeenaKochhar 17000 17170 102 LexDe Haan 17000 17170 103 AlexanderHunold 9000 9090 104 BruceErnst 6000 6060 6 rows selected. 문자열연산자 SQL> SELECT 'RDBMS ' 'Tibero 5' FROM dual; 'RDBMS' 'TIBERO5' ------------------ RDBMS Tibero 5 비교연산자 SQL> select * from employees where salary = '2600'; EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID ------------ ----------- --------- ------ ------------ ---------- ------- ----------- 143 Randall Matos RMATOS 650.121.2874 1998/03/15 ST_CLERK 2600 124 50 SQL> SELECT employee_id, first_name last_name employee_name, hire_date, salary, 22

department_id FROM employees WHERE salary IN (SELECT MIN(salary) FROM employees GROUP BY department_id); EMPLOYEE_ID EMPLOYEE_NAME HIRE_DATE SALARY DEPARTMENT_ID ----------- -------------- ------------ ----------- ------------- 101 NeenaKochhar 1989/09/21 17000 90 102 LexDe Haan 1993/01/13 17000 90 104 BruceErnst 1991/05/21 6000 60 107 DianaLorentz 1999/02/07 4200 60 144 PeterVargas 1998/07/09 2500 50 176 JonathonTaylor 1998/03/24 8600 80 178 KimberelyGrant 1999/05/24 7000 NULL 200 JenniferWhalen 1987/09/17 4400 10 EMPLOYEE_ID EMPLOYEE_NAME HIRE_DATE SALARY DEPARTMENT_ID ----------- --------------------------------------------- --------------------------- 202 PatFay 1997/08/17 6000 20 206 WilliamGietz 1994/06/07 8300 110 10 rows selected. SQL> SELECT employee_id, first_name last_name employee_name, hire_date, salary, manager_id FROM employees WHERE salary > '15000' AND manager_id = '100'; EMPLOYEE_ID EMPLOYEE_NAME HIRE_DATE SALARY MANAGER_ID ----------- --------------- -------------- -------------- ------------- 101 NeenaKochhar 1989/09/21 17000 100 102 LexDe Haan 1993/01/13 17000 100 2 rows selected. 집합연산자 SQL> SELECT department_id FROM departments UNION SELECT department_id FROM employees; DEPARTMENT_ID ------------- 10 20 50 60 80 90 110 190 9 rows selected. SQL> SELECT department_id FROM departments UNION All 23

SELECT department_id FROM employees; DEPARTMENT_ID ------------- 10 20 50 60 80 90 110 190 90 90 90 60 60 60 50 50 50 50 50 80 80 80 DEPARTMENT_ID ------------- 10 20 20 110 110 28 rows selected. SQL> SELECT department_id FROM departments INTERSECT SELECT department_id FROM employees; DEPARTMENT_ID ------------- 10 20 50 60 80 90 110 7 rows selected. 기타연산자 SQL> SELECT employee_id, first_name last_name employee_name, email, hire_date, salary FROM employees WHERE email LIKE 'E%'; 24

EMPLOYEE_ID EMPLOYEE_NAME EMAIL HIRE_DATE SALARY ----------- -------------- --------------- ---------------- --------------- 174 EllenAbel EABEL 1996/05/11 11000 149 EleniZlotkey EZLOTKEY 2000/01/29 10500 2 rows selected. SQL> SELECT employee_id, first_name last_name employee_name, hire_date, salary, manager_id FROM employees WHERE manager_id IS NULL; EMPLOYEE_ID EMPLOYEE_NAME HIRE_DATE SALARY MANAGER_ID ----------- ------------- ---------- --------- -------------- 100 StevenKing 1987/07/17 24000 4. SQL 함수 제공되는함수는주어진인수를처리하여결과값을반환하는기능을수행하여, SELECT 문을간결하게만들어 Data 조작을쉽고간결하게만든다. 함수의종류는다음과같다. 1) 단일행함수 : 행별로함수가적용되어하나의결과를 RETURN 하는함수 ( 문자함수, 숫자함수, 날짜함수, 변환함수, 기타함수 ) 2) 다중행함수 : 여러행또는테이블전체에대해함수가적용되어하나의결과를 RETURN 하는함수 (Group 함수 ) 4.1. 단일행함수종류 종류명령어설명 문자함수 LOWER/UPPER INITCAP CONCAT INSTR LENGTH REPLACE LPAD/RPAD SUBSTR TRIM 문자를모두소문자로변경 / 대문자로변경첫번째글자만대문자로변경두문자열을결합한값을반환문자내의특정스트링의위치를구함문자열의길이를구함특정문자열을대신왼쪽 / 오른쪽문자자리채움문자열중특정문자또는문자열의일부분을선택왼쪽또는오른쪽문자를자름 25

숫자함수날짜함수변환함수기타함수 ABS(n) CEIL(n) MOD(m,n) ROUND(n,[m]) TRUNC(n,[m]) POWER SQRT SIGN CHR MONTHS_BETWEEN ADD_MONTHS NEXT_DAY LAST_DAY ROUND TRUNC TO_CHAR TO_DATE TO_NUMBER TO_TIME TO_TIMESTAMP NVL NVL2 NULLIF COALESCE DECODE CASE 절대값 주어진값보다크지만가장근접하는최소값을구하는함수 m 을 n 으로나누어남는값을구하는함수임 n 값의반올림을하는함수로 m 은소수점아래자릿수를표기함 n 값을버림하는함수로 m 은소수점아래자릿수를표기함 거듭제곱 제곱근 양수, 음수, 0 인지를구분 ASCII 값에해당하는문자를구함 두날짜사이의월수를계산 날짜에월을더함 명시된날짜로부터다음요일에대한날짜 월의마지막날을계산 날짜를반올림 날짜를절삭 주어진 date type, number type 의값을형식에따라문자열로변환 주어진 string type 값을 format 에따라 date type 값으로변환 주어진 string type 값을숫자형식으로변환 주어진 string 을형식에따라시간값으로변환 주어진 string 을형식에따라 timestamp 타입값으로변환 NULL 값을어떤특정한값 ( 실제값 ) 으로변환하는데사용 값이 NULL 인경우와 NULL 이아닌경우리턴해주는값을다르게해주는함수 두개의값을비교하여값이값으면 NULL 을리턴하고서로다른값을가지면첫번째값을리턴하는함수 나열된값을순차적으로체크하여 NULL 이아닌값을리턴하는함수 CASE 나 IF-THEN-ELSE-END IF 문장의조건적조회를하는함수 DECODE 함수와동일하나 DECODE 에서지원하지않는비교연산자를사용할수있는함수 문자함수 -- LOWER SQL> SELECT LOWER('TIBERO') FROM dual; LOWER('TIBERO') --------------- tibero 26

-- UPPER SQL> SELECT UPPER('tibero') FROM dual; UPPER('TIBERO') --------------- TIBERO -- CONCAT SQL> SELECT CONCAT('RDBMS ', 'Tibero 5') FROM dual; CONCAT('RDBMS','TIBERO5') ------------------------- RDBMS Tibero 5 -- INSTR SQL> SELECT INSTR('World best! Tibero','e') FROM dual; INSTR('WORLDBEST!TIBERO','E') ----------------------------- 8 -- LENGTH SQL> SELECT LENGTH('Tibero') FROM dual; LENGTH('TIBERO') ---------------- 6 -- REPLACE SQL> SELECT REPLACE('tibero', 't','r') FROM dual; REPLACE('TIBERO','T','R') ------------------------- ribero -- LPAD SQL> SELECT LPAD('Tibero',20,'*') FROM dual; LPAD('TIBERO',20,'*') ---------------------------------------- 27

**************Tibero 숫자함수 -- ABS SQL> SELECT ABS(-100) FROM dual; ABS(-100) ---------- 100 -- CEIL SQL> SELECT CEIL(11.1) FROM dual; CEIL(11.1) ---------- 12 -- MOD SQL> SELECT MOD(10,4) FROM dual; MOD(10,4) ---------- 2 -- ROUND SQL> SELECT ROUND(11.985, 2) FROM dual; ROUND(11.985,2) --------------- 11.99 -- TRUNC SQL> SELECT TRUNC(11.985, 1) FROM dual; TRUNC(11.985,1) --------------- 11.9 28

-- POWER SQL> SELECT POWER(4,2) FROM dual; POWER(4,2) ---------- 16 -- SQRT SQL> SELECT SQRT(8) FROM dual; SQRT(8) ---------- 2.82842712 -- SIGN SQL> SELECT SIGN(-19) FROM dual; SIGN(-19) ---------- -1 -- CHR SQL> SELECT CHR(65) FROM dual; CHR(65) ------- A 날짜함수 -- ADD_MONTHS SQL> SELECT ADD_MONTHS(TO_DATE('2014/05/20','YYYY/MM/DD'),1) "Add_months" FROM dual; Add_months -------------------------------- 2014/06/20 -- ROUND SQL> SELECT ROUND(TO_DATE('2014/05/20','YYYY/MM/DD'),'YEAR') "Round" FROM dual; Round -------------------------------- 2014/01/01 29

-- TRUNC SQL> SELECT TRUNC(TO_DATE('2014/05/20','YYYY/MM/DD'),'YEAR') "Trunc" FROM dual; Round -------------------------------- 2014/01/01 -- SYSDATE SQL> SELECT SYSDATE FROM dual; SYSDATE -------------------------------- 2014/05/20 -- LAST_DAY SQL> SELECT LAST_DAY(SYSDATE) FROM dual; LAST_DAY(SYSDATE) -------------------------------- 2014/05/31 -- MONTHS_BETWEEN SQL> SELECT MONTHS_BETWEEN(LAST_DAY(SYSDATE),SYSDATE) FROM dual; MONTHS_BETWEEN(LAST_DAY(SYSDATE),SYSDATE) -----------------------------------------.35483871 변환함수 -- TO_CHAR SQL> SELECT TO_CHAR(sysdate) FROM dual; TO_CHAR(SYSDATE) -------------------------------- 2014/05/20 -- TO_DATE 30

SQL> SELECT TO_DATE('20/05/2014','DD/MM/YYYY') FROM dual; TO_DATE('20/05/2014','DD/MM/YYYY') ---------------------------------- 2014/05/20 -- TO_NUMBER SQL> SELECT TO_NUMBER('$35,000.00','$99,999.99') FROM dual; TO_NUMBER('$35,000.00','$99,999.99') ------------------------------------ 35000 -- TO_TIME SQL> SELECT TO_TIME('13:07:33','HH24:MI:SS') FROM dual; TO_TIME('13:07:33','HH24:MI:SS') -------------------------------- 13:07:33.000000 -- TO_TIMESTAMP SQL> SELECT TO_TIMESTAMP('13:07:15','HH24:MI:SS.FF') FROM dual; TO_TIMESTAMP('13:07:15','HH24:MI:SS.FF') ----------------------------------------------------------------- 2014/05/01 13:07:15.000000 기타함수 -- NVL2 SQL> SELECT NVL2(DUMMY, 'NOT NULL', 'NULL') FROM DUAL; NVL2(DUMMY, 'NOT NULL', 'NULL') ------------------------------- NOT NULL -- COALESCE SQL> SELECT COALESCE(NULL, 'A', 'B') FROM DUAL; COALESCE(NULL, 'A', 'B') ------------------------ A 31

-- DECODE SQL> SELECT DECODE('1', 1, 'Male', 2, 'Female') FROM DUAL; DECODE('1',1,'MALE',2,'FEMALE') ------------------------------- Male 4.2. 그룹행함수종류 종류명령어설명 그룹함수 분석함수 AVG COUNT MAX MIN STDDEV SUM VARIANCE ROLLUP CUBE FIRST_VALUE COUNT SUM ROW_NUMBER RANK DENSE_RANK LAG LEAD RATIO_TO_REPORT 그룹내의모든로우에대한평균 해당그룹의행의개수를반환 그룹내의모든로우에대한최대값을반환 그룹내의모든로우에대한최소값을반환 그룹내의표준편차 그룹내의로우에대한합계 그룹내의분산 주어진그룹핑조건에따라 ROW 들을그룹화하고각그룹에대해부분합을제공 Group BY 절에기술된조건에따라모든가능한그룹핑조합에대한결과를출력 정렬된로우에서첫번째값을반환 OVER analytic_clause 를사용해함수를분석함수로수행 OVER analytic_clause 를사용해함수를분석함수로수행 그룹내의로우를정렬한다음각로우에유일한번호를부여하여값비교 그룹내의로우를정렬한다음그룹내의각로우에대한순위를돌려주는함수 그룹내의로우를정렬한다음그룹내의각로우에대한순위를돌려주는함수 그룹내의로우를정렬한다음현재로우에서 offset 갯수만큼앞서있는로우에대한접근을제공 그룹내의로우를정렬한다음현재로우에서 offset 갯수만큼뒤에나오는로우에대한접근을제공 값들의집합의합에대한집합의하나의값의비율을계산 그룹함수 -- AVG 32

SQL> SELECT AVG(SALARY) AVG FROM employee GROUP BY DEPT_CD; AVG ---------- 4666.66667 -- RANK SQL> SELECT DEPT_CD, RANK(3000) WITHIN GROUP (ORDER BY SALARY) AS RANK FROM employee GROUP BY DEPT_CD; DEPT_CD RANK ------- ---------- 1000 1 -- COUNT - 급여가 3000 인사람의상위급여순위를나타낸다. SQL> SELECT TO_CHAR(hiredate,'YYYY'), COUNT(emp_no), MAX(salary), MIN(salary) FROM employee GROUP BY TO_CHAR(hiredate,'YYYY'); TO_CHAR(HIREDATE,'YYYY') COUNT(EMP_NO) MAX(SALARY) MIN(SALARY) ------------------------ ------------- ----------- ----------- 2008 3 6000 3000 분석함수 -- FIRST_VALUE SQL> SELECT EMP_NAME, SALARY, FIRST_VALUE(EMP_NAME) OVER (PARTITION BY DEPT_CD ORDER BY SALARY ASC ROWS UNBOUNDED PRECEDING) AS LOWEST_SAL FROM employee; EMP_NAME SALARY LOWEST_SAL -------------------- ---------- -------------------- Lolly 3000 Lolly Angel 5000 Lolly Judy 6000 Lolly 3 rows selected. -- COUNT SQL> SELECT EMP_NAME, SALARY, COUNT(*) OVER (ORDER BY SALARY RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING) AS W_COUNT FROM employee; EMP_NAME SALARY W_COUNT -------------------- ---------- ---------- Lolly 3000 1 33

Angel 5000 2 Judy 6000 2 3 rows selected. -- SUM SQL> SELECT EMP_NAME, SALARY, SUM(SALARY) OVER (ORDER BY SALARY RANGE UNBOUNDED PRECEDING) SUM_SALARY FROM employee; EMP_NAME SALARY SUM_SALARY -------------------- ---------- ---------- Lolly 3000 3000 Angel 5000 8000 Judy 6000 14000 3 rows selected. -- ROW_NUMBER SQL> SELECT DEPT_CD, EMP_NAME, SALARY FROM ( SELECT DEPT_CD, EMP_NAME, SALARY, ROW_NUMBER() OVER (PARTITION BY DEPT_CD ORDER BY SALARY) RN FROM EMPLOYEE ) WHERE RN <= 1; DEPT_CD EMP_NAME SALARY ------- -------------------- ---------- 1000 Lolly 3000 -- RANK SQL> SELECT EMP_NAME, DEPT_CD, SALARY, RANK() OVER (PARTITION BY DEPT_CD ORDER BY SALARY) FROM employee; EMP_NAME DEPT_CD SALARY RANK -------------------- ------- ---------- ---------- Lolly 1000 3000 1 Angel 1000 5000 2 Judy 1000 6000 3 3 rows selected. -- DENSE_RANK SQL> SELECT DEPT_CD, DENSE_RANK(3000) WITHIN GROUP (ORDER BY SALARY) AS DENSE_RANK FROM EMPLOYEE GROUP BY DEPT_CD; DEPT_CD DENSE_RANK ------- ---------- 1000 1 34

5. JOIN 5.1. JOIN 유형 종류 설명 기본 JOIN 유형 CARTESIAN PRODUCT 모든가능한행들의 JOIN ( 양쪽 ROW 의개수를곱한개수 ) EQUI JOIN (=) JOIN 조건이정확히일치하는경우에사용 NON-EQUI JOIN (Between) OUTER JOIN (+) SELF JOIN Simple Join (Inner Join) Anti Join Semi Join 테이블의한컬럼의값이다른테이블의컬럼과컬럼값의범위에해당될때사용 EQUE JOIN에사용된조건에만족하지않는데이터를포함시키고자할때사용. 조인시킬값이없는테이블쪽에 (+) 를위치시킨다. 하나의테이블에서행들을 JOIN하고자할경우에사용. 테이블 Alias가꼭필요하다. 조인조건을만족하는로우만반환하는 2개이상의테이블에대한조인프리디키트의오른쪽부분에해당하는로우가없는왼쪽부분의프리디키트에해당하는로우를반환한다. 즉프리디키트의오른쪽부분을 NOT IN의부질의로실행했을때일치하지않는로우를반환한다. 프리디키트의오른쪽의다수의로우에해당하는왼쪽부분의로우를중복없이처리하는 EXIST 부질의와같은로우를반환한다. 부질의가 WHERE 절의 OR로연결되어있으면세미조인과안티조인으로변환되지않는다. ANSI 표준을따르는 JOIN 유형기본 JOIN 유형과문법상의차이가있을뿐성능상의이점은없다. CROSS JOIN NATURAL JOIN JOIN ~ USING JOIN ~ ON JOIN시잘못된조건을주었을경우데이터매치가일어나지않아모든행에 JOIN이일어나는것이다. (=CARTESIAN PRODUCT) 두테이블에서똑같은이름을가지는모든컬럼을기준으로 JOIN (=EQUI JOIN) JOIN시 EQUE JOIN이일어날컬럼을지정한다조인조건을임의로명시하거나조인에참여할컬럼을명시하고자할때사용한다. LEFT RIGHT [OUTER JOIN] INNER JOIN 에서출력되지않은결과들을출력한다. 기본 JOIN 유형 35

-- CARTESIAN PRODUCT SQL> SELECT employee_id, first_name ' ' last_name employee_name, d.department_id, d.department_name FROM employees, departments d order by employee_id; EMPLOYEE_ID EMPLOYEE_NAME DEPARTMENT_ID DEPARTMENT_NAME ----------- -------------- -------------- ------------------ 100 Steven King 10 Administration 100 Steven King 20 Marketing 100 Steven King 50 Shipping 100 Steven King 60 IT 100 Steven King 80 Sales 160 rows selected. -- EQUI JOIN SQL> SELECT e.employee_id, e.first_name ' ' e.last_name employee_name, d.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id order by e.employee_id; EMPLOYEE_ID EMPLOYEE_NAME DEPARTMENT_ID DEPARTMENT_NAME ----------- -------------- -------------- ------------------ 100 Steven King 10 Executive 101 Neena Kochhar 90 Executive 102 Lex De Haan 90 Executive 19 rows selected. -- NON-EQUI JOIN SQL> SELECT e.employee_id, e.first_name ' ' e.last_name employee_name, d.department_id, d.department_name FROM employees e, departments d WHERE e.department_id BETWEEN d.department_id AND d.department_id AND e.salary = 13000; EMPLOYEE_ID EMPLOYEE_NAME DEPARTMENT_ID DEPARTMENT_NAME ----------- ------------------- -------------- ----------------- 201 Michael Hartstein 20 Marketing -- OUTER JOIN SQL> SELECT e.employee_id, e.first_name ' ' e.last_name employee_name, d.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id order by e.employee_id; EMPLOYEE_ID EMPLOYEE_NAME DEPARTMENT_ID DEPARTMENT_NAME ----------- -------------- -------------- ------------------ 100 Steven King 90 Executive 101 Neena Kochhar 90 Executive 102 Lex De Haan 90 Executive 103 Alexander Hunold 60 IT 36

104 Bruce Ernst 60 IT 20 rows selected. JOIN 유형 (ANSI) -- CROSS JOIN SQL> SELECT employee_id, first_name ' ' last_name emp_name, d.department_id, department_name FROM employees CROSS JOIN departments d; EMPLOYEE_ID EMP_NAME DEPARTMENT_ID DEPARTMENT_NAME ----------- ------------------- ------------- ----------------- 100 Steven King 10 Administration 100 Steven King 20 Marketing 100 Steven King 50 Shipping 100 Steven King 60 IT 100 Steven King 80 Sales 100 Steven King 90 Executive 100 Steven King 110 Accounting 100 Steven King 190 Contracting 160 rows selected. -- NATURAL JOIN ( 단, 참조되어지는컬럼에 table 명이나 alias 를사용하면안된다.) SQL> SELECT employee_id, first_name ' ' last_name emp_name, department_name FROM employees NATURAL JOIN departments; EMPLOYEE_ID EMP_NAME DEPARTMENT_NAME ----------- ------------------- ------------------------------ 202 Pat Fay Marketing 141 Trenna Rajs Shipping 142 Curtis Davies Shipping 143 Randall Matos Shipping 144 Peter Vargas Shipping 104 Bruce Ernst IT 107 Diana Lorentz IT 174 Ellen Abel Sales 12 rows selected. -- JOIN ~ USING ( 단, 참조되어지는컬럼에 table 명이나 alias 를사용하면안된다.) SQL> SELECT e.employee_id, e.first_name ' ' e.last_name emp_name, d.department_name FROM employees e JOIN departments d USING (department_id); EMPLOYEE_ID EMP_NAME DEPARTMENT_NAME ----------- ------------------------ ----------------------- 200 Jennifer Whalen Administration 201 Michael Hartstein Marketing 202 Pat Fay Marketing 124 Kevin Mourgos Shipping 141 Trenna Rajs Shipping 142 Curtis Davies Shipping 143 Randall Matos Shipping 144 Peter Vargas Shipping 37

19 rows selected. -- JOIN ~ ON SQL> SELECT e.first_name ' ' e.last_name "Employee", m.first_name ' ' m.last_name "Manager" FROM employees e JOIN employees m ON (e.manager_id=m.employee_id); Employee Manager ------------------ ----------------------------------------- Neena Kochhar Steven King Lex De Haan Steven King Kevin Mourgos Steven King Eleni Zlotkey Steven King Michael Hartstein Steven King Jennifer Whalen Neena Kochhar Shelley Higgins Neena Kochhar Alexander Hunold Lex De Haan 19 rows selected. -- LEFT RIGHT OUTER JOIN SQL> SELECT e.employee_id, e.first_name ' ' e.last_name emp_name, d.department_id, d.department_name FROM departments d LEFT OUTER JOIN employees e ON(e.department_id=d.department_id); EMPLOYEE_ID EMP_NAME DEPARTMENT_ID DEPARTMENT_NAME ----------- -------------------- ---------------- ------------- 200 Jennifer Whalen 10 Administration 201 Michael Hartstein 20 Marketing 202 Pat Fay 20 Marketing 124 Kevin Mourgos 50 Shipping 141 Trenna Rajs 50 Shipping 142 Curtis Davies 50 Shipping 143 Randall Matos 50 Shipping 144 Peter Vargas 50 Shipping 20 rows selected. 5.2. JOIN 방식 NESTED LOOP JOIN (NLJ) - Driving Table 에서 Row를추출한후그결과를다른테이블에연결하여 Join - 특징 순차적 : 부분범위처리가능 종속적 : 먼저처리되는테이블의처리범위에따라처리량결정 랜덤 (Random) 액세스위주 연결고리상태에따라영향이큼 주로좁은범위처리에유리 -- NESTED LOOP JOIN 38

SELECT a.fld1,..., b.fld1,... FROM TAB1 a, TAB2 b WHERE a.key1 = b.key2 AND a.fld1 = 'AB' AND b.fld2 = '10' MERGE JOIN - 양쪽테이블의처리범위를각각 Access 하여정렬한결과를차례로 Scan 하면서연결고리의조건을만족하는지를 Merge 해가는방식 - 특징 동시적 : 무조건전체범위처리 독립적 : 자기의처리범위만으로처리량결정 스캔 (Scan) 액세스위주 연결고리상태에영향이없음 주로넓은범위처리에유리 -- MERGE JOIN 39

SELECT /*+ use_merge(a b) */ a.fld1,..., b.fld1,... FROM TAB1 a, TAB2 b WHERE a.key1 = b.key2 AND a.fld1 = 'AB' AND b.fld2 = '10' HASH JOIN - 크기가작은파티션을메모리에로딩 (Building) 하여 Hash Table 생성후, 나머지파티션의 Row를읽어 Hash Table 상대응되는 Row 체크 - 특징 독립적 : 자기의처리범위만으로처리량결정 반부분범위처리 : Hash Function 을이용하여매핑하는후행테이블은전체범위처리수행 메모리영역만으로 Hash Table 생성시최적의효과가능하므로적은테이블이선행테이블로선택됨 Hash Function 을이용하므로결과값정렬보장받을수없음 -- HASH JOIN 40

SELECT a.fld1,..., b.fld1,... FROM TAB1 a, TAB2 b WHERE a.key1 = b.key2 AND a.fld1 = 'AB' AND b.fld2 = '10' 6. SUBQUERY 6.1. SUBQUERY 유형 종류단일행 SUBQUERY 다중행 SUBQUERY 다중열 SUBQUERY (Pairwise SUBQUERY) INLINE VIEW Scalar SUBQUERY 설명 SELECT 문장으로부터오직하나의행만을검색하는질의 ( 단일행비교연산자 : =, >, >=, <, <=, <>,!=) SELECT 문장으로부터하나이상의행을검색하는질의 ( 복수행비교연산자 : IN, NOT IN, ANY, ALL, EXISTS) SELECT 문장으로부터하나이상의열을검색하는질의 SUBQUERY가 FROM 절에서사용되었을경우하나의 Coulmn 및하나의 ROW 값만을리턴하는 SUBQUERY로하나의 Coulmn 및하나의 ROW 값보다많은값이리턴될경우 error를리턴 41

Correlated SUBQUERY MIAN-QUERY절에사용된테이블이 SUBQUERY절에다시재사용되는경우의 SUBQUERY (SUBQUERY의결과가한행씩 MAIN으로리턴되는방식으로처리되어내부적으로성능을저하시키기때문에잘사용하지않음 ) SUBQUERY 유형예제 -- 단일행 SUBQUERY SQL> SELECT employee_id, first_name ' ' last_name employee_name, salary, department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = 50) AND department_id = 50; EMPLOYEE_ID EMPLOYEE_NAME SALARY DEPARTMENT_ID ----------- --------------- ---------- --------------- 124 Kevin Mourgos 5800 5 -- 다중행 SUBQUERY SQL> SELECT employee_id, first_name ' ' last_name employee_name, salary, department_id FROM employees WHERE salary IN (SELECT MAX(salary) FROM employees GROUP BY department_id); EMPLOYEE_ID EMPLOYEE_NAME SALARY DEPARTMENT_ID ----------- ------------------ ---------- --------------- 100 Steven King 24000 90 103 Alexander Hunold 9000 60 124 Kevin Mourgos 5800 50 174 Ellen Abel 11000 80 178 Kimberely Grant 7000 200 Jennifer Whalen 4400 10 201 Michael Hartstein 13000 20 205 Shelley Higgins 12000 110 8 rows selected. -- 다중열 SUBQUERY SQL> SELECT e.employee_id, e.first_name ' ' e.last_name employee_name, e.salary, e.department_id FROM employees e WHERE (e.department_id, e.salary) IN (SELECT department_id, MIN(salary) FROM employees GROUP BY department_id) ORDER BY e.department_id; EMPLOYEE_ID EMPLOYEE_NAME SALARY DEPARTMENT_ID 42

----------- ----------------- --------------- -------------- 200 Jennifer Whalen 4400 10 202 Pat Fay 6000 20 144 Peter Vargas 2500 50 107 Diana Lorentz 4200 60 176 Jonathon Taylor 8600 80 101 Neena Kochhar 7000 90 102 Lex De Haan 17000 90 206 William Gietz 8300 110 8 rows selected. -- INLINE VIEW SQL> SELECT b.employee_id, b.email, b.salary, b.department_id FROM ( SELECT employee_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = 90) )a, employees b WHERE a.employee_id = b.employee_id AND b.department_id!= 20; EMPLOYEE_ID EMAIL SALARY DEPARTMENT_ID ----------- -------------- ---------- ------------- 100 SKING 24000 90 -- Scalar SUBQUERY SQL> SELECT first_name last_name employee_name, salary, CASE WHEN department_id = (SELECT department_id FROM employees WHERE employee_id = '200') THEN 'PART1' ELSE 'OTHER' END AS "location" FROM employees; EMPLOYEE_NAME SALARY location --------------------------------------------- ---------- -------- StevenKing 24000 OTHER NeenaKochhar 17000 OTHER LexDe Haan 17000 OTHER AlexanderHunold 9000 OTHER BruceErnst 6000 OTHER DianaLorentz 4200 OTHER KevinMourgos 5800 OTHER TrennaRajs 3500 OTHER CurtisDavies 3100 OTHER RandallMatos 2600 OTHER PeterVargas 2500 OTHER EleniZlotkey 10500 OTHER EllenAbel 11000 OTHER JonathonTaylor 8600 OTHER KimberelyGrant 7000 OTHER JenniferWhalen 4400 PART1 MichaelHartstein 13000 OTHER PatFay 6000 OTHER ShelleyHiggins 12000 OTHER WilliamGietz 8300 OTHER 43

20 rows selected. -- Correlated SUBQUERY SQL> SELECT email, salary, department_id FROM employees e WHERE salary > (SELECT avg(salary) FROM employees WHERE department_id=e.department_id); EMAIL SALARY DEPARTMENT_ID ------------------------- ---------- ------------- SKING 24000 90 EZLOTKEY 10500 80 EABEL 11000 80 SHIGGINS 12000 110 AHUNOLD 9000 60 MHARTSTE 13000 20 KMOURGOS 5800 50 7 rows selected. 7. DML DML(Data Manipulation Language) 명령은데이터를입력, 수정, 삭제하는 SQL 명령어이다. 7.1. DML 유형 종류 설명 INSERT UPDATE DELETE MERGE 테이블에새로운행추가테이블의행내용을변경테이블의행삭제 UPDATE, DELETE, INSERT를결합한문장 주의사항 : DELETE는 UPDATE 없이단독으로사용할수없다. 7.1.1. INSERT 구분 SQL 문법 기본 INSERT 문 INSERT INTO 테이블명 ( 컬럼명 1, 컬럼명 2,...) VALUES ( 값 1, 값 2,...) SELECT 문을이용한 INSERT 문 Multi Table INSERT 문 INSERT INTO 테이블명 ( 컬럼명 1, 컬럼명 2,...) SELECT 구문 INSERT [ALL \ FIRST] WHEN 조건 1 THEN 44

INTO 테이블명1 WHEN 조건2 THEN INTO 테이블명2 ELSE INTO 테이블명0 SELECT 구문 ; INSERT 예제 SQL> CREATE TABLE employee ( EMP_NO VARCHAR(8) NOT NULL, EMP_NAME VARCHAR(20), HIREDATE DATE, SALARY NUMBER(8,3), BONUS NUMBER(8,3), DEPT_CD VARCHAR(4), MANAGER VARCHAR(8) ); Table 'EMPLOYEE' created. SQL> INSERT INTO employee VALUES ('20081120','Lolly',TO_DATE('2008-06-20', 'YYYY-MM-DD'),3000,'','1000','20032813'); 1 row inserted. SQL> INSERT INTO employee (emp_no, emp_name, hiredate, dept_cd) VALUES ('20083311','Angel',TO_DATE('2008-07-13', 'YYYY-MM-DD'), '0000'); 1 row inserted. SQL> COMMIT; Commit completed. SQL> CREATE TABLE employee_0000(emp_no, emp_name, hiredate) AS SELECT emp_no, emp_name, hiredate FROM employee WHERE 1=2; Table 'EMPLOYEE_0000' created. SQL> INSERT INTO employee_0000 SELECT emp_no, emp_name, hiredate FROM employee WHERE dept_cd=0000; 1 row inserted. SQL> COMMIT; Commit completed. 45

7.1.2. UPDATE 구분 SQL 문법 기본 UPDATE 문 UPDATE 테이블명 SET 컬럼명 = 값 1, 컬럼명 = 값 2... [WHERE 조건 ] SUBQUERY 를이용한 UPDATE 문 UPDATE 테이블명 SET 컬럼명 = (SELECT 컬럼명 FROM 테이블명 [WHERE 조건 ]) [WHERE 조건 ] UPDATE 예제 SQL> UPDATE employee SET dept_cd = '1000' WHERE emp_no='20083311'; 1 row updated. 7.1.3. MERGE 기본 MERGE 문 구분 SQL 문법 MERGE INTO 테이블명 USING {table view subquery} ON ( 조건 ) WHEN MATCHED THEN UPDATE SET 컬럼명1 = 값1 [, 컬럼명2 = 값2...] DELETE WHERE 조건 WHEN NOT MATCHED THEN INSERT ( 컬럼리스트 ) VALUES ( 값...) MERGE 예제 SQL> MERGE INTO employee d USING(SELECT emp_name,salary,dept_cd FROM employee WHERE emp_no='20081120') s ON(d.emp_name=s.emp_name) WHEN MATCHED THEN UPDATE SET d.bonus= d.bonus+s.salary*0.1 WHEN NOT MATCHED THEN INSERT (d.emp_name,d.bonus) VALUES(s.emp_name,s.salary*0.1); 1 row merged. 7.1.4. DELETE 구분 SQL 문법 기본 DELETE 문 DELETE FROM 테이블명 [WHERE 조건 ] DELETE 예제 46

SQL> DELETE employee WHERE emp_no='20083311' OR emp_no='20081120'; 2 rows deleted. 8. TRANSATION Tibero 는 TRANSACTION 의논리적인작업단위를나누어데이터의일관성을보장한다. 8.1. TRANSACTION TRANSACTION 은첫번째 DML 문을실행하였을때시작된다. 트랜잭션정상종료 : COMMIT/ROLLBACK 의명령어를사용했을때, DDL 이나 DCL 문장사용했을때 트랜잭션비정상종료 : 사용자에의한 tbsql 의비정상종료또는시스템의비정상적인종료. ROLLBACK 처리 종류 설명 COMMIT SAVEPOINT ROLLBACK ROLLBACK TO SAVEPOINT_NAME 데이터변경내용을영구히저장하고현재트랜잭션을종료 트랜잭션내에저장위치를표시 데이터변경내용을버리고트랜잭션을종료 현재트랜잭션을지정된 (SAVEPOINT_NAME) 저장위치로돌아가, 그이전의데이터변경내용을버리고트랜잭션을종료 8.1.1. COMMIT Commit 실행 COMMIT; COMMIT WORK; 존재하지않는트랜잭션을지정하면, 다음과같은에러가발생 SQL> COMMIT FORCE '2.16.18'; TBR-21022: No prepared transaction found with transaction ID 2.16.18. 8.1.2. SAVEPOINT SAVEPOINT 를사용해서저장점을설정 SAVEPOINT sp1; 47

8.1.3. ROLLBACK / ROLLBACK TO SAVEPOINT_NAME SAVEPOINT 로저장점을지정하고, ROLLBACK 을사용해지정된저장점까지 ROLLBACK SQL> CREATE TABLE T ( A NUMBER ); Table 'T' created. SQL> INSERT INTO T VALUES(1); 1 row inserted. SQL> COMMIT; Commit completed. SQL> FROM T; A ---------- 1 SQL> UPDATE T SET A=2; 1 row updated. SQL> SAVEPOINT SP1; Savepoint created. SQL> UPDATE T SET A=3; 1 row updated. SQL> FROM T; A ---------- 3 SQL> ROLLBACK TO SAVEPOINT SP1; Rollback completed. SQL> FROM T; A ---------- 2 48

SQL> ROLLBACK; Rollback completed. SQL> FROM T; A ---------- 1 SQL> ROLLBACK TO SAVEPOINT SP1; TBR-21008: Savepoint 'SP1' was not found. 9. SQL 활용가이드 9.1. SQL 구문활용가이드 튜닝관점보다 SQL 구문을이해하기쉽게작성 SQL 구문작성표현은 SQL Text 표준지침을따름 SQL 구문의이해를돕기위해복잡한 In-Line View의사용보다는테이블조인을사용 불필요한 DUAL 테이블의사용은제한 Hint 는사용하지않으며, 인덱스구성이완료되고튜닝시점에 DBA와협의하여 Hint를사용하도록함 서로다른 Data 타입비교시 DBMS가한쪽을기준으로내부변형을발생시키므로주의 Outer Join 의정확한이해후작성 한서비스당가능한한 SQL 구문으로작성할수있도록함 - 여러로직으로처리하는것보다한 SQL 구문이이해하기쉬우며유지보수또한용이 - Optimizer 가한 SQL 구문단위로최적화하기때문에성능향상에유리 Trigger 는사용금지 / View 는원칙적으로금지 ( 필요할경우 DBA 와협의 ) 9.2. SQL 함수, 키워드및조건절활용가이드 9.2.1. Distinct, Decode, 그룹함수 (SUM, AVG) Distinct 는항상 Sort연산을수행하므로, 결과값이항상 unique 한경우사용하지않음 SELECT 절의 DECODE 단계는가능한 2 레벨을초과하지않도록함 하나의 DECODE 내에여러개의비교를연속해서사용하는경우가 DECODE 내에 DECODE 가반복되는형식보다오버헤드가적음 SUM 함수는만족하는조건의합을구하는것으로 NULL 값은자동제외후값을구함 -> SUM( Col_name ) = SUM( NVL ( Col_name, 0 ) ) 이므로불필요한 NVL 사용제한 AVG 함수는 NULL 을제외하고평균을구함 49

-> AVG( Col_name ) AVG( NVL ( Col_name,0 ) ) 그룹함수사용시처리할대상집합이공집합이라하더라도논리적인결과는존재하므로수행결과는결코공집합이 되지않음 SQL> SELECT MAX(salary) FROM employees WHERE 1 = 2; MAX(SALARY) ----------- NULL 9.2.2. OR 및 WHERE 절구문 OR 사용은가급적제한해서사용 - WHERE 절에서단순한 OR 의경우는최적화실행계획으로분리된후이들을결합하는실행계획으로수립되지만복잡하게사용된 OR 은거의 Full Table Scan 하게됨 - OR 을 IN 으로사용할수있다면 IN 을사용 - 상수쪽을 DECODE 등으로가공하여 OR 연산자를제거하도록함 - OR 사용으로 Full Table Scan 될경우 UNION ALL 을사용, SQL 구문을분리하고이를통하여인덱스사용을유도 - 복잡한 OR 을사용하는 SQL 구문은 DBA에게통보 WHERE 구문에서사용되어지는 Host 변수명은개발명명규칙을따름 WHERE 절구문에있는컬럼에대하여인위적인함수를사용하지않음 인덱스사용을위해부정형조건보다긍정형조건을사용 9.2.3. IN IN - 데이터집합의처리범위가좁고데이터집합이작을시유리 - 일반적으로전체범위처리를통하여서브쿼리의조건을만족하는모든데이터집합을구한다음메인쿼리의조건처리 ( 제공자역할 ) - IN 으로묶인서브쿼리의테이블의범위를줄이면성능을보장할수있음 SELECT COL1, COL2 FROM OUT_TAB WHERE COL3 IN (SELECT IN_COL1 FROM IN_TAB); Main Query에대하여제공자역할 - Main Query 보다먼저수행되어수행결과값 SubQuery가먼저수행됨을확인 IN_TAB 의범위를줄여주어야성능보장 50

9.2.4. EXISTS EXISTS - 데이터집합의처리범위가넓고부분범위처리가필요한경우에유리 - EXISTS 의서브쿼리는메인쿼리가찾은데이터에대하여하나씩존재여부를판단하는 Loop 형식으로서브쿼리의부분범위처리가능 - 메인쿼리가먼저수행되며서브쿼리는존재여부판단후다시메인쿼리로돌아가는확인자역할을함 - 메인쿼리의테이블의범위를줄여주어야성능을보장받을수있음 9.3. Join 활용가이드 9.3.1. Outer Join 조인순서가미리정해지므로조인순서를이용한튜닝이불가함 가능한 Outer Join 은피하도록함 Outer Join 을담당하는테이블에대한모든조건에 (+) 가붙어야원하는결과를얻음 (+) 기호를이용하여 IN, OR 의연산자로비교할수없음 - Inline View 이용, 또는 decode 문을사용 (ORA-01719 error : outer join operator (+) not allowed in operand of OR or IN) (+) 기호를이용하여 subquery 와비교할수없음 - OR IS NULL 조건과비교 9.4. Index 활용가이드 9.4.1. Index 를사용할수없는경우 유형설명권고안 Index 구성컬럼의외부적변형 Index 구성컬럼의내부적변형부정형비교 NULL 값비교기타 Index 구성컬럼에대한함수사용, 연산수행등과같은외부적요인으로인한변형데이터형이상이한컬럼간의비교의경우숫자를기준으로문자타입이숫자타입으로변형되는내부적변형부정형비교 (!=, <>, NOT IN 등 ) 를수행하는경우발생 IS (NOT) NULL 로비교하는경우인덱스에는 NULL 데이터가존재하지않으므로사용할수없게되는경우발생결합 index 사용부적절 LIKE 비교부적절 index 컬럼에대한외부적변형제거 데이터모델상의정합성정비 데이터형에적합한연산수행유도 가능한경우문형변경등을통해부정형비교제거 =, ( 공백 ) 으로가능한경우변경 Default 값적용으로 NULL 값제거 선행컬럼의조건사용 BETWEEN, <= 같은연산자사용및사용자입력값검토 51

HAVING 절의조건추가 WHERE 절에서조건추가 9.4.2. 유형별해결방안 - Index 구성컬럼의외부적변형 원본 SQL 구문권고 SQL 구문 ( 변경 ) 비고 FROM TEST_TAB WHERE SUBSTR( VAR,1,3 ) = ABC FROM TEST_TAB WHERE NUM * 365 = 730 FROM TEST_TAB WHERE TO_CHAR(DATE, YYMM ) = 9902 FROM TEST_TAB WHERE NVL(CHR, X ) = X-MAN FROM TEST_TAB WHERE NUM VAR = 2ABC FROM TEST_TAB WHERE VAR LIKE ABC% FROM TEST_TAB WHERE NUM = 730 / 365 FROM TEST_TAB WHERE DATE = TO_DATE( 9902, YYMM ) FROM TEST_TAB WHERE CHR = X-MAN FROM TEST_TAB WHERE NUM = 2 AND VAR = ABC 외부적 변형제거 9.4.3. 유형별해결방안 - Index 구성컬럼의내부적변형 원본 SQL 구문내부적변형으로인한실제 SQL 구문권장 SQL 구문 ( 변경 ) FROM TEST_TAB WHERE CHR = 10 FROM TEST_TAB WHERE VAR = 10 FROM TEST_TAB WHERE NUM LIKE 10% FROM TEST_TAB WHERE TO_NUMBER(CHR) = 10 FROM TEST_TAB WHERE TO_NUMBER(VAR) = 10 FROM TEST_TAB WHERE TO_CHAR(NUM) LIKE 10% FROM TEST_TAB WHERE CHR = 10 FROM TEST_TAB WHERE VAR = 10 FROM TEST_TAB WHERE NUM BETWEEN 100 AND 200 52

9.4.4. 유형별해결방안 - 부정형비교 원본 SQL 구문권고 SQL 구문 ( 변경 ) 비고 FROM TEST_TAB WHERE NUM <> 100 FROM TEST_TAB WHERE VAR LIKE 'AB%' AND NUM <> 100 FROM TEST_TAB A WHERE NOT EXISTS ( SELECT '' FROM TEST_TAB B WHERE A.CHR = B.CHR AND B.NUM ='100') FROM TEST_TAB A WHERE A.VAR LIKE 'AB%' AND NOT EXISTS (SELECT '' FROM TEST_TAB B WHERE A.VAR = B.VAR AND B.NUM = 100) FROM TEST_TAB WHERE VAR LIKE 'AB%' MINUS FROM TEST_TAB WHERE NUM = 100 NOT EXISTS 로문형변경 NOT EXISTS 로문형변경 MINUS 사용으로문형변경 9.4.5. 유형별해결방안 - NULL 값비교 원본 SQL 구문권고 SQL 구문 ( 변경 ) 비고 FROM TEST_TAB WHERE VAR IS NOT NULL FROM TEST_TAB WHERE VAR IS NULL FROM TEST_TAB WHERE NUM IS NOT NULL FROM TEST_TAB WHERE VAR > /*SPACE*/ FROM TEST_TAB WHERE VAR = default_value FROM TEST_TAB WHERE NUM > 0 VAR 은문자열이므로 NOT NULL 비교를 로교체테이블생성시 VAR 컬럼의 DEFAULT 값을지정하여 NULL 값비교를 DEFAULT 값비교로대체 NUM 은숫자형이고양수값만존재한다는가정이성립하면 > 0 으로교체가능 53

Copyright 2014 TmaxData Co., Ltd. All Rights Reserved. Trademarks Tibero RDBMS is a registered trademark of TmaxData Co., Ltd. Other products, titles or services may be registered trademarks of their respective companies. Contact Information TmaxData can be contacted at the following addresses to arrange for a consulting team to visit your company and discuss your options. Korea TmaxData Co., Ltd 5, Hwangsaeul-ro 329beon-gil, Bundang-gu, Seongnam-si, Gyeonggi-do. South Korea Tel: +82-31-779-7113 Fax: +82-31-779-7119 Email: info@tmax.co.kr Web (Korean): http://www.tmaxdata.com Technical Support: http://technet.tmaxsoft.com USA TmaxSoft, Inc. 560 Sylvan Avenue Englewood Cliffs, NJ 07632. U.S.A Tel: +1-201-567-8266 Fax: +1-201-567-7339 Email: info@tmaxsoft.com Web (English): http://www.tmaxsoft.com Russia Tmax Russia L.L.C. Grand Setun Plaza, No A204 Gorbunova st.2, Moscow, 121596 Tel: +7(495)970-01-35 Email: info.rus@tmaxsoft.com Web (Russian): http://ru.tmaxsoft.com Singapore Tmax Singapore Pte. Ltd. 430 Lorong 6, Toa Payoh #10-02, OrangeTee Building. Singapore 319402 Tel: +65-6259-7223 Email: info.sg@tmaxsoft.com United Kingdom TmaxSoft UK Ltd. Surrey House, Suite 221, 34 Eden Street, Kingston-Upon- Thames, KT1 1ER United Kingdom Tel: + 44-(0)20-8481-3776 Email: info.uk@tmaxsoft.com Web (English): http:/www.tmaxsoft.com Japan TmaxSoft Japan Co., Ltd. 5F Sanko Bldg, 3-12-16 Mita, Minato-Ku, Tokyo, 108-0073 Japan Tel: +81-3-5765-2550 Fax: +81-3-5765-2567 Email: info.jp@tmaxsoft.com Web (Japanese): http://www.tmaxsoft.co.jp China TmaxSoft China Co., Ltd. Beijing Silver Tower, RM 1508, 2 North Rd Dong San Huan, Chaoyang District, Beijing, China, 100027. China Tel: +86-10-6410-6145~8 Fax: +86-10-6410-6144 Email: info.cn@tmaxsoft.com Web (Chinese): http://www.tmaxsoft.com.cn Brazil TmaxSoft Brazil Avenida Copacabana, 177-3 andar 18 do Forte Empresarial, Alphaville - Barueri, Sao Paulo, SP-Brasil CEP 06472-001 Email: contato.brasil@tmaxsoft.com TD-TRDV-D0530101 54