Thanks :)
Working on the assumption that either:
The search date will be later than the max date in the history table OR
If it's not, you'll get relatively few hits in this table
I wouldn't worry too much about trying to avoid reading the history table.
Provided you create an index with logon_date first:
create index dt_u on user_logon_hist_np ( logon_date, user_id );
The database can use this to see there's few/no entries in the table to query.
Plus the query is a NOT EXISTS. So the database can stop processing as soon as it finds one matching row. Meaning it kinda already does this optimization ;)
e.g. running the query I get this plan:
SELECT *
FROM user_np usn
WHERE NOT EXISTS (
SELECT null
FROM user_logons_np uln
WHERE uln.user_id = usn.user_id
AND uln.logon_date > date'2020-02-20'
UNION ALL
SELECT null
FROM user_logon_hist_np uhp
WHERE uhp.user_id = usn.user_id
AND uhp.logon_date > date'2020-02-20'
) ;
select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 19 | | | |
|* 1 | HASH JOIN ANTI | | 1 | 1 | 0 |00:00:00.01 | 19 | 1009K| 1009K| 1280K (0)|
| 2 | TABLE ACCESS FULL | USER_NP | 1 | 46 | 47 |00:00:00.01 | 6 | | | |
| 3 | VIEW | VW_SQ_1 | 1 | 2796 | 227 |00:00:00.01 | 13 | | | |
| 4 | UNION-ALL | | 1 | | 227 |00:00:00.01 | 13 | | | |
|* 5 | INDEX FAST FULL SCAN| ULN_IDX1 | 1 | 229 | 227 |00:00:00.01 | 13 | | | |
|* 6 | INDEX RANGE SCAN | IX | 0 | 2567 | 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("VW_COL_1"="USN"."USER_ID")
5 - filter("ULN"."LOGON_DATE">TO_DATE(' 2020-02-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
6 - access("UHP"."LOGON_DATE">TO_DATE(' 2020-02-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"UHP"."LOGON_DATE" IS NOT NULL) Notice: zero rows read and zero buffers consumed reading the history table (I called my date, user index IX).
The optimizer does seem to process the union top-to-bottom; not sure if this is guaranteed based on the order in your SQL or cost-based.