New

사용자가 요구하는 데이터에 대한 전체 블록의 액세스가 모두 끝난후 데이터를 반환하는 방식.

Group by나 Order by 는 특정한 범위의 데이터를 추출하기 위해서 정렬 후 계산 또는 정렬 결과 를 리턴한다.

2차가공을 해야 만 결과를 추출할 수 있는 경우.

SELECT ENAME, SUM(PAY)

  FROM EMP

 WHERE DNAME LIKE '총무%'

GROUP BY ENAME

;

- 범위를 줄여주는 Index로 범위는 줄었으나 group 함수 사용으로 전체 범위에 대한 1차 추출 후 2차 가공이 발생.

- 조건에 맞는 데이터를 추출함과 동시에 사용자에게 데이터를 리턴하는 방식.

- 2차 가공이 필요없이 데이터 운반단위에 데이터가 차는대로 사용자에게 리턴.

SELECT * FROM EMP

 WHERE deptno ='10'

     AND ename LIKE 'S%'

ORDER BY deptno, ename

;

-->

INDEX : deptno,ename

SELECT * FROM EMP

 WHERE deptno ='10'

     AND ename LIKE 'S%'

;

- 인덱스로 설정된 컬럼값으로 인덱스 블록내에 이미 정렬되어 있기 때문에 해당인덱스를 이용해서 추출할 경우 이미 정렬되어 조회됨.

where절의 조건에 대한 데이터 추출 후 그 데이터를 그룹별로 정렬하여 가공한 후 리턴

SELECT ename, SUM(salary)

   FROM emp

 WHERE ename LIKE 'S%'

GROUP BY ename

-->

INDEX설정 : ename, salary

SELECT ename, SUM(salary)

   FROM emp

 WHERE ename LIKE 'S%'

GROUP BY ename

- 인덱스는 생성 시 ASC가 기본으로 생성되고, 사용자가 직접 DESC로 생성할수 있다.

- ASC로 설정되어 있다면 인덱스의 가장 윗부분은 가장 작은 값이 된다. 마지막은 가장 큰값.

/*+INDEX_DESC */ 힌트 사용.

; 인덱스의 가장 마지막 데이터가 추출.

; ROWNUM = 1 사용.

; 수억건의 데이터가 존재하는 경우 인덱스 역시 수억건이 될수 있으므로 인덱스 생성 시 DESC로 생성하여 최근 건에 대해 추출속도를 개선.

-오라클은 프로그램 내의 문법을 해석하고 실행하는 것이 아니라 프로그램 내에 있는 각각의 SQL을 개별적으로 수행한 후  결과에 따라 데이터를 리턴. SQL문장 하나를 수행하면 DBMS CALL이 한번 발생.

- 여러명이 사용하는 dbms call이 발생하면 시스템의 부하가 누적되어 메모리 누수 및 성능저하 관련.

count() 보다 Exists 나 Not Exists 사용.

-->

- 실행구조에 차이.

- Union, Intersect, Minus의 실행구조는 동일.

  Union >

emp 테이블에서 부서가 '인사', '총무'에 대해 union 시 

1) 각 테이블 에서 처리조건에 맞는 데이터를 추출.

2) 중복row 단일화 하기 위해 정렬한 뒤 병합하여 데이터 리턴.(병합하기 전에 정령작업은 전체범위처리)

인덱스 추가설정 시 통계정보 부재로 문제가 발생할 시 

정상적으로 갱신되어있지 않다면 

- 형식>

SELECT X.WORK_DATE    AS WORK_DATE

          FROM (SELECT SUBSTR( '201111', 1, 6 )||'01' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'02' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'03' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'04' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'05' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'06' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'07' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'08' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'09' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'10' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'11' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'12' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'13' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'14' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'15' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'16' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'17' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'18' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'19' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'20' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'21' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'22' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'23' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'24' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'25' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'26' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'27' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'28' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'29' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'30' WORK_DATE FROM DUAL UNION ALL

                SELECT SUBSTR( '201111', 1, 6 )||'31' WORK_DATE FROM DUAL 

               ) X

         WHERE X.WORK_DATE <=  TRIM(TO_CHAR(LAST_DAY(TO_DATE('20111101','YYYYMMDD')),'YYYYMMDD'))

[sqlplus] 검색결과 파일로 저장하기.

1. TXT파일 생성 예제

- 오라클에서는 SPOOL기능을 이용해서 TXT파일이나 SAM파일에 검색된 테이터를 저장 할 수 있습니다.

- 먼저 파일에 저장될 검색 결과를 얻기위한 SQL문을 스크립트 파일로 만듭니다.

- 여러번에 SQL문을 돌리는 것보다 하나의 스크립트 파일을 만들어서 실행하는것이 더 효율적입니다.

실행시킬 SQL문을 C:\SpoolSelect.sql로 저장 합니다.

---- SpoolSelect.sql 시작 ---

SELECT empno, sal, ename, to_char(hiredate, 'YYYY-MM-DD') FROM emp;

SELECT deptno, dname FROM dept;

---- SpoolSelect.sql 끝 ---

SQLPLUS scott/tiger을 실행 시킵니다.

-- header가 display되지 않고 데이터만 display됩니다. 

SQL>SET HEADING OFF

-- pagesize의 default는 14이며 그대로 하면 14줄마다 1줄씩 공백이 생기므로  그런 현상을 방지하기 위해 크게 지정합니다. 

SQL>SET PAGESIZE 1000

-- linesize도 record 길이만큼 지정하여 아래로 구분되지 않도록 합니다.

SQL>SET LINESIZE 300

-- 명령이 display되지 않도록 합니다.

SQL>SET ECHO OFF

-- 조회 결과가 화면에 나오지 않도록 합니다. 

SQL>SET TERM OFF

-- data가 들어가는 화일 이름을 지정 합니다. 

SQL>SPOOL C:\test.txt

-- 스크립트 파일을 실행 시킵니다. 

SQL>@C:\SpoolSelect.sql

SQL>SPOOL Off

test.txt file이 생성 되었는지 확인 합니다.

2. 필드 값 구분하기.

1-1.select empno||' '||ename from emp; <== Tab으로 구분

1-2.select empno||','||ename from emp; <== 콤마로 구분

2-1.select empno||'"'||ename from emp; <== 필드 값에 큰따옴표 붙이는 방법

(작은 따옴표는 예약어 이기 때문에 에러 남)