CBO learning ---- 03 -- Selectivity)

CBO learning ---- 03 -- Selectivity CBO learning --- Chapter 1 -- Table scan (Tablescans) http://www.bkjia.com/database/201307/229069.html Chapter 2 Single Table Selectivity is the optimizer to estimate Rows (Cards) important Basis.

/**************************************************************************************************************************************/3.1 Getting Startedselect count(*)from audiencewhere month_no = 12;

 

From the optimizer perspective, the number of people born in 1200 (1) user_tab_col_statistics.num_distinct = 12 (2) user_tab_histograms indicates low (1), high (12 ), uniform Distribution (3) user_tab_col_statistics.density = 1/12 (4) month_no = 12, single column, uniform, so user_tab_col_statistics.density available (5) low (1) <= 12 <= high (12) (6) rows = 0, no null value (7) user_tables.num_rows = 1200 (8) 1200/12 = 100 in the Code attachment of this chapter: birth_month_01.sqlhack_stats. SQL birth_month_01. SQL build table, first two query system tables, count ( *) Query twice. Hack_stats. SQL can be executed in other sessions twice to modify the statistical information of the table to see which statistical items affect the calculation of rows (1) Fill in the table name and column name in hack_stats. SQL, and modify the number of rows in the table numrows

[sql] define m_source_table='AUDIENCE'  define m_source_column='month_no'  --m_avgrlen := m_avgrlen + 25;  m_numrows:=m_numrows+1200;  

 

After the cross-execution, the rows changes from 100 to 200, and then numrows are restored (2) Modify distcnt and density to open the comment of Column statistics.

[sql] --m_distcnt:=m_distcnt+12;  m_density:=m_density/2;  

 

After multiple tests, the distcnt modification does not take effect, indicating that only density is involved in computing (Oracle version 10204 ). Not as mentioned in the book, in 9i and 10gR1, distcnt may be used to calculate rows when no histogram exists. In Versions later than Oracle, density is restored after the test is improved.

/*************************************** **************************************** **************************************** * *************/[SQL] begin dbms_stats.gather_table_stats (user, 'audience ', cascade => true, estimate_percent => null, method_opt =>' for all columns size 1 '); end; /method_opt => 'for all columns size 1' indicates that histograms are not collected. The default values of 8i and 9i are method_opt =>' for all columns size auto'. The default value is 10 Gb. Read [SQL] SQL> select dbms_stats.get_param ('method _ opt') from dual; DBMS_STATS.GET_PARAM ('method _ opt') rows FOR ALL COLUMNS SIZE AUTO has selected 1 row. /*************************************** **************************************** **************************************** ***************/

 

3.2 Null Values adds null to the calculation of rows. Assume that 10% of people do not remember their birthdays. In the code attachment in this chapter: birth_month_02. SQL, set row 120 to Null, in the end, rows = 90 indicates that the optimizer has excluded the influence of null on the estimation rows; density is still 1/12 and has not changed, indicating that density is the result after the number of null values is subtracted.

/**************************************************************************************************************************************/3.3 Using Listsselect count(*)from audiencewhere month_no in (6,7,8);

 

Start to study month_no in (6, 7, 8). When this condition is met, the rows calculation method is as follows in the Code attachment in this chapter: in_list. SQL, when there are no duplicate values, rows calculation is correct.

/*************************************** **************************************** **************************************** * **************/8i calculation error cause: select count (*) from audience where month_no in (6, 7, 8); select/* + use_concat */count (*) from audience where month_no in (6, 7, 8 ); /*************************************** **************************************** **************************************** ***************/

 

In the Code attachment of this chapter: in_list_02. SQL expands the number of months from 12 to 1000, and density is 0.001 to obtain the list 3-1, which is used to compare the computing values between 8i and 10g, with little influence between versions.

/*************************************** **************************************** **************************************** * **************/The code attachment in this chapter: oddities. when SQL statements contain repeated values, null values, and pass-through values, the calculation of rows is significantly improved by 10204 and the maximum and minimum values are taken into account (the original version does not consider pass-through values) condition 8i 92/10102 10204month_no = 25 100 100 1month_no in (4, 4) 100 100 100month_no in (3, 25) 192 200 100month_no in (3, 25, 26) 276 300 101month_no in (3, 25, 25, 26) 276 300 101month _ No in (3, 25, null) 276 300 200month_no in (: b1,: b2,: b3) 276 300 300 the rows cannot be correctly calculated only when null values exist, however, the error is smaller than the previous version. /*************************************** **************************************** **************************************** ***************/

 

