Archives of the TeradataForum
Message Posted: Tue, 10 Jun 2003 @ 13:19:22 GMT
First let me sing the praises of the 'Order By' feature for Join Indexes. It is a great feature. It has proven to be a huge time-saver in terms of query response times.
However, I was wondering why the 'Order By' feature is ignored by the optimizer if I place an 'or' in the respective where statement on the Ordered column (not to be confused with an Order By NUSI). For instance:
Select Product_Dim.Product_Nm , Sum(case when Day between 1020101 and 1020131 then Vol_Sold else null end) as Jan_Vol , Sum(case when Day between 1020601 and 1020630 then Vol_Sold else null end) as Jun_Vol From Fact_table , Product_Dim Where Fact_table.Product_Cd = Product_Dim.Product_Cd and ( Fact_Table.Day between 1020101 and 1020131 or Fact_Table.Day between 1020601 and 1020630 )
-- Assuming a JI exists which prejoins the Fact_Table and Product_Dim and covers all the necessary columns and is Ordered By Day
In this example, the optimizer will ignore the Order By feature of the Join Index and scan the entire join index with a residual conidtion on Day. Seemingly, it would be much faster to use the Order By feature and only pull the requested data instead of scanning the entire table.
Does anyone know why the Optimizer is ignoring the Order By feature in this example?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|