[Louis] Also, if I remove the order by, IP reports a drastic reduction is execution time i.e. down to 6msec. Does the ORDER BY on the PK make such a difference?
[Set]My guess would rather be that the ORDER BY forces Fb to fetch all rows, whereas without ORDER BY only the first few (random) rows are fetched, and that the time for fetching all rows rather than the first row are similar. [Louis] Unlikely, the table has millions of rows… [Ann] You seem to use left outer joins and that can be a problem because (in general) the order in which outer joins are declared in the query is the order in which they have to be accessed in the query plan. [Set] Sure, LEFT JOIN limits the optimizer. However, the WHERE clause only refers to the TILD (main) table, and the three LEFT JOINs seem to have a function similar to subselects used to find lookup values (the last three fields in the view). Hence, I'm pretty certain the optimizer would have chosen a very similar plan if LEFT had been deleted (it could of course have reordered the three "lookup tuples", but they're not related anyway (they each join to the TILD table, not eachother), so the same indexes should be used. [Louis] Correct, the view is just a select from the main table with some joins to ‘flatten’ it out so that immediate lookups are pulled in. Set [Non-text portions of this message have been removed]
