Oracle select execution plan, how to remove FILTER

Question

I have remote connection to not oracle database.

And I try to execute two sql's . Column day on remote database is char.

this works ok

select * from tab
where day='2021-11-11'

execution plan

PLAN_TABLE_OUTPUT

Plan hash value: 1788691278
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
|   0 | SELECT STATEMENT |             |    20 | 44760 |   200   (0)| 00:00:01 |        |      |
|   1 |  REMOTE          | EV_LOGS     |    20 | 44760 |   200   (0)| 00:00:01 | RS_HD~ | R->S |

this never ends

select * from tab
where day=to_char(sysdate-5,'yyyy-mm-dd')

PLAN_TABLE_OUTPUT

Plan hash value: 2703195431
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
|   0 | SELECT STATEMENT |             |    20 | 44760 |   200   (0)| 00:00:01 |        |      |
|*  1 |  FILTER          |             |    20 | 44760 |   200   (0)| 00:00:01 |        |      |
|   2 |   REMOTE         | EV_LOGS     |       |       |            |          | RS_HD~ | R->S |

Predicate Information (identified by operation id):
   1 - filter("day"=TO_CHAR(SYSDATE@!-20,'yyyy-mm-dd'))

I try different hints and any of them didn't help. I suppose that I can rewrite it to execute immediate, but how to avoid it?

Please, show the entire execution plans of both the statements (with `dbms_xplan.display(format => 'TYPICAL +PREDICATE')`). Now it is not clear what is going on here. — astentx, Nov 17 '21 at 12:50

score 1 · Answer 1 · answered Nov 19 '21 at 02:08

In the first plan, REMOTE means we could send the predicate over the line to the remote database. Thus the filtering can be applied remotely and only the resultant data comes back.

In the second example, we know that "SYSDATE-5" is effectively a constant, but the database does not - it will see that as an expression, and it generally risky to send an expression to a remote database because expressions might evaluate to a different result depending what database you are on. The remote database might be running in a different timezone/locale etc, so it is not safe to send that across the line.

If you need to do that, perhaps evaluate the value locally and then send it statically across the link as a bind variable.

Oracle select execution plan, how to remove FILTER

1 Answers1