Re: High volume table comparison in Oracle

joel-garry_at_home.com (Joel Garry) wrote in message
> > Running a query from unix doesn't make anything run faster. It's just
>
> No, but in some cases it can avoid a lot of running! Not the OP
> situation, though.

What do you mean, by "it can avoid a lot of running"? In which cases? Myself, the only time I purposedly ran SQL from Unix was because I knew a big sort would be handled quicker with the Unix "sort" than by Oracle's.

>
> > more convenient when you want to incorporate SQL logic into a Unix
> > program (Shell script, Perl script, Java program, ...). This is
> > clearly not your situation. As a rule of thumb, use PL/SQL only if SQL
> > can't do the job, since it's (generally) slower. Instead, try this:
> > Bump as much as you can (without disturbing the other users) the
> > parameter HASH_AREA_SIZE, set OPTIMIZER_MODE to ALL_ROWS, analyze the
> > tables, and run a query such as:
> >
> > select * from table1 where not exists (select 1 from table2 where
> > columns_in_table1 = columns_in_table2);
>
> Isn't this the slowest way to do this? Maybe CBO is smarter than I thought,
> but "not exists"... I'm probably just stuck in RBO-think.

From what I've seen, "not exists" almost always outperforms "not in". This is especially true when the subquery returns many rows (as is the case here). I also believe that with everything set up properly (objects recently analyzed, useful indexes present, ...), CBO will most of the time outperform RBO, which must be Oracle's opinion also, since RBO will be history in 10i.

Daniel Received on Wed Feb 26 2003 - 16:29:17 CET