Re: Tuning queries with "IS NULL" / "IS NOT NULL" conditions
- From: Srinivas Chintamani <srinivas.chintamani@xxxxxxxxx>
- To: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- Date: Sat, 30 Jan 2010 19:57:25 -0500
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 <sfaroult@xxxxxxxxxxxx>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.
<Response>
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 <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.
> >
>
>
> --
Regards,
Srinivas Chintamani
Other related posts:
