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

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
SelectTrunc (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
SelectTrunc (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.