SQL performance - optimizer question

I have a table of the form

	create table MYTABLE (
		key1		varchar2(10),
		key2		varchar2(10),
		key3		varchar2(10),
		val1		varchar2(30),
		val2		int
	);

There table could be large (perhaps a few million rows). Indexes exist for all the key fields. Any of the key fields can be NULL.

I'd like to write a -single- parametrized SQL statement (we're using OCI) that would allow for searches on this table where values can be provided for any number of parameters (at least one, up to as many keys as exist on the table.

What I'd like:

	select	val1, val2
	from	MYTABLE
	where	(key1 = :key1 or :key1 is null)
	and	(key2 = :key2 or :key2 is null)
	and	(key3 = :key3 or :key3 is null);

The following is legal syntax, but it's reeeal slow. It appears that the optimizer isn't clever enough to figure things out in advance and decides to do table scans. The Sybase optimizer can deal with this sort of thing; I'm trying to figure out if any trick will allow this to be done efficiently in Oracle.

I'd really like to avoid having to do this (pseudocode):

	if (:key1 is null)
		if (:key2 is null)
			if (:key3 is null)
				ERROR ("Must provide at least one key")
			else
				select	...
				where	key3 = :key3;
		else
			if (:key3 is null)
				select	...
				where	key2 = :key2;
			else
				select	...
				where	key2 = :key2
				and	key3 = :key3;
	else
		etc.

I tried the following, which requires the key fields to be made NOT NULL:

	select	...
	where	key1 like :key1||'%'
	and	key2 like :key2||'%'
	and	key3 like :key3||'%';

but this also causes table scans.

I couldn't find anything in the O'Reilly Performance Tuning book that would help out in this case.

Any help is appreciated,
-Jason

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


=======================================================================