Oracle Execution Plan (2)-Cardinality cardinality__oracle

Oracle Execution Plan (2)-Cardinality

Executes the cardinality column for the plan. Cardinality is the number of rows of data returned from a datasheet, result set, or index.

cardinality = table row Data * selection rate. So the cardinal point is to talk about the selection rate, the calculation of the selection rate.

Version 10G default table space information:

Tablespace_name	USERS
Block_size	8192
Extent_management	Local
Allocation_type	SYSTEM
Segment_space_management	AUTO
Def_tab_compression	DISABLED
Bigfile	NO

Like we build a watch.

Create table student (ID number,name varchar2 (m), Birday date,month_no number (2));

Insert into student
Select
Trunc (dbms_random. Value(1,1200)) ID,
A.object_name,
sysdate-rownum as Birday,
Trunc (dbms_random. Value(1,13)) month_no
from All_objects a
where RowNum<=1200

Analysis under

sql> Analyze table student compute statistics;

Look at the data distribution

Select month_no,Count(1) from student Group by Month_no;

Month_no	COUNT (1)
1	104
2	87
3	100
4	91
5	114
6	102
7	102
8	96
9	100
10	114
11	97
12	93

The data is distributed fairly evenly.

Look at the table columns:

Select * from User_tab_col_statistics where table_name= ' STUDENT '

table_name	column_name	Num_distinct	Low_value	High_value	Density	Num_nulls	Num_buckets	Avg_col_len
STUDENT	Id	762	C102	C20c61	0.00131233595800525	0	1	3
STUDENT	NAME	1192	41434345535324	5741524e494e475f53455454494e475324	0.000838926174496644	0	1	14
STUDENT	Birday	1200	786c0c1f100d2e	7870040d100d2e	0.000833333333333333	0	1	7
STUDENT	Month_no	12	C102	c10d	0.0833333333333333	0	1	2

Column name Explanation:

1 num_distinct How many different values the column has, not NULL.

2 low_value the column minimum value

3 High_value The maximum value of the column

4 density This value is computed if there is a histogram for the column density.

5 Num_nulls How much of this column is vacant

6 Num_buckets How many barrels the column uses the histogram

7 Avg_col_len The average length of the column

Select * from User_tables where table_name= ' STUDENT '

Avg_row_len	Num_rows	Empty_blocks	BLOCKS
33	1200	3	13

Table information: The average length, the number of rows, the number of empty blocks, the total number of blocks.

OK, we're done with the data. Then we can talk about the selection rate, and look at the execution plan for such a statement.

Sql> set Autotrace on

Sql> Select COUNT (*) from STUDENT where month_no=12;

COUNT (*)

----------

93

Time used: 00:00:00.01

Execution plan

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

Plan Hash value:4030891848

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

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

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

| 0 |         SELECT STATEMENT |     |     1 |     2 | 4 (0) | 00:00:01 |

|  1 |         SORT AGGREGATE |     |     1 |            2 |          | |

|* 2 | TABLE ACCESS full|   STUDENT |   100 |     200 | 4 (0) | 00:00:01 |

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

The Sqlplus environment is the base of rows

There are actually 93 lines and the plan says there are 100 lines, based on the formula cardinality = row number * selection rate. Then the selection rate = cardinality/Line number =>. =100/1200=1/12.

The execution plan is calculated based on statistics to estimate the number of rows returned.

The selection rate is =1/num_distinct or density. Returns the value in the information above that looks at the table column. This is the basic selection rate.

1 selection rate in case of NULL value

Set the birthday of each month 10 total 120.

Update student set Month_no =null where Month_no=1 and rownum<=10;

...

Update student set Month_no =null where month_no=12 and rownum<=10;

Analyze the table below again and view the table column information to see the Month_no column num_nulls=120

Run the above statement again to get the execution plan:

Sql> Select COUNT (*) from STUDENT where month_no=12;

COUNT (*)

----------

83

Time used: 00:00:00.00

Execution plan

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

Plan Hash value:403089184

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

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

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

| 0 |         SELECT STATEMENT |     |     1 |     2 | 4 (0) | 00:00:01 |

|  1 |         SORT AGGREGATE |     |     1 |            2 |          | |

|* 2 | TABLE ACCESS full|    STUDENT |   90 |     180 | 4 (0) | 00:00:01 |

