[oracle ] global temporary table 연습 ~~

http://suite.tistory.com/ 2009 02 fs

global temporary TABLE

//테이블 먼저 생성
create global temporary TABLE DEPT_TMP2
( cst_no CHAR(10) ,
  ang_data VARCHAR2(100) ,
  name VARCHAR2(50)
  )ON COMMIT preserve rows;

AS SELECT  a.cst_no ,  a.agent ||'_'|| a.cst_type ||'_'|| a.cst_grade as ang_data  , b.name from tbl__ang_agent a, wjcom.wc_bp_sap_agent b where a.agent = b.agent
SELECT

//인덱싱
create   INDEX  idxcst_no222 ON  DEPT_TMP2 (cst_no)

//삽입
INSERT into  DEPT_TMP2  ( cst_no,ang_data,name )
SELECT a.cst_no ,  a.agent ||'_'|| a.cst_type ||'_'|| a.cst_grade as ang_data  , b.name from tbl__ang_agent a, wjcom.wc_bp_sap_agent b where a.agent = b.agent

// 생성과 동시에
create global temporary TABLE dept_tmp2

ON COMMIT preserve rows
AS SELECT  a.cst_no ,  a.agent ||'_'|| a.cst_type ||'_'|| a.cst_grade as ang_data  , b.name from tbl__ang_agent a, wjcom.wc_bp_sap_agent b where a.agent = b.agent

drop TABLE DEPT_TMP2
TRUNCATE TABLE DEPT_TMP2

COMMIT

SELECT * FROM dept_tmp2 WHERE cst_no='0000000377'
wjcom.wc_bp_sap_agent 

CREATE INDEX WJTH.IDX_TBL_ERP_cst_X1
ON WJTH.TBL_ERP_cst (REG_NO)
insert into gtt1
select level
from dual
connect by level <= 1101
;

그냥 연습~~~~~ 쓸모가 있을려나 ...