Re: speeding up multi-table query across db link

On 5 Aug 2002 10:32:01 -0700, deja29_at_pikecreek.com (Scooter) wrote:

>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

The code as posted above is correct. Whether you need a ref cursor depends on how you want to get it returned. However in your case that would probably include using the code above, putting the data in a globally temporary table and returning the temporary table as a resultset. Doesn't sound very promising to me.

If you are using the Cost Based Optimizer, you can try using either -or both- two hints
pushq (which will force the subquery to be executed first) and driving_site (which in this particular case will do the same)

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Aug 05 2002 - 13:05:34 CDT