Bad plan with timestamp comparison condition
Have an issue with bad hash plan value
FIELD1 is partition key and index exists INDEX1(FIELD1)
FIELD2 is part of a partitioned local index with FIELD1 INDEX2(FIELD2, FIELD1)
query:
select
....
....
where
FIELD1 >= TRUNC(sys_extract_utc(current_timestamp)) - 4
and
FIELD2 = 'ALFA'
...
...
when my search remains inside current (weekly) partition a plan is choosen using INDEX2 and everything works fine
but when my search needs to get data from more than one partition a plan is choosen using INDEX1 and query is very slow
that doesn't happen when I run in my condition
trunc(FIELD1) >= TRUNC(sys_extract_utc(current_timestamp)) - 4
0