speeding up multi-table query across db link

I have this query that I'm trying to make faster on Oracle 8.1.7.0.0 It is currently doing a multi-table join across a dblink. A bad idea.

SELECT

        A.UNIT_NUM,
        A.POLICY_NUM
        B.NAME_NUM,
        C.name1,
        C.name2
FROM
        tablea A,
        tableb B,
        vw_table_at_some_dblink C
WHERE
        A.policy_num = B.policy_num AND
        A.unit_num = B.unit_num AND
        B.name_num = C.name_num AND
        A.POLICY_NUM
                IN


                (SELECT DISTINCT

                        POLICY_NUM
                FROM
                        BILL_ACCT_UNIT
                WHERE
                        BILL_ACCT_NUM = 3243234)
                        

My first thoughts are to break the query into two pieces like this:

SELECT

        A.UNIT_NUM,
        A.POLICY_NUM
        B.NAME_NUM
FROM
        tablea A,
        tableb B,
WHERE
        A.policy_num = B.policy_num AND
        A.unit_num = B.unit_num AND
        A.POLICY_NUM
                IN


                (SELECT DISTINCT

                        POLICY_NUM
                FROM
                        BILL_ACCT_UNIT
                WHERE
                        BILL_ACCT_NUM = 3243234)
                        

This will give me the necessary rows, then I just need to run a 2nd query such as:

select c.name1, c.name2 from vw_table_at_some_dblink c where c.name_num in ( the group I just select from the previous query);

This would be very easy to do with temp tables, but after reading a lot of the discussions temp tables seem to be bad. I started to investigate VARRAY's and bulk copies, but I'm just not sure of the proper method I should be using.

Any help would be appreciated.

Chris Received on Sat Aug 03 2002 - 00:38:52 CDT