Archives of the TeradataForum

Message Posted: Tue, 10 Jun 2003 @ 13:19:22 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

All,

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?

Thanks,

Clay

	<Prev	Next>		<<First	<Prev	Next>	Last>>