10104 linear decay oracle adopted a more complex linear decay algorithm before 10.2, that is, when the maximum value or lower than the minimum value, the slope of a single value rows is "1/maximum-minimum ". Through the above 10204 tests, this algorithm is replaced in the new version. Result of in_list_10g. SQL in 10104 in code attachment of this chapter: Condition 10102 101_month_no = 13 100 91month_no = 15 100 73month_no in (200) 200 10910104 164month_no in (10102) is slightly improved, 10204 is more obvious than 10104. Although there are still defects (null), it indicates that the oracle opt team is constantly updating the Code.

/*************************************** **************************************** **************************************** */3.4: ranges. the SQL statements summarize the versions and the statistics are as follows: predicate 8i 92/10102 10204 437 mathematical expression true value 1 month_no> 8 436 436 4002 537 (8, 536 month_no> = 8 536 [8, 5003 month_no <8 764 764 764, 8) 7004 month_no <= 8 864 864 864, 8] 8005 month_no between 6 and 9 528 527 527 [6, 9] 4006 month_no> = 6 and month_no <= 9 528 527 527 [6, 9] 4007 month_no> = 6 and month_no <9 428 427 427) 3008 month_no> 6 and month_no <= 9 428 427 427 (3009] 328 month_no> 6 and month_no <9 327 327 20010 (101) month_no>: b1 60 60 (: b1, 11 month_no >=: b1 60 60 101 [: b1, 12 month_no <: b1 60 60 101,: b1) 13 month_no <=: b1 60 60 101 ,: b1] 14 month_no between: b1 and: b2 4 3 9 [: b1,: b2] 15 month_no >=: b1 and month_no <=: b2 4 3 9 [: b1 ,: b2] 16 month_no >=: b1 and month_no <: b2 4 3 9 [: b1,: b2) 17 month_no>: b1 and month_no <: b2 4 3 9 (: b1,: b2) 18 month_no>: b1 and month_no <=: b2 4 3 9 (: b1 ,: b2] 19 month_no> 12 100 100 100 (12,020 month_no between 25 and 30 100 100 100 [] 0

 

Among them, there is no difference between 4 of 8i and 3 of 9i (there is a decimal point between the 10th and binary conversion), but the 8i is used as a floating point in the computing formula, 9i rounds several rules found from the table above: (1) the gap between "(" and "]" is 100 (2) beyond the boundary value of 100 (3) rows with Bound variables have no influence on the open and closed intervals (4) rows 10-13. range of a single bound variable. The previous version is set to 5% (1200*5% = 60 ), more complex after 10204 (to be studied) (5) rows 14-18, 5% * 5% = 2.5/5%, 1200*5% * = 3 rows (6) lines 19-20, when the maximum and minimum values are exceeded, a fixed selection rate is given. The Code selectivity_one. SQL in this chapter provides the 9i test code for this link. In the Code attachment of this chapter: selectivity_one. SQL constructs a four-column nologging table with 3000 rows. Compared with the value query on the four boundaries and ">" query, rows are the same. It indicates the selection rate provided by the query that exceeds the boundary value, which is the same as the query that equals the boundary value.

/*************************************** **************************************** **************************************** * *************/CARDINALITY base change, it is vital for the follow-up selection of the execution plan /******************************* **************************************** **************************************** * ********************/bind variables and ranges because colx like 'a % 'can be equivalent to colx> = 'A' and colx <= 'B '; colx like: b1 seems to be equivalent to the between and structure, but the two The selection rate is different. The former is 5%, and the latter is 5% * 5%. In the Code attachment in this chapter: like_test. SQL constructs a table with 10 million rows, and the rows bound to the variable query is 5000, Which is exactly 5%. The queries with other values are different, but the lower (col) is used) like method, the selection rate also changes to 5%. It indicates that opt processes the predicate expression according to the variable and is a single variable. /*************************************** **************************************** **************************************** * *************/Selectivity = "required range"/"total available range"

 

The following uses several cases to verify the above formula and obtain the results of the previous list.

