5

I'm running a query across a database link to a Sybase server from Oracle.

In it's where clause is a restriction on date, and I want it tied to sysdate, so something like this:

select * from some_remote_view where some_numeric_key = 1 and some_date > sysdate+2

The problem is, when I do explain plan, only the condition some_numeric_key = 1 shows up in the actual sql that is getting remoted to the sybase server. Oracle is expecting to perform the date filter on its side.

This is causing a performance nightmare - I need that date filter remoted across to have this query working quickly

Even if I try something like casting the sysdate to a charcater string like this: to_char(sysdate-2,'YYYY-MM-DD')

It still does not remote it.

Is there anything I can do to get Oracle to remote this date filter across the db link to Sybase?

Trant
  • 3,461
  • 6
  • 35
  • 57
  • 1
    The joys of db links. Don't know much about SYbase, but when faced with similar issues Oracle-Oracle I look to see if I can drive the results from a view on the remote database, that normally gets the filtering going remotely. If the query/view requires a parameter, a possibility is to drive this from a suitable table for this specific query on the remote db, first inserting the parameter data before running the query. – TenG Aug 14 '14 at 22:01

1 Answers1

2

Doing integration between Oracle and other platforms I often run into this problem, not just with SYSDATE but with other non-standard functions as well.

There are two methods to work around the issue, the first being the most reliable in my experience.

First, you can create a view on the remote db with the filters you need, then on the Oracle side you just select from the new view without additional filters.

Second, if you are not allowed to create objects on the remote side, try using bind variables (of the correct data type!) in your Oracle SELECT statement, e.g.:

declare
   v_some_date constant date := sysdate + 2;
begin
    insert into oracle_table (...)
    select ...
      from remote_table@db_link t
     where t.some_numeric_key = 1
       and t.some_date > v_some_date;

    commit;
end;
/
Marco Baldelli
  • 3,638
  • 1
  • 22
  • 29