차이

문서의 선택한 두 판 사이의 차이를 보여줍니다.

--- notepad:mulling [2010/10/04 17:26]
mulling
+++ notepad:mulling [2010/10/05 19:35]
mulling 옛 버전 복구
@@ 줄 5: / 줄 5: @@
 <code :sql>
-a2dev01:db2instl:/db2/db2instl>db2expln -d amedb01 -f 1 -t -g -z ';'
+---------------------------------------------------------------------------------------------------------------------------------------------------
+.to_date 함수 안씌우고 date 비교 ex) 컬럼 >= '2010-07-01'
-DB2 Universal Database Version 9.7, 5622-044 © Copyright IBM Corp. 1991, 2008 Licensed Material - Program Property of IBM IBM DB2 Universal Database SQL and XQUERY Explain Tool
-DB2 Universal Database Version 9.7, 5622-044 © Copyright IBM Corp. 1991, 2008 Licensed Material - Program Property of IBM IBM DB2 Universal Database SQL and XQUERY Explain Tool
-DYNAMIC ***
-STATEMENT
-      Isolation Level          = Cursor Stability
-      Blocking                 = Block Unambiguous Cursors
-      Query Optimization Class = 5      Partition Parallel       = No
-      Intra-Partition Parallel = No      SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
-                                 "DB2INSTL"Statement:
 select *
-from DB2INSTL.DW_MACHINETIME_LTPS
+from DB2INSTL.DW_XXXXXX
-where ENDTIME >=TO_DATE('2010-06-01 00:00:00', 'YYYY-MM-DD
+where ENDTIME >='2010-07-01' and ENDTIME < '2010-08-01'
-        HH24:MI:SS')and ENDTIME < TO_DATE('2010-07-01 00:00:00',
-        'YYYY-MM-DD HH24:MI:SS')Section Code Page = 1208
-Estimated Cost = 410345.156250 Estimated Cardinality = 31338.298828
-Ship Federated Subquery #1
-Return Data to Application
-Federated Substatement #1: Server: AMDW (ORACLE 10g) SQL Statement:
-   SELECT A0."ENDTIME" , A0."PRODUCTNAME" , A0."LOTNAME" , A0.
-           "PRODUCTIONTYPE" , A0."PROCESSOPERATIONNAME" , A0.
-           "MACHINENAME" , A0."MACHINETYPE" , A0."STARTTIME" , A0.
-           "ITEMNAME" , A0."TIME"
-   FROM "ORADWADM" ."DW_MACHINETIME_LTPS" A0
-   WHERE (TO_TIMESTAMP(:H0 )<=A0."ENDTIME" )AND (A0."ENDTIME" <
-           TO_TIMESTAMP(:H1 ))Nicknames Referenced:
-: DB2INSTL.DW_MACHINETIME_LTPS  ID = 65533,32775
-    Base = ORADWADM.DW_MACHINETIME_LTPS#Input Columns = 2 #Output Columns = 10
-End of section
+SELECT *
+FROM "ORACLE" ."DW_XXXXXX" A0
+WHERE (TO_TIMESTAMP('20100701 000000000000', 'YYYYMMDD HH24MISSFF6') <=A0."ENDTIME" )
+AND (A0."ENDTIME" < TO_TIMESTAMP('20100801 000000000000', 'YYYYMMDD HH24MISSFF6'))
-Optimizer Plan:
+.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'))
+)
-             Rows
+<< DB2 >>
-           Operator
+CREATE TABLE "EDBUSER "."DW_XXXXXX"
-             (ID)
+(
-             Cost
+  "PRTNAME" 						VARCHAR(30) NOT NULL ,
+  "LTNAME" 							VARCHAR(30) ,
-.3
+  "PRTIONTYPE" 					VARCHAR(30) ,
-             n/a
+  "PROCESSOPNAME" 			VARCHAR(30) ,
-           RETURN
+  "MNAME" 							VARCHAR(30) ,
-            ( 1)
+  "MTYPE" 							VARCHAR(30) ,
+  "STARTTIME" 					TIMESTAMP NOT NULL ,
-             |
+  "ENDTIME" 						TIMESTAMP NOT NULL ,
-.3
+  "ITEMNAME" 						VARCHAR(30) NOT NULL ,
-             n/a
+  "TIME" 								DOUBLE
-            SHIP
+)
-            ( 2)
+COMPRESS YES
+DISTRIBUTE BY HASH("PRODUCTNAME","STARTTIME","ITEMNAME")
-          /       \
+PARTITION BY RANGE("ENDTIME")
-.80221e+08     4.84698e+08  Index: n/a <65533,32775,1> Nickname:
+(
+  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"
+);
-                <65533,32775>
+.오라클 호환모드
+=> 현재 설정되어 있지 않음!!
+---------------------------------------------------------------------------------------------------------------------------------------------------
 </code>

adminschool.net wiki

사용자 도구

사이트 도구

차이

문서 도구