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

 < Last>>

 Subj: Optimizer and Transitive Property of Equality From: Anomy Anom

<-- 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.
```

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2005 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback