먹고산다는것2008.12.21 22:19

UTL_FILE 패키지를 사용하여 PL/SQL에서 파일의 입출력을 실시할 수 있다.
PL/SQL에는 SQL*Plus의 SPOOL에 해당하는 간략화된 명령어가 없으므로 큰 로그를 출력하고 싶은 경우에는 UTL_FILE 패키지를 사용하든지 혹은 테이블을 사용한다.

표준 출력에는 DBMS_OUTPUT 패키지를 사용한다.
Oracle 8i이전이라면 초기화 파라미터 UTL_FILE_DIR를 설정해야 한다. 

 

준비작업


 

■ 디렉토리 작성

디렉토리를 작성할 경우에 OS 의 DBA 인 유저(통상은 oracle)로 디렉토리를 작성한다.

root 로 작성하는 경우에는, chown, chgrp, chmod 등에 의해 읽고 쓰기의 권한을 올바르게 설정한다.

파일의 I/O는 서버 프로세스에 의해서 행해지므로 그 프로세스의 오너(oracle)가 읽고 쓰기할 수 있어야 한다.

작성과 권한의 설정예 (root 에 의한 조작:권한만 있으면 root 일 필요는 없습니다)

# mkdir /u05/file_storage/recv_dir

# mkdir /u05/file_storage/send_dir

 

# chgrp dba /u05/file_storage/recv_dir

# chgrp dba /u05/file_storage/send_dir

 

# chown oracle /u05/file_storage/recv_dir

# chown oracle /u05/file_storage/send_dir

 

# chmod 700 /u05/file_storage/recv_dir

# chmod 700 /u05/file_storage/send_dir

 

디렉토리·오브젝트에 의한 파일 액세스


 

■ 디렉토리의 작성 CRAETE DIRECTORY

Oracle 9i 버젼부터 UTL_FILE 패키지가 CREATE DIRECTORY에 대응하게 되었다.
디렉토리의 추가에 따르는 재기동도 불필요.

  • 디렉토리 오브젝트의 작성
    DIRECTORY의 작성은 CREATE DIRECTORY 권한이 필요

         CREATE DIRECTORY recv_area AS '/u05/file_storage/recv_dir';
         CREATE DIRECTORY send_area AS '/u05/file_storage/send_dir';

  • 디렉토리에의 액세스권의 설정
    읽기 권한과 쓰기 권한은 개별적으로 처리한다.

        GRANT READ ON DIRECTORY recv_area TO user_name ;
        GRANT WRITE ON DIRECTORY send_area TO user_name ;
         --
         SELECT * FROM ALL_DIRECTORIES ;

 

■ 파일쓰기 (초기화 파라메터 사용시 )

CREATE OR REPLACE PROCEDURE RIVUS.CREATE_DIR_WRITE_SAMPLE

AS

  vHandle   UTL_FILE.FILE_TYPE;

  vDirname  VARCHAR2(250);

  vFilename VARCHAR2(250);

  vOutput   VARCHAR2(32767);

BEGIN

  vDirname  := 'SEND_AREA'; -- (주)디렉토리 오브젝트명을 대문자로 지정한다

  vFilename := 'test.txt';

  vHandle   := UTL_FILE.FOPEN(vDirname ,vFilename,'w', 32767);

  vOutput   := 'CREATE DIRECTORY 경유로의 파일출력';

  UTL_FILE.PUT_LINE(vHandle, vOutput);

  UTL_FILE.FCLOSE(vHandle);

EXCEPTION WHEN OTHERS THEN

  UTL_FILE.FCLOSE_ALL;

  RAISE;

END;

■ 파일읽기 (초기화 파라메터 사용시 )

CREATE OR REPLACE PROCEDURE RIVUS.CREATE_DIR_READ_SAMPLE

AS

  vHandle   UTL_FILE.FILE_TYPE;

  vDirname  VARCHAR2(250);

  vFilename VARCHAR2(250);

  vInput    VARCHAR2(32767);

BEGIN

  vDirname  := 'RECV_AREA';

  vFilename := 'test.txt';

  vHandle   := UTL_FILE.FOPEN(vDirname ,vFilename,'r', 32767);

 

  BEGIN

    LOOP

      UTL_FILE.GET_LINE(vHandle, vInput,32767);

      DBMS_OUTPUT.PUT_LINE(vInput);

    END LOOP;

  EXCEPTION WHEN NO_DATA_FOUND THEN

    DBMS_OUTPUT.PUT_LINE('파일의 마지막');

  END;

 

  UTL_FILE.FCLOSE(vHandle);

EXCEPTION WHEN OTHERS THEN

  UTL_FILE.FCLOSE_ALL; RAISE;

END;

/

주의사항 


  • UTL_FILE 패키지를 사용해 한 번에 입출력할 수 있는 길이는 32767(32K) 바이트 (※) 

  • OPEN 한 파일은 반드시 CLOSE 하도록 예외 처리를 해둔다.UTL_FILE.FCLOSE_ALL 를 사용하면 편리

  • RAW형을 출력할 수 있지만 줄 끝에는 OS 고유의 개행 코드가 반드시 부여된다.
    Oracle 10g 에서는 wb 에 의한 (RAW 모드) FOPEN 가 서포트되고 있으므로, 그 쪽을 사용하면 문제 없다.

이 제한에 있어서 Oracle 10g 이후가 아니면 순수한 바이너리필드를 사용할수 없다.

(※) 한 번의 기입으로 32KB 를 넘을 수 없지만, RAW 모드로의 기입에 대해서는 여러 차례에 기입을 분할하는 것으로 1행이 32KB 를 초과하는 것이 가능하다

 

◆DIRECTORY 편

UTL_FILE 패키지를 사용해 PL/SQL에서 파일의 입출력을 실시할 수 있다.Oracle 9i 이후부터는 CREATE DIRECTORY 를 사용하여 유저 단위, 읽어들이기, 쓰기의 제한이 가능하게 되었다. 또한  디렉토리를 추가하고 싶은 경우에도 Oracle 의 재기동이 필요하지 않으므로 다운 타임을 줄일 수 있다

 

준비작업


■ 디렉토리 작성

디렉토리 파일의 작성 (UTL_FILE_DIR 편과 같다)

디렉토리를 작성할 경우에 OS 의 DBA 인 유저(통상은 oracle)로 디렉토리를 작성한다.

내용은 생략 ⇒ UTL_FILE 의 사용법 (UTL_FILE_DIR 편)의 준비작업을 참조

 

디렉토리·오브젝트에 의한 파일 액세스


■ 디렉토리의 작성 CRAETE DIRECTORY

Oracle 9i 버젼부터 UTL_FILE 패키지가 CREATE DIRECTORY 에 대응하게 되었다.
디렉토리의 추가에 따르는 재기동도 불필요.

  • 디렉토리 오브젝트의 작성
    DIRECTORY의 작성은 CREATE DIRECTORY 권한이 필요

         CREATE DIRECTORY recv_area AS '/u05/file_storage/recv_dir';
         CREATE DIRECTORY send_area AS '/u05/file_storage/send_dir';

  • 디렉토리에의 액세스권의 설정
    읽기 권한과 쓰기 권한은 개별적으로 처리한다.

        GRANT READ ON DIRECTORY recv_area TO user_name ;
        GRANT WRITE ON DIRECTORY send_area TO user_name ;
         --
         SELECT * FROM ALL_DIRECTORIES ;

 

■ 파일쓰기 (초기화 파라메터 사용시 )

CREATE OR REPLACE PROCEDURE RIVUS.CREATE_DIR_WRITE_SAMPLE

AS

  vHandle   UTL_FILE.FILE_TYPE;

  vDirname  VARCHAR2(250);

  vFilename VARCHAR2(250);

  vOutput   VARCHAR2(32767);

BEGIN

  vDirname  := 'SEND_AREA'; -- (주)디렉토리 오브젝트명을 대문자로 지정한다

  vFilename := 'test.txt';

  vHandle   := UTL_FILE.FOPEN(vDirname ,vFilename,'w', 32767);

  vOutput   := 'CREATE DIRECTORY 경유로의 파일출력';

  UTL_FILE.PUT_LINE(vHandle, vOutput);

  UTL_FILE.FCLOSE(vHandle);

