Sql Tuning?

From: Emanuel Blaser <emanuelblaser_at_yahoo.com>
Date: 15 Oct 2002 10:10:44 -0700
Message-ID: <857de1e3.0210150910.5703b278@posting.google.com>

Hi,

I'm trying to optimize a query wich takes too long. I'm not a DBA.

It looks like that.

Select a.f1 
     , b.f2 
     , c.f3 
From a      -- on current database1 / 20'000 rows


   , b      -- with a link on database2 / 1'000'000 rows

   , c      -- with a link on database2 / over 1'000'000 rows

Where a.nr1 || a.nr2 = b.nr -- is 1 to max(5)
  and b.fnr = c.id          -- is 1 to 1
  and a.str = '10'          -- returns only 500 rows

All fields used in the where statement have indexes.

Query Plan

---

SELECT STATEMENT Cost = 105105

    MERGE JOIN

        SORT JOIN  
            MERGE JOIN   
                SORT JOIN  
                    REMOTE   
                SORT JOIN  
                    REMOTE   
        SORT JOIN  
            TABLE ACCESS FULL T_VERTRAG

Execution Time: ~270s!!

How can I advice Oracle (v. 7.3.4) that it first executes the statement that returns only 500 rows and would this be the best way to do. How do I know wich statment was first executed?

Thanks a lot for your help.
Emanuel Blaser Received on Tue Oct 15 2002 - 12:10:44 CDT