CBO picks wrong plan after analyze. FIRST_ROWS hint is workaround. ALL_ROWS causes wrong plan

From: <oracle10_at_gmail.com>
Date: 21 Sep 2006 10:58:34 -0700
Message-ID: <1158861514.550243.206350@m73g2000cwd.googlegroups.com>

We have a query joining large and small tables. Small table has 130K rows. LargeTable has 4M (4 million) rows Both have PK column called pk_id with Index on PK

    SELECT ST.pk_id

      FROM smalltable ST, largetable SL
     WHERE ST.pk_id = LT.pk_id
       AND LT.code_tp = 'maybe'
       AND LT.trans_date IS NULL
       AND LT.status <> 'Incomplete'

Table and Index were not analyzed for 6 months and CBO was picking correct plan where small table drives large table i.e. full scan on small table
for each row in smalltable, oracle uses PK index on large table to locate matching join row on largetable

This happened with all_rows or first_rows optimizer goal/mode

Few days ago, we updated / analyzed all tables and indexes

After analysis CBO started picking wrong plan and do FULL scan on large and small tables both and do Hash Join instead of Nested Loops in case of ALL_ROWS
FIRST_ROWS still works after analysis

Are there cases when following Oracle Recommendation by frequent ANALYZE/stats gathering causes CBO to go astray?

Thanks

Good Plan:
Execution Plan

---

   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=170900 Car
          d=42699 Bytes=1067475)

   1    0   NESTED LOOPS (Cost=170900 Card=42699 Bytes=1067475)
   2    1     TABLE ACCESS (FULL) OF 'SMALLTABLE' (Cost=104 Car
          d=42699 Bytes=384291)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'LARGETABLE' (Cost=4
Card


          =3830901 Bytes=61294416)

   4    3       INDEX (RANGE SCAN) OF 'PK_ID' (UNIQUE) (Cost=3 Card


          =3830901)

Bad Plan:

---

   0      SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=19388 Card=4
          2699 Bytes=1067475)

   1    0   HASH JOIN (Cost=19388 Card=42699 Bytes=1067475)
   2    1     TABLE ACCESS (FULL) OF 'SMALLTABLE' (Cost=104 Car
          d=42699 Bytes=384291)
   3    1     TABLE ACCESS (FULL) OF 'LARGETABLE' (Cost=15214
Card=38309
          01 Bytes=61294416)

Received on Thu Sep 21 2006 - 12:58:34 CDT