Re: Optimizing Many-Table Joins

mmueller@calvin.bellahs.com (Michael Mueller RD) writes: >Can anyone tell me the tactics that the query optimizer uses for >optimizing joins, especially queries joining a large number (e.g. >4) >of tables? What is the max number of tables allowed in a query? >How does this compare to Sybase's handling? Thanks! First, keep in mind that Informix's optimizer is a cost-based optimizer. So, when examining the tables in a query the Informix optimizer will tend to process them in an order that produces the fewest number of returned rows with each join. In a case where there is no filter on any table, the first table processed will be the one with the fewest number of rows. Where there are filters on one or more tables, the first table processed will be the one judged by the optimizer to produce the fewest number of rows as the result of applying the filter(s). One way to make the optimizer's job (and your life) easier is to make sure each table has an index on joined columns. Note that the performance of any query is dependent on the quality of the statistics maintained in the system catalog tables. These statstics are used by the optimizer to estimate the cost of using different indexes to search a table and to estimate the number of rows returned using different query paths. It is the responsibility of the DBA to run UPDATE STATISTICS frequently enough to maintain accurate statistics on the value profile of each indexed column. I don't know if there is a maximum number of tables you can join in a query; I, personally, have written a query with a 13-table join which performs very nicely, thank you, and I have heard of joins containing up to 16 tables. ___ ___ Senior Consultant / ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210 _/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111 >--Michael > mmueller@bellahs.com