Are left outer joins messing up the optimizer?

I'm on Oracle 9i Release 2 on XP. I am having problems understanding exactly what the optimizer is trying to do. I have a small query joining three tables. Indexes were created for the primary keys. They look something like:

table a (aid number, other data columns);

table b (aid number, version number, other data columns)

table c (aid number, version)

Table a has 25,000 rows. Table B has in excess of 25,000 rows. Table c has about 12 rows. The query is:

SELECT A.aid, A.otherdatacolumns                      
  FROM A, B, C
 WHERE A.AId = C.AId
   AND B.AId = C.AId
   AND B.Version = C.Version
    

Am I missing something? Is there something disabled that I should enable?

The reason you see differences in the execution plan is that after you collect statistics, the cost based optimizer can use the statistics to generate what it believes to be an "optimal" plan based on the statistics. You comment that before analyzing the tables, you get a "good query". Then after analyzing the tables, you see full tables scans. Then if you put in a rule hint (which ignores the cost based optimizer and thus the statistics you collected via analyze), you get a "good query" again. When you say "good query" are you meaning that you consider a "bad query" to be one with a full table scan? Have you timed the executions of the query each way? Do you see a big difference in the time to execute for the "good query" vs. the "bad query"?

What I want to point out is that full tables scans don't always equate to "bad". If the optimizer is choosing a FTS over using an index, it thinks that it has chosen the optimal plan for your query. Now, if you are seeing a difference in the response time under each scenario, here are a couple of things to look at that will effect how the optimizer makes decisions.

There are a few init.ora parameters that you may want to look at modifying which will effect the choices the optimizer makes. They are:

1. optimizer_index_caching: you can change this from its default of 0 to something higher, let's say 50. This will cause the optimizer to assume that it will find 50% of index blocks in the cache.
2. optimizer_index_cost_adj: you can change this from its default of 100 to something lower, let's say 50. This lets you tune optimizer behavior for access path selection to be more or less index friendly, that is, to make the optimizer more or less prone to selecting an index access path over a full table scan. The default makes FTS and index use equal.
3. optimizer_mode: set this to FIRST_ROWS. That way, the optimizer will attempt to select a plan that will minimize response time.

Hope that helps!

For More Information

• Dozens more answers to tough Oracle questions from Karen Morton are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.