Re: Tunnig Help Please

From: Michael J. Ort <michael_ort_at_my-deja.com>
Date: 2000/04/10
Message-ID: <8cr898$cti$1@nnrp1.deja.com>#1/1

Try this. It's not pretty, but it'll make effective use of the indexes. If that's not fast enough, you may want to consider attacking it programmatically. Depends on what you need it for...

Good luck,
Michael J. Ort

--for inserts
select
  'INSERT' operation
, cur.*
from
  cur_month cur
where not exists(
  select
    'x'
  from
    pre_month pre
  where pre.p_key = cur.p_key
  )
UNION ALL
--for deletes
select
  'DELETE' operation
, pre.*
from
  pre_month pre
where not exists (
  select
    'x'
  from
    cur_month cur
  where pre.p_key = cur.p_key
  )
UNION ALL
--for updates
select
  'UPDATE' operation
, cur.*
from
  cur_month cur
  pre_month pre
where pre.p_key = cur.p_key
and pre.row1 != cur.row1
and pre.row2 != cur.row2

.
.
.

In article <955235392.20888.0.nnrp-01.c2de4f17_at_news.demon.co.uk>, "Mujahid Hamid" <mujahid_at_pharmco.demon.co.uk> wrote:
> Hi all,

> I have a two tables, table 'Pre_month' for Previous month and table
> 'Cur_months' for current month, these tables have with over 100,000
rows
> and I want to find out new inserted and updated records from
the 'cur_month'
> tables

>
> The following query will give me updated and inserted rows but I have
to
> mark the inserted and updated records , which I cannot do from the
following
> query.

> Solution 1.

> Select * from cur_month
> minus
> Select * from pre_month

>
> I know this query will do full table scans, but is this query quicker
than
> the following ??.

> Solution 2.

> select * from cur_month cur
> where not exists (select 'x' from pre_month pre where pre.p_key =
> cur.p_key)

>
> The above query will give me the new inserted records only, and I
still
> don't know the updated or deleted row.
>
> Is there a way where I can get inserted ,updated and deleted record
by just
> using set query (e.g. minus,intersect and union) if I can then will
this set
> query be quicker than exists or not exists using the primary key
columns.

> Please help me I am really stuck on this problem for days.

> Thanks in advance.

>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Apr 10 2000 - 00:00:00 CDT