Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Sql Tuning?

Re: Sql Tuning?

From: Emanuel Blaser <emanuelblaser_at_yahoo.com>
Date: 16 Oct 2002 07:11:04 -0700
Message-ID: <857de1e3.0210160611.5566a07e@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:11:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US