utilize index

From: Vadim Grepan <kezal_at_mail.ru>
Date: Mon, 26 Feb 2001 11:49:34 +0300
Message-ID: <3A9A189E.CB7322C@mail.ru>

Hello All!

   I cannot use existing index during select with join-clause. There are two large tables, with common relation Parent-Child (PK-FK). Both columns indexed. Simple select from child table successfull using index. Meanwhile select with join rejects them and does fullscan. Index has valid state but even hint is helpless.

  Tables are CONTRIBUTOR and CONTRIBUTORINFORMATION.   Simple select-clause has good execution plan:

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=7)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CONTRIBUTORINFORMATION'           (Cost=3 Card=1 Bytes=7)

   2    1     INDEX (UNIQUE SCAN) OF 'PK_CONTRIBUTORINFORMATION' (UNIQ
          UE) (Cost=2 Card=1)

Select with join-clause:

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4877 Card=167435336
          Bytes=28966313128)

   1    0   HASH JOIN (Cost=4877 Card=167435336 Bytes=28966313128)
   2    1     TABLE ACCESS (FULL) OF 'CONTRIBUTOR' (Cost=627 Card=1686
          7 Bytes=2799922)

   3    1     TABLE ACCESS (FULL) OF 'CONTRIBUTORINFORMATION' (Cost=30
          0 Card=992680 Bytes=6948760)

It works on Sun SPARC, Oracle EE 8.1.6

Rgds, Vadim Grepan

---

Moscow, Russia Received on Mon Feb 26 2001 - 02:49:34 CST