The impact of not EXISTS external query in Oracle

Another example of "12c more consumption than 10g index back-table" case, colleagues in 12c run the buffer get is very high, but in 10g run a low buffer. Doubt whether the 12c optimizer has a problem.

This 10g environment and 12c environment, the amount of data is roughly the same, but there are very few differences, but this is a very small part of the difference, resulting in the NOT EXISTS subquery return different values, and then the outer query has a different impact.

Let's use the following code to simulate this.

Initializing data:
--10g
drop table T1;
drop table T2;

CREATE TABLE T1 (ID number,name varchar2 (), dep_id varchar2 (10));
CREATE TABLE t2 (ID number,name varchar2 (), dep_id varchar2 (10));

INSERT INTO T1 select RowNum, ' A ', ' KK ' to dual connect by level <=3000000;
Insert INTO T2 select RowNum, ' A ', ' KK ' to dual connect by level <=1000000;
Insert INTO T2 select RowNum, ' a ', ' mm ' from dual;

Commit


--12c
drop table T1;
drop table T2;

CREATE TABLE T1 (ID number,name varchar2 (), dep_id varchar2 (10));
CREATE TABLE t2 (ID number,name varchar2 (), dep_id varchar2 (10));


INSERT INTO T1 select RowNum, ' A ', ' KK ' to dual connect by level <=3000000;
Insert INTO T2 select RowNum, ' A ', ' KK ' to dual connect by level <=1000000;

Commit
We see that 12c of data and 10g are only a few differences, T1 table 12c and 10g are the same, the T2 table in 12c is only a single line of data.

--10g
Sql> Select Dep_id,count (*) from the T1 group by dep_id;

dep_id COUNT (*)
-------------------- ----------
KK 3000000

Sql> Select Dep_id,count (*) from the T2 group by dep_id;

dep_id COUNT (*)
-------------------- ----------
MM 1
KK 1000000

Sql>


--12c
Sql> Select Dep_id,count (*) from the T1 group by dep_id;

dep_id COUNT (*)
-------------------- ----------
KK 3000000

Sql> Select Dep_id,count (*) from the T2 group by dep_id;

dep_id COUNT (*)
-------------------- ----------
KK 1000000

Sql>

The SQL statement we are going to execute is:

Select COUNT (*)
from T1, T2
where t1.id = T2.id
and t1.dep_id = ' KK '
And NOT EXISTS (select 1
from T1, T2
where t1.id = T2.id
and t2.dep_id = ' mm ');
We first look at the implementation of the gap, 10g bufferget small, 12c more:

--10g
Sql> Select/*+ gather_plan_statistics */COUNT (*) from t1,t2 where t1.id=t2.id and t1.dep_id= ' KK ' and NOT EXISTS (Sele Ct 1 from T1,t2 where t1.id=t2.id and t2.dep_id= ' mm ');

COUNT (*)
----------
0

Sql> select* from table (Dbms_xplan.display_cursor (Null,null, ' allstats-last '));

Plan_table_output
--------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------
sql_id 22T5MB43W55PR, child number 0
-------------------------------------
Select/*+ Gather_plan_statistics */COUNT (*) from t1,t2 where t1.id=t2.id and t1.dep_id= ' KK ' and not
Exists (select 1 from t1,t2 where t1.id=t2.id and t2.dep_id= ' mm ')

Plan Hash value:3404612428

--------------------------------------------------------------------------------------------------------------- ---
| Id | Operation | Name | Starts | E-rows |   A-rows | A-time |  Buffers |  Omem | 1Mem | Used-mem |
--------------------------------------------------------------------------------------------------------------- ---
| 0 |      SELECT STATEMENT |      |        1 |      |    1 |00:00:00.02 |       2086 |       |          | |
|  1 |      SORT AGGREGATE |      |      1 |      1 |    1 |00:00:00.02 |       2086 |       |          | |
|* 2 |      FILTER |      |        1 |      |    0 |00:00:00.02 |       2086 |       |          | |
|* 3 |      HASH JOIN |      |    0 |      901k|       0 |00:00:00.01 |    0 |  39m|          5518k| |
|     4 | TABLE ACCESS full|      T2 |    0 |      901k|       0 |00:00:00.01 |       0 |       |          | |
|* 5 | TABLE ACCESS full|      T1 |   0 |      2555k|       0 |00:00:00.01 |       0 |       |          | |
|* 6 |      HASH JOIN |      |     1 |      23 |    1 |00:00:00.02 |  2086 |  1517k|  1517k| 612K (0) |
|* 7 | TABLE ACCESS full|      T2 |     1 |      23 |    1 |00:00:00.02 |       2082 |       |          | |
|     8 | TABLE ACCESS full|      T1 |   1 |      2555k|       1 |00:00:00.01 |       4 |       |          | |
--------------------------------------------------------------------------------------------------------------- ---

