Skip to Main Content
  • Questions
  • Best way to check for existence based on last event date

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Narendra.

Asked: February 25, 2020 - 10:51 am UTC

Answered by: Chris Saxon - Last updated: April 02, 2020 - 3:57 pm UTC

Category: SQL - Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hello,

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.

Below is my table setup and brief description
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.

Can you 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 |  
----------------------------------------------------------------------------------  
  
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 |  
------------------------------------------------------------------------------------  
  
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 |  
--------------------------------------------------------------------------------  
  
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)  

and we said...

None of the above ;)

With the having clauses, you're

1 Grouping
2 Filtering

Really you want to filter first in a where clause. Then group.

Or in this case, NOT group at all!

For your test data, the following comes out best for me:

SELECT *  
FROM   user_np usn  
WHERE  EXISTS (  
  SELECT null
  FROM   user_logons_np uln  
  WHERE uln.user_id = usn.user_id  
  AND   logon_date <= SYSDATE  
  UNION ALL  
  SELECT null
  FROM  user_logon_hist_np uhp  
  WHERE uhp.user_id = usn.user_id  
  AND   logon_date <= SYSDATE  
);  


To measure this, you should compare the EXECUTION plans, not the EXPLAIN plans. This includes key metrics, such as the number of rows processed at each step, buffers (I/O) done, time taken, etc.

Do this by running:
set serveroutput off
alter session set statistics_level = all;

select <your query>

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));


Doing this, I find the best alternative above (as measured by the operation that does the smallest number of buffers) is the last. Which has this plan:

------------------------------------------------------------------------------------------    
| Id  | Operation           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT    |          |      1 |        |     46 |00:00:00.01 |      97 |    
|*  1 |  FILTER             |          |      1 |        |     46 |00:00:00.01 |      97 |    
|   2 |   TABLE ACCESS FULL | USER_NP  |      1 |     46 |     46 |00:00:00.01 |       3 |    
|*  3 |   FILTER            |          |     46 |        |     46 |00:00:00.01 |      94 |    
|   4 |    SORT AGGREGATE   |          |     46 |      1 |     46 |00:00:00.01 |      94 |    
|*  5 |     INDEX RANGE SCAN| ULN_IDX1 |     46 |     50 |   2300 |00:00:00.01 |      94 |    
|*  6 |   FILTER            |          |      0 |        |      0 |00:00:00.01 |       0 |    
|   7 |    SORT AGGREGATE   |          |      0 |      1 |      0 |00:00:00.01 |       0 |    
|*  8 |     INDEX RANGE SCAN| UHN_IDX1 |      0 |    500 |      0 |00:00:00.01 |       0 |    
------------------------------------------------------------------------------------------ 


Note the 97 buffers (I/O)s

Whereas filtering in the where clause gives:

----------------------------------------------------------------------------------------------    
| Id  | Operation               | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
----------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT        |          |      1 |        |     46 |00:00:00.02 |      15 |    
|*  1 |  HASH JOIN SEMI         |          |      1 |     46 |     46 |00:00:00.02 |      15 |    
|   2 |   TABLE ACCESS FULL     | USER_NP  |      1 |     46 |     46 |00:00:00.01 |       2 |    
|   3 |   VIEW                  | VW_SQ_1  |      1 |  23042 |   2251 |00:00:00.01 |      13 |    
|   4 |    UNION-ALL            |          |      1 |        |   2251 |00:00:00.01 |      13 |    
|*  5 |     INDEX FAST FULL SCAN| ULN_IDX1 |      1 |   2300 |   2251 |00:00:00.01 |      13 |    
|*  6 |     INDEX FAST FULL SCAN| UHN_IDX1 |      0 |  20742 |      0 |00:00:00.01 |       0 |    
---------------------------------------------------------------------------------------------- 


Nearly a quarter of the work! Just 15 buffers instead of ~100.

Also...

As I'd expect lots of rows for each user, I'd also compress the leading columns of these indexes:

alter index uhn_idx1 
  rebuild compress 1;
alter index uln_idx1 
  rebuild compress 1;


This should make them smaller => more efficient to read.

and you rated our response

  (10 ratings)

Reviews

last login date

February 25, 2020 - 7:44 pm UTC

Reviewer: Narendra from UK

Hello Chris,

Thank you for your response.
I believe I may not have explained the requirement clearly. The requirement is to generate list of users who have last logged on earlier than (or on) a specific date

If I replace SYSDATE with an earlier date so that some users could have logged on before as well as after the parameter date then your sql will include such users in the final result.
However, because such users will not satisfy the last logged on earlier than parameter date, they should be excluded.

Hope this helps.
Chris Saxon

Followup  

February 26, 2020 - 11:43 am UTC

In that case your queries are wrong too!

The original queries find the max date in either table. So you could have a max in one table less than the limit. And greater in the other. But the query still returns it!

You want to search for all the users where:

There's NOT EXISTS a logon AFTER the target date
AND there is at least one logon

Giving:

SELECT *  
FROM   user_np usn  
WHERE  NOT EXISTS (  
  SELECT null
  FROM   user_logons_np uln  
  WHERE uln.user_id = usn.user_id  
  AND   uln.logon_date > date'2020-02-25'  
  UNION ALL  
  SELECT null
  FROM  user_logon_hist_np uhp  
  WHERE uhp.user_id = usn.user_id  
  AND   uhp.logon_date > date'2020-02-25'  
) 
and EXISTS (  
  SELECT null
  FROM   user_logons_np uln  
  WHERE uln.user_id = usn.user_id  
  UNION ALL  
  SELECT null
  FROM  user_logon_hist_np uhp  
  WHERE uhp.user_id = usn.user_id
);  


If you know every user has logged on at least once, you can skip the second EXISTS check.

Excellent

February 26, 2020 - 2:43 pm UTC

Reviewer: Narendra from UK

Hello Chris,

You Rock!!!
Now when I see your solution, I feel silly.....how come I did not think of that?

Thanks and much appreciated.
One final doubt on this if you don't mind.

What is the best way (using SQL only) to take your approach and tweak it such that the sql checks user_logons_np table first (such that user's last logon is before the specified date) and only probe user_logon_hist_np table (on the same criteria) if user_logons_np does not return any records meeting the criteria?

I am asking because user_logons_np table is expected to hold only data from recent past and an archival job moves data from user_logons_np table to user_logon_hist_np every day.
Due to this user_logon_hist_np is going to be very large and I am hoping to reduce the number of times it is accessed.

In procedural way, the logic would be
IF EXISTS in user_logons_np table user with last logon date earlier than specified date THEN TRUE
ELSE
IF EXISTS in user_logon_hist_np table user with last logon date earlier than specified date THEN TRUE
ELSE FALSE
END IF
Chris Saxon

Followup  

February 26, 2020 - 10:00 pm UTC

Thanks :)

Working on the assumption that either:

The search date will be later than the max date in the history table OR
If it's not, you'll get relatively few hits in this table

I wouldn't worry too much about trying to avoid reading the history table.

Provided you create an index with logon_date first:

