Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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?
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
Received on Fri Jul 05 2002 - 03:05:21 CDT