Re: Tuning queries with "IS NULL" / "IS NOT NULL" conditions
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: srinivas.chintamani@xxxxxxxxx
- Date: Sat, 30 Jan 2010 10:30:31 +0100
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.
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
