About DATABASE

☞ EXPLAIN PLAN(실행계획) 이란?

 

 

◈ SQL문의 엑세스 경로를 확인하고 튜닝할 수 있도록 SQL문을 분석하고 해석하여

   실행계획을 수립한 후 실행계획을 PLAN_TABLE에 저장하도록 해주는 명령 입니다.

 

◈ SQL  Trace없이 사용 할 수 있습니다.

 

◈ ORACLE_HOME/rdbms/admin/utlxplan.sql실행

 

◈ statement_id컬럼에 인덱스를 생성해주는것이 수행속도를 향상시켜주고 

    id값이 중복되는 것을 막을 수 있습니다.

 

[Syntax]

 

 

 - statement_id = 'identifiedr' : 1-30자로 부여할 수 있는 해당 실행문의 제목

 - INTO tablename : 출력문을 저장하는 테이블명 PLAN_TABLE을 사용하지 않을경우 사용

 - FOR statement : 실행계획을 수립하고자 하는 SQL문(SELECT, INSERT, DELETE, UPDATE)

 

 

 

 

 

1. Plan_table 생성

 

◈  Explain plan을 sql에 포함해서 수행하면 옵티마이저가 실행 계획까지만 수립하여

     plan_table에 저장해 둡니다.

◈  이 table을 생성하기 위한 script는 ORACLE_HOME/rdbms/admin/utlxplan.sql 입니다.

 

SQL>@C:\oracle\ora81\rdbms\admin\utlxplan.sql

 

테이블이 생성되었습니다.

 

 

 

 

 

2. Index 생성

 

◈ 테이블 생성 후 수행 속도 향상과 동일한 statement_id가 생성되는 것을  방지하기 위해 

    index를 생성 합니다.

 

SQL> CREATE UNIQUE INDEX plan_index ON PLAN_TABLE(statement_id,id);

 

인덱스가 생성되었습니다.

 

 

 

 

 

3. SQL 문 사용

 

◈ FOR 뒷 부분에 확인하고자 하는 sql을 입력 합니다.

 

SQL>EXPLAIN PLAN SET STATEMENT_ID='a1' FOR

       SELECT /*+ index(emp emp_pk) */ * FROM emp WHERE empno > 0;

 

해석되었습니다.

 

 

 

 

 

4. PLAN_TABLE 을 SELECT 하는 SQL 문을 실행

 

 ◈ plan.sql이라고 저장해서 사용하면 편리합니다.

 

SQL>SELECT LPAD(operation,LENGTH(operation)+ 2*(LEVEL-1)) ||DECODE(id,0,'cost  estimate:' ||

       DECODE(position,'0','N/A',position),null) || ' ' ||options || DECODE(object_name,null,null,':') ||

       RPAD(object_owner, LENGTH(object_name)+1,',') || object_name ||

       DECODE (object_type,'UNIQUE' ,'(U) ','NON_UNIQUE','(NU)',null) ||

       DECODE(object_instance,null,null,'('||object_instance||')')  "Explain Plan"

       FROM PLAN_TABLE

       START WITH ID= 0 and STATEMENT_ID = '&&id'

       CONNECT by prior ID=PARENT_ID and STATEMENT_ID='&&id'

 

 

a1을 입력하면 아래와 같은 실행계획을 볼 수 있습니다.

 

 Explain Plan

-----------------------------------------------

 

SELECT STATEMENTcost  estimate:1

  TABLE ACCESS BY INDEX ROWID:TESTEMP(1)

    INDEX RANGE SCAN:TEST,,,EMP_PK

 

 

 

 

 

☞ PLAN_TABLE 컬럼 설명

 

 ------------------------------------------------------------------------------------------------

 컬 럼 명                                    설    명

 ------------------------------------------------------------------------------------------------

 STATEMENT_ID                                EXPLAIN PLAN문에서 사용자가 지정한 제목

 TIMESTAMP                            실행계획이 수립된 날짜와 시간

 REMARKS                                     사용자가 부여한 주석(COMMENT)

 OPERATION                            아래 표에 자세히 설명 되어 있습니다.

 OPTIONS                                     아래 표에 자세히 설명 되어 있습니다.

 OBJECT_NODE                          사용한 데이터베이스 링크

 OBJECT_OWNER                                해당 라인의 오브젝트를 생성한 사용자 그룹

 OBJECT_NAME                          테이블이나 인덱스, 클러스터등의 이름

 OBJECT_INSTANCE                      SQL의 FROM절에 기술된 오브젝트를 좌에서 우로 부여한 번호

 OBJECT_TYPE                          오브젝트의 종류(예 non-unique index)

 ID                                          수립된 각 실행단계에 붙여진 일련번호

 PARENT_ID                            해당 ID의 부모가 가진 ID

 POSITION                                    같은 부모 ID를 가지고 있는 자식 ID간의 처리 순서

 OTHER                                       다른 필요한 텍스트를 지정하기 위한 필트

 ------------------------------------------------------------------------------------------------

 

 

☞ OPERATION의 종류와 OPTIONS에 대한 설명

 

OPERATION(기능)               OPTIONS(옵션)                 설      명

---------------------------------------------------------------------------------------------------------------------------------

AGGREGATE                             GROUP BY                              그룹함수를 사용하여 하나의 로우가 추출되도록 하는 처리(버전 7에서만 표시됨)

AND-EQUAL                                                                   인덱스 머지를 이용하는 경우

CONNECT BY                                                                  CONNECT BY를 사용하여 트리 구조로 전개

CONCATENATION                                                               단위 액세스에서 추출한 로우들의 합집합을 생성

COUNTING                                                                           테이블의 로우스를 센다

