What is the explain plan and the tkprof? Also compress the index and
analyze the tables and indexes.
Jim
"Emanuel Blaser" <emanuelblaser_at_yahoo.com> wrote in message
news:857de1e3.0210160611.5566a07e_at_posting.google.com...
> 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') -- returns
> 500 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:48:44 CDT
