Re: Tuning queries with "IS NULL" / "IS NOT NULL" conditions

From: Srinivas Chintamani <>
Date: Sat, 30 Jan 2010 19:57:25 -0500
Message-ID: <>

Hi Stephane,
Thank you very much for the response ... and ... the humor. I was actually laughing at the dig on the DESIGN or lack of it :) ! Unfortunately I do not have the authority to re-design this :( .

Please see my responses below (inline).

On Sat, Jan 30, 2010 at 4:30 AM, Stephane Faroult <>wrote:

> Srinivas,
> I hope that sale_name is indexed?

<Response> Yep, it is indexed. </Response>

> I am sorry that I'll have to disappoint you, but the query suggests that
> you should have several tables, not one. However skilled you are, when
> the design is rotten there isn't much that can be done. Obviously you
> cannot make NOT NULL a column that contains null values. But if I were
> you I'd try to negotiate a re-design with whomever has the power to give
> a go ahead.
> You don't seem to be encumbered with foreign keys.

Actually all of the columns Ent_id, rep and client are foreign keys into other tables and have indexes on each one of them. </Response>

> One option, if
> converting ENT_ID from number to varchar2 is acceptable (certainly
> easier than the reverse), would be to have something such as
> (id number not null, -- if you are not using it as an FK
> somewhere else, pretty useless IMHO
> whatever_id varchar2(30) not null,
> whatever_type char(1) not null, -- 'E', 'C' or 'R' depending on
> what whatever_id represents
> amount ...
> sale_name ...)
> This assumes that one sale is attached on only ONE E, C or R, which I
> suspect is the case from your query but which the current design (or
> rather lack of) doesn't enforce.

<Response> Yes, that is correct - one sale is attached to ONLY ONE E, C or R. </Response>

> If one sale can be attached to, say, one E and one R at once, you need
> two tables
> (id number not null, -- here it's useful
> amount ...
> sale_name ...)
> and
> (whatever_id ..
> whatever_type ...
> sale_id ...) FK that references id in the other table.
> If you have FKs (which would be a good thing but somehow doesn't fit
> with what you have told us) you should have four tables, one "head" table
> that contains (id, amount, sale_name) just as above, and three tables,
> one for type E, one for type C and one for type R that contains
> identifier and sales_id (just as above, but minus the type since all
> rows in one table are for ids of one type). That would allow you to mix
> varchar2 and numerical ids, and to have all the FKs you want.
> Your query would change a lot (in some cases you might have a UNION ALL)
> but you could scan what you really need and nothing else.
> But it would require to change all inserts/updates (for selects, you can
> build a view that looks like the current hmmph design. There are INSTEAD
> OF triggers of course, but my lizard brain finds them repulsive)
> Hope that helps,
> Stephane Faroult
> RoughSea Ltd <>
> RoughSea Channel on Youtube <>
> Srinivas Chintamani wrote:
> > Hi Listers,
> > Can you please share your thoughts about how to go about tuning
> > queries that use "IS NULL" / "IS NOT NULL" conditions in where clause?
> >
> > I have a million row table similar to the one shown below and a search
> > procedure that as indicated below. The table was built long time ago
> > and there is a LOT of existing data and making any of the NULL columns
> > NOT NULL is just not possible.
> >
> > I will very much appreciate any ideas about how to re-write the query
> > to be more efficient.
> >
> --

Srinivas Chintamani

Received on Sat Jan 30 2010 - 18:57:25 CST

Original text of this message