Re: High volume table comparison in Oracle

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

Some types of joins with ridiculous (from a relational design standpoint) business rules. Some specific situations in commercially available packages that generate OCI or ODBC calls. DBLINKS or different instances involved in the compare. Those are just some I've dealt with recently, I'm sure there are many more. Since the OP didn't give a whole lot of detail, there may be more going on here than we know. I'm not advocating using things outside of Oracle as a habit, just pointing out that some situations benefit greatly. I normally point this out to my customers and get explicit consent that they understand there may be future maintenance issues. The usual response is extreme gratitude that I got something useful that no one else could make work.

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

Well, there you go.

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

I should have been more specific that I meant versus the MINUS and similar other posts. I'd really like to see the OP post results from trying it all suggested ways, but am not holding my breath.

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

The key here is "most of the time." How much time are you going to put in after the Oracle Tuning Methodology is exhausted and you still have a problem?

As to RBO being history, I think that is a mistake on Oracle's part, essentially giving up on part of the low to mid (and some high) end of the market. If CBO is so great, why can't it reduce to RBO? I think CBO _is_ great, now that it finally is working, as are some things dependent on it, but saying there is no value to a large existing code base is wrong. Even a hacked up '60s application is better than vaporware.

jg

--
_at_home is bogus.
I know, let's give dangerous radiation to ordinary people with
conflicting directions!  "Don't watch the food cook."  "Watch food
carefully to avoid boil-over."