Originally posted by abhaysk Order of table may not matter ... not sure
Order of columns in where clause wud matter some times...
it wud be best if most restrictive column is preceded before any columns...(though it wud go for toss if not indexed col)
The order in which columns are indexes...if used in where clause in same order wud have beter performance ( i blv )..
if i am wrong please bark at me...
With the CBO, the order of the entries in the FROM and WHERE clause do not matter 99% of the time. Having said that, the best way to structure a statement, IMHO, is to bring one table at a time into the WHERE clause and join it to everything that's already there. Think like the compiler and bring the tables into the WHERE clause in the same order you want the optimizer to. Then change the FROM clause to match this order. This has several benefits:
- It is more readable
- It gets you thinking about how the query will/should be solved
- It makes sure that you don't miss a join. Remember that joining to everything that's valid and restricting by everything you can gives the optimizer more choices and will solve the majority of your performance problems.
- Things are now set up for an ORDERED hint if you need it
- Things are now set up for that 1% where the order actually does matter.
If yes, then ur first query went smoothly coz u restricted the table TPTFBDR with _A alias and the access order seems good.
If no, i am compeltely wrong.
Now column ttralnktix.NUMTRAFRTLNK was not indexed and with most restrictive statement on table TPTFBDR was with _B alias joined to this indirectly..
so its joining table also went for toss..for FTS.
Mind this is my feeling..may be wrong..just trying to analyze query :-)
Thanks for the response..all the columns of the table ttralnktix form a composite primary key and henece they were indexed.when i created one more index on ttralnktix.NUMTRAFRTLNK the query plan changed and the response was just a fraction of a second...any ideas why the plan changed so much