Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Best way to check for existence based on last event date

NarendraPFeb 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

Processing

Post Details

Added on Feb 19 2020
5 comments
554 views