2019 - 02 - 11 (월)
실습환경
오라클 11g SQL*PLUS/SQL Developer
테이블 정의 원칙 (DDL)
1.1 개채무결성 : ROW 를 유일하게 식별할 수 있는 캃럼을 갖는다 이를 PK라 함. 1.2 참조무결성 : 테이블은 외래키를 통해 서로 관계를 맺는다. 다른 테이블 또는 자기자신 테이블의 PK를 참조하는 칼럼을 말한다.
SET 명령어 : SQL*Plus
SQL*PLUS SET 명령은 SQL 명령문의 결과를 사용자가 효과적으로 확인할 수 있도록 시스템 환경을 제어하기 위한 명령어이다. 명령문의 유지기간은 세션종료 혹은 환경설정을 변경할 때 까지 이다.
SHOW ALL -- 이 명령어를 통해 현재 환경을 확인할 수 있다.
SET AUTOTRACE ON/OFF -- Optimizer 에서 실행계획과 통계정보를 얻을 수 있다.
SET ARRAYSIZE 5 -- 한번에 5로우씩 패치
SET TIME ON/OFF : SQL*Plus 프롬프트 상에 시각 표시 여부 결정
SET TIMING ON/OFF : SQL 문의 실행 때부터 실행 결과가 응답될 때까지 시간을 파악하여 출력할지의 여부를 지정, 기본적으로 OFF이며 응답시간은 표시되지 않는다.
SET LINESIZE 130
오라클 SQL (DML)
- 대용량 테이블에서의 ORDER BY
ORDER BY 는 데이터를 메모리에 올려놓고 소트를 진행하고 메모리 공간이 부족하면 디스크에 놓고 정렬을 수행한다. 즉, 대용량 데이터에서는 사용을 자제해야한다. 인덱스를 적절히 이용해야하며 오라클 힌트등을 사용한다면 성능상 이점을 볼 수 있다.
select * from emp order by ename;
만일 위의 emp 테이블에 데이터가 1000만건이 있다고 할 경우 위 쿼리가 실행되는데 10초이상의 시간이 소요될 수 있다.
CREATE INDEX IDX_EMP_ENAME ON EMP(ENAME DESC);
select /*+ index(emp idx_emp_ename) */
empno, ename, sal
from emp
where ename >= ‘ㄱ’;
위의 쿼리에서는 EMP 에서 ENAME 에 인덱스를 생성하고 힌트를 주었다. 훨씬 빠르게 된 속도를 확인 할 수 있다. 위에서 보면 where 에 enmae 에 조건을 준다. 이 이유는 인덱스를 활용하기 위해서이다.
합성 연산자 (Concatenated Operator)
SELECRT ename , sal||'('||comm||')' as "pay"
FROM emp
WHERE ename = 'TURNER'
SELECT ename , CONCAT(SAL, CONCAT('(', concat(COMM,')')))
FROM emp
WHERE ename = 'TURNER'
위의 쿼리 두 문장은 같은 역할을 한다. ( concatenated Operator == CONCAT 함수 )
조건연산자를 이용한 조건검색
-- MYEMP1 테이블에서 입사일자가 2013년 10월25일~2014년12월31일 사이의 입사자 수 출력, 먼저
두가지 쿼리를 보자.
SELECT COUNT(*) FROM MYEMP1
WHERE TO_CHAR(HIREDATE,'RR/MM/DD') >= '13/10/26'
AND TO_CHAR(HIREDATE,'RR/MM/DD') <= '14/12/31';
COUNT(*)
----------
1440288
경 과: 00:00:16.60
SELECT COUNT(*) FROM MYEMP1
WHERE HIREDATE >= '13/10/26'
AND HIREDATE < '15/01/01';
COUNT(*)
----------
1440288
경 과: 00:00:17.20
-- 위 두 쿼리의 차이는 WHERE절 HIREDATE칼럼에 SQL함수 TO_CHAR가 적용되었느냐의 여부다. 현
재 MYEMP1의 HIREDATE 칼럼에는 검색을 빠르게 해주는 인덱스가 생성되어 있지 않다. 인덱스라는
것을 만든 후 두 쿼리를 다시 실행하여 실행시간의 차이에 대해 확인하자.
-- 아래 쿼리는 HIREDATE 칼럼에 SQL함수 TO_CHAR가 적용되어 칼럼값에 변형이 생겨 만들어 놓은
인덱스를 이용하지 못한다.
SELECT COUNT(*) FROM MYEMP1
WHERE TO_CHAR(HIREDATE,'RR/MM/DD') >= '13/10/26'
AND TO_CHAR(HIREDATE,'RR/MM/DD') <= '14/12/31';
COUNT(*)
----------
1440288
경 과: 00:00:16.53
Execution Plan
--------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
---------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 9 25678 00:05:09
1 SORT AGGREGATE 1 9 |
* 2 | TABLE ACCESS FULL MYEMP1 25000 219K 25678 00:05:09
---------------------------------------------------------------------------------------------
-- 아래 쿼리의 실행계획을 보면 인덱스를 경유하여 데이터를 추출한 것을 알 수있다. 두 쿼리중 어떤
쿼리를 사용해야 할지 고민해 보자.
SELECT COUNT(*) FROM MYEMP1
WHERE HIREDATE >= '13/10/26'
AND HIREDATE < '15/01/01';
COUNT(*)
----------
1440288
경 과: 00:00:00.71 -- 1초도 걸리지 않는다.
Execution Plan
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 123 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | FILTER | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_MYEMP1_HIREDATE | 25000 | 219K| 123 (1)| 00:00:02 |
------------------------------------------------------------------------------------------
-- BETWEEN ~ AND ~는 AND연산으로 변형할 수 있다.
TO_CHAR() 함수를 사용하여 Index 컬럼을 변형시킨다면 Index 사용을 못하게 된다. 이 부분은 주의할 필요가 있겠다.
-- MYEMP1 테이블의 COMM 칼럼에 인덱스를 만들자.
SQL> CREATE INDEX IDX_MYEMP1_COMM ON MYEMP1(COMM);
-- MYEMP1 테이블에서 COMM이 NULL인 사원의 수를 출력하세요. IS NULL은 인덱스를 사용하지 못
한다. 인덱스 영역에는 NULL값은 보관하지 않는다.
SQL> SELECT COUNT(*) FROM MYEMP1
WHERE COMM IS NULL;
COUNT(*)
----------
8333335
경 과: 00:00:16.63
-- MYEMP1 테이블에서 COMM이 NULL이 아닌 사원의 수를 출력하세요. IS NOT NULL인 경우에는
인덱스를 사용함을 확인하고 실행시간도 위 쿼리와 비교하라.
SQL> SELECT COUNT(*) FROM MYEMP1
WHERE COMM IS NOT NULL;
COUNT(*)
----------
1666667
경 과: 00:00:00.49
인덱스에서는 NULL 을 보관할 수 없다 그러므로 인덱스가 NULL 인지 비교하는 조건에서는 인덱스를 타지 못한다.
- ANY , ALL, EXISTS
SELECT ENAME , SAL FROM EMP
WHERE SAL> ANY(SELECT SAL FROM EMP WHERE DEPTNO = 30)
ORDER BY SAL;
-- ANY 는 어떠한 하나의 값만 만족시키면 되므로 최소급여인 사람을 제외한 ROW를 SELECT 함.
SELECT ename, sal FROM emp
WHERE sal > some (SELECT sal FROM emp WHERE deptno = 30)
ORDER BY SAL;
-- ANY와 SOME 은 동일한 효과를 갖는다.