FILTER                                                                             선택된 로우에 대해서 다른 집합에 대응되는 로우가 있다면 제거하는 작업

FIRST ROW                                                                          조회 로우 중에 첫번째 로우만 추출한다.

FOR UPDATE                                                                         선택된 로우에 LOCK을 지정한다. 

INDEX                                 UINQUE                                UNIQUE인덱스를 사용한다. (단 한개의 로우 추출)

                                             RANGE SCAN                            NON-UNIQUE한 인덱스를 사용한다.(한 개 이상의 로우)

                                             RANGE SCAN                            RANGE SCAN하고 동일하지만 역순으로 로우를 추출한다.

                                             DESCENDING 

INTERSECTION                                                                교집합의 로우를 추출한다.

MERGE JOIN                            OUTER                                 먼저 자신이ㅡ 조건만으로 액세스한 후 각각을 SORT하여

                                                                                           MERGE해 가는 조인

                                                                                           위와 동일한 방법이지만  outer join을 사용한다.

MINUS                                                                              MINUS 함수를 사용한다.

NESTED LOOPS                  OUTER                                 먼저 어떤 드라이빙 테이블의 로우를 액세스한 후 그 결과를

                                                                                           이용해 다른 테이블을 연결하는 조인

                                                                                           위와 동일하지만 outer join을 사용한다.

PROJECTION                                                                         내부적인 처리의 일종

REMOTE                                                                             다른 분산 데이터베이스에 있는 오브젝트를 추출하기 위해

                                                                                           DATABASE LINK를 사용하는 경우

SEQUENCE                                                                           시퀀스를 액세스 한다.

SORT                                  UNIQUE                                같은 로우를 제거하기 위한 SORT

                                             GROUP BY                              액세스 결과를 GROUP BY 하기 위한 SORT

                                             JOIN                                  MERGE JOIN을 하기 위한 SORT

                                             ORDER BY                              ORDER BY를 위한 SORT

TABLE ACCESS                  FULL                                  전체 테이블을 스캔한다.

                                             CLUSTER                               CLUSTER를 액세스 한다.

                                             HASH                                  키값에 대한 해쉬 알고리즘을 사용(버전 7에서만)

                                             BY ROWID                              ROWID를 이용하여 테이블을 추출한다.

UNION                                                                              두 집합의 합집합을 구한다.(중복없음)

                                                                                           항상 전체 범위 처리를 한다.

UNION ALL                                                                          두 집합의 합집합을 구한다.(중복가능)

                                                                                           UNION과는 다르게 부분범위 처리를 한다.

VIEW                                                                               어떤 처리에 의해 생성되는 가상의 집합에서 추출한다.(주로 서브쿼리에 의해 수행된 결과)

출처 : http://movestar.egloos.com/1867475

테스트 서버라 서버 안내리고 그냥 해봤는데 이상 없이 잘 되는 군요..^^

오라클을 오라클 공식문서 (quick install guide for linux)를 따라 설치하게 되면

em 화면에서 버튼이 아래 처럼 깨져있다.


http://imagesearch.naver.com/search.naver?where=idetail&rev=4&query=em%20%B1%FA%C1%FC&from=image&ac=1&sort=0&res_fr=0&res_to=0&merge=0&spq=0&start=1&a=pho_l&f=tab&r=1&u=http%3A%2F%2Fcafe.naver.com%2Focmkorea%2F2123 에서 펌 (한글 버튼 깨짐 문제를 해결하기전에 찍은 스샷이 없어서... 퍼왔습니다)


내 작업환경은 IDC에 오라클 서버가 있고 (linux), 내 컴퓨터는 공유기에 연결되어 내부 아이피를 사용하는 windows XP에 cygwin을 깔린 상태이다.

내부아이피를 사용하므로, 공유기에서 내 컴퓨터의 Xserver 포트(6000)로 포드포워딩을 해줘야 한다.

cygwin X server를 뛰우고, X term을 열여서 외부에서 cygwin X server로 들어오는 접속이 가능 하도록 설정 해준다.