predicate information (identified by Operation ID):
---------------------------------------------------

2-filter (is NULL)
3-access ("T1".) ID "=" T2 "." ID ")
5-filter ("T1".) dep_id "= ' kk ')
6-access ("T1".) ID "=" T2 "." ID ")
7-filter ("T2".) dep_id "= ' mm ')

Note
-----
-Dynamic sampling used for this statement

Selected rows.

Sql>

--12c
Sql> Select/*+ gather_plan_statistics */COUNT (*) from t1,t2 where t1.id=t2.id and t1.dep_id= ' KK ' and NOT EXISTS (Sele Ct 1 from T1,t2 where t1.id=t2.id and t2.dep_id= ' mm ');

COUNT (*)
----------
1000000

Sql> select* from table (Dbms_xplan.display_cursor (Null,null, ' allstats-last '));

Plan_table_output
--------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------
sql_id 22T5MB43W55PR, child number 0
-------------------------------------
Select/*+ Gather_plan_statistics */COUNT (*) from T1,T2 where
T1.id=t2.id and T1.dep_id= ' KK ' and NOT EXISTS (select 1 from T1,t2
where t1.id=t2.id and t2.dep_id= ' mm ')

Plan Hash value:1692274438

--------------------------------------------------------------------------------------------------------------- -----
| Id | Operation | Name | Starts | E-rows |   A-rows | A-time |  Buffers |  Omem | 1Mem | Used-mem |
--------------------------------------------------------------------------------------------------------------- -----
| 0 |      SELECT STATEMENT |      |        1 |      |   1 |00:00:00.79 |       10662 |    |     | |
|  1 |      SORT AGGREGATE |      |      1 |      1 |   1 |00:00:00.79 |       10662 |    |     | |
|* 2 |      FILTER |      |        1 |   |   1000k|00:00:00.74 |       10662 |    |     | |
|* 3 |      HASH JOIN |      |   1 |   1215k|    1000k|00:00:00.52 |    8579 |  43m|   6111k| 42M (0) |
|     4 | TABLE ACCESS Full |      T2 |   1 |   1215k|    1000k|00:00:00.01 |       2083 |    |     | |
|* 5 | TABLE ACCESS Full |      T1 |   1 |   2738k|    3000k|00:00:00.07 |       6496 |    |     | |
|* 6 |      HASH JOIN Right semi|      |     1 |      35 |    0 |00:00:00.02 |  2083 |  1245k|  1245k| 461K (0) |
|* 7 | TABLE ACCESS Full |      T2 |     1 |      23 |    0 |00:00:00.02 |       2083 |    |     | |
|     8 | TABLE ACCESS Full |      T1 |   0 |      2738k|       0 |00:00:00.01 |       0 |    |     | |
--------------------------------------------------------------------------------------------------------------- -----

predicate information (identified by Operation ID):
---------------------------------------------------

2-filter (is NULL)
3-access ("T1".) ID "=" T2 "." ID ")
5-filter ("T1".) dep_id "= ' kk ')
6-access ("T1".) ID "=" T2 "." ID ")
7-filter ("T2".) dep_id "= ' mm ')

Note
-----
-Dynamic Statistics used:dynamic sampling (level=2)

Rows selected.

Sql>
Sql>

You can see 23rd, 24 lines, in the 10g run, buffers is 0, and in 12c, that is 78, 79 lines, buffer is 2083+6496.

In other words, in 10g, the outer query does not perform T1 and T2 scans, returns the result directly, and in 12c, the outer query also T1 table and T2 surface scan to return the result.

