Re: Newbie SQL question

From: Peter Y. Hsing <hsing_at_ix.netcom.com>
Date: 1996/05/23
Message-ID: <4o0jbj$jb6_at_dfw-ixnews3.ix.netcom.com>#1/1


Newbie:

Try this (the view is actually unnecessary, but if it helps you then use it):

delete

from	t
where	exists
	(select	*
	 from	v
	 where	t.client_id = v.client_id
	 and	abs(t.billed) = abs(v.billed)
	 and	abs(t.paid) = abs(v.paid))

without the view, it looks like:

delete

from	t
where	exists
	(select	*
	 from	t t2
	 where	t.client_id = t2.client_id
	 and	abs(t.billed) = abs(t2.billed)
	 and	abs(t.paid) = abs(t2.paid)
	 and	nvl(t2.type,' ') = 'VOID')

These two queries can be somewhat slow if the table is large. In the main query, Oracle is performs a full table scan and then performs the subquery using an index for each t.client_id value retrieved from the main query. NOTE: You want to "disable" the index on t2.type by placing the "nvl()" function around it (there is a space between the two single quotes). Otherwise, Oracle will use the t2.client_id index AND the t2.type index, which is wasteful. There's no need for Oracle to do this, since the row have probably be more effectively retrieved using the t2.client_id index--Oracle's is "dumb" in this way. Some will say it provides more control to the coder--true, only if the coder is aware. The reason is more like: it's really difficult to create an optimizer that can evaluate index usage.

  • "abs()" is the absolute value function

***OR you can do (this is the best, optimized form, if t2.type is indexed):

delete

from	t
where	(client_id, abs(t.billed), abs(t.paid)) in
	(select	client_id, abs(t2.billed), abs(t2.paid)
	 from 	t t2
	 where	t2.type = 'VOID')

In this last SQL statement, assuming t.type is indexed, Oracle will first execute the subquery and create a subset of values. Oracle will then scan the index for t.client_id against this temporary subset and the delete the resultant rows. This form is fast--uses two indexes then hits the table.

If t.type is not indexed, maybe it should be (depends on the query usage and variability of the data that is stored in the column). If this is not indexed, then a full table scan is performed to create this temporary subset.

-Peter

On Wed, 22 May 1996 19:52:48 GMT, gressett_at_computek.net wrote:

>I have a table of transactions which contains records
>some of which need to be removed;
>Here is a simplified version of the table
>which we can call T:
>
>client_id varchar2(11)
>type varchar2(4)
>billed number(7, 2)
>paid number(7, 2)
>
>I have created a view V which serves
>as an identical copy of T.
>
>There are a number of records in T
>which contain in the type field the value
>'VOID'. For every such record there is
>at least one other record with the same
>client_id and billed and paid values which
>are the negative of the corresponding values
>in the VOID record. I need to delete both the
>VOID record and at most ONE of the matching
>records. I.e, consider these two records:
>
>'12345678901' 'XXXX' 100.00 50.00
>'12345678901' 'VOID' -100.00 -50.00
>
>Both of these should be deleted.
>
>The following SQL select statement will report
>the records that need to be deleted:
>
>select
> T.client_id,
> T.type,
> T.billed,
> T.paid
>from
> T,
> V
>where (
> T.client_id = V.client_id
> and
> T.billed = - V.billed
> and
> T.paid = - V.paid
>)
>
>What SQL will delete the same records
>that this select statement finds?
>
Received on Thu May 23 1996 - 00:00:00 CEST

Original text of this message