이제 원격 오라클 서버에 oracle 계정으로 접속해서 (위 cygwin 터미널 또는 다른 프로그램(putty 등)

# export DISPLAY=내공유기IP:0.0

# (오라클 설치 파일이 있는 디렉토리 에서) ./runinstaller -addLangs $*

위와 같이 실행하면 원격 오라클 서버에 설치되는 과정을 내 컴퓨터에서 확인할 수 있다.



Next 버튼을 클릭하다보면 아래와 같은 화면이 뜨는데, 여기서 "Korean"을 선택해서 추가하면된다.



이게 끝이 아니다.

오라클을 custom으로 설치하는 과정에서 Korean을 추가해주면 ALBANWTK.TTF 파일이 생성되는데,

이미 설치된 오라클에서는 파일이 생성되지 않는다.

ALBAMWTK.TTF 파일을 (아래 첨부)

/u01/app/oracle/product/11.1.0/db_1/jdk/jre/lib/fonts/    디렉토리에 넣어주고,

/u01/app/oracle/product/11.1.0/db_1/oc4j/j2ee/oc4j_applications/applications/em/em/cabo/images/cache/ko/ 에 있는 파일들을 모두 지운다. (깨진 버튼 이미지들)

em을 종료시킨후에 (# emctl stop dbconsole)
재시작하고 (# emctl start dbconsole)
 em주소를 치고 들어가면,

정상적인 한글 버튼을 볼 수 있다.

┌──────────────┬─────────────────────┬────────┐
│구분 │ 설명 │ 예 │
├──────────────┼─────────────────────┼────────┤
│DML │데이터를 조회하거나 변경 │insert,update │
│(Data Manipulation Language)│ │delete,select │
│ │ │commit,rollback │
├──────────────┼─────────────────────┼────────┤
│DDL │데이터의 구조를 정의 │create,drop │
│(Data Definition Language) │ │alter,rename, │
│ │ │truncate │
├──────────────┼─────────────────────┼────────┤
│DCL │데이터베이스 사용자에게 부여된 권한을 정의│grant,revoke │
│(Data Control Language) │ │ │
└──────────────┴─────────────────────┴────────┘

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ACCOUNT TABLE
BONUS TABLE
DEPARTMENT TABLE
DEPT TABLE
EMP TABLE
EMPLOYEE TABLE
RECEIPT TABLE
SALGRADE TABLE

SQL> desc emp -- desc(ribe)는 테이블 정의를 나타내는 명령어

이름 널? 유형
----------------------------------------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)


▶ 명령어 버퍼
-- SQL *Plus는 명령어 버퍼(command buffer)에서 현재의 SQL문을 보관한다.
-- 이 명령어 버퍼의 각각의 줄은 번호가 부여되어 있다.
-- 사용자는 명령어 버퍼의 내용을 편집하고, 호출하고 저장할 수 있으며,
-- SQL*Plus의 프롬프트에 list나 그의 축약표현인 L을 입력하여 버퍼의 내용을 나타내게 할 수 있다.


--------------------------------------------------------------------------------
명령어 설명
--------------------------------------------------------------------------------
L(LIST) SQL명령어 버퍼의 내용을 나열
L(LIST) n SQL명령어 버퍼의 내용중 n번째줄만 나열
L(LIST) m n SQL명령어 버퍼의 내용중 m번째 부터 n번째줄까지 나열
R(RUN) SQL명령어 버퍼에 있는 SQL문을 실행시킴
N(N은 숫자) N을 편집될 현재의 줄로 만듦
I(INSERT) 현재의 줄 다음에 새로운 줄을 삽입
새로운 줄이 현재의 줄이 됨
DEL(DELETE) 현재의 줄을 삭제
DEL n n번째 줄을 삭제
DEL m n m번째 부터 n번째 까지 줄을 삭제
A(APPEND) text 현재의 줄에 텍스트를 첨가
C(CHANGE)/string1/string2/ 문자열1(string1)의 내용을 문자열2(string2)로 변경함
CL(CLEAR) BUFF(BUFFER) SQL명령어 버퍼에 있는 SQL문을 삭제
CL(CLEAR) SCR(SCREEN) SQL*Plus 에디터 화면을 지움
--------------------------------------------------------------------------------

SQL> select empno,ename
2 from emp
3 where sal > (select avg(sal) from emp);

EMPNO ENAME
---------- ----------
7566 JONES
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7902 FORD

6 개의 행이 선택되었습니다.


1. 방금했던 질의의 내용을 다시 나타내게 하고 싶다면 즉, 현재 버퍼에 있는 내용을 나타내 보는것

SQL> l
1 select empno,ename
2 from emp
3* where sal > (select avg(sal) from emp)


2. 이번에는 평균 급여보다 적게 받는 사원들을 알고 싶어서 3번째 줄을 편집하고자 한다면 SQL프롬프트에서 3을 입력한다.

SQL> 3
3* where sal > (select avg(sal) from emp)

3. 3번째 줄이 현재의 줄이 되었고(라인 번호 옆에 * 표시가 있는 곳이 현재의 줄이다), '>'을 '<'로 변경하기 위해서
change명령(축약 c)을 사용한다.
SQL> c/>/<
3* where sal < (select avg(sal) from emp)

4. 다시 현재 버퍼의 내용을 확인해본다.
SQL> l
1 select empno,ename
2 from emp
3* where sal < (select avg(sal) from emp)

5. 이 질의 결과가 사원들의 이름순으로 정렬되기를 원한다. 그래서 현재 버퍼의 내용의 끝에 새로운 줄을 입력하기 위해서
input명령(축약어 i)을 사용한다.
SQL> i
4 order by ename
5

6. 다시 현재 버퍼의 내용을 확인한다.
SQL> l
1 select empno,ename
2 from emp
3 where sal < (select avg(sal) from emp)
4* order by ename

7. 그런데, 이들의 구체적인 급여도 알고 싶어서 첫번째 줄의 뒤에 찾는 컬럼 sal을 추가해야 한다.
먼저 위의 버퍼 내용에서는 현재의 줄이 4번째 줄이므로, 첫번째 줄로 현재의 줄을 바꾸기 위해서 1을 입력한다.
SQL> 1
1* select empno,ename

8. ,sal을 이 줄의 뒤에 추가시키기 위해 append명령(축약 a)을 사용한다.
SQL> a ,sal
1* select empno,ename,sal

9. 다시 현재 버퍼의 내용을 확인한다.
SQL> l
1 select empno,ename,sal
2 from emp
3 where sal < (select avg(sal) from emp)
4* order by ename

10. 현재 버퍼에 있는 SQL문을 run 이나 /을 입력하여 실행시킨다.
SQL> /

EMPNO ENAME SAL
---------- ---------- ----------
7876 ADAMS 1100
7499 ALLEN 1600
7900 JAMES 950
7654 MARTIN 1250
7934 MILLER 1300
7369 SMITH 800
7844 TURNER 1500
7521 WARD 1250

8 개의 행이 선택되었습니다.


11. 편집명령어 ==> ed

SQL> select * from 사원
2 where 부서펀호=20;
where 부서펀호=20
*
2행에 오류:
ORA-00904: 열명이 부적합합니다


SQL> ed

select * from 사원
where 부서번호=20 ==> 메모장에서 펀을 번으로 수정한 후 alt+f , x , 엔터
/

file afiedt.buf(이)가 기록되었습니다

1 select * from 사원
2* where 부서번호=20

SQL> / ==> /으로 실행함



▶------ SQL문을 파일로 저장하기 -------◀

★★★
1. save 명령어를 사용하여 명령어 버퍼의 현재 내용만을 사용자가 명시하는 파일로 저장한다.
기본적으로 save 명령어는 .sql 이라는 확장자를 사용한다. 사용자는 다른 파일 확장자를 사용할 수 있다.
save 명령어는 자동적으로 덮어쓰기를 하지 않으므로
기존의 파일 위에 덮어쓰기를 하려면 replace 라는 옵션을 사용해야 한다.

SQL> l
1 select empno,ename,sal
2 from emp
3 where sal < (select avg(sal) from emp)
4* order by ename

SQL> save c:\test\lowsal
file c:\test\lowsal(이)가 생성되었습니다

SQL> save c:\test\lowsal
SP2-0540: "c:\test\lowsal.sql" 파일은 이미 존재합니다.
"SAVE 파일명[.ext] REPLACE"을 사용합니다.

SQL> save c:\test\lowsal replace
file c:\test\lowsal(이)가 기록되었습니다

★★★
2. get을 사용하여 save 명령어를 사용하여 파일로 저장된 명령어 버퍼의 내용을 조회하고, r을 입력하여 SQL문을 실행시킨다.

SQL> get c:\test\lowsal
1 select empno,ename,sal
2 from emp
3 where sal < (select avg(sal) from emp)
4* order by ename
SQL> r
1 select empno,ename,sal
2 from emp
3 where sal < (select avg(sal) from emp)
4* order by ename

EMPNO ENAME SAL
---------- ---------- ----------
7876 ADAMS 1100
7499 ALLEN 1600
7900 JAMES 950
7654 MARTIN 1250
7934 MILLER 1300
7369 SMITH 800
7844 TURNER 1500
7521 WARD 1250

8 개의 행이 선택되었습니다.


★★★
3. 출력을 파일로 저장하기
spool 명령어는 질의 결과를 파일로 저장해주는 것이다.
spool off 명령어는 스풀링을 중단하는 SQL *Plus 명령어이다.

SQL> spool c:\test\spool_1
SQL> select empno,ename,sal
2 from emp
3 where sal < (select avg(sal) from emp)
4 order by ename;

EMPNO ENAME SAL
---------- ---------- ----------
7876 ADAMS 1100
7499 ALLEN 1600
7900 JAMES 950
7654 MARTIN 1250
7934 MILLER 1300
7369 SMITH 800
7844 TURNER 1500
7521 WARD 1250

8 개의 행이 선택되었습니다.

SQL> select empno,ename,sal
2 from emp
3 where sal = (select max(sal) from emp);

EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000

SQL> spool off

============================================================
c:\test\spool_1.lst 파일을 메모장에서 열어 보았을 때의 내용
SQL> select empno,ename,sal
2 from emp
3 where sal < (select avg(sal) from emp)
4 order by ename;

EMPNO ENAME SAL
---------- ---------- ----------
7876 ADAMS 1100
7499 ALLEN 1600
7900 JAMES 950
7654 MARTIN 1250
7934 MILLER 1300
7369 SMITH 800
7844 TURNER 1500
7521 WARD 1250

8 개의 행이 선택되었습니다.

SQL> select empno,ename,sal
2 from emp
3 where sal = (select max(sal) from emp);

EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000

SQL> spool off
============================================================

★★★
4. 스크립트를 실행하기

c:\test\script1.sql 이라는 파일로 아래의 내용을 저장한다.

select empno,ename,job,sal
from emp
where job='CLERK';

SQL> @ c:\test\script1.sql

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7934 MILLER CLERK 1300

SQL> start c:\test\script1.sql

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7934 MILLER CLERK 1300


★★★
5. 출력의 포맷팅
SQL *Plus 명령어를 통해서 사용자가 형식, 총계와 소계를 명시할 수 있고 반복되는 값을 통제할 수 있다.
포맷팅 명령어는 임시적이다. 이것들은 동일한 SQL *Plus 세션 기간중 처리되는 SQL문에 대해서만 그 효력을 유지한다.

※ SQL*Plus 세션이란?
-- SQL *Plus에 연결할 때부터 SQL *Plus에서 연결을 끊을 때까지 발생하는 명령어와 그에 대한 반응을 말한다.

------------------------------------------------------------------------------------------------
명령어 목적
------------------------------------------------------------------------------------------------
col[umn] 컬럼의 옵션 컬럼의 형식을 바꾼다.
tti[tle] [문장|off|on] 보고서 각 페이지 상단의 머릿글을 나타낸다.
bti[tle] [문장|off|on] 보고서 각 페이지 하단의 머릿글을 나타낸다.
bre[ak] [on 반복을 배제할 리스트] 라인을 넘겨 중복값을 통제한다.
comp[ute] [함수 of{컬럼명} on {컬럼명}] 합계를 계산한다.
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
포맷 지정 요소 내용
------------------------------------------------------------------------------------------------
An (n은 폭을 나타내는 숫자) 문자와 날짜 컬럼에 대해서 n폭 만큼 출력 설정
9 자리수 표현
0 앞에 0을 붙임
$ 달러 사인 표시
L 국내 통화 표시
. 소숫점의 위치
, 천 단위 구분자
------------------------------------------------------------------------------------------------

1). emp 테이블에서, 사원들의 급여를 세 자리마다 콤마를 찍고 앞에 달러 사인($)을 표시하고 싶다면 다음과 같이 한다.

SQL> col sal format $9,999
SQL> select empno, ename, sal
2 from emp;

EMPNO ENAME SAL
---------- ---------- -------
7369 SMITH $800
7499 ALLEN $1,600
7521 WARD $1,250
7566 JONES $2,975
7654 MARTIN $1,250
7698 BLAKE $2,850
7782 CLARK $2,450
7788 SCOTT $3,000
7839 KING $5,000
7844 TURNER $1,500
7876 ADAMS $1,100

EMPNO ENAME SAL
---------- ---------- -------
7900 JAMES $950
7902 FORD $3,000
7934 MILLER $1,300

14 개의 행이 선택되었습니다.


2). 문자 컬럼의 경우, 사용자는 Format 명령어를 사용하여 컬럼의 넓이를 명시할 수 있다.
부서명을 한 줄에 다섯글자만 표현하고 다음줄에 나타내는 보고서를 만든다면 다음과 같이 한다.