This is actually not the difference between 10g and 12c, but the effect of the not exists return data on the outer layer. The subquery returns the result of the outer query by returning 0 rows of records to satisfy the condition of not exist.

In 10g, the subquery returns a row of records

--10g
Sql> Select 1 from t1,t2 where t1.id=t2.id and t2.dep_id= ' mm ';

1
----------
1

Sql>

Does not satisfy not exists (that is, 0 lines are satisfied), so you do not have to continue the query in the outer layer. Return directly to record 0 rows.

In 12c, the subquery returns 0 rows of records, satisfying the condition of not exist, so you also need to continue the query in the outer query.

--12c
Sql> Select 1 from t1,t2 where t1.id=t2.id and t2.dep_id= ' mm ';

No rows selected

Sql>

It is the difference in this line of records that leads to the impact of the not exists external query. This results in a difference in buffer get for the entire SQL.

To disprove this result, I just in 12c, run the subquery result returns more than 0 rows, does not satisfy not exists, also should not go to the outer query. See below:

--12c
Sql> Select COUNT (*) from t1,t2 where t1.id=t2.id and t2.dep_id= ' KK ';

COUNT (*)
----------
1000000

Sql> Set Line 1000
Sql> Set pages 1000
Sql> Col plan_table_output for a250
Sql>
Sql>
Sql> Select/*+ gather_plan_statistics */COUNT (*) from t1,t2 where t1.id=t2.id and t1.dep_id= ' KK ' and NOT EXISTS (Sele Ct 1 from T1,t2 where t1.id=t2.id and t2.dep_id= ' KK ');

COUNT (*)
----------
0

Sql> select* from table (Dbms_xplan.display_cursor (Null,null, ' allstats-last '));

Plan_table_output
--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------
sql_id C5HJ2P2JT1FXF, child number 0
-------------------------------------
Select/*+ Gather_plan_statistics */COUNT (*) from T1,T2 where
T1.id=t2.id and T1.dep_id= ' KK ' and NOT EXISTS (select 1 from T1,t2
where T1.id=t2.id and t2.dep_id= ' KK ')

Plan Hash value:1692274438

--------------------------------------------------------------------------------------------------------------- -----
| Id | Operation | Name | Starts | E-rows |   A-rows | A-time |  Buffers |  Omem | 1Mem | Used-mem |
--------------------------------------------------------------------------------------------------------------- -----
| 0 |      SELECT STATEMENT |      |        1 |      |    1 |00:00:00.28 |       2087 |    |     | |
|  1 |      SORT AGGREGATE |      |      1 |      1 |    1 |00:00:00.28 |       2087 |    |     | |
|* 2 |      FILTER |      |        1 |      |    0 |00:00:00.28 |       2087 |    |     | |
|* 3 |      HASH JOIN |      |   0 |      1215k|       0 |00:00:00.01 |    0 |  69m|          7428k| |
|     4 | TABLE ACCESS Full |      T2 |   0 |      1215k|       0 |00:00:00.01 |       0 |    |     | |
|* 5 | TABLE ACCESS Full |      T1 |   0 |      2738k|       0 |00:00:00.01 |       0 |    |     | |
|* 6 |      HASH JOIN Right semi|      |   1 |      2738k|    1 |00:00:00.28 |    2087 |  43m|   6111k| 42M (0) |
|* 7 | TABLE ACCESS Full |      T2 |   1 |   1215k|    1000k|00:00:00.12 |       2083 |    |     | |
|     8 | TABLE ACCESS Full |      T1 |   1 |      2738k|       1 |00:00:00.01 |       4 |    |     | |
--------------------------------------------------------------------------------------------------------------- -----

predicate information (identified by Operation ID):
---------------------------------------------------

2-filter (is NULL)
3-access ("T1".) ID "=" T2 "." ID ")
5-filter ("T1".) dep_id "= ' kk ')
6-access ("T1".) ID "=" T2 "." ID ")
7-filter ("T2".) dep_id "= ' kk ')

Note
-----
-Dynamic Statistics used:dynamic sampling (level=2)


Rows selected.

Sql>

You can see that the 38th, 39 line of buffer is 0.