EXCEPTION WHEN OTHERS THEN

  UTL_FILE.FCLOSE_ALL;

  RAISE;

END;

/

■ 파일읽기 (초기화 파라메터 사용시 )

CREATE OR REPLACE PROCEDURE RIVUS.CREATE_DIR_READ_SAMPLE

AS

  vHandle   UTL_FILE.FILE_TYPE;

  vDirname  VARCHAR2(250);

  vFilename VARCHAR2(250);

  vInput    VARCHAR2(32767);

BEGIN

  vDirname  := 'RECV_AREA';

  vFilename := 'test.txt';

  vHandle   := UTL_FILE.FOPEN(vDirname ,vFilename,'r', 32767);

 

  BEGIN

    LOOP

      UTL_FILE.GET_LINE(vHandle, vInput,32767);

      DBMS_OUTPUT.PUT_LINE(vInput);

    END LOOP;

  EXCEPTION WHEN NO_DATA_FOUND THEN

    DBMS_OUTPUT.PUT_LINE('파일의 마지막');

  END;

 

  UTL_FILE.FCLOSE(vHandle);

EXCEPTION WHEN OTHERS THEN

  UTL_FILE.FCLOSE_ALL; RAISE;

END

/

주의사항 


  • UTL_FILE에서 한번에 입출력할 수 있는 길이는 32767(32K) 바이트 (※) 

  • OPEN 한 파일은 반드시 CLOSE 하도록 예외 처리를 해둔다.UTL_FILE.FCLOSE_ALL 를 사용하면 편리

  • RAW형을 출력할 수 있지만 줄 끝에는 OS 고유의 개행 코드가 반드시 부여된다.
    Oracle 10g 에서는 wb 에 의한 (RAW 모드) FOPEN 가 서포트되고 있으므로, 그 쪽을 사용하면 문제 없다.

이 제한에 있어서 Oracle 10g 이후가 아니면 순수한 바이너리필드를 사용할수 없다.

(※) 한 번의 기입으로 32KB 를 넘을 수 없지만, RAW 모드로의 기입에 대해서는 여러 차례에 기입을 분할하는 것으로 1행이 32KB 를 초과하는 것이 가능하다 

신고
Posted by 기억습작
먹고산다는것2008.01.31 20:02

/* mssql */
exec sp_addlinkedserver
 @server='Tulip',
 @srvproduct = 'mssql',
 @provider = 'sqloledb',
 @datasrc = 'Tulip',
 @provstr='',
 @catalog=''
go

exec sp_addlinkedsrvlogin 'TUlip', 'false', null, 'sa', '<sqlpass>'
go


/* Oracle */
EXEC sp_addlinkedserver
  @server = 'INSIDER'
  , @srvproduct = 'oracle'
  , @provider = 'MSDAORA'
  , @datasrc = '211.169.***.***'
Go

EXEC sp_addlinkedsrvlogin
  'INSIDER'
  , 'false'
  , NULL
  , 'scott'
  , 'tiger'
Go



/* File Linked */
-- Create File Linked Srv
exec sp_addlinkedserver FileSrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\',
null,
'Text'

-- Login At File Linked Srv
exec sp_addlinkedsrvlogin FileSrv, false, sa, null

-- Drop File Linked Srv Drop
exec sp_droplinkedsrvlogin 'FileSrv', null
exec sp_dropserver FileSrv

-- Show TxtTable File Linked Srv
exec sp_tables_ex FileSrv



use master
go


-- Linked Server를 등록한다.
EXEC  sp_addlinkedserver   
 @server='SecondInstance'
,  @srvproduct=''
,             @provider='SQLOLEDB'
,  @datasrc='snoopy\second'


-- sp_addlinkedserver의 구성을 살펴보면 Linked Server의 정보가 sysserver 테이블에 있음을 알 수 있다.
exec sp_helptext sp_addlinkedserver


-- 등록이 되었나 확인해보자.
select * from sysservers


-- 등록된 Linked Server에 접속할 수 있는 계정을 만들어야 한다.
EXEC sp_addlinkedsrvlogin 'SecondInstance', 'false', NULL, 'id', 'passwd'

-- sp_addlinkedsrvlogin의 구성을 살펴보면 계정 정보가 sysxlogins 테이블에 있음을 알 수 있다.


exec sp_helptext sp_addlinkedsrvlogin


-- 등록이 되었나 확인해보자.
select * from sysxlogins


-- 이제 등록된 Linked Server에 쿼리를 날려보자.
select * from secondinstance.pubs.dbo.sales


-- 등록된 Linked Server의 정보를 지운다.
EXEC sp_droplinkedsrvlogin 'SecondInstance',NULL


EXEC sp_dropserver 'SecondInstance', 'droplogins'

신고
Posted by 기억습작
먹고산다는것2008.01.31 20:01
Simple Statment EXECUTE IMMEDIATE <sql_statement_string>;
CREATE TABLE test (
testcol VARCHAR2(20));

INSERT INTO test VALUES ('ABC');
INSERT INTO test VALUES ('DEF');
INSERT INTO test VALUES ('xyz');
COMMIT;

SELECT * FROM test;

BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE test';
END;
/

SELECT * FROM test;

INSERT INTO test VALUES ('ABC');
INSERT INTO test VALUES ('DEF');
INSERT INTO test VALUES ('xyz');
COMMIT;

SELECT * FROM test;

DECLARE
x user_tables.table_name%TYPE := 'TEST';
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || x;
END;
/

SELECT * FROM test;
Statment With USING Clause EXECUTE IMMEDIATE <sql_statement_string_with_bind_variable>
USING <substitution_value>;
CREATE TABLE t (
mycol NUMBER(5));

BEGIN
  FOR i IN 1 .. 10000
  LOOP
    EXECUTE IMMEDIATE 'INSERT INTO t VALUES (:x)'
    USING i;
  END LOOP;
END;
/
 
Demonstrations
Simple Create Table DECLARE

x   VARCHAR2(200);

BEGIN
   x := 'CREATE TABLE xyz (col1 NUMBER(10),
         col2 VARCHAR2(20), col3 DATE)';
   EXECUTE IMMEDIATE x;
END;
/
More Complex Demo DECLARE

i   PLS_INTEGER;
x   VARCHAR2(200);

BEGIN
   SELECT COUNT(*)
   INTO i
   FROM all_tables
   WHERE table_name = 'XYZ';

   IF i = 0 THEN
      x := 'CREATE TABLE xyz(col1 NUMBER(10), col2 VARCHAR2(20), col3 DATE)';
      EXECUTE IMMEDIATE x;
   ELSE
      x := 'ALTER TABLE xyz ADD(new_col VARCHAR2(100))';
      EXECUTE IMMEDIATE x;
   END IF;
END;
/
Another Complex Demo CREATE TABLE dept_new (
department_no VARCHAR2(10),
department_name VARCHAR2(30),
location VARCHAR2(30))
TABLESPACE data_sml;

CREATE OR REPLACE PROCEDURE nds_demo (
deptnum dept_new.department_no%TYPE,
deptname dept_new.department_name%TYPE,
location dept_new.location%TYPE) IS

stmt_str VARCHAR2(100);

BEGIN
  stmt_str := 'INSERT INTO dept_new
               VALUES(:deptno, :dname, :loc)';
  EXECUTE IMMEDIATE stmt_str USING deptnum, deptname, location;
END nds_demo;
/

exec nds_demo('100', 'Accounting', 'Los Angeles, CA')

SELECT * FROM dept_new;
 
CREATE OR REPLACE PROCEDURE revmp.load_recs (
nav_lvl VARCHAR2,
esrloc VARCHAR2,
sectloc NUMBER,
ocaval VARCHAR2) IS

IClause VARCHAR2(200) := 'INSERT INTO find_recs (servord_no, rec_name, rec_street, rec_city, rec_zip, assigned_to_esr)';

SClause VARCHAR2(200) := ' SELECT DISTINCT s.servord_no, p.package_name, ';

