Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Optimizing a join
"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.*
(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,
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.htmlReceived on Mon Mar 19 2007 - 17:08:58 CDT