Archives of the TeradataForum
Message Posted: Thu, 04 Aug 2005 @ 18:13:16 GMT
<-- 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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|