SUMMARY: Oracle SQL performance

Thanks for the responses to my earlier inquiry about tuning multi-parameter SQL queries with OCI. Unfortunately, the only solutions that appear to do the trick are real ugly, and require about the same amount of code as doing it wrapped in conditional logic to choose the best SQL based on the inputs. The trouble seems to be that Oracle won't optimize away fixed conditions. Here are a bunch of test cases:

This is quick:

	select	count(*)
	from	table
	where	1 = 0;

This requires a table scan:

	select	count(*)
	from	table
	where	1 = 1;

This (parameter substitution using OCI) runs fast or slow depending on the value of flag:

	select	count(*)
	from	table
	where	:flag = 1;

This is also fast (assuming an index on key):

	select	count(*)
	from	table
	where	key = :key
	AND	:flag = 1;

but this requires a table scan, regardless of the value of flag:

	select	count(*)
	from	table
	where	key = :key
	OR	:flag = 1;

This runs quickly (uses an index on key1):

	select	count(*)
	from	table
	where	key1 = :key1
	and	(key2 = :key2 or :key2 is null);

That ought to be enough examples...

It appears that Oracle is not clever enough to optimize away fixed conditions in advance, except for a single trivial false condition as in the first example. It will use a key in an AND clause to limit the search and then only scan a smaller number of rows; I'm sure it's terribly clever about choosing the best index when there are several potential candidates.

I conclude that it is impossible to write a fast single-query SELECT against a large table when ALL of the parameters are optional, e.g.

	select	count(*)
	from	table
	where	(key1 = :key1 or :key1 is null)
	and	(key2 = :key2 or :key2 is null)
	and	(key3 = :key3 or :key3 is null)
	...

But if any one of the parameters can be made mandatory, eliminating just one OR condition, the resulting reduction in the number of rows that must be scanned can make a huge improvement in performance.

Well, since I'm using OCI, why not just construct the query dynamically when it's needed and get the most efficient SQL depending on the parameters that are provided? The reason that I can't do that in this case is that we have a dedicated server process that will only perform a certain set of predefined SQL statements and no others, and this SQL is read from a test file at startup. This way we get the equivalent of stored procedures that allow select statements without needing ugly temporary tables, we can optimize queries without affecting the developers working on the application front-end, and we don't need to recompile anything when we modify our SQL. One workaround we're considering is to do some preparsing of the query file to allow a syntax like:

	select	count(*)
	from	table
	where	1=1


[key1]	and	key1 = :key1

[key2]	and	key2 = :key2


[key3]	and	key3 = :key3


	;

where the server will only include those WHERE clauses for which input values have been provided. The '1=1' (any trivial true condition) is so that the syntax works out. I think this will give us what we need.

Thanks for the suggestions to:

	GOGI_at_aster.si (Mladen Gogala)
	Kevin Neel <k-neel_at_nwu.edu>
	jl34778_at_corp02.d51.lilly.com
	mloennro_at_se.oracle.com (Magnus Lonnroth)
	mloennro_at_se.oracle.com (Magnus Lonnroth)
	nolan_at_helios.unl.edu (Michael Nolan)
	p0070621_at_oxford-brookes.ac.uk (Tommy Wareing)
	rhib_at_rvax.syntex.com (Hibbard M. Engler)

-Jason

   Jason W. May						jmay_at_ctp.com
   CTP, Inc.   304 Vassar St.   Cambridge MA 02139     (617)374-8223
=======================================================================