SQL> col dname format a5
SQL> select * from dept;

DEPTNO DNAME LOC
---------- ----- -------------
10 ACCOU NEW YORK
NTING

20 RESEA DALLAS
RCH

30 SALES CHICAGO
40 OPERA BOSTON
TIONS

3). 위의 결과를 보기에 좋지 않은 형식이다. 그래서 포맷지정을 다시 원상복귀 시키기 위해서
현재의 포맷지정을 clear 옵션을 사용하여 지운다.
SQL> col dname clear
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


4). clear 옵션을 사용하지 않으면, SQL*Plus 세션을 끝낼때 까지 지정한 포맷형식이 계속 출력에 이용된다.
이번에는 부서의 지역명을 7자리의 포맷으로 맞추어 본다.

SQL> col loc format a7
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------
10 ACCOUNTING NEW YOR
K

20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


5). 사용자는 NEW YOR와 K처럼 단어 중간이 잘리는 것을 원치 않을 수도 있다.
단어가 토막나는 것을 막기 위하여, column명령어에 word_wrap을 추가한다.

SQL> col loc format a7 word_wrap
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------
10 ACCOUNTING NEW
YORK

20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


6). 워드래핑(Wrapping) 대신에, 사용자는 한 컬럼의 내용 끝을 잘라버리기 위하여
trunc 명령어를 사용할 수 있다.

