Re: Problems with OR in Oracle 8i

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

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

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.

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.

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

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:ne9or1djvuk0erdspuq6vi2e77lalogc4n_at_4ax.com...
> On Wed, 4 Jan 2006 13:12:27 -0500, "Dan Dubinsky"
> <sofia_at_salmonllc.com> wrote:
>
>>It isn't just this query. Whenever I do an outer join with an OR in
>>additional filter criteria the query runs really slow.
>
> So run EXPLAIN PLAN on the query, and check the execution path
>>
>>Are there any issues with Oracle 8i optomizer, outer joins and ORs?
>>
>>Thanks
>
> Apart from the fact, there is no such thing as the Oracle 8i
> optomizer, the only issue here is providing insufficient information
> from your end.
> Which 'optomizer' are you using anyway?
> Are statististics current?
> Are you aware using an outer join usually heavily determines the
> choice of the driving table?
>
>
> --
> Sybrand Bakker, Senior Oracle DBA
Received on Wed Jan 04 2006 - 15:39:31 CST