tuning request and join index

From: Pif <fjspam_at_laposte.net>
Date: Wed, 23 Sep 2009 03:04:32 -0700 (PDT)
Message-ID: <709363fd-9a36-4165-9b0a-3ec74e538ac4_at_f10g2000vbf.googlegroups.com>


I've a request that is like:

select l1.bill_id
from file f1

   join file f2 on f1.letter_id = f2.letter_id    join letter l on f1.letter_id = l.letter_id

so, the file table and letter table are big ( > 100000 rows) and I need that execution.
Join between both file aliases is not on ids, so there is no FK but there is an index.

So, how can I optimize the request or the schema to have fast answer ? What type of index should I create and how for such case ?

The only solution that my chief would like to avoid the materialized views...

Thanks a lot for your help Received on Wed Sep 23 2009 - 05:04:32 CDT

Original text of this message