The base here has turned 90.

Formula Selection rate = Basic Selection rate * (num_rows-num_nulls)/num_rows.

The basic selection rate here is 1/num_distinct because of the conditions used =.

Formula derivation => =1/12* (1200-120)/1200=0.075

Cardinal =1200*0.075 =90

2 Use list in (...)

1 rollback update student set month_no=12 where month_no is null and rownum<=10;

2 Analysis Table

3 View table column information found Num_nulls changed back to the 0 value.

4 EXECUTE statement: SELECT COUNT (*) from STUDENT where Month_no in (6,7,8);

Sql> Select COUNT (*) from STUDENT where Month_no in (6,7,8);

COUNT (*)

----------

300

Time used: 00:00:00.00

Execution plan

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

Plan Hash value:4030891848

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

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

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

| 0 |         SELECT STATEMENT |     |     1 |     2 | 4 (0) | 00:00:01 |

|  1 |         SORT AGGREGATE |     |     1 |            2 |          | |

|* 2 | TABLE ACCESS full|   STUDENT |   300 |     600 | 4 (0) | 00:00:01 |

Formula = Basic Selection rate + Basic selection rate + basic selection rate = 3/12

If the in () or in (15,16) cardinality is 73,146 instead of 100,200. Because it exceeds the maximum value, when the maximum and minimum distance (11) is 0.

Distance 15-12=3 spacing 12-1=11 Average descending 100/11=9.09 15 of Cardinal 100-(3*9.09) =72.73=73. This illustrates the Oracle attenuation idea.

3 selection rate of interval predicate

1 Month_no >8, >=8,<8,<=8

2 between 6 and 9

3 >=6 and <=9; >=6 and <9; >6 and <=9; >6 and <9

4 >12

5 between and 30

6 >=:val1,> < <=:VAL1 binding variables

7 >=: Val1 and <=: Val2 between:val1 And:val2

Formula = Need space divided by free space

Free Space =high_vlaue-low_value=12-1=11

Need space = (high_vlaue-limit) or (Limit-low_value) or (High_vlaue-low_value)

1 month_no>8 (High_vlaue-limit)/(High_vlaue-low_value) = (12-8)/11=4/11

2 Month_no>=8 because = is the basic selection rate 1/12 therefore 4/11+1/12

3 Month_no < 8 is less than the minimum value above the maximum value (limit-low_value)/(High_vlaue-low_value) = (8-1)/11=7/11

4 between 6 and 9 or >=6 and <=9: (9-6)/(12-1) +1/12+1/12

5 (>=6,<9) (6>,<=9):(9-6)/(12-1) +1/12

6 >6 and < 9:(9-6)/(12-1)

7 >=:val1 Fixed selection rate 5%

8 >=:val1 and <=:val2 fixed ratio 2.5%

9 over the range will gradually decay.

4 DOUBLE predicate

1 month >8 or month <=8

2 month >8 and month<=8

Selection rate of predicate 1 (12-8)/(12-1) =4/11=0.363636364

Selection rate of predicate 2 (8-1)/(12-1) +1/12=7/11+1/12=0.71969697

Formula 1 (predicate 1 and predicate 2) = predicate 1 selection rate * Predicate 2 so the 2nd one is 4/11* (7/11+1/12) = 0.261707989

Formula 2 (predicate 1 or predicate 2) = predicate 1 selection rate + predicate 2 selection rate-(predicate 1 selectivity * predicate 2 selectivity)

=4/11+ ((7/11+1/12)-(4/11* (7/11+1/12))

=0.363636364+0.71969697-0.261707989

=0.821625344

Cardinal =1200*0.821625344=985.9504128=986

Look at the execution plan:

Sql> Select COUNT (*) from student where Month_no >8 or month_no<=8;

COUNT (*)

----------

1200

Time used: 00:00:00.01

Execution plan

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

Plan Hash value:4030891848

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

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

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

| 0 |         SELECT STATEMENT |     |     1 |     2 | 4 (0) | 00:00:01 |

|  1 |         SORT AGGREGATE |     |     1 |            2 |          | |

|* 2 | TABLE ACCESS full|   STUDENT |  986 |     1972 | 4 (0) | 00:00:01 |

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