Your problem is
a.nr1 || a.nr2 = b.nr
That is creating a full table scan.
Jim
"Emanuel Blaser" <emanuelblaser_at_yahoo.com> wrote in message
news:857de1e3.0210150910.5703b278_at_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:28:19 CDT
