2             from justin_good r

3            where not exists

4                  (select 'x' from justin_count pc where pc.id = r.justin_good_id)

5  ;

COUNT(*)

----------

7229

Executed in 3.437 seconds

SQL> select count(*)

2             from justin_good r

3            where r.justin_good_id not in

4                  (select pc.id from justin_count pc)

5  ;

COUNT(*)

----------

7229

Executed in 128.203 seconds

2             from justin_good r

3            where not exists

4                  (select 'x' from justin_count pc where pc.id = r.justin_good_id);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1087925722

----------------------------------------------------------------------------------------

| Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |               |     1 |     9 |  3452   (2)| 00:00:42 |

|   1 |  SORT AGGREGATE        |               |     1 |     9 |            |          |

|*  2 |   HASH JOIN RIGHT ANTI |               |    59 |   531 |  3452   (2)| 00:00:42 |

|   3 |    INDEX FAST FULL SCAN| PK11_1        |  4562 | 22810 |     4   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL   | justin_good   |   602K|  2355K|  3440   (2)| 00:00:42 |

----------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("PC"."ID"="R"."justin_good_id")

16 rows selected.

SQL> explain plan for select count(*)

2             from justin_good r

3            where r.justin_good_id not in

4                  (select pc.id from justin_count pc);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 4119029611

-------------------------------------------------------------------------------------

| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |               |     1 |     4 | 14216   (2)| 00:02:51 |

|   1 |  SORT AGGREGATE     |               |     1 |     4 |            |          |

|*  2 |   FILTER            |               |       |       |            |          |

|   3 |    TABLE ACCESS FULL| justin_good   |   602K|  2355K|  3442   (2)| 00:00:42 |

|*  4 |    INDEX FULL SCAN  | PK11_1        |     1 |     5 |    11   (0)| 00:00:01 |

-------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter( NOT EXISTS (SELECT 0 FROM "justin"."justin_count" "PC" WHERE

LNNVL("PC"."ID"<>:B1)))

4 - filter(LNNVL("PC"."ID"<>:B1))

18 rows selected.

SQL> explain plan for select count(*)

2             from justin_good r

3            where nvl(r.justin_good_id,'NULL') not in

4                  (select pc.id from justin_count pc);

Explained.

ts