7 Replies Latest reply on Feb 16, 2013 3:45 AM by JustinCave

    How to execute remote query by Oracle Database Link

    989558
      I use Oracle Database Link to query data from SQL Server. The query is like:

      select *
      from tableA@DL_SqlServer a
      join tableB@DL_SqlServer b
      on a.ID = b.ID*

      tableA and tableB is large and the result is relatively small. This query executes quickly in SQL Server since indexes are built both on the two tables. But it is very slow on Oracle Database Link to SQL Server.

      I guess the join operation is performed on Oracle side not on SQL Server side, thus the indexes are not used. Since I just need the joined result, I prefer to perform the query entirely on SQL Server and get the small result only. But I have no privilege to create views on SQL Sevrer.

      I konw that using SQL Server's linked server and OPENQUERY function can achieve this goal. I wonder how to do this on Oracle Database Link. Thanks!
        • 1. Re: How to execute remote query by Oracle Database Link
          sb92075
          986555 wrote:
          I use Oracle Database Link to query data from SQL Server. The query is like:

          select *
          from tableA@DL_SqlServer a
          join tableB@DL_SqlServer b
          on a.ID = b.ID*

          tableA and tableB is large and the result is relatively small. This query executes quickly in SQL Server since indexes are built both on the two tables. But it is very slow on Oracle Database Link to SQL Server.

          I guess the join operation is performed on Oracle side not on SQL Server side, thus the indexes are not used. Since I just need the joined result, I prefer to perform the query entirely on SQL Server and get the small result only.

          I konw that using SQL Server's linked server and OPENQUERY function can achieve this goal. I wonder how to do this on Oracle Database Link. Thanks!
          can you make a VIEW on SQL_Server that does the JOIN (locally)?
          Then you can SELECT * FROM NEW_VIEW@DL_SqlServer;
          • 2. Re: How to execute remote query by Oracle Database Link
            989558
            Sorry, I have no privilege to create views on SQL Sevrer.
            • 3. Re: How to execute remote query by Oracle Database Link
              rp0428
              >
              I guess the join operation is performed on Oracle side not on SQL Server side, thus the indexes are not used
              >
              Why are you guessing? Oracle will gladly tell you what it is doing.

              Create and post the execution plan.
              • 4. Re: How to execute remote query by Oracle Database Link
                onedbguru
                DO NOT DO THIS....specifically:
                "select *
                from tableA@DL_SqlServer a
                join tableB@DL_SqlServer b
                on a.ID = b.ID*"

                You would be better off to do the following:

                create a Materialized View in Oracle and once/day (or as frequently as you feel necessary) pull the data from SQLServer and then do the join locally by creating MV as TABLEA_MV and TABLEB_MV and then have views that have the REALTABLEA and REALTABLEB names that point to these MVs. This can be done without recompiling or changing your code. Trust me, I have seen this sort of thing in the past that completely crippled an IBM mainframe using DB2 along with a major network segment by having this sort of join via DB links. You must understand the ramifications of your "design" and I can tell you for certain that it is a very BAD!!! idea... Fix this before you are issuing another command: "alter DBA update resume/CV;"

                The app went into production at 7AM. By 9:30AM, the mainframe had executed more than 10Billion I/O's. It took > 15hrs for the mainframe to recover once we shutdown the app and implemented the view/MV described above.

                I will leave it as an excercise for the OP to develop the syntax for this.

                Edited by: onedbguru on Feb 15, 2013 7:27 PM
                • 5. Re: How to execute remote query by Oracle Database Link
                  JustinCave
                  You most likely need to use the DBMS_HS_PASSTHROUGH` package. Something like
                  DECLARE
                    l_cursor PLS_INTEGER;
                  BEGIN
                    l_cursor := dbms_hs_passthrough.open_cursor@dblink_to_sql_server;
                    dbms_hs_passthrough.parse@dblink_to_sql_server( l_cursor, <<select statement>> );
                    while dbms_hs_passthrough.fetch_row@link_to_sql_server(l_cursor) > 0 
                    loop
                      dbms_hs_passthrough.get_value@dblink_to_sqlserver( l_cursor, 1, <<local variable for first column>> );
                      dbms_hs_passthrough.get_value@dblink_to_sqlserver( l_cursor, 2, <<local variable for second column>> );
                      ...
                    end loop;
                    dbms_hs_passthrough.close_cursor@dblink_to_sqlserver( l_cursor );
                  END;
                  Justin

                  Edited by: Justin Cave on Feb 15, 2013 10:23 PM
                  • 6. Re: How to execute remote query by Oracle Database Link
                    rp0428
                    Does the DRIVING_SITE hint work with sql server connections?
                    • 7. Re: How to execute remote query by Oracle Database Link
                      JustinCave
                      rp0428 wrote:
                      Does the DRIVING_SITE hint work with sql server connections?
                      Don't believe so. Oracle has to translate the statement into one or more ODBC calls (assuming we're using the ODBC gateway). I've never seen a hint work one a remote non-Oracle database.

                      With DBMS_HS_PASSTHROUGH you can also use SQL Server functions that Oracle doesn't know about and deal with SQL Server identifiers that aren't valid in Oracle.

                      Justin