Re: SQL vs. Oracle

On 27 Jan 2000 11:42:21 EST, "Daniel" <delj_at_flash.net> wrote:

>I use both... have major issues with both... Oracle is indeed more robust,
>but at least SQL 7.0 knows to use an existing index when it optimizes a
>query. My last project for Oracle 8 included a database which was set to
>default for cost optimization. There were instances where a simple 2 table
>inner join refused to use indexes, performed a full table scan and needed a
>hint to work properly. I'm keeping my customers away from 8 until I can
>count on the optimizer.
>

Hate to sound like I'm telling granny how to suck eggs but here are a few things you could try, they usually work for me:

1. Make sure none of your predicates uses functions on the columns, explicitly and/or implicitly. An example is where you compare character to number and ORACLE does the wrong thing and applies an implicit conversion function the wrong way (on the column that is indexed).
2. Use full analyze. If that fails, use "analyze table XYZ compute statistics for all indexed columns". That usually fixes these nagging problems.
3. Turn off the hash joins. For some strange reason, this has been given a WAY TOO high bias in the optimizer and sometimes it insists on doing it even though a nested loop or a merge join would be an order of magnitude faster...

Guy Harrison has an excellent book out on this stuff and how to take advantage of these hints. You probably already know how helpful they can be. Of course, the above suggestions are to be applied instead of the hints. They usually cover 90% of the nasties, particularly 1).

HTH Cheers
Nuno Souto
nsouto_at_nsw.bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Fri Jan 28 2000 - 04:52:23 CST