Identification of external and internal tables connected by nested oracle Loops

Identification of external and internal tables connected by nested oracle Loops

SQL> create table a1 as select * from all_objects ;

Table created

SQL> select count(*) from a1;

  COUNT(*)

----------

     49708

SQL> create table a2 as select * from a1 where rownum<=10000;

Table created

SQL> analyze table a1 computer statistics;

SQL> analyze table a2 computer statistics;

 

1 nested connection

Select/* + Use_nl (a1, a2 )*/A1.object _ name
FromA1, a2
WhereA1.object _ id = a2.object _ id

The External table and internal table cannot be seen below. It is said that a small number of tables or result sets are used as external tables, and then the data of an External table is read to scan the internal table in the whole table. I heard that the second to last table is an external table.

 

Row 10000 has been selected.

Used time: 00: 01: 09.95

Execution Plan

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

Plan hash value: 2866307826

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

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

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

|   0 | SELECT STATEMENT   |      | 10000 |   312K|  1338K  (2)| 04:27:46 |

|   1 |  NESTED LOOPS      |      | 10000 |   312K|  1338K  (2)| 04:27:46 |

|   2 |   TABLE ACCESS FULL| A1   | 49708 |  1359K|   140   (3)| 00:00:02 |

|*  3 |   TABLE ACCESS FULL| A2   |     1 |     4 |    27   (4)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   3 - filter("A1"."OBJECT_ID"="A2"."OBJECT_ID")

Statistics

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

          1  recursive calls

          0  db block gets

    6414326  consistent gets

          0  physical reads

          0  redo size

     278675  bytes sent via SQL*Net to client

       7711  bytes received via SQL*Net from client

        668  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      10000  rows processed

 

2 add an index

create index ix_a2_type on a2(object_type);

select/*+use_nl(a1,a2)*/a1.object_name from  a1,a2where a1.object_id=a2.object_idand a2.object_type='INDEX'

Execution Plan

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

Plan hash value: 4121744415

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

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

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

|   0 | SELECT STATEMENT             |            |   333 | 12654 | 46021   (2)| 00:09:13 |

|   1 |  NESTED LOOPS                |            |   333 | 12654 | 46021   (2)| 00:09:13 |

|   2 |   TABLE ACCESS BY INDEX ROWID| A2         |   333 |  3330 |    22   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | IX_A2_TYPE |   333 |       |     1   (0)| 00:00:01 |

|*  4 |   TABLE ACCESS FULL          | A1         |     1 |    28 |   138   (3)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   3 - access("A2"."OBJECT_TYPE"='INDEX')

   4 - filter("A1"."OBJECT_ID"="A2"."OBJECT_ID")

Statistics

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

          1  recursive calls

          0  db block gets

     657969  consistent gets

          4  physical reads

          0  redo size

      27368  bytes sent via SQL*Net to client

       1078  bytes received via SQL*Net from client

         65  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        956  rows processed

 

Here A1 and A2 are changed. From the plsql developer tool view, if Depth is 3, read the index IX_A2_TYPE of A2 first.

select  count(a2.object_name )from   a2where  a2.object_type='INDEX'

Is 956 rows. So we can determine this theory: "a table with a small amount of data or a result set as an external table". It is unclear about this theory: "The Last and second tables are external tables,

Because the plan of the first statement violates the law of small data size if A1 is used as an external table.

 

3. added the ORDERD prompt.Note that this prompt specifies the sequence of the table following the FROM to determine the connection order. This time A2 connects A1.

select/*+ordered use_nl(a1,a2)*/a1.object_name from  a2,a1where a1.object_id=a2.object_id

 

 

Row 10000 has been selected.

Used time: 00: 01: 13.70

Execution Plan

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

Plan hash value: 3613923551

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

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

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

|   0 | SELECT STATEMENT   |      | 10000 |   312K|  1381K  (2)| 04:36:16 |

|   1 |  NESTED LOOPS      |      | 10000 |   312K|  1381K  (2)| 04:36:16 |

|   2 |   TABLE ACCESS FULL| A2   | 10000 | 40000 |    29   (4)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| A1   |     1 |    28 |   138   (3)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   3 - filter("A1"."OBJECT_ID"="A2"."OBJECT_ID")

Statistics

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

          1  recursive calls

          0  db block gets

    6881435  consistent gets

          0  physical reads

          0  redo size

     278675  bytes sent via SQL*Net to client

       7711  bytes received via SQL*Net from client

        668  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      10000  rows processed

 

