Re: speeding up multi-table query across db link

From: Scooter <deja29_at_pikecreek.com>
Date: 5 Aug 2002 10:32:01 -0700
Message-ID: <63a5751b.0208050932.78de4dd0@posting.google.com>

Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:<69vmkuc8v607lkdckesd5eir5ikn40r910_at_4ax.com>...
> On 2 Aug 2002 22:38:52 -0700, deja29_at_pikecreek.com (Scooter) wrote:
>
> >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
>
>
> You should do several things:
> 1 learn sql. By definition the elements in an in list are a set and a
> set *always* has unique tuples. Your distinct is redundant, but the
> optimizer doesn't know that
> 2 learn Oracle. Do NOT use hardcoded literals
> 3 Post the explain plan.
> 4 If you still want to 'split it up'
> you need to design pl/sql to
> for record in (original query) loop
> select c.name1
> into name1
> from vw_table_at_some_db_link
> where name_num = record.name_num;
> end loop;
>
> That is all there is to it, but generally speaking you should NOT to
> resort to PL/SQL before learning SQL.
>
> Regards
>

Sybrand, thanks for your reply. I'm endevoring to learn as much as I can about proper SQL. With regard to the hard coded literals, that is simply to speed up the multiple execution time and not having to remember the number, I understand completely about not using them normally. Below is the explain plan:

SELECT STATEMENT Cost = 37
  NESTED LOOPS
    NESTED LOOPS

      MERGE JOIN CARTESIAN
        REMOTE    some_DBLINK
        SORT JOIN
          VIEW  VW_NSO_1
            SORT UNIQUE
              INDEX RANGE SCAN XPK_BILL_ACCT_UNIT
      INDEX RANGE SCAN XPK_UNIT

    TABLE ACCESS BY INDEX ROWID ASSIGN_ROLE_UNIT  INDEX RANGE SCAN ASSIGN_ROLE_UNIT_NAME_NUM_01 As for #4, this is where my lack of Oracle features is apparent. If I break it up, the first query will return more than one row. I then need to loop through the rows and return the result set from the 2nd query for each occurence in the first. Am I correct in assuming you can do this with a refcursor? The initial problem I was having was that if you do it with a refcursor you get the last result every time. Obviously it is opening over and over again and the final refcursor is the one returned.

I am certainly open to better suggestions other then breaking it up. As it stands the query is taking over 6 minutes. This is for a web app so anything over .5 seconds it too long. I can get that type of performance results individually. It is from the cross DB join that performace is so terrible. This is because the local DB has no knowledge of the remote indexes and does a full table scan on the remote table every time.

Thanks again for your suggestions,

Chris Received on Mon Aug 05 2002 - 12:32:01 CDT