FClause VARCHAR2(200) := ' FROM servord_package p, service_order s, feed f';

WClause VARCHAR2(300) := ' WHERE p.package_no = s.package_no AND s.servord_no = f.servord_no';

OClause VARCHAR2(100) := ' ORDER BY TO_NUMBER(servord_no)';

XString VARCHAR2(1000);
Xval PLS_INTEGER := 1;

BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE find_recs';

  IF nav_lvl = 'S' THEN
    SClause := SClause || 'p.package_street, p.package_city, p.package_zip_code,
    s.assigned_to_esr';
    WClause := WClause || '(+)';
  ELSIF nav_lvl = 'F' THEN
    SClause := SClause || 'f.feed_to_address, f.feed_to_city, ' ||
    'f.feed_to_zip_code, s.assigned_to_esr';
  ELSIF nav_lvl = 'U' THEN
    SClause := SClause || 'a.service_address, a.service_city, a.service_zip_code,
    s.assigned_to_esr';
    FClause := FClause || ', service_address a';
    WClause := WClause || ' AND s.servord_no = a.servord_no';
  END IF;

  IF esrloc IS NOT NULL THEN
    Xval := Xval + 10;
    WClause := WClause || ' AND s.esr_location = :E';
  END IF;

  IF sectloc IS NOT NULL THEN
    Xval := Xval + 100;
    WClause := WClause || ' AND f.geocode_section = :S';
  END IF;

  IF ocaval = 'O' THEN
    Xval := Xval + 1000;
    WClause := WClause || ' AND servord_compl_date IS NULL';
  ELSIF ocaval = 'C' THEN
    Xval := Xval + 1000;
    WClause := WClause || ' AND servord_compl_date IS NOT NULL';
  END IF;

  Xstring := IClause || SClause || FClause || WClause || OClause;

  IF Xval = 1 THEN
    EXECUTE IMMEDIATE Xstring;
  ELSIF Xval = 11 THEN
    EXECUTE IMMEDIATE Xstring USING esrloc;
  ELSIF Xval = 101 THEN
    EXECUTE IMMEDIATE Xstring USING sectloc;
  ELSIF Xval = 111 THEN
    EXECUTE IMMEDIATE Xstring USING esrloc, sectloc;
  ELSIF Xval = 1001 THEN
    EXECUTE IMMEDIATE Xstring;
  ELSIF Xval = 1011 THEN
    EXECUTE IMMEDIATE Xstring USING esrloc;
  ELSIF Xval = 1101 THEN
    EXECUTE IMMEDIATE Xstring USING sectloc;
  ELSIF Xval = 1111 THEN
    EXECUTE IMMEDIATE Xstring USING esrloc, sectloc;
  END IF;
  COMMIT;
END;
/
신고
Posted by 기억습작
먹고산다는것2008.01.31 20:00

Logical

사용자 삽입 이미지

데이터

비용코드 TB_MSTCODE
MST_CD MST_NM
A00001 하드웨어
A00002 소프트웨어


서브비용코드 TB_SUBCODE
SUB_CD SUB_NM
B00001 사용료
B00002 관리비


년도별비용 TB_YEARCOST
MST_CD SUB_CD YEAR COST
A00001 B00001 2002 10000
A00001 B00001 2003 20000
A00001 B00001 2004 30000
A00001 B00001 2005 40000
A00001 B00002 2002 15000
A00001 B00002 2003 25000
A00001 B00002 2004 35000
A00001 B00002 2005 45000
A00002 B00001 2002 16000
A00002 B00001 2003 26000
A00002 B00001 2004 36000
A00002 B00001 2005 46000
A00002 B00002 2002 17000
A00002 B00002 2003 27000
A00002 B00002 2004 37000
A00002 B00002 2005 47000


년도별 비용 쿼리

SELECT  D.MST_NM
     ,  D.SUB_NM
     ,  SUM(DECODE(E.YEAR, '2002', E.COST, 0)) AS COST1
     ,  SUM(DECODE(E.YEAR, '2003', E.COST, 0)) AS COST2
     ,  SUM(DECODE(E.YEAR, '2004', E.COST, 0)) AS COST3
     ,  SUM(DECODE(E.YEAR, '2005', E.COST, 0)) AS COST4
  FROM  (SELECT  MST_CD
              ,  MST_NM
              ,  SUB_CD
              ,  SUB_NM
           FROM  (SELECT  MST_CD
                       ,  MST_NM
                    FROM  TB_MSTCODE) A
              ,  (SELECT  SUB_CD
                       ,  SUB_NM
                    FROM  TB_SUBCODE) B ) D
     ,  TB_YEARCOST E
 WHERE  D.MST_CD = E.MST_CD(+)
   AND  D.SUB_CD = E.SUB_CD(+)
 GROUP  BY  D.MST_NM
     ,  D.SUB_NM
 ORDER  BY  D.MST_NM
     ,  D.SUB_NM


결과

년도별 비용
MST_NM SUB_NM 2002년 2003년 2004년 2005년
소프트웨어 관리비 17000 27000 37000 47000
소프트웨어 사용료 16000 26000 36000 46000
하드웨어 관리비 15000 25000 35000 45000
하드웨어 사용료 10000 20000 30000 40000


아래와 같이 소계와 합계를 나타내고자 한다면

소계 및 합계
MST_NM SUB_NM 2002년 2003년 2004년 2005년
소프트웨어 관리비 17000 27000 37000 47000
사용료 16000 26000 36000 46000
소계 33000 53000 73000 93000
하드웨어 관리비 15000 25000 35000 45000
사용료 10000 20000 30000 40000
소계 25000 45000 65000 85000
합계 58000 98000 138000 178000


방법1

SELECT  D.MST_NM
     ,  D.SUB_NM
     ,  SUM(DECODE(E.YEAR, '2002', E.COST, 0)) AS COST1
     ,  SUM(DECODE(E.YEAR, '2003', E.COST, 0)) AS COST2
     ,  SUM(DECODE(E.YEAR, '2004', E.COST, 0)) AS COST3
     ,  SUM(DECODE(E.YEAR, '2005', E.COST, 0)) AS COST4
  FROM  (SELECT  MST_CD
              ,  MST_NM
              ,  SUB_CD
              ,  SUB_NM
           FROM  (SELECT  MST_CD
                       ,  MST_NM
                    FROM  TB_MSTCODE) A
              ,  (SELECT  SUB_CD
                       ,  SUB_NM
                    FROM  TB_SUBCODE) B ) D
     ,  TB_YEARCOST E
 WHERE  D.MST_CD = E.MST_CD(+)
   AND  D.SUB_CD = E.SUB_CD(+)
 GROUP  BY  ROLLUP(D.MST_NM
     ,  D.SUB_NM)

 ORDER  BY  D.MST_NM
     ,  D.SUB_NM


 

결과1

MST_NM SUB_NM COST1 COST2 COST3 COST4
소프트웨어 관리비 17000 27000 37000 47000
소프트웨어 사용료 16000 26000 36000 46000
소프트웨어   33000 53000 73000 93000
하드웨어 관리비 15000 25000 35000 45000
하드웨어 사용료 10000 20000 30000 40000
하드웨어   25000 45000 65000 85000
    58000 98000 138000 178000


방법2

SELECT  NVL(D.MST_NM, '합계') AS MST_NM
     ,  DECODE(D.MST_NM, NULL, '합계', NVL(D.SUB_NM, '소계')) AS SUB_NM
     ,  SUM(DECODE(E.YEAR, '2002', E.COST, 0)) AS COST1
     ,  SUM(DECODE(E.YEAR, '2003', E.COST, 0)) AS COST2
     ,  SUM(DECODE(E.YEAR, '2004', E.COST, 0)) AS COST3
     ,  SUM(DECODE(E.YEAR, '2005', E.COST, 0)) AS COST4
  FROM  (SELECT  MST_CD
              ,  MST_NM
              ,  SUB_CD
              ,  SUB_NM
           FROM  (SELECT  MST_CD
                       ,  MST_NM
                    FROM  TB_MSTCODE) A
              ,  (SELECT  SUB_CD
                       ,  SUB_NM
                    FROM  TB_SUBCODE) B ) D
     ,  TB_YEARCOST E
 WHERE  D.MST_CD = E.MST_CD(+)
   AND  D.SUB_CD = E.SUB_CD(+)
 GROUP  BY  ROLLUP(D.MST_NM
     ,  D.SUB_NM)
 ORDER  BY  D.MST_NM
     ,  D.SUB_NM


 

