Skip to Main Content

Best way to check for existence based on last event date

User_S1J6PFeb 19 2020 — edited Feb 20 2020

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

Thanks in advance

Comments
Post Details
Added on Feb 19 2020
5 comments
88 views