Q: Rule optimizer. Full-Table scans

Here's an odd thing I came across today.

Our database is running in RULE optimizer mode.

We've got a large query structured something like:

  SELECT stuff
  FROM table1, table2, table3, table4, table5   WHERE table1.f_key = table2.p_key
  AND table1.p_key IN (SELECT f_key

                          FROM   table3
                          WHERE  stuff)

Now, table1.p_key is uniquely indexed, as you'd expect. However, doing an EXPLAIN PLAN shows that we get a FULL TABLE SCAN on table1. All other tables use the correct indexes.

If I switch table1 and table2 in the FROM clause and in the WHERE clause (so that the IN is on the 1st line of the WHERE...) then the EXPLAIN shows that now table1 uses the index and table2 uses a FTS!!

All the tables are large so it slows things down badly.

What's going on???

Any answers anyone??

Pete

-- 

|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|


| PETER  MOORE  | pt_at_chaff.demon.co.uk       | "Either he's dead, or my watch |


|     DBA       |                            |  has stopped!" - Groucho       |

| MAT Transport | +44 (171) 410 6373         |                                |

|_____________________________________________________________________________|