Re: Cost-based Optimizers

Bill Busch (71333.2231@CompuServe.COM) wrote: : I am doing some research into cost based optimizers. My : experience has been limited to ORACLE (no sympathy please) which : does not have a decent cost-based optimizer (when ORACLEs tech : support and education departments tell you not to use their : optimizer because it will generally hinder performance, it say a : lot about the quality or, lack there of, of their optimizer). : So, what attributes make up a good cost-based optimizer and how : do the major vendors (ORACLE, INGRES, SYBASE, and INFORMIX) : compare. I have heard that the most advance cost-based optimizer : is included in INGRES. If so, what makes their optimizer so much : better? : Thanks in advance. : -- : Bill Busch : Sales Technologies, Atlanta, GA : "If one develops software which idiots can use, only idiots will : use it" Hi, My knowledge about the topic is not most up to date. But as far as I know Ingres has implemented histograms. INGRES engine knows not only of how many rows does table have, but also what is the density of rows in some interval of index values. Informix engines generally speaking know how many rows does table have and what indices are built. Oracle 5.0 optimizer knew only, what indices does table have. Query with different order of tables in the FROM clause have had different plans. They performed differently. But I'd like to know what is the situation for today. Does anybody know? Wiesiek Poszewiecki University of Agriculture and Technology Olsztyn, Poland