7.10.UD1 Optimizer Inconsistencies

Perhaps someone can shed some light on the way the 7.10 optimizer is working (or not working). I ran a benchmark query with the following unexpected results. BTW, it was run on an empty SC2000 with 512MB ram, and OnLine was initialized before each run. 1). Ran the query with indexes attached to the table: 22 minutes. 2). Ran the query with indexes separated from the data on different devices: 10 minutes. This better performance was expected, but it was achieved ONLY after updating statistics high for each leading column in the index and updating statisitics medium for each non-leading column in the index. OPTCOMPIND was set to 0 and set explain reported a cost over 3,000,000. When I ran this prior to updating statisitics high and medium, only low statistics were used and the query never finished. I killed it when I saw it took longer than the 22 minutes above. --- so far so good --- 3). Ran the query again just like in #2 above, except I set OPTCOMPIND to 2 (the new default in UD1). The reported cost was only around 200,000 but the query was performed using hash joins and it actually took 20% longer. I obviously have reverted to using OPTCOMPIND of 0, even though the cost is higher, the performance is better. Doesn't make any sense however. Any optimizer experts out there? ================================================ Michael Reed, Radiix, Inc. 3588 Plymouth Rd. Suite 257 Ann Arbor, MI 48105 voice: 313-677-UNIX email: mreed@radiix.com