Re: <No Subject Supplied>

Sorry, but I'm not about to try and make attributions to this thread; it has grown too complicated and I'm too tired. That being said, somebody opined... > Put simply the Online optimizer gets the costs wrong! > It tends to prefer these Dynamic Hash Joins. This > means sequential scans are done rather than using > indexes. > > Setting OPTCOMPIND=0 means indexes will almost > certainly be used where they should be. The only answer to this is maybe, maybe not. I will agree that I don't always agree with the way it works; this is a battle I constantly fight. But remember that just because an index is faster in some cases, this is not always the case. I have forced queries to use hash joins by deleting indexes and had performance go up several hundred percent. The only way we will all be happy is when we get the optimizer hints^H^H^H^Hdirectives supported. I can't always delete the index; in more than one case the index I had to nuke was the primary key...and that is *rarely* a good idea! But, we will be able to force the optimizer to take a particular path if need be. If you have a problem with hash joins on your system, I (a) wish that *I* had that problem, and (b) think that maybe you have tuned a little closer to DSS system parameters than you may really want or need. David #include <sys/std_weasel_words>