Oracle Execution Plan (3)-Two-table join Base

Source: Internet
Author: User
Oracle Execution Plan (3)-join base of two tables 1 formula: Base join selection rate * filter condition 1 base + filter condition 2 base join selection rate (num_rows (Table 1) -num_nulls (Table 1 connection field) num_rows (table 1) * (num_rows (table 2)-num_nulls (Table 2 connection field) num_rows (table 2 ))

Oracle Execution Plan (3)-Two-table join base 1 formula: base = connection selection rate * filtering condition 1 base + filtering condition 2 base join selection rate = (num_rows (Table 1) -num_nulls (Table 1 connection field)/num_rows (table 1) * (num_rows (table 2)-num_nulls (Table 2 connection field)/num_rows (table 2 ))

Oracle Execution Plan (3)-Two-table join Base

1 formula:

Base = connection selection rate * filtering condition 1 base + filtering condition 2 base

Connection selection rate = (num_rows (table 1)-num_nulls (Table 1 connection field)/num_rows (table 1 ))*

(Num_rows (table 2)-num_nulls (Table 2 join field)/num_rows (table 2 ))/

Greater (num_distinct (Table 1 join field), num_distinct (Table 2 join field ))

create table t1 asselect  trunc(dbms_random.value(0,25)) filter1,trunc(dbms_random.value(0,30)) join1,lpad(rownum,10) v1,rpad('x',100) padding1from all_objectswhere rownum<=10000; create table t2 asselect  trunc(dbms_random.value(0,50)) filter2,trunc(dbms_random.value(0,40)) join2,lpad(rownum,10) v2,rpad('x',100) padding2from all_objectswhere rownum<=10000; select t1.v1,t2.v2from t1,t2where t1.join1=t2.join2and t1.filter=1and t2.filter2=2


Row 2259 has been selected.
Used time: 00: 00: 00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2000 | 56000 |    76   (3)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  2000 | 56000 |    76   (3)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |   200 |  2800 |    38   (3)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |   400 |  5600 |    38   (3)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."JOIN1"="T2"."JOIN2")
   2 - filter("T2"."FILTER2"=2)
   3 - filter("T1"."FILTER"=1)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        504  consistent gets
          0  physical reads
          0  redo size
      60032  bytes sent via SQL*Net to client
       2035  bytes received via SQL*Net from client
        152  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2259  rows processed

Select*FromUser_tab_col_statisticsWhereTable_name = 't1'

TABLE_NAME

COLUMN_NAME

NUM_DISTINCT

DENSITY

NUM_NULLS

T1

FILTER

25

0.04

0

T1

JOIN1

30

0.0333333333333333

0

T1

V1

10000

0.0001

0

T1

PADDING

1

1

0

TABLE_NAME

COLUMN_NAME

NUM_DISTINCT

DENSITY

NUM_NULLS

SAMPLE_SIZE

T2

FILTER2

50

0.02

0

10000

T2

JOIN2

40

0.025

0

10000

T2

V2

10000

0.0001

0

10000

T2

PADDING2

1

1

0

10000

Connection selection rate = (10000-0)/10000) * (1000-0)/10000)/greater (1/40) =

Connection base = 1/40 * (400*200) = 2000

In the Execution Plan, T2 ROWS = 200, T1.ROWS = 400 hash join. ROWS = 2000
|*  1 |  HASH JOIN         |      |  2000 | 56000 |    76   (3)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |   200 |  2800 |    38   (3)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |   400 |  5600 |    38   (3)| 00:00:01 |

2 contains null values

Update t1 set join1 = null where mod (to_number (v1), 20) = 0;

Update t2 set join2 = null where mod (to_number (v2), 30) = 0;

SQL> analyze table t2 compute statistics;

SQL> analyze table t1 compute statistics;

Select*FromUser_tab_col_statisticsWhereTable_name = 't1'

TABLE_NAME

COLUMN_NAME

NUM_DISTINCT

DENSITY

NUM_NULLS

SAMPLE_SIZE

T1

FILTER

25

0.04

0

10000

T1

JOIN1

30

0.0333333333333333

500

10000

T1

V1

10000

0.0001

0

10000

T1

PADDING

1

1

0

10000

TABLE_NAME

COLUMN_NAME

NUM_DISTINCT

DENSITY

NUM_NULLS

SAMPLE_SIZE

T2

FILTER2

50

0.02

0

10000

T2

JOIN2

40

0.025

333

10000

T2

V2

10000

0.0001

0

10000

T2

PADDING2

1

1

0

10000

Set formula selection rate = (10000-500)/10000) * (10000-333)/10000)/greater)

= 9500/10000*9667/10000/40

= 0.95*0.9667/40

= 0.022959125

Base = 200*400*0.022959125 = 1836.73

Execution Plan:

Row 2042 has been selected.

Used time: 00: 00: 00.03

Execution Plan

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

Plan hash value: 2959412835
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1837 | 51436 |    76   (3)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  1837 | 51436 |    76   (3)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |   200 |  2800 |    38   (3)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |   400 |  5600 |    38   (3)| 00:00:01 |
---------------------------------------------------------------------------

3. filter the base number.

Base = basic choice rate * (num_rows-nulls)

Update t1 set filter = null where mod (to_number (v1), 50) = 0;

Update t2 set filter2 = null where mod (to_number (v2), 100) = 0;

