3 Replies Latest reply: Aug 28, 2011 7:52 PM by Tubby RSS

    Optimizing Distributed Queries

    Dev_Win
      Hello All,
      We have a serious problem optimizing a job that fetches data ( 200k + rows) through views that reside in remote db and inserts into local table. We are running this job from Local db and due to contraints we can neither create any object in the remote db nor we have select access to the tables in remote db .We were given grant to select from the remote views built on remote base tables. How to optimize the job .We tried 2 methods neither was faster ( one hour plus min....)

      example 1: (using driving_site hint & append hint)

      begin

      for irec in (Select /*+ driving_site (c)(d) */) c.customer_id,c.customer_name,d.dept_id,d.dept_name
      from
      customers_view@remotedb c,departments_view@remotedb d
      where d.unique_id = c.unique_id)
      loop
      insert /*+append */
      into local_table ( cust_id,cust_name,dept_id,dept_name)
      values
      ( rec.customer_id,
      rec.customer_name,
      rec.dept_id,
      rec.dept_name);
      end loop;
      commit;
      end;

      example 2: (conventional insert with append hint and driving_site will not work here )..

      insert /*+append */
      into local_table ( cust_id,cust_name,dept_id,dept_name)
      Select c.customer_id,c.customer_name,d.dept_id,d.dept_name
      from
      customers_view@remotedb c,departments_view@remotedb d
      where d.unique_id = c.unique_id)


      Limitations :

      1) we do not have privilage to run explain plan for the remote objects..:(. So whatever we do we have no clue will it increase performance..!! )
      2) The job fetches data only from remote objects(views) and no local objects..
      3) We are not allowed to create any object in remote db..( We will never get a grant for that,so no second thought about creating an objects in remote db to increase performance)

      If any one have encontered or experienced similar problems or got any suggestions to optmizie then please do help us out.Thank you all in advance..

      Edited by: 843561 on Aug 26, 2011 1:53 PM