Re: Query with NULL

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 31 Dec 2009 11:24:04 -0700
Message-ID: <4b3cfa54$1_at_news.victoria.tc.ca>

bob123 (bob123_at_gmail.com) wrote:
: Hi,

: I have a lot of queries like below:
: >select * from bxat.no5
: >WHERE (no0_session_id = :wp18 OR :wp18 IS NULL)
: >AND (tbname = :wp19 OR :wp19 IS NULL)
: so an access full on no5

: How can I speed up this query ?
: Thanks in advance
: (Oracle 9.2.0.6)

1. Try adding the not null explicitly to each section, I seem to recall that helps the optimizer recognize what to do.

	select * from bxat.no5
	WHERE 
	( :wp18 IS not NULL and no0_session_id = :wp18 OR :wp18 IS NULL)
	  ---------------------
	AND 
	( :wp19 IS not NUL and tbname = :wp19 OR :wp19 IS NULL)
	  ---------------------

then you could try using union. Someone suggested that though an illustration of it didn't seem to use it correctly

	select * from bxat.no5
	WHERE 
	( :wp18 IS not NULL and no0_session_id = :wp18 OR :wp18 IS NULL)
	union
	select * from bxat.no5
	WHERE 
	( :wp19 IS not NUL and tbname = :wp19 OR :wp19 IS NULL)

if the union helps but the main part of the query is complicated then put it in a with clause (my example may have the syntax wrong)

	with
	( select * from bxat.no5
	  where complicated stuff in common
	) as the_query
	select * from the_query where
	( :wp18 IS not NULL and no0_session_id = :wp18 OR :wp18 IS NULL)
	union
	select * from the_query where
	( :wp19 IS not NUL and tbname = :wp19 OR :wp19 IS NULL)

Received on Thu Dec 31 2009 - 12:24:04 CST