Re: Oracle SQL Tuning

Hi Paul,

The task was to compare the columns other than the Primary Key columns and showed all records with different content of the non-Primary Key columns in table_a . Certainly, we assumed that the Primary Key of each record could not be modified.

Thanks.

Paul Dixon wrote:

> "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

--
Regards,

Patrick Lo