OPT_GOAL

Has anyone experimented with changing the default for OPT_GOAL from all row to first rows? Would anyone recommend using first rows for a 200+GB database? Does anyone have any good/bad experiences that they can share? MS Sent via Deja.com http://www.deja.com/ Before you buy.

matur_suksema@my-deja.com wrote: > Has anyone experimented with changing the default for OPT_GOAL from all > row to first rows? Would anyone recommend using first rows for a 200+GB > database? Does anyone have any good/bad experiences that they can > share? You use FIRST_ROWS when it is most important that the first N rows of a query are returned quickly regardless of how much longer the complete query will take compared to ALL_ROWS. This feature was added at the request of many users, myself included, to optimize form/browse oriented applications that may issue queries which ultimately return thousands of rows but of which the final consumer is only interested in the first few dozen or first few hundred rows. An example of the costs involved is one query that we had that originally took 55 seconds for OL5.0x to complete using an index to satisfy the ORDER BY clause which meant that the first 18 rows (the only ones we really needed) were returned withing one second. When we ported to 7.xx the same query was being optimized to use a different index and sort and the entire query was completed in 13 seconds, however, the first 20 rows were not available to the application for 12 seconds, too long! We dropped that index and a third index was selected and the data sorted resulting in the first 20 rows being available in 13 seconds and the complete query in 14 seconds, even longer! Dropping both indexes caused the optimizer to perform the same indexed search that the older 5.0x optimizer had and return the first rows instantly and the entire query in 42 seconds (an improvement over 5.0x one will notice). The problem was we needed the other indexes for other queries which were now too slow instead. OPT_GOAL set to FIRST_ROWS for that query alone solved the problem (as would an index preference optimizer directive) in 7.24 and later. So the moral is you have to decide what YOUR goal is then you can decide what the optimizer's goal should be by default. You can always set the optimizer goal for specific queries as needed. Art S. Kagel