adminschool.net wiki

문서의 이전 판입니다!

---

- 삭제예정은 이렇게 표시

- SQL은 이렇게~

 SELECT dummy FROM dual

- 중요한내용은 이렇게?

---------------------------------------------------------------------------------------------------------------------------------------------------	   
1.to_date 함수 안씌우고 DATE 비교 ex) 컬럼 >= '2010-07-01'
SELECT * 
FROM DB2INSTL.DW_XXXXXX 
WHERE ENDTIME >='2010-07-01' AND ENDTIME < '2010-08-01' 
 
SELECT * 
FROM "ORACLE" ."DW_XXXXXX" A0 
WHERE (TO_TIMESTAMP('20100701 000000000000', 'YYYYMMDD HH24MISSFF6') <=A0."ENDTIME" )
AND (A0."ENDTIME" < TO_TIMESTAMP('20100801 000000000000', 'YYYYMMDD HH24MISSFF6'))
 
2.DDL
<< Oracle >>
CREATE TABLE ORADWADM.DW_XXXXXX
(
  PRTNAME           		VARCHAR2(30 BYTE),
  LTNAME               	VARCHAR2(30 BYTE),
  PRTIONTYPE        		VARCHAR2(30 BYTE),
  PROCESSOPNAME  				VARCHAR2(30 BYTE),
  MNAME           			VARCHAR2(30 BYTE),
  MTYPE           			VARCHAR2(30 BYTE),
  STARTTIME             DATE,
  ENDTIME               DATE,
  ITEMNAME              VARCHAR2(30 BYTE),
  TIME                  NUMBER
)
TABLESPACE TS_DAT
PARTITION BY RANGE (ENDTIME)
(  
  PARTITION PT_XXXXXX_0801 VALUES LESS THAN (TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  PARTITION PT_XXXXXX_0802 VALUES LESS THAN (TO_DATE(' 2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  ...
  PARTITION PT_XXXXXX_1101 VALUES LESS THAN (TO_DATE(' 2011-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
) 
 
<< DB2 >>
CREATE TABLE "EDBUSER "."DW_XXXXXX"  
(
  "PRTNAME" 						VARCHAR(30) NOT NULL , 
  "LTNAME" 							VARCHAR(30) , 
  "PRTIONTYPE" 					VARCHAR(30) , 
  "PROCESSOPNAME" 			VARCHAR(30) , 
  "MNAME" 							VARCHAR(30) , 
  "MTYPE" 							VARCHAR(30) , 
  "STARTTIME" 					TIMESTAMP NOT NULL , 
  "ENDTIME" 						TIMESTAMP NOT NULL , 
  "ITEMNAME" 						VARCHAR(30) NOT NULL , 
  "TIME" 								DOUBLE 
)   
COMPRESS YES  
DISTRIBUTE BY HASH("PRODUCTNAME","STARTTIME","ITEMNAME")   
PARTITION BY RANGE("ENDTIME") 
(
  PART "PT_XXXXXX_0801" STARTING(MINVALUE) ENDING('2008-02-01-00.00.00.000000') EXCLUSIVE IN "DW_MD_TS" INDEX IN "DW_MI_TS", 
  PART "PT_XXXXXX_0802" ENDING('2008-03-01-00.00.00.000000') EXCLUSIVE IN "DW_MD_TS" INDEX IN "DW_MI_TS", 
  ...
  PART "PT_XXXXXX_1101" ENDING('2011-02-01-00.00.00.000000') EXCLUSIVE IN "DW_MD_TS" INDEX IN "DW_MI_TS"
);
 
3.오라클 호환모드
=> 현재 설정되어 있지 않음!!
---------------------------------------------------------------------------------------------------------------------------------------------------