Re: Oracle SQL Tuning

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 5 Jul 2002 10:23:53 +0100
Message-ID: <1025861021.14424.0.nnrp-14.9e984b29@news.demon.co.uk>

Patrick

This seems like a classic example of:

<quote>
this works well

        on my system
        with my data distribution
        with my indexing strategy
        with my requirement

turning into:

    <quote>
    this works well
    <end quote>

Don't worry - there are lots of 'clever' suggestions in the field which are very
bad ideas because they omit the last
four clauses.

In your case, if you want to find just the one hundred rows in the second table
where the primary key matches, but
the non-primary key doesn't, the size
of the driving set is so small and the
other set so much larger that the
normal MINUS type of trick is not the
best idea.

BTW - if you use MINUS, in the way
you have envisaged, you will only get
the rows in table_a that are not in table_b, so any resulting rows

    might have a row in table_b with the     same primary key, but differences in     other columns
    or
    might not have a matching row at all     in table_b.

Possibly your best bet is to do a nested loop outer join from table_a to table_b on primary key, reporting rows where the
resulting table_b key values are null (no row) then union this with

    (
    table_a
    minus
    (
    table_a joined to table_b on PK
    using a nested loop
    reporting only table_b columns
    )
    )

Both steps can then be driven by
scans on the small table_a.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Patrick Lo wrote in message <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?

>

>

>--

>Regards,

>

>Patrick Lo

>

>

Received on Fri Jul 05 2002 - 04:23:53 CDT