결과2

MST_NM SUB_NM COST1 COST2 COST3 COST4
소프트웨어 관리비 17000 27000 37000 47000
소프트웨어 사용료 16000 26000 36000 46000
소프트웨어 소계 33000 53000 73000 93000
하드웨어 관리비 15000 25000 35000 45000
하드웨어 사용료 10000 20000 30000 40000
하드웨어 소계 25000 45000 65000 85000
합계 합계 58000 98000 138000 178000

 

방법3

SELECT  DECODE(GROUPING(D.MST_NM), 1, '합계', D.MST_NM) AS MST_NM
     ,  DECODE(GROUPING(D.MST_NM), 1, '합계',
               DECODE(GROUPING(D.SUB_NM), 1, '소계', D.SUB_NM)) AS SUB_NM
     ,  SUM(DECODE(E.YEAR, '2002', E.COST, 0)) AS COST1
     ,  SUM(DECODE(E.YEAR, '2003', E.COST, 0)) AS COST2
     ,  SUM(DECODE(E.YEAR, '2004', E.COST, 0)) AS COST3
     ,  SUM(DECODE(E.YEAR, '2005', E.COST, 0)) AS COST4
  FROM  (SELECT  MST_CD
              ,  MST_NM
              ,  SUB_CD
              ,  SUB_NM
           FROM  (SELECT  MST_CD
                       ,  MST_NM
                    FROM  TB_MSTCODE) A
              ,  (SELECT  SUB_CD
                       ,  SUB_NM
                    FROM  TB_SUBCODE) B ) D
     ,  TB_YEARCOST E
 WHERE  D.MST_CD = E.MST_CD(+)
   AND  D.SUB_CD = E.SUB_CD(+)
 GROUP  BY  ROLLUP(D.MST_NM
     ,  D.SUB_NM)

 ORDER  BY  D.MST_NM
     ,  D.SUB_NM


 

결과3

MST_NM SUB_NM COST1 COST2 COST3 COST4
소프트웨어 관리비 17000 27000 37000 47000
소프트웨어 사용료 16000 26000 36000 46000
소프트웨어 소계 33000 53000 73000 93000
하드웨어 관리비 15000 25000 35000 45000
하드웨어 사용료 10000 20000 30000 40000
하드웨어 소계 25000 45000 65000 85000
합계 합계 58000 98000 138000 178000

※ 참고 : GROUPING 함수는 그룹핑 값이 NULL 이면 1을 리턴한다.


방법4

SELECT  DECODE(GROUPING(D.MST_NM), 1, '합계', D.MST_NM) AS MST_NM
     ,  DECODE(GROUPING(D.MST_NM), 1, '합계',
               DECODE(GROUPING(D.SUB_NM), 1, '소계', D.SUB_NM)) AS SUB_NM
     ,  SUM(DECODE(E.YEAR, '2002', E.COST, 0)) AS COST1
     ,  SUM(DECODE(E.YEAR, '2003', E.COST, 0)) AS COST2
     ,  SUM(DECODE(E.YEAR, '2004', E.COST, 0)) AS COST3
     ,  SUM(DECODE(E.YEAR, '2005', E.COST, 0)) AS COST4
  FROM  (SELECT  MST_CD
              ,  MST_NM
              ,  SUB_CD
              ,  SUB_NM
           FROM  (SELECT  MST_CD
                       ,  MST_NM
                    FROM  TB_MSTCODE) A
              ,  (SELECT  SUB_CD
                       ,  SUB_NM
                    FROM  TB_SUBCODE) B ) D
     ,  TB_YEARCOST E
 WHERE  D.MST_CD = E.MST_CD(+)
   AND  D.SUB_CD = E.SUB_CD(+)
 GROUP  BY  GROUPING SETS (
        (D.MST_NM, D.SUB_NM)
     ,  (D.MST_NM)
     ,  ())

 ORDER  BY  D.MST_NM
     ,  D.SUB_NM


 

결과4

MST_NM SUB_NM COST1 COST2 COST3 COST4
소프트웨어 관리비 17000 27000 37000 47000
소프트웨어 사용료 16000 26000 36000 46000
소프트웨어 소계 33000 53000 73000 93000
하드웨어 관리비 15000 25000 35000 45000
하드웨어 사용료 10000 20000 30000 40000
하드웨어 소계 25000 45000 65000 85000
합계 합계 58000 98000 138000 178000


신고
Posted by 기억습작
먹고산다는것2008.01.31 19:58
lock확인만 하려면
select *
from v$locked_object
where oracle_username = 'oracle유저명'
이러면 lock확인은 됩니다..
lock후 kill 시키려면 위의 query가지고는 kill시킬수가 없어여..
kill시키고자 하는 시리얼 번호를 알아야 하거든여..
select a.session_id as SESSION_ID,
b.serial# as SERIAL_NO,
a.os_user_name as OS_USER_NAME,
a.oracle_username as ORACLE_USERNAME,
b.status as STATUS
from v$locked_object a, v$session b
where a.session_id = b.sid
SESSION_ID.SERIAL_NO.OS_USER_NAME..ORACLE_USERNAME.STATUS
24.........1102......Administrator.ABCDWRK.........INACTIVE...<--kill 대상
92.........4665......OPS$ABCD......OPS$ABCDUSR.....INACTIVE
92.........4665......OPS$ABCD......OPS$ABCDUSR.....INACTIVE
'.' 는 걍 쓰면 밀려서 안미리게 하려구 집어넣은겁니다..신경쓰지마시고여...
여기서 나온 결과를 가지고 원하는것을 kill시키면 됩니다..
kill sql문 : alter system kill session 'SESSION_ID,SERIAL_NO'
alter system kill session '24,1102'
이러면 삭제가 됩니다..
신고
Posted by 기억습작
먹고산다는것2008.01.31 19:57

column과 row가 서로 가변적인 table의 현황표을 구하는 sql문을 구사하고자 합니다.
즉, 다음과 같은 현황표를 구하고자 합니다.
          | 인사팀, 총무팀, 영업기획팀, 강서고객센타, 영업개발팀......
 ------|-----------------------------------------------------------
 의자   | 2            4                5                6                     7
 책상   |
 선풍기|
 케비넷|
   ''     |
   ''     |
   .....  |
한쪽이라도 fix가 되어있으면, 할 수 있을 것 같은데, 가로 /세로 모두 가변적인 항목인 경우의 집계표는 구하지를 못하겠군요!부탁드립니다. 예제라도....

 

이화식답변글................

 

질문하신 내용은 실무에서 많은 분들이 질문하는 것 중 하나입니다.
사실 이 내용은 '대용량 데이터베이스 솔루션 III'권에 포함된 내용입니다.
시간이 부족하여 아직 출판이 되지 않았지만 금년 말까지는 기대하셔도 좋습니다.
일단 많은 분들이 이러한 솔루션을 원하고 있으므로 해결의 원리를 설명드리겠습니다.


가로로 가변적인 값들을 sum(decode...)용법을 사용하여 처리할 수는 없으므로해결의

초점은 이 가변적인 값들을 어떻게 하면 고정정이고 규칙적인 값으로 변환하느냐에 있습니다.
또한 가로에 위치할 항목 개수가 무한하다면 정보시스템 특성상 이를 출력할 수가 없으므로 이 무한한 값을 유한한 값으로 만들어야 합니다.
이를 위해 우리는 rownum을 이용하여 규칙적인 값으로 환산하고 페이지 개념을도입하여 무한한 값을 유한한 값의 반복으로 변환시킵니다.
다시 말해서 우리가 가로 항목에 나타날 부서를 찾아 냈을 때 우리는 그 코드들이 어떤 것들이 올지 알지 못하지만 ROWNUM을 취하여 '몫'과 '나머지'를 구하면 항상 우리가 알고 있는 값으로 변환됩니다.


