Re: speeding up multi-table query across db link

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 Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Aug 03 2002 - 01:55:01 CDT