Here, we can determine that the second to the last is the external table. Although the law conflicts, the optimizer only gets the path error, that is, the execution plan is wrong and manual intervention is required!

 

4. Create an index for the A1 table

create index ix_a1_type on a1(object_type);

select/*+ use_nl(a2,a1)*/a1.object_name from  a2,a1where a1.object_id=a2.object_idand a1.object_type='SYNONYM'

The number of synonym rows returned by this index is 20026, and the number of lead rows below is 1420. Do not make a mistake. The A2 table rows = 1 in the 4th row of the execution plan means that data is returned every time a row is matched.

A total of 1420 matches are required. Why is it 1420 rows? Why not 20026 rows? If it is 20026, A2 should be 10000 rows qualified as an external table.

 

 

Used time: 00: 00: 58.64

Execution Plan

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

Plan hash value: 2348548291

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

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

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

|   0 | SELECT STATEMENT             |            |  1420 | 56800 | 38305   (2)| 00:07:40 |

|   1 |  NESTED LOOPS                |            |  1420 | 56800 | 38305   (2)| 00:07:40 |

|   2 |   TABLE ACCESS BY INDEX ROWID| A1         |  1420 | 51120 |    63   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | IX_A1_TYPE |  1420 |       |     4   (0)| 00:00:01 |

|*  4 |   TABLE ACCESS FULL          | A2         |     1 |     4 |    27   (4)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   3 - access("A1"."OBJECT_TYPE"='SYNONYM')

   4 - filter("A1"."OBJECT_ID"="A2"."OBJECT_ID")

Statistics

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

          1  recursive calls

          0  db block gets

    2584503  consistent gets

          0  physical reads

          0  redo size

      74804  bytes sent via SQL*Net to client

       2277  bytes received via SQL*Net from client

        174  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       2592  rows processed

 

View specific values

select count(a1.object_name )from  a2,a1where a1.object_id=a2.object_idand a1.object_type='SYNONYM'

Result data volume: 2592

The execution plan is correct this time. The number of rows returned by the index is less than 10 thousand, but the number of rows calculated is less.

Because it calculates the number of rows divided by density.

select num_rows,distinct_keys,num_rows/distinct_keys from user_ind_statistics where index_name='IX_A1_TYPE'

NUM_ROWS	DISTINCT_KEYS	NUM_ROWS/DISTINCT_KEYS
49708	35	1420.22857142857

Similarly

select num_rows,distinct_keys,num_rows/distinct_keys from user_ind_statistics where index_name='IX_A2_TYPE'

NUM_ROWS	DISTINCT_KEYS	NUM_ROWS/DISTINCT_KEYS
10000	30	333.333333333333

 

Conclusion: the second law of nested loops is that the number of external tables is small (that is, the legendary driving table, which is not professional). The External table is the penultimate table in the plan.

 

5. Let's take a look.

select/*+ordered use_nl(a1,a2)*/a1.object_name from  a2,a1where a1.object_id=a2.object_idand a1.object_type='SYNONYM'

 

Row 2592 has been selected.

Used time: 00: 00: 02.93

Execution Plan

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

Plan hash value: 3029564842

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

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

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

|   0 | SELECT STATEMENT            |               |  1420 | 56800 | 20055   (1)| 00:04:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| A1            |     1 |    36 |     2   (0)| 00:00:01 |

|   2 |   NESTED LOOPS              |               |  1420 | 56800 | 20055   (1)| 00:04:01 |

|   3 |    TABLE ACCESS FULL        | A2            | 10000 | 40000 |    29   (4)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN         | IX_A1_ID_TYPE |     1 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   4 - access("A1"."OBJECT_ID"="A2"."OBJECT_ID" AND "A1"."OBJECT_TYPE"='SYNONYM')

Statistics

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

          1  recursive calls

          0  db block gets

      10928  consistent gets

          0  physical reads

          0  redo size

      74804  bytes sent via SQL*Net to client

       2277  bytes received via SQL*Net from client

        174  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       2592  rows processed

 

In this case, the plan is incorrect. The index returns 1420 results, and the A2 table returns 10 thousand results. Forcibly specifying a connection prompt will also lead to an incorrect execution plan. Here we regard the index as an internal table! Scan the index for 10 thousand times.