Re: High volume table comparison in Oracle

danielroy10_at_hotmail.com (Daniel Roy) wrote in message news:<1b061893.0302240813.52eb8fa4_at_posting.google.com>...
> 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.

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

>
> If the performance is not acceptable, run the event 100046 to see how
> Oracle decides on its execution path. You might need to use some hints
> (use_hash, ordered, ...) or change some other parameter values
> (optimizer_index_cost_adj, db_file_multiblock_read_count, ...). Post
> more details (versions, execution plan, event 10046 log file,
> parameter values, ...) if you want more details.
>
> Daniel

jg

--
_at_home is bogus.
They shoulda asked McNealy about it:
http://www.signonsandiego.com/news/uniontrib/mon/business/news_mz1b24powerp.html