Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Sql Tuning?
Thanks for your help,
I created a field wich has an index and filled it with the two keys a.nr1 || a.nr2 and it gets much faster! execution time 120s. But thats not fast enough!
So I evalueted the Oracle hint PUSH_SUBQ (found it in this newsgroup)
Select /*+ PUSH_SUBQ */ ta.f1
, tb.f2 , tc.f3 From ta -- on current database1 / 20'000 rows , tb -- with a link on database2 / 1'000'000 rows , tc -- with a link on database2 / over 1'000'000 rows Where ta.nr3 = tb.nr -- is 1 to max(5) and tb.fnr = tc.id -- is 1 to 1 and ta.nr = (select tai.nr from ta tai where ta.nr = tai.nr and tai.str = '10') -- returns500 rows
and it takes only 4s!
So my question again is there anyway I can tell Oracle (with a hint), do first execute the str = '10' statement and then all others, without using a subquery.
I couldn't find, or maybe didn't understand, wich Oracle hint I could use to do so.
Thanks for your help again.
Emanuel Blaser
emanuelblaser_at_yahoo.com (Emanuel Blaser) 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 A
>
> 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 Wed Oct 16 2002 - 09:11:04 CDT