예를 들어 관련된 부서 개수가 96개라고 가정하고 한 페이지에 출력할 항목의 수를 20개 씩으로 한다면 추출된 부서의 rownum를 20으로 나누어 절상하면 '8 page'가 생성될 것이고 이에 대한 나머지를 구하면 한 페이지에 위치할 컬럼의 순번이 생성됩니다.
이를 SQL로 표현해 보면,
    select ceil(rownum/20) page,
           mod(rownum,20)  colseq,
           deptcd,
           dname
    from 부서테이블
    where .......

이 SQL의 수행결과는

 PAGE  COLSEQ  DEPTCD   DNAME
 ----  ------ ------- ---------
   1      1    1000   인사팀
   1      2    1100   총무팀
  ...    ...   ....   ......
   1     19    2800   경영기획팀
   1      0    2900   업무혁신팀
   2      1    3000   예산팀
   2     ...   ....   ......

만약, 여러분이 데이터를 처리할 테이블에 데이터가 발생된 부서들만 처리하기를 고집한다면 처리범위가 넓지 않다면 처리테이블을 읽어 GROUP BY를 취한 집합을 인라인뷰로 묶고 그 결과에 대한 ROWNUM을 사용하면 될 것이며, 처리범위가 넓다면 다음과 같은 방법을 사용해야 한다.
   select ceil(rownum/20) page,
          mod(rownum,20)  colseq,
          deptcd,
          dname
   from 부서테이블 x
   where exists (select '' from 처리테이블 y 
                 where y.부서코드 = x.부서코드)

왜 이렇게 해야 하는지에 대한 자세한 내용은 '대용량 데이터베이스 솔루션 II'의
'4.3.5발생 데이터의 목록처리(page 2-152~154)'를 참조하시기 바랍니다.
자! 이제 우리는 가변적인 가로항목을 고정적인 값들로 변환해 두었습니다. 이때
deptcd, dname을 같이 추출해 둔 것은 나중에 실 데이터 처리 집합과의 조인 및
항목의 타이틀을 만들기 위해서 입니다.
이제 부터는 실 데이터의 집합을 준비해 봅시다.

   select 고정자산코드, 부서코드, sum(수량) 수량
   from 처리테이블
   where conditions ....
   group by 고정자산코드, 부서코드

이 집합의 처리 결과는 다음과 같이 나타나겠지요.

  고정자산코드   부서코드    수량
 -------------- --------- -------
  111           1000           2
  111           1100           4
  ...           ....          ..
  112           1000           5
  ...           ....          ..

그렇다면 앞서 구해둔 집합과 이 집합은 부서코드로 조인이 가능합니다.
이 때 주의할 점은 각각의 가공된 집합은 인덱스를 사용할 수 없으므로 반드시
해쉬조인(hash Join)을 사용해야 합니다. 물론 힌트는 /*+ use_hash(x y) */로
합니다. 여기서 x, y는 각 집합을 인라인뷰로 만든 것의 alias 명입니다.
이 두집합이 조인된 결과는 다음과 같이 나타날 것입니다.

  고정자산코드   부서코드    수량 page  colseq  부서명  
 -------------- --------- ------- ----- ------- -------
  111           1000           2    1     1     인사팀
  111           1100           4    1     2     총무팀
  ...           ....          ..   ..    ..     ......
  112           1000           5    2     1     인사팀
  ...           ....          ..   ..    ..     ......

우리에게 이와 같은 집합이 갖추어져 있다면 다음과 같은 sum(decode..) 용법을
사용하면 쉽게 해결 됩니다.

  select /*+ use_hash(x y) */
         page, 고정자산코드,
         sum(decode(colseq,1,수량))  t1_수량,
         min(decode(colseq,1,dname)) t1_title,
         ........................... t.._수량,
         ........................... t.._title,
         sum(decode(colseq,1,수량))  t20_수량,
         min(decode(colseq,1,dname)) t20_title
  from (selectceil(rownum/20) page, mod(rownum,20)  colseq, deptcd, dname
        from 부서테이블
        where .... ) x,
       (select 고정자산코드, 부서코드, sum(수량) 수량
        from 처리테이블
        where conditions ....
        group by 고정자산코드, 부서코드 ) y
  where x.부서코드 = y.부서코드
  group by page, 고정자산코드

이 수행 결과를 화면이나 보고서로 출력하는 프로그램에서 페이지가 바뀌면 타이틀 란에는
t?_title를 옮겨서 출력하고 나머지는 t?_수량을 옮겨서 출력하면 되겠지요.
자세한 것은 이 원리를 토대로 열심히 연구해 보세요.

신고
Posted by 기억습작
먹고산다는것2008.01.31 19:56
How to Use Hints
  응용설계자는 특정 데이터에 대하여 optimizer가 알 수 없는 정보를 가질 수 있음.
  응용설계자는 optimizer보다 더 좋은 execution plan을 선택할 수 있음.
  응용설계자는 hint를 사용하여 강제적으로 사용자가 선택한 execution plan을 생성하도록 optimizer에게 지시 가능.

Hints의 사용
  Hints의 사용범위

  SQL문을 위한 Optimization approach
  SQL문에 대한 Cost-based approach의 goal(best throughput, best response time)
  SQL문에 의해 접근되는 테이블에 대한 access path
  join문에 대한 join순서
  join문을 처리하기 위한 join operation

  Statement Block

  간단한 SELECT, UPDATE, DELETE SQL문
  한 parent SQL문나 complex SQL문 내의 subquery
  한 compound query중의 한 부분
· UNION operator에 의해 조합된 두 개의 component query로 구성한 한 compound query는 두 개의 SQL문 block을 가짐. (각 component query를 위해 하나의 SQL문 block이 생김.)
· 첫 번째 component query에 있는 hint는 첫 번째 component의 optimizer에만 적용
· 두 번째 component query에 대한 optimizer에는 적용되지 않는다.

  hint를 포함하는 Syntax

·DELETE, SELECT, UPDATE : SQL문 block을 시작하는 DELETE, SELECT, UPDATE 키워드이다. Hint를 포함하는 comments는 이 키워드들이 나타난 후에 써야한다.
·+ : 오라클이 그 comment를 해석할 수 있게 해주는 기능을 한다. 이 ' + '는 comment 표시 후에 빈칸(blank)이 없이 즉시 따라와야만 한다.
·hint : 이절에서 언급되는 hint중의 하나이다. 만약 comment가 여러 hint를 가진다면 그 hint들은 최소한 하나의 공간으로서 분리되어 있어야만 한다.
·text : hint에 대한 설명

  hint를 무시하는 경우

  hint가 쓰인 comment가 DELETE, SELECT, UPDATE 키워드 뒤에 오지 않고, 다른 곳에 쓰여 있는 경우
  hint가 Syntax error를 가진 경우
     (동일한 comment내에 error를 가지지 않고 올바로 표현된 다른 hint들은 인정)
  충돌한 hint의 조합들은 무시(한 comment내에 두 개 이상의 hint가 있을 경우, Optimizer가 어떤 hint를 먼저 써야하는지 모르는 경우)
      (동일한 comment내에 충돌한 hint외에 다른 hint는 인정.)

· 오라클은 SQL*Forms Version 3 Trigger와 같은 PL/SQL Version1을 사용하는 환경에서는 모든 SQL 문에 있는 hint들을 무시.
· Optimizer는 cost-based approach를 사용하는 경우에 hint를 인식.
· 한 SQL문 block에 RULE hint를 제외한 어떤 hint들이 포함되어 있으면, optimizer 는 자동적으로 cost-based approach를 사용.

Hint for Optimization Approaches and Goals
  hint는 cost-based approach와 rule-based optimization approach 중에 하나를 선택
  cost-based approach를 선택한 경우에는 best throughput와 best reponse time사이에 하나를 선택
  SQL문이 optimization approach 와 goal을 기술한 한개의 hint를 가진다면, optimizer는 statistics의 존재여부와 ALTER SESSSION명령에 있는 OPTIMIZER_GOAL와 OPIMIZER_MODE 초기 파라메타의 값과는 상관없이 기술된 approach를 사용.

