Best way to check for existence based on last event date

User_S1J6P

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

User_S1J6P

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

GregV

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?

User_S1J6P

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,

Mark D Powell

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 --

User_S1J6P

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