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: Optimizing a join

Re: Optimizing a join

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 19 Mar 2007 22:08:58 -0000
Message-ID: <wKGdnVbjm5FllGLYnZ2dnUVZ8qKvnZ2d@bt.com>

"tim" <traggatt_at_gmail.com> wrote in message news:1174328701.485428.259150_at_b75g2000hsg.googlegroups.com...
> Hello folks
>
> I am trying optimize a join between two tables. The first is a small
> table which is generated on the fly like so:
>
>
> select (user_data).get_string_property('EAI_JOBNAME') class_name,
> (user_data).get_string_property('EAI_DELAYED_RELEASE_ID')
> DELAYED_REL_ID,
> RAWTOHEX(msgid) msgid
> from staffware_callback_qt
> where q_name = 'AQ$_STAFFWARE_CALLBACK_QT_E'
>
>
> It is using getxxx methods to get the values of the fields from
> objects.
>
>
> The second table is a very large remote table called case_data.
>
>
> I am trying to join on the case_data.casenum and DELAYED_REL_ID
> (above
> table) column. When I execute this query, it seems to look in the
> case_data table first and then the staffware_callback_qt table. Since
> there are only about 6 rows being returned by the top query, this is
> really innefficient. I have tried using the ordered and leading
> optimizer hints but they don't seem to make any difference.
>
>
> Any ideas?
>
>
> Thanks in advance
>
>
> Tim.
>

Not really enough information in this (and the next) post to make a sensible comment but, in outline, and allowing for off-the-cuff typos, something like

select

        /*+ driving_site(rem) */
        rem.*, qt.*

from

    (select /*+ no_merge */

                (user_data).get_string_property('EAI_JOBNAME') class_name,
                (user_data).get_string_property('EAI_DELAYED_RELEASE_ID') 
DELAYED_REL_ID,
                RAWTOHEX(msgid) msgid
            from staffware_callback_qt
            where q_name = 'AQ$_STAFFWARE_CALLBACK_QT_E'
    ) qt,
    case_data_at_remote_site rem
where

    qt.colX = rem.colX
    etc.

Use no_merge to instantiate the local result set, and driving_site to get it sent to the remote site for the join, and leave it to the remote database to sort the problem out and send the result back.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Received on Mon Mar 19 2007 - 17:08:58 CDT

Original text of this message

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