SQL> col loc format a7 trunc
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------
10 ACCOUNTING NEW YOR
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

※ column 명령어로 컬럼의 형식을 설정한다면, SQL*Plus는 컬럼들이 다른 테이블에 있을지라도,
동일한 이름을 가진 모든 컬럼에 대하여 그 형식을 사용하게 된다.
사용자는 각각의 select문 앞에 다른 형식으로 column명령어를 재실행하거나,
Alias를 명시하여 각각의 컬럼명이 유일하게 할 수 있다.

7). 각 사원의 연봉을 구하기 위한 다음의 예는 컬럼 수식 sal*12에 대해 annual_salary라는 별칭을 사용한다.

SQL> select empno,ename,
2 sal*12 annual_salary
3 from emp;

EMPNO ENAME ANNUAL_SALARY
---------- ---------- -------------
7369 SMITH 9600
7499 ALLEN 19200
7521 WARD 15000
7566 JONES 35700
7654 MARTIN 15000
7698 BLAKE 34200
7782 CLARK 29400
7788 SCOTT 36000
7839 KING 60000
7844 TURNER 18000
7876 ADAMS 13200

EMPNO ENAME ANNUAL_SALARY
---------- ---------- -------------
7900 JAMES 11400
7902 FORD 36000
7934 MILLER 15600

14 개의 행이 선택되었습니다.

8). 사용자가 컬럼의 표제로 한 단어 이상을 사용하거나 대소문자를 반드시 구분하고자 한다면,
컬럼의 표제를 " "로 묶어야 한다. 이와 같은 경우에, Annual 과 Salary라는 단어 사이에 _를 사용하지 않아도 된다.

SQL> select empno,ename,
2 sal*12 "Annual Salary"
3 from emp;

EMPNO ENAME Annual Salary
---------- ---------- -------------
7369 SMITH 9600
7499 ALLEN 19200
7521 WARD 15000
7566 JONES 35700
7654 MARTIN 15000
7698 BLAKE 34200
7782 CLARK 29400
7788 SCOTT 36000
7839 KING 60000
7844 TURNER 18000
7876 ADAMS 13200

EMPNO ENAME Annual Salary
---------- ---------- -------------
7900 JAMES 11400
7902 FORD 36000
7934 MILLER 15600

14 개의 행이 선택되었습니다.


9). format 명령어로 컬럼의 표제를 변경하기 위해서 HEADING 옵션을 사용하는 방법이다.

SQL> col dname format a15 heading "Department Name"
SQL> select * from dept;

DEPTNO Department Name LOC
---------- --------------- -------
10 ACCOUNTING NEW YOR
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

※ format 명령어로 컬럼 헤딩을 변경할 때 변경하려는 헤딩의 길이를 충분히 포함할 정도의 폭 넓이를 지정해 줘야 한다.
지정 폭이 충분치 않으면 변경이 제대로 되지 않고, 자릿수에 맞춰 짤리게 된다.
즉, 아래의 예에서는 "Department Name"이라는 컬럼 헤딩이 a10 자릿수 때문에 "Department"까지만 나타난다.

SQL> col dname format a10 heading "Department Name"
SQL> select * from dept;

DEPTNO Department LOC
---------- ---------- -------
10 ACCOUNTING NEW YOR
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

