Re: High volume table comparison in Oracle

Running a query from unix doesn't make anything run faster. It's just 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);

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 Received on Mon Feb 24 2003 - 17:13:15 CET