Apologies in advance if my question does not make sense or the details are not good enough.
I am trying to find out the best way to include a criteria in a sql to check is the latest event for a user has occurred before a specific date.
drop table user_np purge ;
drop table user_logons_np purge ;
drop table user_logon_hist_np purge ;
create table user_np as select * from all_users ;
create table user_logons_np as select user_id, logon_date, dbms_random.string('a', 50) pad_data from all_users, (select sysdate - level as logon_date from dual connect by level <= 50) ;
create table user_logon_hist_np as select user_id, logon_date, dbms_random.string('a', 50) pad_data from all_users, (select sysdate - (level - 51) as logon_date from dual connect by level <= 500) ;
drop index uhn_idx1 ;
drop index uln_idx1 ;
create index uhn_idx1 on user_logon_hist_np(user_id, logon_date) ;
create index uln_idx1 on user_logons_np(user_id, logon_date) ;
exec dbms_stats.gather_table_stats(user, 'USER_NP', cascade => true) ;
exec dbms_stats.gather_table_stats(user, 'USER_LOGONS_NP', cascade => true) ;
exec dbms_stats.gather_table_stats(user, 'USER_LOGON_HIST_NP', cascade => true) ;
explain plan for SELECT
*
FROM
user_np
WHERE
EXISTS (
SELECT
user_id
FROM
(
SELECT
user_id,
MAX(logon_date)
FROM
user_logons_np
GROUP BY user_id
HAVING
MAX(logon_date) <= SYSDATE
UNION
SELECT
user_id,
MAX(logon_date)
FROM
user_logon_hist_np
GROUP BY user_id
HAVING
MAX(logon_date) <= SYSDATE
) a
WHERE
a.user_id = user_np.user_id
);
Plan hash value: 1460566721
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 68 (8)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 1 | 34 | 68 (8)| 00:00:01 |
| 2 | TABLE ACCESS FULL | USER_NP | 32 | 672 | 6 (0)| 00:00:01 |
| 3 | VIEW | | 4 | 52 | 62 (9)| 00:00:01 |
| 4 | SORT UNIQUE | | 4 | 48 | 62 (9)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
|* 6 | FILTER | | | | | |
| 7 | HASH GROUP BY | | 2 | 24 | 7 (15)| 00:00:01 |
| 8 | INDEX FULL SCAN| ULN_IDX1 | 1600 | 19200 | 6 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
| 10 | HASH GROUP BY | | 2 | 24 | 55 (8)| 00:00:01 |
| 11 | INDEX FULL SCAN| UHN_IDX1 | 16000 | 187K| 52 (2)| 00:00:01 |
----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / USER_NP@SEL$1
3 - SET$1 / A@SEL$2
4 - SET$1
6 - SEL$3
8 - SEL$3 / USER_LOGONS_NP@SEL$3
9 - SEL$4
11 - SEL$4 / USER_LOGON_HIST_NP@SEL$4
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."USER_ID"="USER_NP"."USER_ID")
6 - filter(MAX("LOGON_DATE")<=SYSDATE@!)
9 - filter(MAX("LOGON_DATE")<=SYSDATE@!)
explain plan for SELECT
*
FROM
user_np usn
WHERE
EXISTS (
SELECT
user_id,
MAX(logon_date)
FROM
user_logons_np uln
WHERE uln.user_id = usn.user_id
GROUP BY user_id
HAVING
MAX(logon_date) <= SYSDATE
UNION ALL
SELECT
user_id,
MAX(logon_date)
FROM
user_logon_hist_np uhp
WHERE uhp.user_id = usn.user_id
GROUP BY user_id
HAVING
MAX(logon_date) <= SYSDATE
);
Plan hash value: 1665332333
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 86 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | USER_NP | 32 | 672 | 6 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
| 5 | SORT GROUP BY NOSORT| | 1 | 12 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | ULN_IDX1 | 50 | 600 | 2 (0)| 00:00:01 |
|* 7 | FILTER | | | | | |
| 8 | SORT GROUP BY NOSORT| | 1 | 12 | 3 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | UHN_IDX1 | 500 | 6000 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / USN@SEL$1
3 - SET$1
4 - SEL$2
6 - SEL$2 / ULN@SEL$2
7 - SEL$3
9 - SEL$3 / UHP@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS ( (SELECT "USER_ID",MAX("LOGON_DATE") FROM
"USER_LOGONS_NP" "ULN" WHERE "ULN"."USER_ID"=:B1 GROUP BY "USER_ID" HAVING
MAX("LOGON_DATE")<=SYSDATE@!) UNION ALL (SELECT
"USER_ID",MAX("LOGON_DATE") FROM "USER_LOGON_HIST_NP" "UHP" WHERE
"UHP"."USER_ID"=:B2 GROUP BY "USER_ID" HAVING
MAX("LOGON_DATE")<=SYSDATE@!)))
4 - filter(MAX("LOGON_DATE")<=SYSDATE@!)
6 - access("ULN"."USER_ID"=:B1)
7 - filter(MAX("LOGON_DATE")<=SYSDATE@!)
9 - access("UHP"."USER_ID"=:B1)
explain plan for SELECT
*
FROM
user_np usn
WHERE
CASE WHEN EXISTS (
SELECT
null
FROM
user_logons_np uln
WHERE uln.user_id = usn.user_id
HAVING
MAX(logon_date) <= SYSDATE ) THEN 1
ELSE
CASE WHEN EXISTS (
SELECT
null
FROM
user_logon_hist_np uhp
WHERE uhp.user_id = usn.user_id
HAVING
MAX(logon_date) <= SYSDATE ) THEN 1
ELSE 0 END
END = 1 ;
Plan hash value: 339077023
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32 | 672 | 38 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | USER_NP | 32 | 672 | 6 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
| 4 | SORT AGGREGATE | | 1 | 12 | | |
|* 5 | INDEX RANGE SCAN| ULN_IDX1 | 50 | 600 | 2 (0)| 00:00:01 |
|* 6 | FILTER | | | | | |
| 7 | SORT AGGREGATE | | 1 | 12 | | |
|* 8 | INDEX RANGE SCAN| UHN_IDX1 | 500 | 6000 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / USN@SEL$1
3 - SEL$2
5 - SEL$2 / ULN@SEL$2
6 - SEL$3
8 - SEL$3 / UHP@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(CASE WHEN EXISTS (SELECT MAX("LOGON_DATE") FROM
"USER_LOGONS_NP" "ULN" WHERE "ULN"."USER_ID"=:B1 HAVING
MAX("LOGON_DATE")<=SYSDATE@!) THEN 1 ELSE CASE WHEN EXISTS (SELECT
MAX("LOGON_DATE") FROM "USER_LOGON_HIST_NP" "UHP" WHERE
"UHP"."USER_ID"=:B2 HAVING MAX("LOGON_DATE")<=SYSDATE@!) THEN 1 ELSE 0
END END =1)
3 - filter(MAX("LOGON_DATE")<=SYSDATE@!)
5 - access("ULN"."USER_ID"=:B1)
6 - filter(MAX("LOGON_DATE")<=SYSDATE@!)
8 - access("UHP"."USER_ID"=:B1)