★★★
10). 총계와 소계
오라클 자체내에서도 총계를 계산하기 위하여 sum함수를 내장하고 있지만,
SQL*Plus도 소계, 평균과 총계를 포함하는 요약 줄을 출력하기 위한 break 와 compute 명령어를 제공하고 있다.

comp[ute] 함수(avg,count,max,min,std,sum,var) [lab[el] 텍스트]...
of 계산을 수행할 대상 컬럼명
on 구분이 되는 (break에 사용되는) 컬럼명, 식, 별칭
report

compute 명령에 사용되는 함수
----------------------------------------------
함수 설명
----------------------------------------------
avg 평균값
cou[ut] Null이 아닌 값의 갯수
max[imum] 최대값
min[imum] 최소값
std 표준편차
sum 값의 합계
var[iance] 분산값
----------------------------------------------

emp 테이블의 부서별로 급여 합계를 구하고, 보고서 끝에는 전체 합계를 구하는 보고서를 작성하고 싶다.
부서번호와 사원번호 순서로 정렬을 한다. 부서별 합계를 구한 후에 한줄을 건너뛰게 하고,
부서번호가 중복해서 인쇄되는 것을 막고자 한다.

10-1. 먼저 deptno별로 한 줄을 건너 뛰게 하기 위해서 다음과 같이 한다.
SQL> break on deptno skip 1 on report
10-2. 부서별 급여 합계를 구하고 라벨을 'Subtotal'로 하기 위해선 다음과 같이 한다.
SQL> compute sum label 'Subtotal' of sal on deptno
10-3. 보고서 끝 부분에 한 번 전체 합계를 구해서 라벨을 "Grand Total"로 하기 위해선 다음과 같이 한다.
SQL> compute sum label 'Grand Total' of sal on report
10-4. 그리고, 앞에서 지정한 급여 sal의 포맷 '$9,999'는 합계액에는 부족하므로 늘리도록 한다.
SQL> col sal format $999,999
10-5. 보고서 형식 준비가 끝났으므로, 적당한 질의를 실행시켜 본다.
SQL> select deptno, empno, ename, sal
2 from emp
3 order by deptno, empno;

DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ---------
10 7782 CLARK $2,450
7839 KING $5,000
7934 MILLER $1,300
********** ---------
Subtotal $8,750

20 7369 SMITH $800
7566 JONES $2,975
7788 SCOTT $3,000
7876 ADAMS $1,100
7902 FORD $3,000

DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ---------
********** ---------
Subtotal $10,875

30 7499 ALLEN $1,600
7521 WARD $1,250
7654 MARTIN $1,250
7698 BLAKE $2,850
7844 TURNER $1,500
7900 JAMES $950
********** ---------
Subtotal $9,400

DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ---------

---------
Grand Tota $29,025

14 개의 행이 선택되었습니다.


11). SQL*Plus 출력에 제목달기
---- 다른 포맷팅 명령어와 관련되어, SQL*Plus는 제목을 만들기 위해 두개의 명령어를 제공한다.
ttitle은 각 페이지의 상위에 제목을 명시하기 위한 것이며, btitle은 각 페이지의 하단 부분에
제목을 명시하기 위한 명령어이다.
다음의 예에서, ttitle과 btitle은 제목, 날짜와 현재의 페이지 번호를 나타낸다.

11-1. 우선 보기 좋은 출력을 위해서 적당한 페이지 사이즈와 라인 사이즈를 지정한다.

SQL> set pages 35 ==> pagesize(축약어 pages)는 한 페이지에서의 줄의 수
SQL> set line 50 ==> linesize(축약어 line)는 출력의 한줄에 나타나는 문자의 최대수
SQL> ttitle left '2001-12-17' center 'EMP table'|'Salary Report' - 공백-(하이픈) : SQL*Plus에서 긴 명령을 다음 줄에 쓰기 위해서
> right 'Page' format 99 sql.pno ==> sql.pno는 현재의 페이지번호를 나타내는 SQL*Plus 변수
SQL> btitle '예제' ==> ttile과 btitle에서 위치를 따로 지정하지 않으면 기본적으로 가운데(center)에 위치하게 된다.
SQL> select deptno,empno,ename,sal
2 from emp
3 order by deptno,empno;


2001-12-17 EMP table|Salary Report> Page 1
DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ---------
10 7782 CLARK $2,450
7839 KING $5,000
7934 MILLER $1,300
********** ---------
Subtotal $8,750

20 7369 SMITH $800
7566 JONES $2,975
7788 SCOTT $3,000
7876 ADAMS $1,100
7902 FORD $3,000
********** ---------
Subtotal $10,875

30 7499 ALLEN $1,600
7521 WARD $1,250
7654 MARTIN $1,250
7698 BLAKE $2,850
7844 TURNER $1,500
7900 JAMES $950
********** ---------
Subtotal $9,400

---------
Grand Tota $29,025





예제

14 개의 행이 선택되었습니다.


★★★ SQL*Plus 시스템 변수 ★★★

SQL*Plus는 각각의 SQL*Plus 세션의 특성을 통제하는 많은 시스템 변수를 채택하고 있다.
예를 들어, SET명령어는 특정 값으로 시스템 변수를 설정하며, SHOW명령어는 시스템 변수의
현재 설정을 볼 수 있다. 또한 사용자는 모든 시스템 변수 설정을 보기 위하여 SHOW ALL명령어를 사용한다.

SQL> show all

▶ autocommit
autocommit(축약어 auto)은 SQL*Plus의 트랜잭션을 실행완료(commit)하는 방법을 통제한다.
autocommit이 on으로 설정되어 있으면, SLQ*Plus는 각각의 SQL문이 처리된 후에 실행완료된다.
즉, autocommit이 on으로 설정된 상태에서, 사용자가 실수로 테이블에서 일부 레코드들을 삭제했다면
사용자는 트랜잭션을 rollback 할 수 없다.
사용자는 일반적으로 autocommit을 off로 설정하는 것이 좋다.

