Re: Problems with OR in Oracle 8i

Dan Dubinsky wrote:

>Not a very friendly bunch on this forum. I didn't really expect a solution
>to the specific queries. I just wanted to know if anybody knew of a problem
>with Oracle 8 and "OR".

People are a lot friendlier with a friendlier attitude from newbies. You are essentially saying "the optimizer doesn't work" with insufficient information for people to evaluate that statement (ie, the plans, incorrect equivalencies), so it comes across as attitude. Also, some of what you may be perceiving as unfriendliness may be a simple desire for specificity and accuracy, a common attribute of serious DBA's.

>Well in case anybody gets the same problem and comes across this thread, I
>think I've got it solved.

Well, hmmmm.

>Apparently this installation is set to use the old rule based optomizer.
>I'm told it can't be switched to the more modern cost based optomizer
>because a vendor supplied software application is using it and that would
>void the support agreement. But the "OR" queries can be made to work by
>telling Oracle to use cost based optomization with a query hint.

Has it occurred to you that you are voiding the support agreement?

>SELECT /*+ALL_ROWS*/ col1,co2 from table1, table2 where field1='val' or
>field2='val' etc...

>The /*+ALL_ROWS*/ hint made it work 100x faster.

>BTW: I did run explain plan. It told me it was doing a full table scan of
>the 2,000,000 row table. But it didn't make sense why it was doing that
>because there were indexes on all the columns being joined or searched and
>stats were up to date on all the tables. It seemed to be that there was a
>bug in the optomizer, which there is IMO, but only if you use the crappy old
>one. I guess Oracle developers didn't use "OR" before 1995.

Think on this: if you have a hundred rows with one index per row, would it be faster to get a hundred indices and then a hundred rows, or just get a hundred rows in whatever order they come? Doesn't outer join mean "get all the rows whether or not they match to the other table?"

Now think on this: If you are getting 50 rows near the beginning of a table, and 50 rows near the end, would it be faster to randomly get the first set and then the second set, or look through the whole table which may be much larger? The problem with rule-based is you have no way of knowing that they are near the beginning or end of the table or if it is large. Rule based is just a few simple rules which you can look up in the docs.

As far as the vendor requiring rule based, it's been my experience that nearly everything runs better with cost-based, and the few things that don't can be dinked with as you have figured out.

One more oddity: When it comes to the cost based optimizer, the plan you see may be different from the one at runtime. There are better tools.

>Also FYI: The version of: Oracle is "Oracle8i Enterprise Edition Release
>8.1.7.0.0 - Production".

You might check metalink for supported configuration information. You do have Oracle support, right?

See also http://www.dbaoracle.net/readme-cdos.htm

jg

--
@home.com is bogus.
http://www.thesneeze.com/mt-archives/cat_steve_dont_eat_it.php