Re: Oracle SQL Tuning

Hi,

First of all, thanks for your reply.

As it cannot use the Primary key as indexing, is this way good for comparing records? Traditionally, did people use this way to compare records? For me, if this cannot use the Primary Key as indexing, I would prefer using the following SQL :

SELECT * FROM table_a

MINUS SELECT * FROM table_b b
WHERE exists
(SELECT * FROM table_a a
 WHERE a.pk_field_1 = b.pk_field_1
 AND a.pk_field_2 = b.pk_field_2
 AND .......
)

**REMARK: where pk_field_# is the column of the primary key for tables table_a and table_b.

Will this have the better performance?

Sybrand Bakker 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?
>
> No, it can not.
> Depending on version (which you of course don't mention) you might be
> capable to do an Index Fast Full Scan, but that's about it.
> You might be looking at a badly tuned database (insufficient
> sort_area_size, incorrect db_file_multiblock_read_count), as the MINUS
> is usually fast and other solutions will not compare complete records
> and will consume more I/O
>
> Hth
>
> Sybrand Bakker
> Senior Oracle DBA

--
Regards,

Patrick Lo