Identification of external and internal tables connected by nested oracle Loops

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.