Sql Performance - HCM - Partitioned table - Query not filtering partitions.

Hello All,

I have a table in HCM the famous PS_JOB table. It is partitioned by paygroup. The query does not reference paygroup. The query runs long with a full scan of PS_JOB.

When I modified the query with AND paygroup in ( List all the paygroups) then the query returns quickly. ITs not feasible to let the developers to add this change and there could be many paygroups added in the future, so how do i work around this? Without the filter it does PARTITION LIST ALL and with filter it shows a PARTITION LIST INLIST in the explain plan and is faster. Is there a hint or another workaround in the SQL that will help. I did see a UNION ALL in another blog but imagine doing a UNION ALL for