create index dt_u on user_logon_hist_np ( logon_date, user_id );


The database can use this to see there's few/no entries in the table to query.

Plus the query is a NOT EXISTS. So the database can stop processing as soon as it finds one matching row. Meaning it kinda already does this optimization ;)

e.g. running the query I get this plan:

SELECT *  
FROM   user_np usn  
WHERE  NOT EXISTS (  
  SELECT null
  FROM   user_logons_np uln  
  WHERE uln.user_id = usn.user_id  
  AND   uln.logon_date > date'2020-02-20'  
  UNION ALL  
  SELECT null
  FROM  user_logon_hist_np uhp  
  WHERE uhp.user_id = usn.user_id  
  AND   uhp.logon_date > date'2020-02-20'  
) ;  

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

-------------------------------------------------------------------------------------------------------------------------    
| Id  | Operation               | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |    
-------------------------------------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT        |          |      1 |        |      0 |00:00:00.01 |      19 |       |       |          |    
|*  1 |  HASH JOIN ANTI         |          |      1 |      1 |      0 |00:00:00.01 |      19 |  1009K|  1009K| 1280K (0)|    
|   2 |   TABLE ACCESS FULL     | USER_NP  |      1 |     46 |     47 |00:00:00.01 |       6 |       |       |          |    
|   3 |   VIEW                  | VW_SQ_1  |      1 |   2796 |    227 |00:00:00.01 |      13 |       |       |          |    
|   4 |    UNION-ALL            |          |      1 |        |    227 |00:00:00.01 |      13 |       |       |          |    
|*  5 |     INDEX FAST FULL SCAN| ULN_IDX1 |      1 |    229 |    227 |00:00:00.01 |      13 |       |       |          |    
|*  6 |     INDEX RANGE SCAN    | IX       |      0 |   2567 |      0 |00:00:00.01 |       0 |       |       |          |    
-------------------------------------------------------------------------------------------------------------------------    
                                                                                                                             
