Apr 9, 2007
Could some body in microsoft database team explain this behavior? Problem is predominant when cardinality of a column is very high and a where clause is specified on that column. Both use the same index.
select a12.DATE_INVOICE_ID DATE_INVOICE_ID,
a11.CUSTOMER_ID CUSTOMER_ID,
sum(a11.EXTENDED_PRICE) WJXBFS1,
sum(a11.TOTAL_COST) WJXBFS2,
(sum(a11.EXTENDED_PRICE) - sum(a11.TOTAL_COST)) WJXBFS3
from FACT_SALES_LINE a11
join RLTN_V_SL_INVOICE_YTD a12
on (a11.DATE_INVOICE_ID = a12.DATE_INVOICE_CYTD_ID)
join LKP_V_SL_EXPENSE_CODE a13
on (a11.EXPENSE_CODE_ID = a13.EXPENSE_CODE_ID)
join LKP_V_SL_LAST_STATUS a14
on (a11.LAST_STATUS_ID = a14.LAST_STATUS_ID)
join LKP_V_SL_NEXT_STATUS a15
on (a11.NEXT_STATUS_ID = a15.NEXT_STATUS_ID)
join LKP_V_SL_ORDER_TYPE a16
on (a11.ORDER_TYPE_ID = a16.ORDER_TYPE_ID)
where (a11.CUSTOMER_ID in (1523364, 1522717, 1523004, 1523728, 1523809, 1523012)
and a12.DATE_INVOICE_ID = 106365
and a16.ORDER_TYPE_SRCCD not in ( 'ST','SG','SI','SU','SK','DL','S1','YU')
and a11.BUSINESS_UNIT_ID in (461100, 461400, 461600)
and a13.EXPENSE_CODE_SRCCD <> 'LC'
and a15.NEXT_STATUS_SRCCD = '999'
and a14.LAST_STATUS_SRCCD in ( '620','914') )
group by a12.DATE_INVOICE_ID,
a11.CUSTOMER_ID
OPTION ( FORCE ORDER )
PLAN without force order:
select a12.DATE_INVOICE_ID DATE_INVOICE_ID, a11.CUSTOMER_ID CUSTOMER_ID, sum(a11.EXTENDED_PRICE) WJXBFS1, sum(a11.TOTAL_COST) WJXBFS2, (sum(a11.EXTENDED_PRICE) - sum(a11.TOTAL_COST)) WJXBFS3 from FACT_SALES_LINE a11 join RLTN_V_SL_INVOICE_YTD a12 on (a11.DATE_INVOICE_ID = a12.DATE_INVOICE_CYTD_ID) join LKP_V_SL_EXPENSE_CODE a13 on (a11.EXPENSE_CODE_ID = a13.EXPENSE_CODE_ID) join LKP_V_SL_LAST_STATUS a14 on (a11.LAST_STATUS_ID = a14.LAST_STATUS_ID) join LKP_V_SL_NEXT_STATUS a15 on (a11.NEXT_STATUS_ID = a15.NEXT_STATUS_ID) join LKP_V_SL_ORDER_TYPE a16 on (a11.ORDER_TYPE_ID = a16.ORDER_TYPE_ID) where (a11.CUSTOMER_ID in (1523364, 1522717, 1523004, 1523728, 1523809, 1523012) and a12.DATE_INVOICE_ID = 106365 and a16.ORDER_TYPE_SRCCD not in ( 'ST','SG','SI','SU','SK','DL','S1','YU') and a11.BUSINESS_UNIT_ID in (461100, 461400, 461600) and a13.EXPENSE_CODE_SRCCD <> 'LC' and a15.NEXT_STATUS_SRCCD = '999' and a14.LAST_STATUS_SRCCD in ( '620','914') ) group by a12.DATE_INVOICE_ID, a11.CUSTOMER_ID 1 1 0 NULL NULL 1 NULL 1.138269 NULL NULL NULL 2.716851 NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE[Expr1022]=[Expr1020]-[Expr1021])) 1 2 1 Compute Scalar Compute Scalar DEFINE[Expr1022]=[Expr1020]-[Expr1021]) [Expr1022]=[Expr1020]-[Expr1021] 1.138269 0 1.138269E-07 66 2.716851 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1020], [Expr1021], [Expr1022] NULL PLAN_ROW 0 1
|--Compute Scalar(DEFINE[Expr1020]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END, [Expr1021]=CASE WHEN [Expr1025]=(0) THEN NULL ELSE [Expr1026] END)) 1 3 2 Compute Scalar Compute Scalar DEFINE[Expr1020]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END, [Expr1021]=CASE WHEN [Expr1025]=(0) THEN NULL ELSE [Expr1026] END) [Expr1020]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END, [Expr1021]=CASE WHEN [Expr1025]=(0) THEN NULL ELSE [Expr1026] END 1.138269 0 1.273072E-06 49 2.716851 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1020], [Expr1021] NULL PLAN_ROW 0 1
|--Stream Aggregate(GROUP BY[a11].[CUSTOMER_ID]) DEFINE[Expr1023]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1024]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1025]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [Expr1026]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=ANY([JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]))) 1 4 3 Stream Aggregate Aggregate GROUP BY[a11].[CUSTOMER_ID]) [Expr1023]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1024]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1025]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [Expr1026]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=ANY([JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]) 1.138269 0 1.273072E-06 49 2.716851 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1023], [Expr1024], [Expr1025], [Expr1026] NULL PLAN_ROW 0 1
|--Sort(ORDER BY[a11].[CUSTOMER_ID] ASC)) 1 5 4 Sort Sort ORDER BY[a11].[CUSTOMER_ID] ASC) NULL 1.17323 0.01126126 0.0001004628 41 2.71685 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 0 1
|--Nested Loops(Inner Join, OUTER REFERENCES[a11].[DATE_INVOICE_ID])) 1 6 5 Nested Loops Inner Join OUTER REFERENCES[a11].[DATE_INVOICE_ID]) NULL 1.17323 0 8.671883E-06 41 2.705488 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 0 1
|--Nested Loops(Inner Join, OUTER REFERENCES[a11].[EXPENSE_CODE_ID])) 1 7 6 Nested Loops Inner Join OUTER REFERENCES[a11].[EXPENSE_CODE_ID]) NULL 2.074613 0 8.671883E-06 41 2.702026 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| |--Nested Loops(Inner Join, OUTER REFERENCES[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID])) 1 8 7 Nested Loops Inner Join OUTER REFERENCES[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]) NULL 2.074613 0 8.671883E-06 45 2.697204 [a11].[CUSTOMER_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| | |--Stream Aggregate(GROUP BY[a11].[FSL_SEQ_NO]) DEFINE[a11].[CUSTOMER_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[CUSTOMER_ID] as [a11].[CUSTOMER_ID]), [a11].[EXPENSE_CODE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), [a11].[TOTAL_COST]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [a11].[EXTENDED_PRICE]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [a11].[DATE_INVOICE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]), [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]))) 1 9 8 Stream Aggregate Aggregate GROUP BY[a11].[FSL_SEQ_NO]) [a11].[CUSTOMER_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[CUSTOMER_ID] as [a11].[CUSTOMER_ID]), [a11].[EXPENSE_CODE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), [a11].[TOTAL_COST]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [a11].[EXTENDED_PRICE]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [a11].[DATE_INVOICE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]), [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=ANY([JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]) 2.074613 0 0.1950575 49 2.692634 [a11].[CUSTOMER_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID], [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 0 1
| | | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[ORDER_TYPE_ID]) OPTIMIZED) 1 10 9 Nested Loops Inner Join OUTER REFERENCES[a11].[ORDER_TYPE_ID]) OPTIMIZED NULL 390113 0 1.630672 53 2.497577 [a11].[FSL_SEQ_NO], [a11].[CUSTOMER_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID], [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 0 1
| | | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[LAST_STATUS_ID])) 1 12 10 Nested Loops Inner Join OUTER REFERENCES[a11].[LAST_STATUS_ID]) NULL 2.074613 0 9.189784E-06 53 0.0406176 [a11].[FSL_SEQ_NO], [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| | | | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[NEXT_STATUS_ID])) 1 13 12 Nested Loops Inner Join OUTER REFERENCES[a11].[NEXT_STATUS_ID]) NULL 2.198513 0 0.0001337915 57 0.03713583 [a11].[FSL_SEQ_NO], [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[LAST_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| | | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_06] AS [a11]), SEEK[a11].[CUSTOMER_ID]=(1522717) OR [a11].[CUSTOMER_ID]=(1523004) OR [a11].[CUSTOMER_ID]=(1523012) OR [a11].[CUSTOMER_ID]=(1523364) OR [a11].[CUSTOMER_ID]=(1523728) OR [a11].[CUSTOMER_ID]=(1523809)), WHERE[JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461100) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461400) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461600)) ORDERED FORWARD) 1 14 13 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_06] AS [a11]), SEEK[a11].[CUSTOMER_ID]=(1522717) OR [a11].[CUSTOMER_ID]=(1523004) OR [a11].[CUSTOMER_ID]=(1523012) OR [a11].[CUSTOMER_ID]=(1523364) OR [a11].[CUSTOMER_ID]=(1523728) OR [a11].[CUSTOMER_ID]=(1523809)), WHERE[JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461100) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461400) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461600)) ORDERED FORWARD [a11].[FSL_SEQ_NO], [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[BUSINESS_UNIT_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] 32.00753 0.0268287 0.00104035 66 0.02786905 [a11].[FSL_SEQ_NO], [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[BUSINESS_UNIT_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 0 1
| | | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[LU_NEXT_STATUS].[IX_LU_NEXT_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_SRCCD]=N'999' AND [JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[NEXT_STATUS_ID] as [a11].[NEXT_STATUS_ID]) ORDERED FORWARD) 1 15 13 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[LU_NEXT_STATUS].[IX_LU_NEXT_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_SRCCD]=N'999' AND [JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[NEXT_STATUS_ID] as [a11].[NEXT_STATUS_ID]) ORDERED FORWARD NULL 1 0.003125 0.0001581 9 0.008185391 NULL NULL PLAN_ROW 0 32.00753
| | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[LU_LAST_STATUS].[IX_LU_LAST_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'620' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID] OR [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'914' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID]) ORDERED FORWARD) 1 16 12 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[LU_LAST_STATUS].[IX_LU_LAST_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'620' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID] OR [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'914' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID]) ORDERED FORWARD NULL 1 0.003125 0.0001581 9 0.003472585 NULL NULL PLAN_ROW 0 2.198513
| | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_01]), SEEK[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] as [a11].[ORDER_TYPE_ID]) ORDERED FORWARD) 1 17 10 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_01]), SEEK[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] as [a11].[ORDER_TYPE_ID]) ORDERED FORWARD [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] 188041.3 0.2616435 0.2070025 11 0.8262868 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 0 2.074613
| | |--Clustered Index Seek(OBJECT[JDE_DATA].[dbo].[LU_ORDER_TYPE].[PK__LU_ORDER_TYPE__265B8A40] AS ), SEEK.[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), WHERE[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'DL' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'S1' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SG' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SI' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SK' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'ST' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SU' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'YU') ORDERED FORWARD) 1 31 8 Clustered Index Seek Clustered Index Seek OBJECT[JDE_DATA].[dbo].[LU_ORDER_TYPE].[PK__LU_ORDER_TYPE__265B8A40] AS ), SEEK.[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), WHERE[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'DL' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'S1' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SG' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SI' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SK' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'ST' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SU' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'YU') ORDERED FORWARD .[ORDER_TYPE_SRCCD] 1 0.003125 0.0001581 14 0.004555401 .[ORDER_TYPE_SRCCD] NULL PLAN_ROW 0 2.074613
| |--Clustered Index Seek(OBJECT[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[PK__LU_EXPENSE_CODE__2843D2B2]), SEEK[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), WHERE[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD]<>N'LC') ORDERED FORWARD) 1 32 7 Clustered Index Seek Clustered Index Seek OBJECT[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[PK__LU_EXPENSE_CODE__2843D2B2]), SEEK[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), WHERE[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD]<>N'LC') ORDERED FORWARD [JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD] 1 0.003125 0.0001581 16 0.004812614 [JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD] NULL PLAN_ROW 0 2.074613
|--Index Seek(OBJECT[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[IX_REL_CALENDAR_YEAR_TO_DATE_01]), SEEK[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=(106365) AND [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID_CYTD]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]) ORDERED FORWARD) 1 33 6 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[IX_REL_CALENDAR_YEAR_TO_DATE_01]), SEEK[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=(106365) AND [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID_CYTD]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]) ORDERED FORWARD [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] 1 0.003125 0.0001581 11 0.003452996 [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 0 2.074613
PLAN WITH FORCE ORDER:
select a12.DATE_INVOICE_ID DATE_INVOICE_ID, a11.CUSTOMER_ID CUSTOMER_ID, sum(a11.EXTENDED_PRICE) WJXBFS1, sum(a11.TOTAL_COST) WJXBFS2, (sum(a11.EXTENDED_PRICE) - sum(a11.TOTAL_COST)) WJXBFS3 from FACT_SALES_LINE a11 join RLTN_V_SL_INVOICE_YTD a12 on (a11.DATE_INVOICE_ID = a12.DATE_INVOICE_CYTD_ID) join LKP_V_SL_EXPENSE_CODE a13 on (a11.EXPENSE_CODE_ID = a13.EXPENSE_CODE_ID) join LKP_V_SL_LAST_STATUS a14 on (a11.LAST_STATUS_ID = a14.LAST_STATUS_ID) join LKP_V_SL_NEXT_STATUS a15 on (a11.NEXT_STATUS_ID = a15.NEXT_STATUS_ID) join LKP_V_SL_ORDER_TYPE a16 on (a11.ORDER_TYPE_ID = a16.ORDER_TYPE_ID) where (a11.CUSTOMER_ID in (1523364, 1522717, 1523004, 1523728, 1523809, 1523012) and a12.DATE_INVOICE_ID = 106365 and a16.ORDER_TYPE_SRCCD not in ( 'ST','SG','SI','SU','SK','DL','S1','YU') and a11.BUSINESS_UNIT_ID in (461100, 461400, 461600) and a13.EXPENSE_CODE_SRCCD <> 'LC' and a15.NEXT_STATUS_SRCCD = '999' and a14.LAST_STATUS_SRCCD in ( '620','914') ) group by a12.DATE_INVOICE_ID, a11.CUSTOMER_ID OPTION ( FORCE ORDER ) 1 1 0 NULL NULL 1 NULL 1.08425 NULL NULL NULL 9.249098 NULL NULL SELECT 0 NULL
|--Compute Scalar(DEFINE[Expr1022]=[Expr1020]-[Expr1021])) 1 2 1 Compute Scalar Compute Scalar DEFINE[Expr1022]=[Expr1020]-[Expr1021]) [Expr1022]=[Expr1020]-[Expr1021] 1.08425 0 1.08425E-07 66 9.249098 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1020], [Expr1021], [Expr1022] NULL PLAN_ROW 0 1
|--Compute Scalar(DEFINE[Expr1020]=CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END, [Expr1021]=CASE WHEN [Expr1037]=(0) THEN NULL ELSE [Expr1038] END)) 1 3 2 Compute Scalar Compute Scalar DEFINE[Expr1020]=CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END, [Expr1021]=CASE WHEN [Expr1037]=(0) THEN NULL ELSE [Expr1038] END) [Expr1020]=CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036] END, [Expr1021]=CASE WHEN [Expr1037]=(0) THEN NULL ELSE [Expr1038] END 1.08425 0 1.204394E-06 49 9.249098 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1020], [Expr1021] NULL PLAN_ROW 0 1
|--Stream Aggregate(GROUP BY[a11].[CUSTOMER_ID]) DEFINE[Expr1035]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1036]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1037]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [Expr1038]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=ANY([JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]))) 1 4 3 Stream Aggregate Aggregate GROUP BY[a11].[CUSTOMER_ID]) [Expr1035]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1036]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[EXTENDED_PRICE] as [a11].[EXTENDED_PRICE]), [Expr1037]=COUNT_BIG([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [Expr1038]=SUM([JDE_DATA].[dbo].[FACT_SALES_LINE].[TOTAL_COST] as [a11].[TOTAL_COST]), [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=ANY([JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]) 1.08425 0 1.204394E-06 49 9.249098 [a11].[CUSTOMER_ID], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID], [Expr1035], [Expr1036], [Expr1037], [Expr1038] NULL PLAN_ROW 0 1
|--Sort(ORDER BY[a11].[CUSTOMER_ID] ASC)) 1 5 4 Sort Sort ORDER BY[a11].[CUSTOMER_ID] ASC) NULL 1.103783 0.01126126 0.0001002863 41 9.249096 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 0 1
|--Parallelism(Gather Streams) 1 6 5 Parallelism Gather Streams NULL NULL 1.103783 0 0.02850539 41 9.237735 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
|--Merge Join(Inner Join, MANY-TO-MANY MERGE[a11].[ORDER_TYPE_ID])=(.[ORDER_TYPE_ID]), RESIDUAL[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_ID] as .[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] as [a11].[ORDER_TYPE_ID])) 1 7 6 Merge Join Inner Join MANY-TO-MANY MERGE[a11].[ORDER_TYPE_ID])=(.[ORDER_TYPE_ID]), RESIDUAL[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_ID] as .[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] as [a11].[ORDER_TYPE_ID]) NULL 1.103783 0.0004695 0.001520579 41 9.209229 [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
|--Sort(ORDER BY[a11].[ORDER_TYPE_ID] ASC)) 1 8 7 Sort Sort ORDER BY[a11].[ORDER_TYPE_ID] ASC) NULL 1.103783 0.002815315 2.507257E-05 45 0.09645625 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| |--Bitmap(HASH[a11].[ORDER_TYPE_ID]), DEFINE[Bitmap1034])) 1 9 8 Bitmap Bitmap Create HASH[a11].[ORDER_TYPE_ID]) [Bitmap1034] 1.103783 0 0.02850667 45 0.09361587 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[a11].[ORDER_TYPE_ID])) 1 10 9 Parallelism Repartition Streams PARTITION COLUMNS[a11].[ORDER_TYPE_ID]) NULL 1.103783 0 0.02850667 45 0.09361587 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[NEXT_STATUS_ID])) 1 11 10 Nested Loops Inner Join OUTER REFERENCES[a11].[NEXT_STATUS_ID]) NULL 1.103783 0 4.031072E-06 45 0.06510919 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[LAST_STATUS_ID])) 1 12 11 Nested Loops Inner Join OUTER REFERENCES[a11].[LAST_STATUS_ID]) NULL 3.857486 0 3.344787E-05 49 0.06137029 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[NEXT_STATUS_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[EXPENSE_CODE_ID])) 1 13 12 Nested Loops Inner Join OUTER REFERENCES[a11].[EXPENSE_CODE_ID]) NULL 32.00753 0 3.344787E-05 53 0.05315145 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| | | |--Nested Loops(Inner Join, OUTER REFERENCES[a11].[DATE_INVOICE_ID], [Expr1033]) WITH UNORDERED PREFETCH) 1 14 13 Nested Loops Inner Join OUTER REFERENCES[a11].[DATE_INVOICE_ID], [Expr1033]) WITH UNORDERED PREFETCH NULL 32.00753 0 3.344787E-05 57 0.03554453 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 1
| | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_06] AS [a11]), SEEK[a11].[CUSTOMER_ID]=(1522717) OR [a11].[CUSTOMER_ID]=(1523004) OR [a11].[CUSTOMER_ID]=(1523012) OR [a11].[CUSTOMER_ID]=(1523364) OR [a11].[CUSTOMER_ID]=(1523728) OR [a11].[CUSTOMER_ID]=(1523809)), WHERE[JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461100) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461400) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461600)) ORDERED FORWARD) 1 16 14 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_06] AS [a11]), SEEK[a11].[CUSTOMER_ID]=(1522717) OR [a11].[CUSTOMER_ID]=(1523004) OR [a11].[CUSTOMER_ID]=(1523012) OR [a11].[CUSTOMER_ID]=(1523364) OR [a11].[CUSTOMER_ID]=(1523728) OR [a11].[CUSTOMER_ID]=(1523809)), WHERE[JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461100) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461400) OR [JDE_DATA].[dbo].[FACT_SALES_LINE].[BUSINESS_UNIT_ID] as [a11].[BUSINESS_UNIT_ID]=(461600)) ORDERED FORWARD [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[BUSINESS_UNIT_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] 32.00753 0.0268287 0.0002600876 62 0.02708879 [a11].[ORDER_TYPE_ID], [a11].[CUSTOMER_ID], [a11].[BUSINESS_UNIT_ID], [a11].[LAST_STATUS_ID], [a11].[NEXT_STATUS_ID], [a11].[EXPENSE_CODE_ID], [a11].[TOTAL_COST], [a11].[EXTENDED_PRICE], [a11].[DATE_INVOICE_ID] NULL PLAN_ROW 1 1
| | | | |--Index Seek(OBJECT[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[IX_REL_CALENDAR_YEAR_TO_DATE_01]), SEEK[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=(106365) AND [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID_CYTD]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]) ORDERED FORWARD) 1 17 14 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[IX_REL_CALENDAR_YEAR_TO_DATE_01]), SEEK[JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID]=(106365) AND [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID_CYTD]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[DATE_INVOICE_ID] as [a11].[DATE_INVOICE_ID]) ORDERED FORWARD [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] 1 0.003125 0.0001581 11 0.008185391 [JDE_DATA].[dbo].[REL_CALENDAR_YEAR_TO_DATE].[DATE_ID] NULL PLAN_ROW 1 32.00753
| | | |--Clustered Index Seek(OBJECT[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[PK__LU_EXPENSE_CODE__2843D2B2]), SEEK[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), WHERE[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD]<>N'LC') ORDERED FORWARD) 1 18 13 Clustered Index Seek Clustered Index Seek OBJECT[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[PK__LU_EXPENSE_CODE__2843D2B2]), SEEK[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[EXPENSE_CODE_ID] as [a11].[EXPENSE_CODE_ID]), WHERE[JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD]<>N'LC') ORDERED FORWARD [JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD] 1 0.003125 0.0001581 16 0.01755811 [JDE_DATA].[dbo].[LU_EXPENSE_CODE].[EXPENSE_CODE_SRCCD] NULL PLAN_ROW 1 32.00753
| | |--Index Seek(OBJECT[JDE_DATA].[dbo].[LU_LAST_STATUS].[IX_LU_LAST_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'620' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID] OR [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'914' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID]) ORDERED FORWARD) 1 19 12 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[LU_LAST_STATUS].[IX_LU_LAST_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'620' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID] OR [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_SRCCD]=N'914' AND [JDE_DATA].[dbo].[LU_LAST_STATUS].[LAST_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[LAST_STATUS_ID] as [a11].[LAST_STATUS_ID]) ORDERED FORWARD NULL 1 0.003125 0.0001581 9 0.008185391 NULL NULL PLAN_ROW 1 32.00753
| |--Index Seek(OBJECT[JDE_DATA].[dbo].[LU_NEXT_STATUS].[IX_LU_NEXT_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_SRCCD]=N'999' AND [JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[NEXT_STATUS_ID] as [a11].[NEXT_STATUS_ID]) ORDERED FORWARD) 1 20 11 Index Seek Index Seek OBJECT[JDE_DATA].[dbo].[LU_NEXT_STATUS].[IX_LU_NEXT_STATUS_1]), SEEK[JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_SRCCD]=N'999' AND [JDE_DATA].[dbo].[LU_NEXT_STATUS].[NEXT_STATUS_ID]=[JDE_DATA].[dbo].[FACT_SALES_LINE].[NEXT_STATUS_ID] as [a11].[NEXT_STATUS_ID]) ORDERED FORWARD NULL 1 0.003125 0.0001581 9 0.003734868 NULL NULL PLAN_ROW 1 3.857486
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS.[ORDER_TYPE_ID]), ORDER BY.[ORDER_TYPE_ID] ASC), WHEREPROBE([Bitmap1034])=TRUE)) 1 21 7 Parallelism Repartition Streams PARTITION COLUMNS.[ORDER_TYPE_ID]), ORDER BY.[ORDER_TYPE_ID] ASC), WHEREPROBE([Bitmap1034])=TRUE) NULL 20.5 0 0.0285224 11 9.11078 .[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Merge Join(Inner Join, MERGE.[ORDER_TYPE_ID])=([JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), RESIDUAL[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_ID] as .[ORDER_TYPE_ID])) 1 22 21 Merge Join Inner Join MERGE.[ORDER_TYPE_ID])=([JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), RESIDUAL[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]=[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_ID] as .[ORDER_TYPE_ID]) NULL 20.5 0 0.001586888 11 9.082257 .[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS.[ORDER_TYPE_ID]), ORDER BY.[ORDER_TYPE_ID] ASC)) 1 23 22 Parallelism Repartition Streams PARTITION COLUMNS.[ORDER_TYPE_ID]), ORDER BY.[ORDER_TYPE_ID] ASC) NULL 328 0 0.03033649 11 0.03529974 .[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
| |--Clustered Index Scan(OBJECT[JDE_DATA].[dbo].[LU_ORDER_TYPE].[PK__LU_ORDER_TYPE__265B8A40] AS ), WHERE[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'DL' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'S1' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SG' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SI' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SK' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'ST' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SU' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'YU') ORDERED FORWARD) 1 24 23 Clustered Index Scan Clustered Index Scan OBJECT[JDE_DATA].[dbo].[LU_ORDER_TYPE].[PK__LU_ORDER_TYPE__265B8A40] AS ), WHERE[JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'DL' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'S1' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SG' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SI' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SK' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'ST' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'SU' AND [JDE_DATA].[dbo].[LU_ORDER_TYPE].[ORDER_TYPE_SRCCD] as .[ORDER_TYPE_SRCCD]<>N'YU') ORDERED FORWARD .[ORDER_TYPE_ID] 328 0.004606482 0.00013165 18 0.004738132 .[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Stream Aggregate(GROUP BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID])) 1 25 22 Stream Aggregate Aggregate GROUP BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]) NULL 27 0 1.6875E-05 11 9.045368 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), ORDER BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] ASC)) 1 26 25 Parallelism Repartition Streams PARTITION COLUMNS[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]), ORDER BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] ASC) NULL 108 0 0.0291047 11 9.045351 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Stream Aggregate(GROUP BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID])) 1 27 26 Stream Aggregate Aggregate GROUP BY[JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID]) NULL 108 0 0.634653 11 9.016247 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
|--Index Scan(OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_01]), ORDERED FORWARD) 1 28 27 Index Scan Index Scan OBJECT[JDE_DATA].[dbo].[FACT_SALES_LINE].[IX_FACT_SALES_LINE_01]), ORDERED FORWARD [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] 5077116 6.985347 1.396246 11 8.381594 [JDE_DATA].[dbo].[FACT_SALES_LINE].[ORDER_TYPE_ID] NULL PLAN_ROW 1 1
View 4 Replies
View Related
Jul 23, 2005
HelloI have these tables:CREATE TABLE [dbo].[COREAttribute] ([oid] [uniqueidentifier] NOT NULL ,[CLSID] [uniqueidentifier] NOT NULL) ON [PRIMARY]CREATE UNIQUE CLUSTERED INDEX [COREAttributeOidIndex] ON[dbo].[COREAttribute]([oid], [CLSID]) WITH FILLFACTOR = 90 ON[PRIMARY]CREATE TABLE [dbo].[COREBstrAttribute] ([oid] [uniqueidentifier] NOT NULL ,[iid] [uniqueidentifier] NOT NULL ,[dispid] [int] NOT NULL ,[value] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]CREATE CLUSTERED INDEX [COREBstrAttributeOidIndex] ON[dbo].[COREBstrAttribute]([oid]) WITH FILLFACTOR = 90 ON [PRIMARY]Now when I try this query, it's taking 8-10mins.Declare @t TABLE (oid uniqueidentifier primary key,[Description] nvarchar(1024) NULL,[Name] nvarchar(1024) NULL,[UID] nvarchar(1024) NULL)DECLARE @COREBSTRAttribute TABLE (oid uniqueidentifier, dispid intNULL, value nvarchar(1024) NULL)INSERT INTO @COREBSTRAttribute select oid, dispid, valueFROM dbo.COREBSTRAttributeWHERE iid ='{1449DB20-DB97-11D6-A551-00B0D021E10A}'INSERT @tSELECT distinctc0.oid,c1.Value,c2.Value,c3.ValueFROM(SELECT oid FROM dbo.COREAttributeWHERE CLSID IN ('{1449DB2B-DB97-11D6-A551-00B0D021E10A}','{1449DB2D-DB97-11D6-A551-00B0D021E10A}','{1449DB2F-DB97-11D6-A551-00B0D021E10A}','{1449DB31-DB97-11D6-A551-00B0D021E10A}','{1449DB33-DB97-11D6-A551-00B0D021E10A}','{1449DB35-DB97-11D6-A551-00B0D021E10A}','{1449DB37-DB97-11D6-A551-00B0D021E10A}','{1449DB39-DB97-11D6-A551-00B0D021E10A}','{1449DB3B-DB97-11D6-A551-00B0D021E10A}','{1449DB3D-DB97-11D6-A551-00B0D021E10A}','{1449DB3F-DB97-11D6-A551-00B0D021E10A}','{1449DB43-DB97-11D6-A551-00B0D021E10A}','{1449DB45-DB97-11D6-A551-00B0D021E10A}','{1449DB47-DB97-11D6-A551-00B0D021E10A}','{1449DB49-DB97-11D6-A551-00B0D021E10A}','{1449DB4B-DB97-11D6-A551-00B0D021E10A}','{1449DB4D-DB97-11D6-A551-00B0D021E10A}','{1449DB51-DB97-11D6-A551-00B0D021E10A}','{DAA598D9-E7B5-4155-ABB7-0C2C24466740}','{6921DAC3-5F91-4188-95B9-0FCE04D3A04D}','{128F17D4-2014-480A-96C6-370599F32F67}','{9F3A64C9-28F3-440B-B694-3E341471ED8E}','{2E3AB438-7652-4656-9A18-4F9C1DC27E8C}','{B69E74A7-0E48-4BA2-B4B7-5D9FFEDC2D97}','{2BB836D3-2DC1-4899-9406-6A495ED395C3}','{9CFFDC3A-5DF5-4AD8-B067-6EF5A9736681}','{E18E470B-B297-43D2-B9CD-71AF65654970}','{9BDCDA97-1171-409D-B3AB-71DA08B1E6D3}','{0E91AC62-7929-4B42-B771-7A6399A9E3B0}','{C8BAE335-CCB7-4F1D-8E9D-85C301188BE2}','{97E6E186-8F32-42E6-B81C-8E2E0D7C5ABA}','{BE5B6233-D4E7-4EF6-B5FC-91EA52128723}','{4ECDAAE1-828A-4C43-8A66-A7AB6966F368}','{19082B90-EF02-45CC-B037-AFD0CF91D69E}','{6F76CEF7-EBC0-48C6-8B78-C5330324C019}','{18492042-B22A-4370-BFA3-D0481800BBC7}','{A71343AD-CC09-4033-A224-D2D8C300904A}','{EC10BD0A-FDE3-4484-BEA6-D5A2E456256C}','{F7F8A4E1-651A-4A48-B55A-E8DA59D401B2}','{A923226F-B920-4CFA-9B0D-F422D1C36902}','{A95ACA6A-16AC-47E4-A9A6-F530D50A475A}','{C31DB61A-5221-42CF-9A73-FE76D5158647}')) AS c0LEFT JOIN @COREBSTRAttribute AS c1ON (c0.oid = c1.oid)AND c1.dispid = 28LEFT JOIN @COREBSTRAttribute AS c2ON (c0.oid = c2.oid)AND c2.dispid = 112LEFT JOIN @COREBSTRAttribute AS c3ON (c0.oid = c3.oid)AND c3.dispid = 192Any help is greatly appreciated.thanksSunit
View 4 Replies
View Related
