Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 437 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Best way to check for existence based on last event date

Hello,
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.
Can someone please advice?
Below is my testcase
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_npWHERE 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 / [email protected]$1 3 - SET$1 / [email protected]$2 4 - SET$1 6 - SEL$3 8 - SEL$3 / [email protected]$3 9 - SEL$4 11 - SEL$4 / [email protected]$4Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("A"."USER_ID"="USER_NP"."USER_ID") 6 - filter(MAX("LOGON_DATE")<[email protected]!) 9 - filter(MAX("LOGON_DATE")<[email protected]!) explain plan for SELECT *FROM user_np usnWHERE 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 / [email protected]$1 3 - SET$1 4 - SEL$2 6 - SEL$2 / [email protected]$2 7 - SEL$3 9 - SEL$3 / [email protected]$3Predicate 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")<[email protected]!) 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")<[email protected]!))) 4 - filter(MAX("LOGON_DATE")<[email protected]!) 6 - access("ULN"."USER_ID"=:B1) 7 - filter(MAX("LOGON_DATE")<[email protected]!) 9 - access("UHP"."USER_ID"=:B1) explain plan for SELECT *FROM user_np usnWHERE 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 / [email protected]$1 3 - SEL$2 5 - SEL$2 / [email protected]$2 6 - SEL$3 8 - SEL$3 / [email protected]$3Predicate 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")<[email protected]!) 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")<[email protected]!) THEN 1 ELSE 0 END END =1) 3 - filter(MAX("LOGON_DATE")<[email protected]!) 5 - access("ULN"."USER_ID"=:B1) 6 - filter(MAX("LOGON_DATE")<[email protected]!) 8 - access("UHP"."USER_ID"=:B1)
Thanks in advance
Answers
-
Hello,
I guess a brief description of tables in my test case might help in understanding the requirement.
USER_NP => Parent table listing all users (in original case, it will be joined to multiple tables to build result set)
USER_LOGONS_NP => Table to record login times for each user; used to store data for limited time
USER_LOGON_HIST_NP => Table to store archived login details (from USER_LOGONS_NP)
The requirement behind the testcase sql is to generate list of users who have last logged on earlier than (or on) a specific date.
Hope this helps and happy to clarify more as needed.
Thanks in advance
-
Hi,
I'd do something like this:
select * from user_np u
where exists (select null from user_logons_np l
where u.user_id = l.user_id
and l.logon_date <= sysdate
union all
select null from user_logon_hist_np lh
where u.user_id = lh.user_id
and lh.logon_date <= sysdate
);
Why check for the max if you check for an earlier date?
-
Hello Greg,
Thank you for your response.
Believe it or not but that was the first change/thought that came to my mind and I was about to conclude that we don't need any GROUP BY and MAX.
But if I consider a situation where a user has last logged on (say) 19th Feb 2020 but has also logged on (say) 17th Feb then
a) original sql with SYSDATE replaced by 18th Feb 2020, will result in EXISTS subquery returning FALSE for the user but
b) your sql with SYSDATE replaced by 18th Feb 2020, will result in EXISTS subquery returning TRUE for the user
Hope above makes sense.
I guess my use of SYSDATE in test case was misleading and I did not clarify that SYSDATE in the sqls will actually be a parameter, whose value can be passed as a past date.
My apologies for the same.
Thanks,
-
user4142691, what you are saying is that you should have posted the query using a bind variable such as :limit_date instead of using SYSDATE in the posted query. Explain plan only shows how Oracle thinks it is going to perform a query. It is generally much better where possible to run the query and use DBMS_XPLAN to extract the actual query plan since bind variable peek, cardinality feedback, profiles, and auto generated baselines could result in the actual plan not matching the explain.
- -
HTH -- Mark D Powell --
-
Hello Mark,
Thank you for your response.
I agree that I should look at actual execution plan when looking at performance.
However, I believe I am more interested in asking if anyone has any better/clever ideas to come up with a sql to address my requirement.
I am happy to do the comparisons of how different approaches will perform.
The only reason to post explain plan outputs for the approaches in my original post, was to give an idea about what all I have thought of so far and which approach looks attractive (in theory).
Hope this helps.
Thanks