ALL_ROWS
  ALL_ROWS hint는 best throughput(minimum total resource consumption)을 목적으로 SQL문 block을 최적화하기 위해 cost-based approach를 선택
  optimizer는 best throughput을 목적으로 SQL문를 최적화하려면
·SELECT /*+ ALL_ROWS */ empno, ename, sal, job
              FROM emp
              WHERE empno = 7566;

FIRST_ROWS
  FIRST_ROWS hint는 best response time을 목적으로 SQL문 block을 최적화하기 위해 cost-based approach를 선택.(minimum resource usage to return first row)
  이 hint는 아래 내용을 이행할 수 있는 optimizer를 생성
· Index scan을 쓸수 있다면, optimizer는 full table scan보다는 Index scan을 사용.
· Index scan을 쓸수 있다면, optimizer는 연관된 table이 nested loop의 inner table일 때마다, sort-merge join보다는 nested loops join을 선택.
· Index scan이 ORDER BY절에 의해 쓰여지면, optimizer는 sort operation을 피하기 위해 index scan을 선택.

  optimizer는 best response time을 목적으로 아래 SQL문를 최적화하려면.
·SELECT /*+ FIRST_ROWS */ empno, ename, sal, job
              FROM emp
              WHERE empno = 7566;


  Optimizer는 아래 Syntax를 가지는 DELETE와 UPDATE SQL문 blocks과 SELECT SQL문 blocls에 있는 hint는 무시.
· set operators(UNION, INTERSECT, MINUS, UNION ALL)
· GROUP BY절
·FOR UPDATE 절
·group functions
·DISTINCT operator
  이들 SQL문는 best response를 목적으로 최적화될 수 없다.
  위의 경우 첫 번째 row를 반환하기 전에 SQL문에 접근되는 모든 행들을 retrieve해야만 함.
  이런 SQL문에 대해 hint를 쓰면 optimizer는 cost-based approach를 사용하고, best throughput을 목적으로 최적화.

  SQL문에 ALL_ROWS나 FIRST_ROWS hint를 기술하고, data dictionary가 그 SQL문에 의해 접근되는 table에 대해 어떤 statistics도 포함하지 않는다면, optimizer는 내부적으로 execution plan을 선택하고, missing statistics를 추정할 default statistics 값을 사용.
  이 추정치(estimates)는 ANALYZE명령에 의해 생성되어진 것만큼 정확하지는 않음.
  ANALYZE명령을 사용하여 Cost-based optimization을 사용하는 SQL문에 의해 접근되는 모든 table에 대한 statistics를 생성해야 함.
  Access path를 위한 hint 또는 ALL_ROWS나 FIRST_ROWS hint를 가지는 join operation을 기술한다면, optimizer는 hint에 의해 기술된 join operation들과 access paths를 우선적으로 취함.

CHOOSE
  CHOOSE hint는 statistics가 존재하고, SQL문에 의해 접근된 테이블에 대해 rule-based approach나 cost-based approach중 어떤 것을 쓸 것인지 optimizer가 선택.
  data dictionary가 이 table들 중 최소한 하나에 대한 statistics를 가진다면 optimizer는 cost-based approach를 사용.
  data dictionary가 이 table들 중 어떤 것에 대해서도 statistics를 가지지 않는다면 optimizer는 rule-based approach를 사용.
  아래 SQL문에서 만약 EMP 테이블에 대해 statistics가 있다면 optimizer는 cost-based approach를 사용.
  data dictionary에 EMP 테이블에 대해 어떤 statistics도 존재하지 않는다면 optimizer는 rule-based approach를 사용.
·SELECT /*+ CHOOSE */ empno, ename, sal, job
              FROM emp
              WHERE empno = 7566;

RULE
  RULE hint는 한 SQL문 block에 대해 rule-based optimization을 선택.
  이 hint는 optimizer가 SQL문 block에 대해 기술한 다른 hint들을 무시.
  Optimizer는 아래 SQL문를 위해서 rule-based approach를 사용.
·SELECT --+ RULE
               empno, ename, sal, job
              FROM emp
              WHERE empno = 7566;

  rule-based approach를 쓰는 RULE hint는 오라클의 다음 버전에서는 사용하지 않는다.

Hint for Access Methods
  각 hint들은 table에 대한 access method를 제안
  hint중의 하나를 기술하는 것은 access path가 인덱스나 클러스터와 SQL문의 의미구조의 존재를 기본적으로 이용할수 있다면 기술된 access path를 선택
  힌트가 access path를 이용할수 없다면 optimizer는 그것을 무시.
  SQL문에서 정확하게 access되는 테이블을 기술
  SQL문이 table에 대한 alias를 사용하면, hint에서 table의 이름보다는 alias를 사용.
  테이블의 이름이나 alias 는 local database에 있는 한 테이블에 대한 하나의 synonym이나 하나의 table을 의미함.

FULL
  FULL hint는 테이블에 대해 full table scan을 선택
  FULL hint의 문법은 FULL(table)
  (table)에는 full table scan을 수행하는 table의 alias나 name을 기술한다.
  예 : ACCOUNT 테이블에 WHERE절의 조건에 의해 사용가능한 ACCNO 칼럼에 대한 인덱스가 있음에도 불구하고, 오라클은 이 SQL 문을 실행할 ACCOUNTS 테이블에 full table scan을 수행.
·SELECT /*+ FULL(a) Don't use the index on ACCNO */ accno, bal
              FROM accounts a
              WHERE accno = 7086854;


NOTE
      ·  ACCONTS테이블이 alias A를 가지기 때문에 hint는 테이블의 이름이 아닌 alias로 테이블을 표현.
      ·  FROM 절에 테이블의 이름이 기술되었음에도 불구하고, hint에서 는 schema names을 기술하지 않는다.

ROWID
  ROWID hint는 테이블에 대해 ROWID에 의한 table scan을 선택
  ROWID hint의 문법은 ROWID(table)
  (table)에는 ROWID에 의한 table scan이 이행되어지는 table의 alias나 이름을 기술.

CLUSTER
  CLUSTER hint는 테이블에 대해 cluster scan을 선택
  CLUSTER hint의 문법은 CLUSTER(table)
  (table)에는 cluster scan에 의해 접근되는 테이블의 이름이나 alias를 기술.

·SELECT --+ CLUSTER emp, ename, deptno
              FROM emp, dept
              WHERE deptno = 10 AND emp.deptno = dept.deptno;

HASH
  HASH hint는 테이블에 대해 HASH scan을 선택
  HASH hint의 문법은 HASH(table)
  (table)에는 hash scan에 의해 접근되는 테이블의 이름이나 alias를 기술

INDEX
  INDEX hint는 테이블에 대해 index scan을 선택
  INDEX hint의 문법은

    table : scan될 index와 관련있는 테이블의 이름이나 alias를 기술
    index : index scan이 수행될 index를 기술

  hint는 하나이상의 indexes들을 기술
· hint가 하나의 사용가능한 index를 기술한다면, optimizer는 index에서 한개의 scan을 수행.
· optimizer는 full table scan이나 테이블에 있는 다른 index에 대한 scan은 수행하지 않음.
· hint가 사용가능한 index의 리스트를 기술한다면, optimizer는 리스트에 있는 각각의 인덱스에 대한 scan을 하는데 드는 비용을 고려한 후에 가장 적은 비용이 드는 index scan을 이행
· 이 access path가 최저의 비용을 가진다면 optimizer는 이 리스트로부터 여러 인덱스를 scan하고 그 결과들을 merge.
· optimizer는 full table scan이나 hint에 있지 않은 index scan은 고려하지 않음.
· hint가 어떤 index도 기술하지 않았다면, optimizer는 테이블에 있는 사용가능한 index를 각각 scan한 비용을 고려한 후에 lowest cost를 가진 index scan을 수행.
· 이 access path가 최저비용을 가진다면 optimizer는 muliple index를 scan하고 그 결과값을 merge.
  SELECT name, height, weight
              FROM patients
              WHERE sex='M'


