Re: Newbie SQL question
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