200 rows updated

100 rows updated

T1.filter cardinatitly = 1/25 * (10000-200) = 392

T2.FILTER2 CARDINATILTY = 1/50 (10000-100) = 198

Connection base = 392*198*0.022959125 = 1781.995

Row 2000 has been selected.

Used time: 00: 00: 00.06

Execution Plan

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

Plan hash value: 2959412835
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1782 | 49896 |    76   (3)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  1782 | 49896 |    76   (3)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |   198 |  2772 |    38   (3)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T1   |   392 |  5488 |    38   (3)| 00:00:01 |

4. Multiple connection conditions

SelectT3.v2, t4.v2
FromT3, t4
WhereT3.join1 = t4.join2
AndT3.join2 = t4.join2

Join formula: = (condition 1 choice rate) * (condition 2 choice rate)

No!

5-range connectionChoice Rate

1 Where t1.join1

2 Where t2.join1 between t1.join1-1 and t1.join1 + 1

1 choice rate = 5% fixed choice Rate

2 convert to bind variable format, fixed choice rate multiplied. 5% * 5%

6 unequal connection selection rate

Where t1.join1! = T2.join2

Choice rate = 1-(t1.join1 = t2.join2 choice rate)

= 1-1/40 = 39/40

7 and or multiple connection conditions

1 where t1.join1 = t2.join1 and t1.join2 = t2.join2

2 where t1.join1 = t2.join1 OR t1.join2 = t2.join2

You can refer to the multi-predicate selection rate of a single table base.

1 join1 choice rate * join2 choice Rate

2 join1 choice rate + join2 choice rate-join1 choice rate * join2 choice Rate

8. Three-table join base selection rate

Create TableT3As
Select
Trunc (dbms_random.Value(0, 50) filter2,
Trunc (dbms_random.Value(0, 30) join1,
Trunc (dbms_random.Value(0, 50) join2,
Lpad (Rownum, 10) v2,
Rpad ('x', 100) padding2
FromAll_objects
Where rownum<= 10000;

Run the statement after the T1 and T2 tables are analyzed again.

SelectT1.v1, t2.v2, t3.v2
FromT1, t2, t3
WhereT1.join1 = t2.join2
AndT2.join2 = t3.join1
AndT1.filter1 = 1
AndT2.filter2 = 1

1. Select the rate and base number of T1 and T2 first

We have obtained 2000

2 T2 and T3 connections

Apply formulas T2 and T3

Choice rate = (10000-0)/10000) * (10000-0)/10000)/greater (1/40) =

Base = 1/40*2000*10000 = 50

Note that 2000 is the base of the first connection, and 10000 is the base of T3 without filtering conditions.

Used time: 00: 00: 09.42

Execution Plan

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

Plan hash value: 1184213596
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   500K|    19M|   123   (9)| 00:00:02 |
|*  1 |  HASH JOIN          |      |   500K|    19M|   123   (9)| 00:00:02 |
|*  2 |   HASH JOIN         |      |  2000 | 56000 |    76   (3)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T2   |   200 |  2800 |    38   (3)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T1   |   400 |  5600 |    38   (3)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | T3   | 10000 |   117K|    39   (3)| 00:00:01 |

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

Predicate Information (identified by operation id ):

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

1-access ("T2". "JOIN2" = "T3". "JOIN1 ")

2-access ("T1". "JOIN1" = "T2". "JOIN2 ")

3-filter ("T2". "FILTER2" = 1)

4-filter ("T1". "FILTER1" = 1)

9 pass Closure

Create TableT4As
Select
Trunc (dbms_random.Value(0, 50) filter2,
Trunc (dbms_random.Value(0, 40) join1,
Trunc (dbms_random.Value(0, 40) join2,
Lpad (Rownum, 10) v2,
Rpad ('x', 100) padding2
FromAll_objects
Where rownum<= 10000;

SelectT3.v2, t4.v2
FromT3, t4
WhereT3.join1 = t4.join1
AndT3.join2 = t4.join2
AndT3.join1 = 20;

The closure is passed because T3.JOIN1 = 20 and T3.JOIN1 = T4.JOIN1 then T4.JOIN1 = 20;

Execution Plan

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

Plan hash value: 920528290
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    52 |  1456 |    78   (3)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    52 |  1456 |    78   (3)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T4   |   250 |  3500 |    39   (3)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T3   |   333 |  4662 |    39   (3)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1-access ("T3". "JOIN1" = "T4". "JOIN1" AND "T3". "JOIN2" = "T4". "JOIN2 ")

2-Filter ("T4". "JOIN1" = 20)

3-filter ("T3". "JOIN1" = 20)

In fact, the number of rows in the result set is: The base number of 1554 differs greatly from that of 52.

Because JOIN1 selection rate * JOIN2 selection rate = (10000-0)/10000) * (10000-0)/10000)/greater)

* (10000-0)/10000) * (10000-0)/10000)/greater (50, 40) = 1/40*1/50 = 1/2000

The minimum selection rate of the selected result set is multiplied by 1/40*1/40 = 1/1600 because of the 10 Gb multi-column integrity check.

Base = 1/1600*10000/30*10000/40 = 52

Base = 1/40*10000/30*10000/50 = 1/40*333*200 = 1665 is equivalent to the result set because the connection condition is not eliminated in this version.

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.