OPTCOMPIND = 0 : Why is this (always?) faster?

does the cost based optimizer take too much time to make up it's mind? Russ Evans Department of Health Information Resource Management Senior DBA 413-8490

Russ_Evans@doh.state.fl.us wrote: > > does the cost based optimizer take too much time to make up it's mind? That depends, Russ, and not on what you define as "too much" either ;-) By default the optimizer runs in HIGH mode in which it calculates the cost of all possible paths for the query before selecting the most cost effective. If there are more than 4 tables joined the time spend testing the thousands (or millions or billions) of possible paths can easily exceed the time to actually perform the query let alone the time saved by selecting the 'best' method. To solve this problem Informix permits you to SET OPTIMIZATION LOW, in this mode the optimizer first decides which table will be the cheapest to select as the first table to drive the join then only looks at the remaining tables as candidates for the second table joined and so on. The HIGH algorithm executes in O(N!) time while the LOW operates in SUM(1..N) time. Big improvement but it may not calculate the best path since while one table may be a slightly better choice for first table than another it may be a HUGELY better choice than any other for the second table and LOW will never test that option. Generally if you have 4 or more tables joined use LOW unless you have fewer than 7 tables and the query will be PREPARED once and executed many times in which case the up front cost may be well worth it. Seven table joins require HIGH to examine 5040 paths and 8 table joins 40320 paths and 10 tables is already 3.6 million paths so you see where this is going. Last week someone had a 16 table join and wondered why it was taking an hour an a half to execute. I think LOW brought that down to a minute or less (20.9 trillion paths for HIGH vs 136 paths for LOW). So you see that 'simple' answer is "it depends" on how complex the query is and what you have the optimization mode set to. Art S. Kagel