Archives of the TeradataForum

Message Posted: Thu, 04 Aug 2005 @ 18:13:16 GMT

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

<-- Anonymously Posted: Thursday, August 04, 2005 14:01 -->

I was having lunch reading the article by Dan Higgins in the TD Magazine and came across this recommendation for choosing a DW database: "A Cost based optimizer that does not require hints. The optimizer must not balk at complex queries, even when they require dozens of joins"

We have been struggling with some MSI generated queries because the MSI engine does not generate enough "hints" for the optimizer. To illustrate my point, I will share an actual example from my shop. Please note that all stats are 100% collected and up to date so we can discard that.

The query below was generated by MSI and runs in two minutes. If I manually introduce the transitive Property of Equality it will run in less than 5 seconds.

The transitive property of equality says: If A=B and B=C then A=C.

If I add the expression "AND a11.ItemID = 1021017" (that would be the A=C part) to the WHERE clause in the query below, its run time goes from 2 minutes to less than 5 seconds.

This is from the Where clause

          a11.ItemID = a13.ItemID    is the A=B part
               a13.ItemID = 1021017  is the B=C part

According to the EXPLAIN statement, without me manually introducing the "a11.ItemID = 1021017" expression in the WHERE clause, the optimizer chooses to perform a full table scan on 250M rows from table a11. If I introduce the expression then it will chose a NUSI on table a11 on ITEMid and hence the less than 5 second response.

This may sound like I am whining but after 15 years in the RDBMS business, I don't recall having to "hint" such a basic property to a plan analyzer.

So my questions are: Why do I need to "hint" to the optimizer this basic property of algebra? Is anyone encountering the same problem? Is there a way to tell MSI to start generation redundant expressions to help the optimizer?

Note: Select clause is abbreviated...

     SELECT  a14.PromoID (NAMED PromoID ) ,
              ETC
     FROM    PRODMSIVws.STORE_SKU_DAILY_PROMO_SALESv a11 ,
           PRODMSIVws.LOCATION_Dimensionv a12 ,
           PRODMSIVws.ITEM_Dimensionv a13 ,
           PRODMSIVws.FACT_PROMOTION_STORE_SKU a14 ,
           PRODMSIVws.PROMOTION_Dimensionv a15
     WHERE      a11.LocID = a12.LocID
             AND     a11.ItemID = a13.ItemID
             AND     a11.PromoID = a14.PromoID
             AND     a12.LocID = a14.LocID
             AND     a13.ItemID = a14.ItemID
             AND     a14.PromoID = a15.PromoID
             AND     a13.ItemID = 1021017
             AND     a14.PromoID > 0
             AND     a12.corpid  IN ( 1 )
             AND     a13.Family_ID  IN ( 1, 10 )
     GROUP   BY a14.PromoID , etc.

Thanks in advance for your thoughts and opinions.

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