Re: Optimizer problems

BradMurray_at_SeeSigIfThere.com (Brad Murray) >> I have found that for our database, Oracle rarely, if ever, uses indexes unless the optimizer_mode is set to FIRST_ROWS. I have 2 SQL statements. One appears to be a subset of the other. All columns joined in the where clause are indexed. The first statement is using indexes on all but three tables. The second statement is not using any indexes. Network traffic and server resource utilization are not an issue. Both of these statements were executed many times with similar results. <<

Your query was rather long and being in upper case I could not stand to read it closely, but I did notice that it involves and outer join. When you do an outer join, in order to get the rows with no matching row in the join, Oracle must usually do a full table scan of the table with the rows that you requested that have no matching row in the other table.

After checking the above I would recommend that you make sure all the tables in the query have current statistics.

Once you have done the above two items, I would look to see which table you think should be the driving table and compare it to the table the optimizer chose. If the driving table in the explain is different than you opinion than use hints and rearrange the where clause to try to change the optimizer choice of drivers. Changing the driver can have a dramatic effect of the explain plan, but some queries only make since to do following one join order and that may be the path already in use.

Try using a '/*+ RULE */' hint just to compare the explain plans if the above do not help as this may help you see where you need to hint the query.

This is all based on the premise that a full table scan of the one sub-query is not the best way to go to begin with. Without the explain plan results for your system no one can really give you the answer.

Good Luck.

Mark Powell -- Oracle 7 Certified DBA
- The only advise that counts is the advise that you follow so follow your own advise - Received on Wed Dec 24 1997 - 00:00:00 CST