SQL> show autocommit
autocommit OFF

▶ echo
SQL*Plus가 처리하는 SQL문을 반복하지 않으려면, echo를 off로 설정한다. 일반적으로 결과물을 만들어낸 SQL문 없이,
원하는 결과문만을 보고싶은 보고서를 준비하는 경우에 off로 설정한다.

SQL> show echo
echo OFF

▶ feedback
시스템 변수 feedback(축약어 feed)은 질의가 반환하는 레코드 수를 SQL*Plus가 나타내는 시기를 통제한다.
feedback의 기본적인 설정은 6이다. 즉, 질의가 6개 이상의 레코드를 조회하면, SQL*Plus는 레코드의 수를 보여준다.
질의가 6개 미만을 반환하면, SQL*Plus는 이 feedback 기능을 제공하지 않는다.
사용자는 feedback을 유발하는 레코드의 수를 설정할 수 있다.

SQL> show feedback
6 또는 그 이상의 행에 대한 FEEDBACK ON
SQL> select * from emp where deptno=10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 2572.5
10

7839 KING PRESIDENT 5500
10

7934 MILLER CLERK 7782 920
10


SQL> set feedback 2
SQL> select * from emp where deptno=10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 2572.5
10

7839 KING PRESIDENT 5500
10

7934 MILLER CLERK 7782 920
10


3 개의 행이 선택되었습니다. <--- feedback이 나타나는 것을 볼 수 있다.

▶ heading
--- heading(축약어 head)은 기본적으로 SQL*Plus는 컬럼 표제를 나타낸다.
표제를 보지 않으려면, heading을 off로 설정하면 된다.

SQL> show heading
heading ON
SQL> set heading off
SQL> select * from dept;

10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

▶ linesize
--- linesize(축약어 line)는 출력의 한 줄에 나타나는 문자의 최대수를 통제한다.

SQL> show line
linesize 80
SQL> set line 150

그러나 사용자가 linesize를 증가시키면, 메뉴바에서 '옵션--환경'을 선택한 후
윈도우 스크린 버퍼의 너비도 늘려야 한다.

SQL> select * from emp where deptno=10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 2572.5 10
7839 KING PRESIDENT 5500 10
7934 MILLER CLERK 7782 920 10

▶ long
사용자가 한 컬럼에 2,000자 이상을 반드시 저장해야 한다면, 사용자는 오라클의 long 데이터형을 사용하여
컬럼을 정의해야 할 것이다. SQL*Plus는 사용자가 long 시스템 변수를 늘리지 않는 한,
long 컬럼의 80자 이상은 나타내지 않을 것이다. 사용자가 예를 들어 5,000자 까지 나타내야 한다면,
long을 5,000으로 설정해야 한다. 이밖에도 사용자는 arraysize(축약어 array)를 1로 줄여야 한다.
arraysize는 데이트베이스의 데이터 인출 크기를 가리키는 환경 변수이다.

SQL> show long
long 80
SQL> show arraysize
arraysize 15

▶ pagesize
pagesize(축약어 pages)는 한 페이지에서의 줄의 수를 정의하며, 컬럼의 표제와 페이지 제목이 표현되어야
할 시기를 결정한다. 사용자가 모든 제목과 컬럼 표제를 없애려면, pagesize를 0으로 설정한다.

SQL> show pagesize
pagesize 14
SQL> set pagesize 0
SQL> select * from dept;
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> set pages 24

▶ pause
사용자가 많은 레코드를 조회하는 질의를 실행하면, SQL*Plus의 기본적인 행동은 스크린에 그 결과를 쉴새 없이 보낸다.
사용자가 pause(축약어 pau)를 on 또는 문자열로 설정하면, SQL*Plus는 사용자가 리턴키를 누른 후에 다음 결과를 스크린에
계속 보내게 된다.

SQL> select * from user_tables;

SQL> show pause
PAUSE는 OFF
SQL> set pause on
SQL> select * from user_tables;

▶ time
사용자는 time(축약어 ti)을 on으로 설정하여, SQL*Plus 프롬프트에 현재 시각을 포함시킬 수 있다.
사용자는 출력을 파일로 spooling 할 때, 이 설정이 유용하다는 것을 알게 될 것이다.

SQL> show time
time OFF
SQL> set time on
12:08:37 SQL> set time off
SQL>

▶ timing
timing(축약어 timi)은 각각의 SQL명령어에 대한 게시(timing) 통계의 표현을 on 또는 off로 설정한다.
SQL> show timing
timing OFF
SQL> set timing on
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

경 과: 00:00:00.10

SQL> set timing off


▶ 대입변수를 사용한 질의 작성하기

-- SQL*Plus 내에서, 사용자는 SQL문에서 대입 변수를 참조할 수 있다.
대입 변수로 사용자는 다른 값으로 동일한 SQL문을 재실행할 수 있다.
한 개 또는 두개의 &로 사용자 변수명 앞에 두어 대입 변수를 표시한다.

SQL> select empno,ename,job,sal
2 from emp
3 where sal > &sal_value;
sal_value의 값을 입력하십시오: 3000
구 3: where sal > &sal_value
신 3: where sal > 3000

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5500
7566 JONES MANAGER 3123.75
7658 CHAN ANALYST 3450

그런데, sal이 4000 보다 큰 사원들도 결과로 얻고 싶다. 이 때에는 질의문을 두 번 작성하지 않고,
대입변수를 사용하면 간단히 해결할 수 있다.

