Re: Oracle SQL Tuning

"Patrick Lo" <cylo_at_vtc.edu.hk> wrote in message news:3D24FF7F.2216FD9B_at_vtc.edu.hk...
> Hi,
>
> I got two data structure identifical tables table_a and table_b. Table
> table_a contains not more than 100 records while Table table_b contains
> more than 100000 records. Both tables have the same Primary Key.
>
> Now, I am going to "diff" the records in table_a with the ones in
> table_b and show the different records in table_a. Someone told me that
> I could use the following SQL statement to "diff" the records :
>
> SELECT * FROM table_a
>
> MINUS
>
> SELECT * FROM table_b
>
> He told me that it was efficient to find the difference of the two
> identificals. As I have not used this way to compare two sets of
> records, I went ahead to run the SQL statement. However, I found that
> the above SQL statement ran a long long time, even though the number
>
> Can the above statement use the Primary Key as indexing?

Patrick,

I think the query you have will compare all fields, not just those in the primary key.

If you only need to exclude records from the result where there is a matching primary key in table_b some thing like the following might work a little faster:-

SELECT *
FROM table_a
Where NOT EXISTS
(SELECT 1
FROM table_b
WHERE table_b.primary_key_fieldname_1 = table_a.primary_key_fieldname_1 AND table_b.primary_key_fieldname_2 = table_a.primary_key_fieldname_2 AND table_b.primary_key_fieldname_3 = table_a.primary_key_fieldname_3)

The example above assumes the primary key is based on three fields. Edit it as needed to match your primary key.

Paul Dixon Received on Fri Jul 05 2002 - 03:24:24 CDT