·  sex의 열은 index되어 있고, 이 칼럼은 'M'과 'F'의 값을 가짐.
·  병원에 남자의 수와 여자의 수가 동일하다면, 이 질의는 연관된 테이블의 행의 최다 퍼센트를 반환하고, full table scan이 index scan보다는 더 빠르게 된다.
·  병원의 환자 중 남자의 비율이 매우 적다면, 질의는 관련된 테이블의 행에 대해 적은 비율을 반환하고, 이 경우에는 index scan이 full table scan보다 더 빠르다.
  각 disinct column value의 발생수는 optimizer에게 별로 유용하지 않다(도움이 되지 않는다.)
 cost-based approach는 각각의 값들이 각각의 행에서 나타나는 빈도수가 동일하다고 가정을 한다.
  한 칼럼이 단 2개의 다른 값들(distinct values)을 가진다면 optimizer는 그 두 값들이 각각 row의 50%로정도 나타난다고 가정한다 그래서 cost-based approcah는 index scan보다는 full table scan을 선택하곤 한다.
  WHERE절에 있는 값이 모든 row에 대해 매우 적은 퍼센트를 가진다면, hint에 index scan을 사용하여 optimizer가 강제로 index scan을 사용하게 할수 있다.
  아래 문장에서 INDEX hint는 SEX_INDEX에 대해 index scan을 선택한다.

· SELECT /*+ INDEX(patients sex_index) Use SEX_INDEX, since there are few male patients */
              name, height, weight
              FROM patients
              WHERE sex = 'M';

FULL hint와 INDEX hint의 비교 예제
  1. EMP테이블의 총건수는 10,000건, DEPT테이블의 총건수는 2400건.
  2. EMP테이블에서 empno, ename, sal을 select
  3. WHERE절의 조건은 JOB 필드의 'SALESMAN'값을 검사
  4. JOB_INDEX 존재.
  5. JOB필드에는 2개의 값이 존재(MANAGER-(9,751건/10,000건), SALESMAN-(249건/10,000건))
·  EMP, DEPT 테이블을 ANALYZE함.
·  sql_trace를 true로
·  OPTIMIZER_GOAL 은 ALL_ROWS
      예제 1
      analyze table emp estimate statistics;
      analyze table dept estimate statistics;

      alter session set sql_trace = true;
      alter session set optimizer_goal = all_rows;

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

      select /*+ full(emp) */ empno, ename, sal
      from emp
      where job = 'SALESMAN';

      select /*+ index(emp job_index) */ empno, ename, sal
      from emp
      where job = 'SALESMAN';
      ·  trace file 결과

·  OPTIMIZER_GOAL 은 FIRST_ROWS
      예제 2
      analyze table emp estimate statistics;
      analyze table dept estimate statistics;

      alter session set sql_trace = true;
      alter session set optimizer_goal = first_rows;

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

      select /*+ full(emp) */ empno, ename, sal
      from emp
      where job = 'SALESMAN';

      select /*+ index(emp job_index) */ empno, ename, sal
      from emp
      where job = 'SALESMAN';
      ·  trace file 결과

INDEX_ASC
  INDEX_ASC hint는 테이블에 대한 index scan을 선택
  이 SQL문이 index range scan을 사용한다면, Oracle은 index된 값들을 오름차순으로 정렬한 index entry들을 scan.
  INDEX_ASC hint의 문법은

  각 파라메타는 INDEX hint에서와 같은 목적을 의미.
  range scan에 대해서 오라클의 default behavior는 index된 값에 대해 오름차순으로 정렬하고 그 index entry들을 scan하는 것이므로 이 hint는 일반적으로 index hint보다 더 나은 점은 없다.

INDEX_DESC
  INDEX_DESC hint는 테이블에 대해 index scan을 선택
  만약 SQL 문이 index range scan을 사용한다면, 오라클은 index된 값들을 내림차순으로 정렬한 index entry들을 scan.
  INDEX_desc hint의 문법은

  각 파라메타는 INDEX hint에서와 같은 목적을 의미.
  이 hint는 테이블보다 INDEX를 더 많이 접근하므로 SQL문에 영향을 주지 않음.
  아래의 SQL문은 index된 값에 대해서 항상 오름차순으로 정렬된 index range scan을 수행
·CREATE TABLE tank_readings (
              time DATE CONSTAINT un_time UNIQUE,
               temperature NUMBER);


  테이블의 각 행들은 한 시점에서 시간과 온도를 저장.
  TIME칼럼에 대해 UNIQUE 제약을 주면 테이블이 동일한 시점에서 한번만 내용을 읽도록 한다.
  오라클은 TIME 칼럼에 강제로 인덱스를 수행.
  특별한 T시간에 대해서 읽은 가장 최근의 온도를 SELECT하는 complex query를 생각해보자.
  Subquery는 온도를 읽는 시점 T나 T이전의 가장 최근시간의 값을 반환.
  Parent query는 그 시간에 대한 온도를 찾는다.
·SELECT temperature
              FROM tank_readings
              WHERE time = (SELECT MAX(time)
                  FROM tank_readings
                  WEHRE time <= TO_DATE(:t) );

  위 SQL문에 대한 execution plan은 아래그림과 같다.

  위 SQL문장을 실행시키면, 오라클은 아래와 같은 operations를 실행.
· step 4와 3은 subquery를 실행.
    - step 4는 시간 T와 같거나 더 적은 모든 TIME 값들을 반환할 UN_TIME 인덱스의 range scan을 수행.
    - step 3는 step 4로부터 최대 TIME값을 선택하고 그 값을 반환.
·step 2 와 1은 parent query를 실행.
    - step 2는 step 3에 의해 반환된 TIME 값에 맞는 UN_TIME index의 unique scan을 수행하고 관련된 ROWID를 반환.
    - step 1은 step 2에 의해 반환된 ROWID를 사용하여 TANK_READING 테이블을 접근하고 TEMPERATURE 값을 반환.

 Step 4에서 오라클은 오름차순으로 정렬된 인덱스에 있는 TIME 값을 scan.
  오라클은 첫 번째 TIME값이 T보다 더 큰 경우에 scaning을 중지하고 그후에 step 3에서 T값과 같거나 더 적은 모든 값을 반환.
  INDEX_DESC hint를 사용하면 index로부터 단 하나의 TIME값을 읽어오는 질의 사용 가능.
·SELECT /*+ INDEX_DESC(tank_readings un_time) */ temperature
              FROM tank_readings
              WHERE time <= TO_DATE(:t)
              AND ROWNUM = 1;
              ORDER BY time DESC;

  이것의 execution plan을 아래 그림과 같다.
· step3 은 T와 동일하거나 더 적은 TIME값을 찾기위해 UN_TIME 인덱스를 range scan하고 그와 관련된 ROWID를 반환.
·step 2는 step 3에 의해 반환된 ROWID값들로서 TANK_READING 테이블에 접근.
· step 1은 step 2로부터 단 하나의 행을 요구함으로 ROWNUM=1이라는 조건을 수행.
  INDEX_DESC hint 때문에 step 3은 T값부터 시작하는 내림차순으로 정렬된 index에서 TIME값을 scan.
  scan된 첫 번째 TIME값은 T이거나 T값보다는 적은 최대 TIME값이다.
  step 1은 단 하나의 행을 요구한 후부터는 , step 3은 첫 번째 TIME값 이후의 index entry에 대해 더 이상 scan하지 않는다.
  default 행동이 오름차순 index scan이므로 INDEX_DESC hint없이 이 질의를 수행하면 오라클은 테이블에서 T와 같거나 그보자 적은 최대 시간을 처음 scaning하는 것보다 최초의 시간을 scaning함으로서 시작하게 됨. step1은 최초의 시간에대한 온도를 반환하게 된다.
  위 질의에서 좀 더 빨리 이 복합 질의에서 요구하는 온도를 반환하려면 INDEX_DESC hint를 사용해야만 한다.