SQL> /
sal_value의 값을 입력하십시오: 4000
구 3: where sal > &sal_value
신 3: where sal > 4000

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5500

<예제2>
SQL> select empno,ename,job,sal
2 from emp
3 where sal > &sal_value
4 and lower(job) like '%&job_value%'; -- 문자열 변수에는 반드시 ' '를 한다.
sal_value의 값을 입력하십시오: 3000
구 3: where sal > &sal_value
신 3: where sal > 3000
job_value의 값을 입력하십시오: manager
구 4: and lower(job) like '%&job_value%'
신 4: and lower(job) like '%manager%'

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7566 JONES MANAGER 3123.75


▶ 사용자 변수를 설정하는 다른 방법
SQL*Plus는 사용자 변수를 설정하는 define과 accept 명령어가 있다.
define 명령어로 사용자는 사용자 변수를 만들고 문자 값을 여기에 부여할 수 있다.
특히, define명령어는 char 데이터형을 정의하고 싶을 때 많이 사용한다.

SQL> define job_value = 'MANAGER'
SQL> select empno,ename,job,sal
2 from emp
3 where upper(job) like '%&job_value%';
구 3: where upper(job) like '%&job_value%'
신 3: where upper(job) like '%MANAGER%'

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7782 CLARK MANAGER 2572.5
7566 JONES MANAGER 3123.75

define으로 정의된 변수는 undefine 명령을 사용할 때까지 그 상태를 유지하게 된다.

SQL> undefine job_value

=============================================================
d:\g\db\oracle\new_mine\대입변수.sql의 내용

accept job_value prompt '직업명을 입력하세요: '

select empno,ename,job,sal
from emp
where lower(job) = '&job_value'
/
=============================================================

SQL> @ d:\g\db\oracle\new_mine\대입변수.sql
직업명을 입력하세요: clerk
구 3: where lower(job) = '&job_value'
신 3: where lower(job) = 'clerk'

EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7934 MILLER CLERK 920


원문: http://blog.naver.com/lyjiny/60002865980

오라클(Oracle)을 사용하여 다건조회(페이징) 처리를 해야하는 경우가 종종 있습니다.

물론 SQL 쿼리는 여러가지 방식으로 만들수 있고
다양한 방식이 존재하기에 어느하나가 정답이라고 할 수는 없습니다.

다만 실제 업무에서 개발중인 노하우를 공유하는 일환으로
다건조회(페이징) 처리시 DB의 성능 (대부분은 검색 속도와 관련된 부분이겠지요..)과 관련한 최적화된 쿼리 방식을 알려드리도록 하겠습니다.

이는 실제 DBA에게서 권고 받은 것이기 때문에 검증을 거쳤다고 할 수 있습니다.

자 그럼 아래를 참고하시어 최적화된 다건조회 쿼리 작성에 도움이 되시길 바랍니다.

DBA의 말에 따르면 위와같은 방식으로
페이징 처리를 해주는 것이 DB(데이타베이스)의 성능향상에 도움이 된다더군요.

실제 예제는 아래처럼 사용됩니다.

설명을 덧붙이자면
페이징시  메인 키가 되는 A >= :io_nx_a 의 인덱스서칭 시간만큼
OR을 동반한 올 시퀀스 서치시간을 줄여줄 수 있기 때문에 실제로 DB 검색시 속도향상 효과를 기대할 수 있습니다.

유용하게 사용하시기 바랍니다. ^^

- 유니온(UNION)

SELECT * FROM A
UNION (ALL)
SELECT * FROM B

A 와 B 의 테이블의 해당하는 컬럼들을 연결하여 보여줍니다.
OR과 유사하다고 생각하면 되며 실제로 OR을 사용하는 쿼리를 UNION ALL으로 대체시 수행속도를 향상할 수 있습니다.
UNION은 중복된 데이타를 제거하며 UNION ALL은 중복된 데이타를 모두 보여 줍니다.
가능하다면 UNION ALL을 사용하는 것이 좋습니다.
(DISTINCT를 사용하는 것보다는 UNION만 쓰는것이 더 효율적이라 생각됩니다.)


길지만 하나의 조회 쿼리문 입니다. (이때까지 사용해본 조회 쿼리 중에서 가장 길군요.^^)

조회시 유용한 쿼리가 많이 포함되어 있으므로 천천히 읽어보시고
유용하게 활용하시기 바랍니다.

보시고 이해가 가지 않는 부분은 댓글로 질문해 주시면 성의껏 답변해 드리겠습니다.  

개별적인 값일 경우 A <> B 를 하면 C에서 A != B 와 같은 뜻으로 동작합니다.
다만 개별값이 아닌 SELECT를 이용한 조회된 그룹 혹은 조회값들이라면 아래와 같이 NOT EXISTS 쿼리문을 WHERE 절에 사용해 줌으로써 여집합의 결과를 얻을 수 있습니다.


위와 같은 역할을 하는 IS NULL을 사용한 다른 사용법입니다.


SELECT를 사용한 단건 혹은 다건조회시 여러가지 옵션을 제공하며 그 옵션을 체크하여 적용시키는 경우.
즉, 여러가지 조건을 동시에 만족하는 것만의 결과값을 구해야 하는 경우 CASE 함수를 활용하여 구할 수 있습니다.

먼저 CASE 함수의 기본 형식을 알아두세요.

아래는 CASE WHEN 조건 분기를 사용하여 여러조건을 동시에 만족하는(교집합) 특정 값들을 얻고, NOT EXISTS 를 써서 특정 결과를 뺀(여집합) 결과를 구하는 쿼리 예제 입니다.


유용하게 사용하시기 바랍니다.