Case 1month_no> 8 (8,) selectimit = (high_value-limit)/(high_value-low_value) = (12-8)/(12-1) = 4/11 Cardinality = 1,200*4/11 = 436.363636 Case 2month_no> = 8 [8,) selectimit = (high_value-limit)/(high_value-low_value) + 1/num_distinct = 4/11 + 1/12 Cardinality = 1,200*(4/11 + 1/12) = 536.363636 Cases 3 and 4 Selectivity (3) = (limit-low_value)/(high_value-low_value) = (8-1)/(12-1) = 7/11 selecti.pdf (4) = (8-1)/(12-1) + 1/12 = 7/11 + 1/12 Cases 5 and 6month_no between 6 and 9 selecti.pdf = (9-6)/(12-1) + 1/12 + 1/12 -- (>=, <=) cases 7, 8, 9 selecti.pdf (7) = (9-6)/(12-1) + 1/12 -- (>=, <) selecti.pdf (8) = (9-6)/(12-1) + 1/12 -- (>,< =) selecti.pdf (9) = (9-6)/(12-1) -- (>, <) /*************************************** **************************************** **************************************** * **************/bind variable peeking (bind variable peeking)

 

Variable binding can share cursors, reducing OLTP by a large amount of hard parsing. However, this chapter also shows its drawbacks (resulting in rows computing errors, all of which are 5%). Therefore, 9i introduced the Bind Variable to peat the SQL statement bound to the variable. During the first execution, peat the variable value, give the correct rows, and select the optimal path, then extract the part that can be shared with the following statement, regardless of whether the bound variable changes or not. When the variable is of the string type, the execution plan changes when the string length is large. Specific changes need to be studied

/*************************************** **************************************** **************************************** * **************/Update the code attachment of this chapter in range 10104: ranges_10g. SQL condition 10102 10104 10204month_no between 6 and 9 527 527 527month_no between 14 and 17 100 82 100month_no between 18 and 21 100 45 100month_no between 24 and 27 100 1 100 as mentioned earlier, at 10104, when the boundary value is exceeded, a linear decay with a slope of "1/maximum-minimum" is used by default, but on 10204, the default value is changed back to the specific parameter control, to be studied /************************************* **************************************** **************************************** *****************/

 

3.5 Two Predicates (double Predicates)

wheremonth_no > 8or month_no <= 8

 

In this case, the calculated rows = 986 in the Code attachment to this chapter: ranges_02. SQL is similar to the preceding conditions, lists All rows from 1 to 12. rows 1, 9895, 9596, 9487, 9578, 9869, 1,200, and. The optimizer only judges the condition as two predicates connected by or.

selectivity(predicate1 AND predicate2) = selectivity(predicate1) * selectivity(predicate2)                                                                      selectivity(predicate1 OR  predicate2) = selectivity(predicate1) + selectivity(predicate2) - selectivity(predicate1 AND predicate2)selectivity(NOT predicate1) = 1 – selectivity(predicate1)

 

In the Code attachment of this chapter: two_predicate_01. SQL explains the calculation of the preceding two predicates bind_between. SQL, and analyzes the selection rate of the two predicates under the binding variable.

/*************************************** **************************************** **************************************** * *************/3.6 questions about multiple predicates calculate the selection rate wheremonth_no> 8 -- (predicate 1) or month_no <= 8 -- (predicate 2) selectivity (predicate1) referred to as s (p1), and so on s (p1) = (12-8)/(12-1) = 4/11 s (p2) = (8-1)/(12-1) + 1/12 = 7/11 + 1/12 s (p1 and p2) = 4/11*(7/11 + 1/12) s (p1 or p2) = s (p1) + s (p2)-s (p1 and p2) = 4/11 + 7/11 + 1/12-4/11*(7/11 + 1/12) = 0.82161200*0.8216 = 986 /********************************* **************************************** **************************************** *********************/

 

What will happen when there are three predicates? Month_no in (6, 7, 8) is similar to three predicates connected by or.

sel(A or B or C) = sel(A) + sel(B) + sel(C) – Sel(A)sel(B) – Sel(B)sel(C) – sel(C)sel(A) + Sel(A)Sel(B)Sel(C)

 

When we bring month_no in (276, 8), we will get the corresponding rows =. If different columns in a table have dependencies, how can we calculate the selection rate? For example, the month column + constellation column 9i dynamic sampling, 10 gfrofile