SEO

Hi,

I have received the challenge to compare a table and a view. What I need to
do is to check on certain columns if all entries in view A are in table B
and if there are any entries in table B not in view A.

The view is a join using 2 columns on 2 tables. The columns I compare on are
in 1 table for the view but I need to restrict and 1 column I need to
restrict on is in the other table of the view.
I have the choice to compare each row or to 1st sum up and then compare.

View A:
OBJNR BELNR BUZEI WKGBTR (.... and some other columns we do not
need to compare but some of them we use to narrow down).

Table B:
OBJNR BELNR BUZEI WKGBTR (.. and again, some other columns we do
not need to compare but we need to use 1 of them to restrict).

To me I have the following options:

A) select objnr, belnr, buzei, wkgbtr from view A where (objnr, belnr,
buzei, wkgbtr) not in (select objnr, belnr, buzei, wkgbtr from table B)

B) select objnr, belnr, buzei, sum_wkgbtr from (select objnr, belnr, buzei,
sum(wkgbtr) as sum_wkgbtr from view A group by objnr, belnr, buzei) where
(objnr, belnr, buzei, sum_wkgbtr) not in (select objnr, belnr, buzei,
sum(wkgbtr) from table B group by objnr, belnr, buzei)

C) select objnr, belnr, buzei, wkgbtr from view A except all select objnr,
belnr, buzei, wkgbtr from table B

D) select objnr, belnr, buzei, sum(wkgbtr) from view A except all select
objnr, belnr, buzei, sum(wkgbtr) from
table B

Now my question is, which one will most likely be the fastest and are there
any other methods for doing this then what I mentioned above which would
more likely be faster? I'm doing benchmarking right now.

For view A, we have
6459947 rows in the table which contains the columns we compare on
1446113 rows in the table we join with and which contains 1 columns we
need to narrow down on.

For table B we have 3692280 rows in the table.

So one could say that we need to compare 3692280 rows since the selection on
the view will narrow down from the number given above.
There is no foreign key relationship between any of the tables.


Thanks.

If any of the columns are numeric, you can use functions like MIN, MAX,
COUNT(), AVG and SUM to determine if identical values are appearing in
both the table and view.

Erik Hendrix wrote:

> Hi,
>
> I have received the challenge to compare a table and a view. What I need to
> do is to check on certain columns if all entries in view A are in table B
> and if there are any entries in table B not in view A.
>
> The view is a join using 2 columns on 2 tables. The columns I compare on are
> in 1 table for the view but I need to restrict and 1 column I need to
> restrict on is in the other table of the view.
> I have the choice to compare each row or to 1st sum up and then compare.
>
> View A:
> OBJNR BELNR BUZEI WKGBTR (.... and some other columns we do not
> need to compare but some of them we use to narrow down).
>
> Table B:
> OBJNR BELNR BUZEI WKGBTR (.. and again, some other columns we do
> not need to compare but we need to use 1 of them to restrict).
>
> To me I have the following options:
>
> A) select objnr, belnr, buzei, wkgbtr from view A where (objnr, belnr,
> buzei, wkgbtr) not in (select objnr, belnr, buzei, wkgbtr from table B)
>
> B) select objnr, belnr, buzei, sum_wkgbtr from (select objnr, belnr, buzei,
> sum(wkgbtr) as sum_wkgbtr from view A group by objnr, belnr, buzei) where
> (objnr, belnr, buzei, sum_wkgbtr) not in (select objnr, belnr, buzei,
> sum(wkgbtr) from table B group by objnr, belnr, buzei)
>
> C) select objnr, belnr, buzei, wkgbtr from view A except all select objnr,
> belnr, buzei, wkgbtr from table B
>
> D) select objnr, belnr, buzei, sum(wkgbtr) from view A except all select
> objnr, belnr, buzei, sum(wkgbtr) from
> table B
>
> Now my question is, which one will most likely be the fastest and are there
> any other methods for doing this then what I mentioned above which would
> more likely be faster? I'm doing benchmarking right now.
>
> For view A, we have
> 6459947 rows in the table which contains the columns we compare on
> 1446113 rows in the table we join with and which contains 1 columns we
> need to narrow down on.
>
> For table B we have 3692280 rows in the table.
>
> So one could say that we need to compare 3692280 rows since the selection on
> the view will narrow down from the number given above.
> There is no foreign key relationship between any of the tables.
>
>
> Thanks.
>
>