> > In Oracle, the order with which you > > reference tables and make comparisons in the WHERE clause has > > a significant impact on performance. > >I had heard that Oracle reads WHERE clauses from the bottom up and that >clauses that exclude the most records should go last. But when I asked >about this on an Oracle list, I was told that WHERE clause order is no >longer relevant as of Oracle 8. Has anyone tested this to verify effect on >query performance? If so, what guidelines do you use?
You heard correctly from the Oracle list. Starting with the Oracle 8 family, the SQL parsing engine was optimized so that it now doesn't care what order you put the WHERE/AND statements in. It parses and determines what order to use on its own. I have tested between Oracle 7 and 8, and can tell you that, in 7, it does matter (it reads from bottom to top). However in 8, there is no difference between ordering from top-down or bottom-up. I don't have the test results on hand, but it's probably irrelevant anyway because the execution times will vary based on how your particular database is set up (table sizes, indices, etc.). Regards, Dave. ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
