> Lowdown: > What is the optimum arrangement of WHERE clauses when > querying an Oracle database? > > Details: > I'm trying to optimize my CF queries running against an > Oracle 8i database. I know that conventional wisdom is to > put highly selective columns (those with many unique values) > first in the WHERE clause so that the result set is as > small as possible for the following clauses. But I recall > reading somewhere that Oracle actually processes WHERE > clauses in reverse order--from last to first. Does anyone > on the list know for sure about this? And does the order/ > placement of indexed columns significantly affect the query > speed?
I sincerely doubt that any modern enterprise database platform (Oracle, Sybase, MS SQL Server for example) will favor one ordering of joins or filters over another. I don't know that for sure, but that's my strong suspicion. In any case, this is easy enough to determine. Just write the same query twice, and test it with Oracle's equivalent of SHOWPLAN, whatever that is (EXPLAIN PLAN, maybe?). I'll bet that the two queries will have the same query plan, and will perform equally well. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.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