Predicate Information (identified by operation id):                                                                          
---------------------------------------------------                                                                          
                                                                                                                             
   1 - access("VW_COL_1"="USN"."USER_ID")                                                                                    
   5 - filter("ULN"."LOGON_DATE">TO_DATE(' 2020-02-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))                                  
   6 - access("UHP"."LOGON_DATE">TO_DATE(' 2020-02-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND                               
              "UHP"."LOGON_DATE" IS NOT NULL) 


Notice: zero rows read and zero buffers consumed reading the history table (I called my date, user index IX).

The optimizer does seem to process the union top-to-bottom; not sure if this is guaranteed based on the order in your SQL or cost-based.

Thank you again

February 28, 2020 - 8:47 pm UTC

Reviewer: Narendra from UK

Hello Chris,

Thank you again for your time and advice.
I am in the process of deploying this technique soon and will soon post the results.

Thanks

NOT EXISTS UNION ALL optimization

March 31, 2020 - 4:45 pm UTC

Reviewer: Narendra from UK

Hello Chris,

Sorry about delay in responding.
I implemented your recommendation of using NOT EXISTS.
However, for some reason, in my original use case, the optimizer always appears to be evaluating both branches of UNION ALL and I can't seem to figure out why.
In original use case, the main table is joined to a lot of tables before it gets compared with logons equivalent tables (from the test case).
Not sure if it helps but below is the relevant extract from dbms_xplan.display_cursor output.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 42 |            HASH JOIN RIGHT ANTI                              |                              |      1 |   2987 |   1406 |00:00:00.11 |    2537 |     48 |  1969K|  1969K| 1186K (0)|
|  43 |             VIEW                                             | VW_SQ_1                      |      1 |     98 |    283 |00:00:00.02 |      97 |     48 |       |       |          |
|  44 |              UNION-ALL                                       |                              |      1 |        |    283 |00:00:00.02 |      97 |     48 |       |       |          |
|* 45 |               VIEW                                           | index$_join$_073             |      1 |      1 |      5 |00:00:00.02 |       6 |      8 |       |       |          |
|* 46 |                HASH JOIN                                     |                              |      1 |        |      5 |00:00:00.02 |       6 |      8 |  1321K|  1321K| 1222K (0)|
|  47 |                 BITMAP CONVERSION TO ROWIDS                  |                              |      1 |      1 |      5 |00:00:00.01 |       3 |      0 |       |       |          |
|* 48 |                  BITMAP INDEX SINGLE VALUE                   | USRLOGONTYP_IDX              |      1 |        |      1 |00:00:00.01 |       3 |      0 |       |       |          |
|* 49 |                 INDEX RANGE SCAN                             | LOGONDT_IDX                  |      1 |      1 |    353 |00:00:00.01 |       3 |      8 |       |       |          |
|* 50 |               VIEW                                           | index$_join$_074             |      1 |     97 |    278 |00:00:00.02 |      91 |     40 |       |       |          |
|* 51 |                HASH JOIN                                     |                              |      1 |        |    278 |00:00:00.02 |      91 |     40 |  1494K|  1494K| 1967K (0)|
|  52 |                 PARTITION RANGE ITERATOR                     |                              |      1 |     97 |   8926 |00:00:00.01 |      36 |     40 |       |       |          |
|* 53 |                  INDEX RANGE SCAN                            | ULN_HIST_LOGONDT_USER_IDX    |      6 |     97 |   8926 |00:00:00.01 |      36 |     40 |       |       |          |
|* 54 |                 INDEX RANGE SCAN                             | ULN_HIST_TYP_USER_IDX        |      1 |     97 |   9548 |00:00:00.01 |      55 |      0 |       |       |          |
|* 55 |             HASH JOIN RIGHT OUTER                            |                              |      1 |   3394 |   1421 |00:00:00.07 |    2440 |      0 |  1079K|  1079K| 1407K (0)|

  42 - access("VW_COL_1"="P"."ID")
  45 - filter(("E"."TYP_CD"='HELLOWORLD' AND "E"."LOGONDT">'02-Oct-2019' AND "E"."USER">0))
  46 - access(ROWID=ROWID)
  48 - access("E"."TYP_CD"='HELLOWORLD')
  49 - access("E"."LOGONDT">'02-Oct-2019' AND "E"."USER">0)
  50 - filter(("EH"."TYP_CD"='HELLOWORLD' AND "EH"."LOGONDT">'02-Oct-2019' AND "EH"."USER">0))
  51 - access(ROWID=ROWID)
  53 - access("EH"."LOGONDT">'02-Oct-2019' AND "EH"."USER">0)
  54 - access("EH"."TYP_CD"='HELLOWORLD')

Chris Saxon

Followup  

April 01, 2020 - 10:08 am UTC

I think this stems from:

HASH JOIN RIGHT ANTI


The database is processing the UNION-ALL before reading rows from the other table(s). So it doesn't have any values to filter it!

Exactly why this is happening will need more digging into the plan.

Thanks

April 01, 2020 - 11:39 am UTC

Reviewer: Narendra from UK

Thanks Chris.
I looked at the outline section and managed to extract the part which was swapping join inputs, thereby causing the NOT EXISTS view to be evaluated first. I made below changes but the UNION ALL optimization does not appear to be taking place still.

1. forced the full table scan for both tables in UNION ALL
2. Added NO_SWAP_JOIN_INPUTS hint to change the default join order for the NOT EXISTS view.

** I am being lazy and not obfuscating the plan this time.

1. Lines 95 to 99 are the NOT EXISTS part, using EVENTS and EVENT_HISTORIES tables
2. Line 32 is joining the NOT EXISTS view to PARTIES table

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                             |      1 |        |     50 |00:00:00.24 |   16042 |    404 |       |       |          |
|   1 |  SORT GROUP BY                                               |                             |    574 |      1 |    574 |00:00:00.01 |     924 |      0 |  2048 |  2048 | 2048  (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID                                | PARTY_ROLE_MEMBERSHIPS      |    574 |      2 |   1381 |00:00:00.01 |     924 |      0 |       |       |          |
|*  3 |    INDEX RANGE SCAN                                          | PARTY_ROLE_MEMBERSHIPS_PK   |    574 |      2 |   1423 |00:00:00.01 |     232 |      0 |       |       |          |
|   4 |  SORT GROUP BY                                               |                             |    574 |      1 |    574 |00:00:00.01 |     924 |      0 |  2048 |  2048 | 2048  (0)|
|*  5 |   TABLE ACCESS BY INDEX ROWID                                | PARTY_ROLE_MEMBERSHIPS      |    574 |      2 |   1381 |00:00:00.01 |     924 |      0 |       |       |          |
|*  6 |    INDEX RANGE SCAN                                          | PARTY_ROLE_MEMBERSHIPS_PK   |    574 |      2 |   1423 |00:00:00.01 |     232 |      0 |       |       |          |
|   7 |  SORT GROUP BY                                               |                             |    574 |      1 |    574 |00:00:00.02 |    2309 |      0 |  2048 |  2048 | 2048  (0)|
|   8 |   NESTED LOOPS                                               |                             |    574 |      2 |   1381 |00:00:00.01 |    2309 |      0 |       |       |          |
|   9 |    NESTED LOOPS                                              |                             |    574 |      2 |   1381 |00:00:00.01 |     928 |      0 |       |       |          |
|* 10 |     TABLE ACCESS BY INDEX ROWID                              | PARTY_ROLE_MEMBERSHIPS      |    574 |      2 |   1381 |00:00:00.01 |     924 |      0 |       |       |          |
|* 11 |      INDEX RANGE SCAN                                        | PARTY_ROLE_MEMBERSHIPS_PK   |    574 |      2 |   1423 |00:00:00.01 |     232 |      0 |       |       |          |
|* 12 |     INDEX UNIQUE SCAN                                        | SYS_C0025143                |   1381 |      1 |   1381 |00:00:00.01 |       4 |      0 |       |       |          |
|* 13 |    TABLE ACCESS BY INDEX ROWID                               | CHANNEL_ROLES               |   1381 |      1 |   1381 |00:00:00.01 |    1381 |      0 |       |       |          |
|  14 |  SORT AGGREGATE                                              |                             |     67 |      1 |     67 |00:00:00.01 |       4 |      0 |       |       |          |
|* 15 |   INDEX UNIQUE SCAN                                          | SYS_C0025143                |     67 |      1 |      7 |00:00:00.01 |       4 |      0 |       |       |          |
|  16 |  SORT GROUP BY                                               |                             |    574 |      1 |    574 |00:00:00.01 |     928 |      0 |  2048 |  2048 | 2048  (0)|
|* 17 |   TABLE ACCESS BY INDEX ROWID                                | PARTY_ROLE_MEMBERSHIPS      |    574 |      2 |   1381 |00:00:00.01 |     924 |      0 |       |       |          |
|* 18 |    INDEX RANGE SCAN                                          | PARTY_ROLE_MEMBERSHIPS_PK   |    574 |      2 |   1423 |00:00:00.01 |     232 |      0 |       |       |          |
|  19 |  SORT AGGREGATE                                              |                             |    574 |      1 |    574 |00:00:00.01 |     800 |      0 |       |       |          |
|* 20 |   TABLE ACCESS BY INDEX ROWID                                | PARTIES                     |    574 |      1 |    292 |00:00:00.01 |     800 |      0 |       |       |          |
|* 21 |    INDEX UNIQUE SCAN                                         | PARTIES_PK                  |    574 |      1 |    574 |00:00:00.01 |     226 |      0 |       |       |          |
|* 22 |  VIEW                                                        |                             |      1 |   1218 |     50 |00:00:00.24 |   16042 |    404 |       |       |          |
|  23 |   WINDOW BUFFER                                              |                             |      1 |   1218 |    574 |00:00:00.24 |   16042 |    404 |   302K|   302K|  268K (0)|
|  24 |    COUNT                                                     |                             |      1 |        |    574 |00:00:00.23 |   16042 |    404 |       |       |          |
|  25 |     VIEW                                                     |                             |      1 |   1218 |    574 |00:00:00.23 |   16042 |    404 |       |       |          |
|  26 |      SORT ORDER BY                                           |                             |      1 |   1218 |    574 |00:00:00.23 |   16042 |    404 |   337K|   337K|  299K (0)|
|  27 |       VIEW                                                   |                             |      1 |   1218 |    574 |00:00:00.23 |   16042 |    404 |       |       |          |
|  28 |        HASH UNIQUE                                           |                             |      1 |   1218 |    574 |00:00:00.23 |   16042 |    404 |  2522K|   918K| 2505K (0)|
|  29 |         WINDOW SORT                                          |                             |      1 |   1218 |   1406 |00:00:00.15 |    9233 |    404 |  1257K|   577K| 1117K (0)|
|* 30 |          HASH JOIN                                           |                             |      1 |   1218 |   1406 |00:00:00.14 |    9233 |    404 |  1599K|  1599K| 1790K (0)|
|  31 |           TABLE ACCESS FULL                                  | PARTIES                     |      1 |   7296 |   7533 |00:00:00.01 |     183 |      0 |       |       |          |
|* 32 |           HASH JOIN ANTI                                     |                             |      1 |   1218 |   1406 |00:00:00.13 |    9050 |    404 |  1634K|   934K| 2366K (0)|
|* 33 |            HASH JOIN                                         |                             |      1 |   4003 |   1421 |00:00:00.05 |    2592 |      0 |  1496K|  1330K| 1698K (0)|
|* 34 |             TABLE ACCESS FULL                                | PARTY_ROLE_MEMBERSHIPS      |      1 |   7517 |   8219 |00:00:00.01 |     183 |      0 |       |       |          |
|* 35 |             HASH JOIN                                        |                             |      1 |   2076 |    588 |00:00:00.06 |    2409 |      0 |  1359K|  1359K| 1550K (0)|
|* 36 |              TABLE ACCESS FULL                               | PARTY_NAMES                 |      1 |   7045 |   7166 |00:00:00.01 |     247 |      0 |       |       |          |
|* 37 |              HASH JOIN                                       |                             |      1 |   2138 |    588 |00:00:00.06 |    2162 |      0 |  1427K|  1427K| 1550K (0)|
|* 38 |               TABLE ACCESS FULL                              | PARTY_NAMES                 |      1 |   7045 |   7166 |00:00:00.01 |     247 |      0 |       |       |          |
|* 39 |               HASH JOIN                                      |                             |      1 |   2024 |    588 |00:00:00.05 |    1915 |      0 |  2061K|  2061K| 1737K (0)|
|  40 |                INDEX FAST FULL SCAN                          | BRAND_PARTIES_PK            |      1 |   2456 |   2570 |00:00:00.01 |      17 |      0 |       |       |          |
|* 41 |                HASH JOIN RIGHT OUTER                         |                             |      1 |   2024 |    588 |00:00:00.05 |    1898 |      0 |  1185K|  1185K| 1323K (0)|
|* 42 |                 TABLE ACCESS FULL                            | ACCOUNT_STATUS              |      1 |    888 |    925 |00:00:00.01 |      17 |      0 |       |       |          |
|  43 |                 NESTED LOOPS OUTER                           |                             |      1 |   2024 |    588 |00:00:00.05 |    1881 |      0 |       |       |          |
|* 44 |                  HASH JOIN RIGHT OUTER                       |                             |      1 |   2024 |    588 |00:00:00.05 |    1881 |      0 |  2616K|  2616K|  232K (0)|
|* 45 |                   TABLE ACCESS FULL                          | INTERNAL_CARD_ACCOUNTS      |      1 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 46 |                   HASH JOIN RIGHT OUTER                      |                             |      1 |   2024 |    588 |00:00:00.04 |    1881 |      0 |  1696K|  1696K| 1712K (0)|
|  47 |                    INDEX FAST FULL SCAN                      | BRAND_PARTIES_PK            |      1 |   2456 |   2570 |00:00:00.01 |      17 |      0 |       |       |          |
|* 48 |                    HASH JOIN RIGHT OUTER                     |                             |      1 |   2024 |    588 |00:00:00.04 |    1864 |      0 |  1185K|  1185K| 1359K (0)|
|* 49 |                     TABLE ACCESS FULL                        | ACCOUNT_STATUS              |      1 |    888 |    925 |00:00:00.01 |      17 |      0 |       |       |          |
|* 50 |                     HASH JOIN RIGHT OUTER                    |                             |      1 |   2024 |    588 |00:00:00.04 |    1847 |      0 |  1199K|  1199K| 1356K (0)|
|  51 |                      TABLE ACCESS FULL                       | ACCOUNTS                    |      1 |    903 |    937 |00:00:00.01 |      28 |      0 |       |       |          |
|* 52 |                      HASH JOIN RIGHT OUTER                   |                             |      1 |   2024 |    588 |00:00:00.04 |    1819 |      0 |  1172K|  1172K| 1418K (0)|
|* 53 |                       TABLE ACCESS FULL                      | CARD_ACCOUNTS               |      1 |   2425 |   2533 |00:00:00.01 |      45 |      0 |       |       |          |
|* 54 |                       HASH JOIN                              |                             |      1 |   2018 |    588 |00:00:00.04 |    1774 |      0 |  1208K|  1208K| 1483K (0)|
|* 55 |                        TABLE ACCESS FULL                     | PARTY_RELATIONS             |      1 |   4345 |   4520 |00:00:00.01 |      92 |      0 |       |       |          |
|* 56 |                        HASH JOIN RIGHT OUTER                 |                             |      1 |   2016 |    588 |00:00:00.03 |    1682 |      0 |  1079K|  1079K| 1425K (0)|
|* 57 |                         TABLE ACCESS FULL                    | ADMIN_GROUPS                |      1 |   3027 |   3191 |00:00:00.01 |      67 |      0 |       |       |          |
|* 58 |                         HASH JOIN RIGHT OUTER                |                             |      1 |   2016 |    588 |00:00:00.03 |    1615 |      0 |  1270K|  1270K| 1484K (0)|
|* 59 |                          TABLE ACCESS FULL                   | ADMIN_GROUP_MEMBERSHIPS     |      1 |   4299 |   4518 |00:00:00.01 |      83 |      0 |       |       |          |
|* 60 |                          HASH JOIN                           |                             |      1 |   1885 |    588 |00:00:00.03 |    1532 |      0 |  1135K|  1135K| 1425K (0)|
|* 61 |                           TABLE ACCESS FULL                  | PARTY_STATUS                |      1 |   3102 |   3561 |00:00:00.01 |     183 |      0 |       |       |          |
|* 62 |                           HASH JOIN RIGHT OUTER              |                             |      1 |   2910 |   4536 |00:00:00.04 |    1349 |      0 |  1281K|  1281K| 1611K (0)|
|* 63 |                            TABLE ACCESS FULL                 | PARTY_ACCESS_DEVICES        |      1 |    847 |    897 |00:00:00.01 |      15 |      0 |       |       |          |
|* 64 |                            HASH JOIN RIGHT OUTER             |                             |      1 |   2910 |   4531 |00:00:00.04 |    1334 |      0 |  1199K|  1199K| 1297K (0)|
|* 65 |                             TABLE ACCESS FULL                | PARTY_TELEPHONY_PIN_STATUS  |      1 |    839 |    883 |00:00:00.01 |      15 |      0 |       |       |          |
|* 66 |                             HASH JOIN RIGHT OUTER            |                             |      1 |   2910 |   4530 |00:00:00.03 |    1319 |      0 |  1281K|  1281K| 1298K (0)|
|* 67 |                              TABLE ACCESS FULL               | VERIFICATION_ATTEMPTS       |      1 |    523 |    523 |00:00:00.01 |      15 |      0 |       |       |          |
|* 68 |                              HASH JOIN RIGHT OUTER           |                             |      1 |   2910 |   4530 |00:00:00.03 |    1304 |      0 |  1263K|  1263K| 1587K (0)|
|  69 |                               VIEW                           |                             |      1 |    162 |    162 |00:00:00.01 |     332 |      0 |       |       |          |
|  70 |                                NESTED LOOPS                  |                             |      1 |    162 |    162 |00:00:00.01 |     332 |      0 |       |       |          |
|  71 |                                 NESTED LOOPS                 |                             |      1 |    162 |    162 |00:00:00.01 |     168 |      0 |       |       |          |
|  72 |                                  VIEW                        | index$_join$_060            |      1 |    162 |    162 |00:00:00.01 |       6 |      0 |       |       |          |
|* 73 |                                   HASH JOIN                  |                             |      1 |        |    162 |00:00:00.01 |       6 |      0 |  1421K|  1421K| 1584K (0)|
|  74 |                                    INDEX FAST FULL SCAN      | PENDING_WORKFLOW_DETAILS_PK |      1 |    162 |    200 |00:00:00.01 |       3 |      0 |       |       |          |
|* 75 |                                    INDEX FAST FULL SCAN      | PENDING_WORKFLOW_DETAILS_UK |      1 |    162 |    162 |00:00:00.01 |       3 |      0 |       |       |          |
|* 76 |                                  INDEX UNIQUE SCAN           | WORKITEMDETAILS_PK          |    162 |      1 |    162 |00:00:00.01 |     162 |      0 |       |       |          |
|  77 |                                 TABLE ACCESS BY INDEX ROWID  | WORK_ITEM_DETAILS           |    162 |      1 |    162 |00:00:00.01 |     164 |      0 |       |       |          |
|* 78 |                               HASH JOIN RIGHT OUTER          |                             |      1 |   2910 |   4530 |00:00:00.02 |     972 |      0 |  1557K|  1557K| 1470K (0)|
|* 79 |                                TABLE ACCESS FULL             | WEB_VISITS                  |      1 |     15 |     15 |00:00:00.01 |       6 |      0 |       |       |          |
|* 80 |                                HASH JOIN                     |                             |      1 |   2910 |   4530 |00:00:00.02 |     966 |      0 |  1198K|  1198K| 1492K (0)|
|* 81 |                                 TABLE ACCESS FULL            | PARTIES                     |      1 |   4788 |   4963 |00:00:00.01 |     183 |      0 |       |       |          |
|* 82 |                                 HASH JOIN                    |                             |      1 |   4243 |   4530 |00:00:00.02 |     783 |      0 |  1315K|  1107K| 1489K (0)|
|* 83 |                                  HASH JOIN                   |                             |      1 |   4243 |   4530 |00:00:00.01 |     435 |      0 |  1214K|  1214K| 1457K (0)|
|  84 |                                   TABLE ACCESS FULL          | ELECTRONIC_ADDRESSES        |      1 |   4369 |   4588 |00:00:00.01 |      83 |      0 |       |       |          |
|  85 |                                   NESTED LOOPS               |                             |      1 |   4243 |   4543 |00:00:00.01 |     352 |      0 |       |       |          |
|  86 |                                    NESTED LOOPS              |                             |      1 |      1 |      1 |00:00:00.01 |       4 |      0 |       |       |          |
|  87 | WID                                 TABLE ACCESS BY INDEX RO | PARTY_STATUS_SORT_ORDERS    |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          |
|* 88 |                                      INDEX UNIQUE SCAN       | SYS_C0025403                |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |       |       |          |
|  89 | WID                                 TABLE ACCESS BY INDEX RO | PARTY_STATUS_CODES          |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          |
|* 90 |                                      INDEX UNIQUE SCAN       | SYS_C0025261                |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |       |       |          |
|* 91 |                                    TABLE ACCESS FULL         | ADDRESSES                   |      1 |   4243 |   4543 |00:00:00.01 |     348 |      0 |       |       |          |
|* 92 |                                  TABLE ACCESS FULL           | PARTY_ADDRESSES             |      1 |  15562 |  16400 |00:00:00.01 |     348 |      0 |       |       |          |
|  93 |                  TABLE ACCESS BY INDEX ROWID                 | ACCOUNTS                    |    588 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 94 |                   INDEX UNIQUE SCAN                          | ACCOUNTS_PK                 |    588 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  95 |            VIEW                                              | VW_SQ_1                     |      1 |     98 |    285 |00:00:00.01 |    6458 |    404 |       |       |          |
|  96 |             UNION-ALL                                        |                             |      1 |        |    285 |00:00:00.01 |    6458 |    404 |       |       |          |
|* 97 |              TABLE ACCESS FULL                               | EVENTS                      |      1 |      1 |      7 |00:00:00.01 |    6048 |      0 |       |       |          |
|  98 |              PARTITION RANGE ITERATOR                        |                             |      1 |     97 |    278 |00:00:00.03 |     410 |    404 |       |       |          |
|* 99 |               TABLE ACCESS FULL                              | EVENT_HISTORIES             |      6 |     97 |    278 |00:00:00.04 |     410 |    404 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Incomplete plan

April 01, 2020 - 11:41 am UTC

Reviewer: Narendra from UK

Not sure why only partial plan got posted previously.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                             |      1 |        |     50 |00:00:00.24 |   16042 |    404 |       |       |          |
|   1 |  SORT GROUP BY                                               |                             |    574 |      1 |    574 |00:00:00.01 |     924 |      0 |  2048 |  2048 | 2048  (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID                                | PARTY_ROLE_MEMBERSHIPS      |    574 |      2 |   1381 |00:00:00.01 |     924 |      0 |       |       |          |
|*  3 |    INDEX RANGE SCAN                                          | PARTY_ROLE_MEMBERSHIPS_PK   |    574 |      2 |   1423 |00:00:00.01 |     232 |      0 |       |       |          |
|   4 |  SORT GROUP BY                                               |                             |    574 |      1 |    574 |00:00:00.01 |     924 |      0 |  2048 |  2048 | 2048  (0)|
|*  5 |   TABLE ACCESS BY INDEX ROWID                                | PARTY_ROLE_MEMBERSHIPS      |    574 |      2 |   1381 |00:00:00.01 |     924 |      0 |       |       |          |
|*  6 |    INDEX RANGE SCAN                                          | PARTY_ROLE_MEMBERSHIPS_PK   |    574 |      2 |   1423 |00:00:00.01 |     232 |      0 |       |       |          |
|   7 |  SORT GROUP BY                                               |                             |    574 |      1 |    574 |00:00:00.02 |    2309 |      0 |  2048 |  2048 | 2048  (0)|
|   8 |   NESTED LOOPS                                               |                             |    574 |      2 |   1381 |00:00:00.01 |    2309 |      0 |       |       |          |
|   9 |    NESTED LOOPS                                              |                             |    574 |      2 |   1381 |00:00:00.01 |     928 |      0 |       |       |          |
|* 10 |     TABLE ACCESS BY INDEX ROWID                              | PARTY_ROLE_MEMBERSHIPS      |    574 |      2 |   1381 |00:00:00.01 |     924 |      0 |       |       |          |
|* 11 |      INDEX RANGE SCAN                                        | PARTY_ROLE_MEMBERSHIPS_PK   |    574 |      2 |   1423 |00:00:00.01 |     232 |      0 |       |       |          |
|* 12 |     INDEX UNIQUE SCAN                                        | SYS_C0025143                |   1381 |      1 |   1381 |00:00:00.01 |       4 |      0 |       |       |          |
|* 13 |    TABLE ACCESS BY INDEX ROWID                               | CHANNEL_ROLES               |   1381 |      1 |   1381 |00:00:00.01 |    1381 |      0 |       |       |          |
|  14 |  SORT AGGREGATE                                              |                             |     67 |      1 |     67 |00:00:00.01 |       4 |      0 |       |       |          |
|* 15 |   INDEX UNIQUE SCAN                                          | SYS_C0025143                |     67 |      1 |      7 |00:00:00.01 |       4 |      0 |       |       |          |
|  16 |  SORT GROUP BY                                               |                             |    574 |      1 |    574 |00:00:00.01 |     928 |      0 |  2048 |  2048 | 2048  (0)|
|* 17 |   TABLE ACCESS BY INDEX ROWID                                | PARTY_ROLE_MEMBERSHIPS      |    574 |      2 |   1381 |00:00:00.01 |     924 |      0 |       |       |          |
|* 18 |    INDEX RANGE SCAN                                          | PARTY_ROLE_MEMBERSHIPS_PK   |    574 |      2 |   1423 |00:00:00.01 |     232 |      0 |       |       |          |
|  19 |  SORT AGGREGATE                                              |                             |    574 |      1 |    574 |00:00:00.01 |     800 |      0 |       |       |          |
|* 20 |   TABLE ACCESS BY INDEX ROWID                                | PARTIES                     |    574 |      1 |    292 |00:00:00.01 |     800 |      0 |       |       |          |
|* 21 |    INDEX UNIQUE SCAN                                         | PARTIES_PK                  |    574 |      1 |    574 |00:00:00.01 |     226 |      0 |       |       |          |
|* 22 |  VIEW                                                        |                             |      1 |   1218 |     50 |00:00:00.24 |   16042 |    404 |       |       |          |
|  23 |   WINDOW BUFFER                                              |                             |      1 |   1218 |    574 |00:00:00.24 |   16042 |    404 |   302K|   302K|  268K (0)|
|  24 |    COUNT                                                     |                             |      1 |        |    574 |00:00:00.23 |   16042 |    404 |       |       |          |
|  25 |     VIEW                                                     |                             |      1 |   1218 |    574 |00:00:00.23 |   16042 |    404 |       |       |          |
|  26 |      SORT ORDER BY                                           |                             |      1 |   1218 |    574 |00:00:00.23 |   16042 |    404 |   337K|   337K|  299K (0)|
|  27 |       VIEW                                                   |                             |      1 |   1218 |    574 |00:00:00.23 |   16042 |    404 |       |       |          |
|  28 |        HASH UNIQUE                                           |                             |      1 |   1218 |    574 |00:00:00.23 |   16042 |    404 |  2522K|   918K| 2505K (0)|
|  29 |         WINDOW SORT                                          |                             |      1 |   1218 |   1406 |00:00:00.15 |    9233 |    404 |  1257K|   577K| 1117K (0)|
|* 30 |          HASH JOIN                                           |                             |      1 |   1218 |   1406 |00:00:00.14 |    9233 |    404 |  1599K|  1599K| 1790K (0)|
|  31 |           TABLE ACCESS FULL                                  | PARTIES                     |      1 |   7296 |   7533 |00:00:00.01 |     183 |      0 |       |       |          |
|* 32 |           HASH JOIN ANTI                                     |                             |      1 |   1218 |   1406 |00:00:00.13 |    9050 |    404 |  1634K|   934K| 2366K (0)|
|* 33 |            HASH JOIN                                         |                             |      1 |   4003 |   1421 |00:00:00.05 |    2592 |      0 |  1496K|  1330K| 1698K (0)|
|* 34 |             TABLE ACCESS FULL                                | PARTY_ROLE_MEMBERSHIPS      |      1 |   7517 |   8219 |00:00:00.01 |     183 |      0 |       |       |          |
|* 35 |             HASH JOIN                                        |                             |      1 |   2076 |    588 |00:00:00.06 |    2409 |      0 |  1359K|  1359K| 1550K (0)|
|* 36 |              TABLE ACCESS FULL                               | PARTY_NAMES                 |      1 |   7045 |   7166 |00:00:00.01 |     247 |      0 |       |       |          |
|* 37 |              HASH JOIN                                       |                             |      1 |   2138 |    588 |00:00:00.06 |    2162 |      0 |  1427K|  1427K| 1550K (0)|
|* 38 |               TABLE ACCESS FULL                              | PARTY_NAMES                 |      1 |   7045 |   7166 |00:00:00.01 |     247 |      0 |       |       |          |
|* 39 |               HASH JOIN                                      |                             |      1 |   2024 |    588 |00:00:00.05 |    1915 |      0 |  2061K|  2061K| 1737K (0)|
|  40 |                INDEX FAST FULL SCAN                          | BRAND_PARTIES_PK            |      1 |   2456 |   2570 |00:00:00.01 |      17 |      0 |       |       |          |
|* 41 |                HASH JOIN RIGHT OUTER                         |                             |      1 |   2024 |    588 |00:00:00.05 |    1898 |      0 |  1185K|  1185K| 1323K (0)|
|* 42 |                 TABLE ACCESS FULL                            | ACCOUNT_STATUS              |      1 |    888 |    925 |00:00:00.01 |      17 |      0 |       |       |          |
|  43 |                 NESTED LOOPS OUTER                           |                             |      1 |   2024 |    588 |00:00:00.05 |    1881 |      0 |       |       |          |
|* 44 |                  HASH JOIN RIGHT OUTER                       |                             |      1 |   2024 |    588 |00:00:00.05 |    1881 |      0 |  2616K|  2616K|  232K (0)|
|* 45 |                   TABLE ACCESS FULL                          | INTERNAL_CARD_ACCOUNTS      |      1 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 46 |                   HASH JOIN RIGHT OUTER                      |                             |      1 |   2024 |    588 |00:00:00.04 |    1881 |      0 |  1696K|  1696K| 1712K (0)|
|  47 |                    INDEX FAST FULL SCAN                      | BRAND_PARTIES_PK            |      1 |   2456 |   2570 |00:00:00.01 |      17 |      0 |       |       |          |
|* 48 |                    HASH JOIN RIGHT OUTER                     |                             |      1 |   2024 |    588 |00:00:00.04 |    1864 |      0 |  1185K|  1185K| 1359K (0)|
|* 49 |                     TABLE ACCESS FULL                        | ACCOUNT_STATUS              |      1 |    888 |    925 |00:00:00.01 |      17 |      0 |       |       |          |
|* 50 |                     HASH JOIN RIGHT OUTER                    |                             |      1 |   2024 |    588 |00:00:00.04 |    1847 |      0 |  1199K|  1199K| 1356K (0)|
|  51 |                      TABLE ACCESS FULL                       | ACCOUNTS                    |      1 |    903 |    937 |00:00:00.01 |      28 |      0 |       |       |          |
|* 52 |                      HASH JOIN RIGHT OUTER                   |                             |      1 |   2024 |    588 |00:00:00.04 |    1819 |      0 |  1172K|  1172K| 1418K (0)|
|* 53 |                       TABLE ACCESS FULL                      | CARD_ACCOUNTS               |      1 |   2425 |   2533 |00:00:00.01 |      45 |      0 |       |       |          |
|* 54 |                       HASH JOIN                              |                             |      1 |   2018 |    588 |00:00:00.04 |    1774 |      0 |  1208K|  1208K| 1483K (0)|
|* 55 |                        TABLE ACCESS FULL                     | PARTY_RELATIONS             |      1 |   4345 |   4520 |00:00:00.01 |      92 |      0 |       |       |          |
|* 56 |                        HASH JOIN RIGHT OUTER                 |                             |      1 |   2016 |    588 |00:00:00.03 |    1682 |      0 |  1079K|  1079K| 1425K (0)|
|* 57 |                         TABLE ACCESS FULL                    | ADMIN_GROUPS                |      1 |   3027 |   3191 |00:00:00.01 |      67 |      0 |       |       |          |
|* 58 |                         HASH JOIN RIGHT OUTER                |                             |      1 |   2016 |    588 |00:00:00.03 |    1615 |      0 |  1270K|  1270K| 1484K (0)|
|* 59 |                          TABLE ACCESS FULL                   | ADMIN_GROUP_MEMBERSHIPS     |      1 |   4299 |   4518 |00:00:00.01 |      83 |      0 |       |       |          |
|* 60 |                          HASH JOIN                           |                             |      1 |   1885 |    588 |00:00:00.03 |    1532 |      0 |  1135K|  1135K| 1425K (0)|
|* 61 |                           TABLE ACCESS FULL                  | PARTY_STATUS                |      1 |   3102 |   3561 |00:00:00.01 |     183 |      0 |       |       |          |
|* 62 |                           HASH JOIN RIGHT OUTER              |                             |      1 |   2910 |   4536 |00:00:00.04 |    1349 |      0 |  1281K|  1281K| 1611K (0)|
|* 63 |                            TABLE ACCESS FULL                 | PARTY_ACCESS_DEVICES        |      1 |    847 |    897 |00:00:00.01 |      15 |      0 |       |       |          |
|* 64 |                            HASH JOIN RIGHT OUTER             |                             |      1 |   2910 |   4531 |00:00:00.04 |    1334 |      0 |  1199K|  1199K| 1297K (0)|
|* 65 |                             TABLE ACCESS FULL                | PARTY_TELEPHONY_PIN_STATUS  |      1 |    839 |    883 |00:00:00.01 |      15 |      0 |       |       |          |
|* 66 |                             HASH JOIN RIGHT OUTER            |                             |      1 |   2910 |   4530 |00:00:00.03 |    1319 |      0 |  1281K|  1281K| 1298K (0)|
|* 67 |                              TABLE ACCESS FULL               | VERIFICATION_ATTEMPTS       |      1 |    523 |    523 |00:00:00.01 |      15 |      0 |       |       |          |
|* 68 |                              HASH JOIN RIGHT OUTER           |                             |      1 |   2910 |   4530 |00:00:00.03 |    1304 |      0 |  1263K|  1263K| 1587K (0)|
|  69 |                               VIEW                           |                             |      1 |    162 |    162 |00:00:00.01 |     332 |      0 |       |       |          |
|  70 |                                NESTED LOOPS                  |                             |      1 |    162 |    162 |00:00:00.01 |     332 |      0 |       |       |          |
|  71 |                                 NESTED LOOPS                 |                             |      1 |    162 |    162 |00:00:00.01 |     168 |      0 |       |       |          |
|  72 |                                  VIEW                        | index$_join$_060            |      1 |    162 |    162 |00:00:00.01 |       6 |      0 |       |       |          |
|* 73 |                                   HASH JOIN                  |                             |      1 |        |    162 |00:00:00.01 |       6 |      0 |  1421K|  1421K| 1584K (0)|
|  74 |                                    INDEX FAST FULL SCAN      | PENDING_WORKFLOW_DETAILS_PK |      1 |    162 |    200 |00:00:00.01 |       3 |      0 |       |       |          |
|* 75 |                                    INDEX FAST FULL SCAN      | PENDING_WORKFLOW_DETAILS_UK |      1 |    162 |    162 |00:00:00.01 |       3 |      0 |       |       |          |
|* 76 |                                  INDEX UNIQUE SCAN           | WORKITEMDETAILS_PK          |    162 |      1 |    162 |00:00:00.01 |     162 |      0 |       |       |          |
|  77 |                                 TABLE ACCESS BY INDEX ROWID  | WORK_ITEM_DETAILS           |    162 |      1 |    162 |00:00:00.01 |     164 |      0 |       |       |          |
|* 78 |                               HASH JOIN RIGHT OUTER          |                             |      1 |   2910 |   4530 |00:00:00.02 |     972 |      0 |  1557K|  1557K| 1470K (0)|
|* 79 |                                TABLE ACCESS FULL             | WEB_VISITS                  |      1 |     15 |     15 |00:00:00.01 |       6 |      0 |       |       |          |
|* 80 |                                HASH JOIN                     |                             |      1 |   2910 |   4530 |00:00:00.02 |     966 |      0 |  1198K|  1198K| 1492K (0)|
|* 81 |                                 TABLE ACCESS FULL            | PARTIES                     |      1 |   4788 |   4963 |00:00:00.01 |     183 |      0 |       |       |          |
|* 82 |                                 HASH JOIN                    |                             |      1 |   4243 |   4530 |00:00:00.02 |     783 |      0 |  1315K|  1107K| 1489K (0)|
|* 83 |                                  HASH JOIN                   |                             |      1 |   4243 |   4530 |00:00:00.01 |     435 |      0 |  1214K|  1214K| 1457K (0)|
|  84 |                                   TABLE ACCESS FULL          | ELECTRONIC_ADDRESSES        |      1 |   4369 |   4588 |00:00:00.01 |      83 |      0 |       |       |          |
|  85 |                                   NESTED LOOPS               |                             |      1 |   4243 |   4543 |00:00:00.01 |     352 |      0 |       |       |          |
|  86 |                                    NESTED LOOPS              |                             |      1 |      1 |      1 |00:00:00.01 |       4 |      0 |       |       |          |
|  87 | WID                                 TABLE ACCESS BY INDEX RO | PARTY_STATUS_SORT_ORDERS    |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          |
|* 88 |                                      INDEX UNIQUE SCAN       | SYS_C0025403                |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |       |       |          |
|  89 | WID                                 TABLE ACCESS BY INDEX RO | PARTY_STATUS_CODES          |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          |
|* 90 |                                      INDEX UNIQUE SCAN       | SYS_C0025261                |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |       |       |          |
|* 91 |                                    TABLE ACCESS FULL         | ADDRESSES                   |      1 |   4243 |   4543 |00:00:00.01 |     348 |      0 |       |       |          |
|* 92 |                                  TABLE ACCESS FULL           | PARTY_ADDRESSES             |      1 |  15562 |  16400 |00:00:00.01 |     348 |      0 |       |       |          |
|  93 |                  TABLE ACCESS BY INDEX ROWID                 | ACCOUNTS                    |    588 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 94 |                   INDEX UNIQUE SCAN                          | ACCOUNTS_PK                 |    588 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  95 |            VIEW                                              | VW_SQ_1                     |      1 |     98 |    285 |00:00:00.01 |    6458 |    404 |       |       |          |
|  96 |             UNION-ALL                                        |                             |      1 |        |    285 |00:00:00.01 |    6458 |    404 |       |       |          |
|* 97 |              TABLE ACCESS FULL                               | EVENTS                      |      1 |      1 |      7 |00:00:00.01 |    6048 |      0 |       |       |          |
|  98 |              PARTITION RANGE ITERATOR                        |                             |      1 |     97 |    278 |00:00:00.03 |     410 |    404 |       |       |          |
|* 99 |               TABLE ACCESS FULL                              | EVENT_HISTORIES             |      6 |     97 |    278 |00:00:00.04 |     410 |    404 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Test Case not showing UNION ALL optimization

April 01, 2020 - 12:17 pm UTC

Reviewer: Narendra from UK

Hello Chris,

I had never tested your approach on the test case. So I thought I would test it again and the UNION ALL optimization does not appear to work (on 11.2.0.4)
SQL_ID  3fuj6nhx0dubj, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ *   FROM   user_np usn   WHERE  
NOT EXISTS (     SELECT null   FROM   user_logons_np uln     WHERE 
uln.user_id = usn.user_id     AND   uln.logon_date > trunc(SYSDATE)   
UNION ALL     SELECT null   FROM  user_logon_hist_np uhp     WHERE 
uhp.user_id = usn.user_id     AND   uhp.logon_date > trunc(SYSDATE) )
 
Plan hash value: 148728547
 
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    |      1 |        |      0 |00:00:00.01 |     260 |       |       |          |
|*  1 |  HASH JOIN ANTI      |                    |      1 |      1 |      0 |00:00:00.01 |     260 |  1245K|  1245K| 1246K (0)|
|   2 |   TABLE ACCESS FULL  | USER_NP            |      1 |     49 |     49 |00:00:00.01 |       2 |       |       |          |
|   3 |   VIEW               | VW_SQ_1            |      1 |   2531 |   2499 |00:00:00.01 |     258 |       |       |          |
|   4 |    UNION-ALL         |                    |      1 |        |   2499 |00:00:00.01 |     258 |       |       |          |
|*  5 |     TABLE ACCESS FULL| USER_LOGONS_NP     |      1 |     49 |      0 |00:00:00.01 |      25 |       |       |          |
|*  6 |     TABLE ACCESS FULL| USER_LOGON_HIST_NP |      1 |   2482 |   2499 |00:00:00.01 |     233 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("VW_COL_1"="USN"."USER_ID")
   5 - filter("ULN"."LOGON_DATE">TRUNC(SYSDATE@!))
   6 - filter("UHP"."LOGON_DATE">TRUNC(SYSDATE@!))
 

Chris Saxon

Followup  

April 01, 2020 - 4:46 pm UTC

Looking at this plan, you're getting zero rows for the first table in the UNION ALL. So the database has to check the second table for existence.

This isn't quite the optimizer I was talking about. Because it's a NOT EXISTS, the database can stop processing as soon as it finds one row. And thus reads no rows in the second table.

Checking on 11.2.0.4 it seems this optimization isn't happening. Here's the plan I get:

select /*+ GATHER_PLAN_STATISTICS */ *
from user_np usn
where not exists (
  select null
  from user_logons_np uln
  where uln.user_id = usn.user_id
  and uln.logon_date > trunc (sysdate)-30
  union all
  select null
  from user_logon_hist_np uhp
  where uhp.user_id = usn.user_id
  and uhp.logon_date > trunc (sysdate)-30
);

----------------------------------------------------------------------------------------------    
| Id  | Operation               | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |    
----------------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT        |          |      1 |        |      0 |00:00:00.01 |      62 |    
|*  1 |  HASH JOIN ANTI         |          |      1 |      1 |      0 |00:00:00.01 |      62 |    
|   2 |   TABLE ACCESS FULL     | USER_NP  |      1 |     29 |     29 |00:00:00.01 |       2 |    
|   3 |   VIEW                  | VW_SQ_1  |      1 |   3224 |   3219 |00:00:00.01 |      60 |    
|   4 |    UNION-ALL            |          |      1 |        |   3219 |00:00:00.01 |      60 |    
|*  5 |     INDEX FAST FULL SCAN| ULN_IDX1 |      1 |    879 |    870 |00:00:00.01 |      10 |    
|*  6 |     INDEX FAST FULL SCAN| UHN_IDX1 |      1 |   2345 |   2349 |00:00:00.01 |      50 |    
---------------------------------------------------------------------------------------------- 


Whereas 19c reports zero row/buffers for line 6 for this plan. So I must have run this on a different version to you, sorry.

Not sure when this change came in.

Sorry...test case does work

April 01, 2020 - 2:19 pm UTC

Reviewer: Narendra from UK

Hello Chris,

Apologies as I was too quick to respond about test case not showing UNION ALL optimization.
I looked again and I stand corrected....the test case does use UNION ALL optimization as you have mentioned.
I am just not sure why it is not working in the original sql.

For some reason, I tried posting execution plan for original sql but it is not getting posted.

Thanks

HASH JOIN appears to prevent UNION ALL optimization

April 02, 2020 - 7:50 am UTC

Reviewer: Narendra from UK

Hello Chris,

You are right again....and I need to learn to resist the urge to post based on incomplete testing.
On 11.2.0.4, what appears to happen is when the USERS_NP is "joined" to UNION ALL view using a HASH JOIN, the UNION ALL optimization does not appear to take place.
However, when I tried to force NESTED LOOP, either using a hint or adding extra predicate to check for 1 or 2 users only, the plan changes from using HASH JOIN ANTI to FILTER and the UNION ALL optimization works.

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name               | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                    |      1 |        |  1872 (100)|      0 |00:00:00.01 |     676 |
|*  1 |  FILTER             |                    |      1 |        |            |      0 |00:00:00.01 |     676 |
|   2 |   TABLE ACCESS FULL | USER_NP            |      1 |     49 |     3   (0)|     49 |00:00:00.01 |       2 |
|   3 |   UNION-ALL         |                    |     49 |        |            |     49 |00:00:00.01 |     674 |
|*  4 |    TABLE ACCESS FULL| USER_LOGONS_NP     |     49 |     29 |     9   (0)|     49 |00:00:00.01 |     674 |
|*  5 |    TABLE ACCESS FULL| USER_LOGON_HIST_NP |      0 |     80 |    67   (0)|      0 |00:00:00.01 |       0 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter( IS NULL)
   4 - filter(("ULN"."USER_ID"=:B1 AND "ULN"."LOGON_DATE">TRUNC(SYSDATE@!-30)))
   5 - filter(("UHP"."USER_ID"=:B1 AND "UHP"."LOGON_DATE">TRUNC(SYSDATE@!-30)))

Chris Saxon

Followup  

April 02, 2020 - 10:07 am UTC

No worries; you've supplied a workable test case. That already puts you far ahead of most people posting questions ;)

Is this solution working for you now?

Thanks

April 02, 2020 - 10:39 am UTC

Reviewer: Narendra from UK

Hello Chris,

I will have to test the sql on more realistic data volumes to see how it behaves. Due to multiple tables being joined to main table in the original sql, I am weary of the join order and whether I need to use sql profile and/or baseline, provided the performance is acceptable.

I will post once I have managed to test further.
Chris Saxon

Followup  

April 02, 2020 - 3:57 pm UTC

I need to use sql profile and/or baseline

If you want to be sure you get the "right" plan, this is the way to go.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database