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

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Sat, 30 Jan 2010 10:30:31 +0100
Message-ID: <4B63FC37.5010009_at_roughsea.com>

Srinivas,

I hope that sale_name is indexed?

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. 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.
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 <http://www.roughsea.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

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.
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Jan 30 2010 - 03:30:31 CST