AND_EQUAL
  AND_EQUAL hint는 몇몇의 single-column index에대한 scan을 merge하는 access path를 사용하는 execution plan을 선택
  이 AND_EQUAL hint의 문법은

    table : merge할 인덱스와 연관된 테이블의 이름이나 alias를 기술.
    index : index scan을 수행하는 index를 기술
 최소 2개 이상 최대 5개 이하의 index를 기술해야 한다.

USE_CONCAT
  USE_CONCAT hint는 OR 조건을 UNION ALL set operator를 사용하는 compound query로 변환
  이 변환은 UNION ALL set operations을 사용하는 질의가 이를 사용하지 않을 때보다 비용이 더 적을 경우에만 발생

Hint for join Orders
ORDERED hint는 join order를 제안

ORDERED
  ORDERED hint는 FROM절에 table이 나타나는 순서대로 테이블을 join시킨다.
  예를들어, 아래 SQL문은 테이블 TAB1과 테이블 TAB2를 조인한 후에 그 결과와 테이블 TAB3을 조인한다.

·SELECT /*+ ORDERED */ tab1.col1, tab2.col2, tab3.col3
              FROM tab1, tab2, tab3
              WHERE tab1.col1 = tab2.col1 AND tab2.col1 = tab3.col1;


  SQL문에서 ORDERED hint를 생략하고 join를 수행하면, optimizer가 table을 join할 순서를 선택
  각 테이블에서 select해 오는 행의 수에대해 알고 있다면 join 순서를 기술하는 ORDERED hint를 사용하는 것이 좋다.
  사용자가 inner 와 outer table을 선택하는 것이 optimizer가 할수 있는 것보다 나을 수도 있다.

Hint for Join Operations
 이절에서의 hint는 테이블을 위한 join operation을 언급
 SQL문에서 나타나는 조인된 table을 정확하게 기술해야만 한다.
 SQL문이 테이블의 alias를 사용한다면 hint에서도 테이블의 이름보다는 alias를 사용해야만 한다.
 테이블의 이름이나 alias는 local database에 테이블의 synonym이나 테이블을 가지고 있어야만 한다.
 USE_NL과 USE_MERGE hint는 ORDERED hint를 사용해야만 한다.
 오라클은 참조된 테이블이 조인에서 강제로 inner테이블이 될 때 이 hint를 사용해야만 한다.
 참조된 테이블이 outer table이라면 이 hint들을 무시한다.

USE_NL
  USE_NL hint는 테이블을 적는 부분에 테이블 기술된 table은 inner table로서 사용하여 nested loops로서 다른 테이블의 row source와 기술된 table을 join하게 한다.
  USE_NL hint의 문법은

    table : nested loops join의 inner table로서 사용될 테이블의 이름과 alias이다.
  예를 들어서, ACCOUNTS와 CUSTOMERS 테이블을 조인하는 SQL문이 있다고 생각해보자. 이들 테이블들은 cluster에 함께 저장되지 않았다고 가정한다.

·SELECT accounts.balance, customers.last_name, customers.first_name
              FROM accounts, customers
              WHERE accounts.custno = customers.custo;

  cost-based approach의 default 목적은 best throughput이므로 이 optimizer는 좀 더 빨리 질의에 의해 select된 모든 행들을 반환하기위해 nested loops operation이나 sort-merge operation중 하나를 선택한다.
  그러나 질의에 의해 선택된 첫 번째 행만 반환할 때 필요시간이 매우 적어야 할 경우에는 best throughput보다 best response time으로 SQL문을 최적화하는 것이 더 낫다.
  그렇게 하려면 USE_NL hint를 사용함으로서 optimizer가 nested loops join을 강제로 선택하게 할 수 있다.
  SQL문에서 USE_NL hint는 CUSTOMERS테이블을 inner table로 가지는 nested loop를 선택
·SELECT /*+ ORDERED USE_NL(customers) Use N-L to get first row faster */
              accounts.balance, customers.last_name, customers.first_name
              FROM accounts, customers
              WHERE accounts.custno = customers.custno;


  많은 경우에 nested loops join은 sort-merge join보다는 더빨리 첫 번째 행을 반환한다.
  Nested loop join은 한 테이블로부터 첫 번빼 select한 행을 읽은 후에 첫 번째 행을 반환할 수 있고, 다른 테이블에서 첫 번째로 일치하는 행을 찾고 그것들을 결합한다.
  반면에 sort-merge join은 양 테이블에서 select한 모든 row들을 읽고 정렬한 후 각각의 저장된 row source의 첫 번째 행들을 결합할 때까지 첫 번째 행을 반환할 수 없다.

USE_MERGE
  USE_MERGE hint는 오라클이 sort-merge join으로 각 테이블을 조인하게끔 하는 방법이다.
  USE_MERGE hint의 문법은

Hints for Parallel Query Execution

CACHE
  CACHE hint는 full table scan이 수행됐을 때 hint에 있는 테이블에 대해 retrieve된 블록들은 버퍼 캐시에 있는 LRU list의 가장최근(most recently)에 사용되어진 것의 끝에 위치
  이 option은 small lookup table에 유용하다. CACHE hint는 테이블의 default caching specification을 무시
·SELECT/*+ FULL (scoot_emp) CACHE(scott_emp) */ ename
              FROM scott.emp scott_emp;

NOCACHE
  NOCACHE hint는 full table scan이 수행됐을 때 hint에 있는 테이블에 대해 retrieve된 블록들은 버퍼 캐시에 있는 LRU list의 가장 오래전(least recently)에 사용되어진 것의 끝에 위치
  버퍼캐수에 blocks의 일반적인 행동
·SELECT/*+ FULL (scoot_emp) NOCACHE(scott_emp) */ ename
              FROM scott.emp scott_emp;

Considering Alternative Syntax
  SQL이 융통성이 있는 언어이기 때문에 하나 이상의 SQL문이 Application을 필요로 할 것이다.
  2개의 SQL문이 동일한 결과를 산출함에도 불구하고, 오라클은 2중의 하나가 더 처리속도가 빠르다.
  execution plans을 비교하기 위해 EXPLAIN PLAN SQL문의 결과와 두 SQL문의 비용들을 사용할 수 있다.

두 SQL문에 대한 execution plan 비교
  첫 번째 SQL문과 그것에 대한 execution plan
·SELECT dname, deptno
              FROM dept
              WHERE deptno NOT IN
              (SELECT deptno FROM emp);

 Execution Plan with Two Full Table Scans
 step3을 통해 오라클은 DEPTNO 칼럼에 인덱스가 있음에도 불구하고 EMP테이블을 full table scan함으로서 위 SQL문을 실행한다는 것을 알수 있다.
 full table scan은 time-consuming operation을 할 수 있다.
 EMP테이블을 찾는 subquery에 index사용이 가능한 WHERE절이 없기 때문에 오라클은 index를 사용할 수 없다.

  그러나, 아래의 SQL문은 인덱스에 접근함으로서 동일한 행을 SELECT한다.
·SELECT dname, deptno
              FROM dept
              WHERE NOT EXISTS
              (SELECT deptno
              FROM emp
              WHERE dept.deptno = emp.deptno);

 Execution Plan with a Full Table Scan and an Index Scan
 subquery의 WHERE절이 EMP테이블의 DEPTNO칼럼을 사용하므로 DEPTNO_INDEX를 사용할 수 있다.
 인덱스의 사용은 execution plan의 step3에서 하게된다
  DEPTNO_INDEX의 index range scan은 첫 번째 문장에서 EMP테이블의 full scan하는 것보다 시간이 더 적게 걸린다.
  첫 번째 query는 DEPT 테이블에서 모든 DEPTNO를 가져오기 위해 EMP테이블을 한번 full scan한다.
 이런 이유로 두 번째 SQL 문은 첫 번째보다는 더 빠르다.
 Application에 NOT IN operator를 사용하는 SQL문를 가진다면 NOT EXISTS operator를 사용해서 그것들을 다시 써라. 이것은 인덱스가 있다면 그 인덱스를 사용할 수 있게 해줄 것이다.

신고
Posted by 기억습작

티스토리 툴바