Optimizing a join

From: tim <traggatt_at_gmail.com>
Date: 19 Mar 2007 11:25:01 -0700
Message-ID: <1174328701.485428.259150@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. Received on Mon Mar 19 2007 - 13:25:01 CDT