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.