论坛 › DB2 › SQL COST 過高 急急急

68930089 发表于 2010-09-24 15:17

SQL COST 過高 急急急

SELECT A.*,B.GLASS_ID,MAX(B.IN_TIME) IN_TIME, MAX(B.OUT_TIME) OUT_TIME FROM ( SELECT * FROM DBO.EQPM
AP WHERELINE_NAME= 'R' AND USE_PRO_DATA_NO=1 ) A LEFT OUTER JOINDBO.R_PROCESS_TIME AS B ON ( B.G
LASS_ID ='C092756Q'AND A.LINK_MAP_EQP_NO = int(B.EQUIPMENT_NO)) GROUP BY A.LINE_NAME, A.LINK_MAP_E
QP_NO, A.EQP_NAME, A.USE_PRO_DATA_NO,A.DATA_ITEM_COUNT,A.TABLE_NAME,A.USE_RECIPE_DATA_NO,B.GLASS_ID
ORDER BY A.LINK_MAP_EQP_NO

user sql 語法如上一直會使用table scan 執行db2advis 一直會出現錯誤

Estimated Cost = 1178436.875000
Estimated Cardinality = 13.000000

Access Table Name = DBO.EQPMAPID = 9,196
|Index Scan:Name = DBO.EQPMAP_IID = 3
||Regular Index (Not Clustered)
||Index Columns:
|||1: LINE_NAME (Ascending)
|||2: LINK_MAP_EQP_NO (Ascending)
|||3: EQP_NAME (Ascending)
|||4: USE_PRO_DATA_NO (Ascending)
|||5: DATA_ITEM_COUNT (Ascending)
|||6: TABLE_NAME (Ascending)
|||7: USE_RECIPE_DATA_NO (Ascending)
|#Columns = 5
|#Key Columns = 1
||Start Key: Inclusive Value
||||1: 'R         '
||Stop Key: Inclusive Value
||||1: 'R         '
|Index-Only Access
|Index Prefetch: None
|Lock Intents
||Table: Intent Share
||Row: Next Key Share
|Sargable Index Predicate(s)
||#Predicates = 1
Left Outer Nested Loop Join
|Access Table Name = DBO.R_PROCESS_TIMEID = 9,307
||#Columns = 3
||Relation Scan
|||Prefetch: Eligible
||Lock Intents
|||Table: Intent Share
|||Row: Next Key Share
||Sargable Predicate(s)
|||#Predicates = 2
Insert Into Sorted Temp TableID = t1
|#Columns = 8
|#Sort Key Columns = 6
||Key 1: LINK_MAP_EQP_NO (Ascending)
||Key 2: EQP_NAME (Ascending)
||Key 3: DATA_ITEM_COUNT (Ascending)
||Key 4: TABLE_NAME (Ascending)
||Key 5: USE_RECIPE_DATA_NO (Ascending)
||Key 6: GLASS_ID (Ascending)
|Sortheap Allocation Parameters:
||#Rows   = 13
||Row Width = 108
|Piped
Access Temp TableID = t1
|#Columns = 8
|Relation Scan
||Prefetch: Eligible
|Sargable Predicate(s)
||Predicate Aggregation
|||Group By
|||Column Function(s)
Aggregation Completion
|Group By
|Column Function(s)
Return Data to Application
|#Columns = 10

End of section


Optimizer Plan:

               RETURN
               (   1)
               |
               GRPBY
               (   2)
               |
               TBSCAN
               (   3)
               |
                SORT
               (   4)
               |
               NLJOIN
               (   5)
            /-/      \
      IXSCAN         TBSCAN
      (   6)         (   5)
   /      \            |
Index:    Table:Table:
DBO       DBO   DBO
EQPMAP_IEQPMAPR_PROCESS_TIME

請問一下該在那個table 新增什麼index 呢?
急急急

查看完整版本: SQL COST 過高 急急急