The first 3 Chapter Single table selection rate (Single Table Selectivity)

Selectivity It's the optimizer estimate Rows(Cards) An important basis for .

/**************************************************************************************************************************************/

3.1 Getting Started

select count(*)

from audience

where month_no = 12

;

From the optimizer's point of view , analysis 1200 In the audience , Estimate 12 The number of people born in a month

(1)user_tab_col_statistics.num_distinct=12

(2)user_tab_histograms Pointed out that low(1),high(12), Uniform distribution

(3)user_tab_col_statistics.density=1/12

(4)month_no=12, Single column , uniform , therefore user_tab_col_statistics.density You can use

(5)low(1)<=12<=high(12)

(6)user_tab_col_statistics.num_nulls=0, There is no null value

(7)user_tables.num_rows=1200

(8)1200/12=100

In the code attachment of this chapter :

birth_month_01.sql

hack_stats.sql

birth_month_01.sql Build table , First, query the system table twice , Do later count(*) Check twice .

Between the two can be in other session perform hack_stats.sql, Modify table statistics , See which statistics can affect rows The calculation of

(1) Fill in the table and column names hack_stats.sql, And change the number of rows in the table numrows

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

After cross execution ,rows from 100 become 200
After the test will numrows Restore

(2) modify distcnt and density

open Column statistics Notes

--m_distcnt:=m_distcnt+12;
m_density:=m_density/2;

After many tests ,distcnt The changes to the rules don't work , The explanation is only density Participate in calculation (Oracle edition 10204).
Not as the book suggests , Probably 9i and 10gR1 in , No histogram is used distcnt To calculate rows,Oracle Later versions improved

After the test will density Restore

/**************************************************************************************************************************************/

begin
dbms_stats.gather_table_stats(
user,
'audience',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/

among method_opt => 'for all columns size 1', Do not collect histograms ,8i and 9i The default value of

method_opt =>'for all columns size auto'10g The default value is , You can read it in the following way

SQL> select dbms_stats.get_param('METHOD_OPT') from dual;
DBMS_STATS.GET_PARAM('METHOD_OPT')
---------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO Have chosen 1 That's ok .

/**************************************************************************************************************************************/

3.2 Null Values

take null Add to rows In the calculation of

Assume 10% Most people don't remember their birthday in a few months

In the code attachment of this chapter :

birth_month_02.sql

In this script , take 120 Line set to null , Final rows=90

Explain that the optimizer excludes null To estimate rows Influence ;density Is still 1/12, It hasn't changed , explain density Is the result of subtracting the number of null values .

/**************************************************************************************************************************************/

3.3 Using Lists

select count(*)

from audience

where month_no in (6,7,8)

;

Start studying month_no in (6,7,8), In this condition ,rows Calculation method of

In the code attachment of this chapter :

in_list.sql

When there are no duplicate values ,rows The calculation is correct

/**************************************************************************************************************************************/

8i The reason for the calculation error in :

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

Change the month from 12 Expand to 1000,density by 0.001

Get list 3-1, To compare 8i To 10g Calculated value , It doesn't make much difference between versions

/**************************************************************************************************************************************/

In the code attachment of this chapter :

oddities.sql

There are duplicate values in the database 、 Null value 、 When the boundary value is exceeded , Calculation rows The state of ,10204 Obviously improved , The maximum and minimum values are considered ( The original version of the book has not considered the problem of boundary value )

Conditions 8i 92/10102 10204
month_no = 25  100 100 1
month_no in (4, 4) 100 100 100
month_no in (3, 25)  192 200 100
month_no in (3, 25, 26)  276 300 101
month_no in (3, 25, 25, 26)  276 300 101
month_no in (3, 25, null)  276 300 200
month_no in (:b1, :b2, :b3)  276 300 300

Only when there are null values , Can't calculate correctly rows, But it's also smaller than the previous version .

/**************************************************************************************************************************************/

10104 The linear decay of

oracle stay 10.2 A more complex linear decay algorithm has been used before , That is, when the maximum value is exceeded or below the minimum value , Single value rows The slope of is "1/ Maximum - minimum value ".

Pass above 10204 Test of , This algorithm is replaced in the new version .

In the code attachment of this chapter :

in_list_10g.sql

stay 10104 Results in :

Conditions 10102 10104
month_no = 13  100 91
month_no = 15  100 73
month_no in (13,15) 200 164
month_no in (16,18) 200 109

10104 Than 10102 Slightly improved ,10204 Than 10104 The improvement is even more obvious , Although there are still flaws (null On ), But it's not clear oracle Of opt The team is constantly updating the code .

/**************************************************************************************************************************************/

3.4 Range

In the code attachment of this chapter :

ranges.sql

Summarize the versions , Statistics are as follows :

Number The predicate 8i 92/10102 10204 Mathematical expression True value
1 month_no > 8  437 436 436 (8, 400
2 month_no >= 8  537 536 536 [8, 500
3 month_no < 8  764 764 764  ,8) 700
4 month_no <= 8  864 864 864  ,8] 800
5 month_no between 6 and 9  528 527 527 [6,9] 400
6 month_no >= 6 and month_no <= 9  528 527 527 [6,9] 400
7 month_no >= 6 and month_no < 9  428 427 427 [6,9) 300
8 month_no > 6 and month_no <= 9  428 427 427 (6,9] 300
9 month_no > 6 and month_no < 9  328 327 327 (6,9) 200
10 month_no > :b1  60 60 101 (: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, 0
20 month_no between 25 and 30  100 100 100 [25,30] 0

among 8i Of 4 and 9i Of 3, There is no difference (10 There is a decimal in the conversion between decimal and binary ), It's just 8i Use float up in the formula ,9i Rounding with

Several rules found from the above table :

(1)"(" and "]" The gap is 100

(2) Beyond the boundary value is 100

(3) With bound variables rows, There is no effect on the opening and closing interval

(4)10-13 That's ok , Of a single bound variable range, The previous version is 5%(1200*5%=60),10204 And then it's more complicated ( To be studied )

(5)14-18 That's ok ,5%*5%=2.5/1000,1200*5%*5%=3 That's ok

(6)19-20 That's ok , When the maximum and minimum values are exceeded , Give a fixed selection rate . Code of this chapter selectivity_one.sql The link is given 9i Test code .

In the code attachment of this chapter :

selectivity_one.sql

Construct a 4 Column nologging surface ,3000 That's ok ; contrast 4 Two boundary valued queries and ">" Inquire about ,rows It's the same .

Explain the selection rate given by queries that exceed the boundary value , The selection rate is the same as that of the boundary value query .

/**************************************************************************************************************************************/

CARDINALITY

The change in base number , It's crucial for the subsequent selection of the implementation plan

/**************************************************************************************************************************************/

Bind variables and intervals

because colx like 'A%' It can be equivalent to colx >='A' and colx <='B';colx like :b1 It seems to be equivalent to between and Structure ; But their choice rates are different , The former is 5% The latter is 5%*5%.

In the code attachment of this chapter :

like_test.sql

Construct a 10W Table of rows , Bound variable query rows by 5000, Is precisely 5%; Other constant value queries are different ; But use lower(col) like The way , The selection rate also becomes 5%.

explain opt When dealing with predicate expressions , It's handled in terms of variables , And it's a single variable .

/**************************************************************************************************************************************/

Selectivity = “required range” / “total available range”

Let's go through a few Case, To verify the above formula , And get the results of the previous list .

Case 1

month_no > 8

(8,)

Selectivity = (high_value – limit) / (high_value – low_value) = (12 – 8) / (12 – 1) = 4/11

Cardinality = 1,200 * 4 / 11 = 436.363636

Case 2

month_no >= 8

[8,)

Selectivity = (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

Selectivity (4) = (8 – 1) / (12 – 1) + 1/12 = 7/11 + 1/12

Cases 5 and 6

month_no between 6 and 9

Selectivity = (9 – 6) / (12 – 1) + 1/12 + 1/12             --( >= , <= )

Cases 7, 8, 9

Selectivity (7) = (9 – 6) / (12 – 1) + 1/12

--( >= , < )

Selectivity (8) = (9 – 6) / (12 – 1) + 1/12

--( > , <= )

Selectivity (9) = (9 – 6) / (12 – 1)                               --( > , <)

/**************************************************************************************************************************************/

BIND VARIABLE PEEKING( Bind variable peek )

Bound variables can share cursors , send OLTP Reduced a lot of hard parsing ; But there is also a drawback in this chapter ( cause rows The calculation is wrong , All 5%)

therefore ,9i Introduced bound variable peeping

In the bound variable SQL sentence , On first execution , Look at the value of the variable , Give the right rows, And choose the best path , Then extract the parts that can be shared with the following statements , Whether the bound variable changes or not , Will use the share .

When the variable is of string type , When the length of the string changes greatly , The implementation plan will change .

How to change remains to be studied

/**************************************************************************************************************************************/

range On 10104 Update

In the code attachment of this chapter :

ranges_10g.sql

Conditions 10102 10104 10204
month_no between 6 and 9  527 527 527
month_no between 14 and 17 100 82 100
month_no between 18 and 21 100 45 100
month_no between 24 and 27 100 1 100

As mentioned earlier , stay 10104, When the boundary value is exceeded , By default, a slope of "1/ Maximum - minimum value " The linear decay of

But in 10204 I changed the default back again

Which parameter controls it , To be studied

/**************************************************************************************************************************************/

3.5 Two Predicates( Double predicate )

where

month_no > 8

or month_no <= 8

Now it's calculated rows=986

In the code attachment of this chapter :

ranges_02.sql

Similar to the above conditions , Lists 1-12 All of the rows

  rows 
1 1,108
2 1,110
3 1,040
4 989
5 959
6 948
7 957
8 986
9 1,035
10 1,103
11 1,192
12 1,200

The optimizer just judges the condition as : Two by or Connected predicates

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

Explain the operation of the above two predicates

bind_between.sql

Analysis of binding variables under , The choice rate of two predicates

/**************************************************************************************************************************************/

3.6 Problems related to multiple predicates

Calculate the selection rate below

where

month_no > 8
-- (predicate 1)

or month_no <= 8
-- (predicate 2)

selectivity(predicate1) abbreviation 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.8216

1200*0.8216=986

/**************************************************************************************************************************************/

Three predicates ?

month_no in (6,7,8) It's like three predicates are or Connect

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)

Into the month_no in (6,7,8) We'll get the corresponding rows=276

If in a table , When different columns have dependencies , How to calculate the selection rate ? For example, the month column + Constellation

9i Dynamic sampling 、10gfrofile ( To be studied )

CBO Study ----03-- Selection rate (Selectivity) More articles about

  1. JavaScript Study 03 JS function

    JavaScript Study 03 JS function Functions are blocks of code wrapped in curly braces , Key words used earlier function: function functionName() { Here's the code to execute } Function parameter Functional ...

  2. Java virtual machine JVM Study 03 Connection process : verification 、 Get ready 、 analysis

    Java virtual machine JVM Study 03 Connection process : verification . Get ready . analysis After the class is loaded , Just enter the connection phase . Connection is to merge the binary data of the class that has been read into the memory into the runtime environment of the virtual machine . There are three steps in the connection phase : verification . Preparation and analysis . class ...

  3. Java Study 03

    Java Study 03 1.java Interview some questions One . What is a variable Variables are data that can be changed during program execution . The variables in the class are used to represent the properties of the class , During programming , You can modify the value of a variable . Variables are usually variable , That is, the value changes Two . ...

  4. ThinkPhp Study 03

    original text :ThinkPhp Study 03 One .ThinkPHP 3 Output       ( a key ) a. adopt echo etc. PHP The native output mode outputs in the page b. adopt display Method output    If you want to assign variables, you can use as ...

  5. JVM Study 03: Performance monitoring tools

    JVM Study 03: Performance monitoring tools Write it at the front : The main resources shared in this series are   Teacher zhou zhiming's < In depth understanding of Java virtual machine > The second edition . Key knowledge points of performance monitoring tools Xmind carding case analysis case analysis 1-JPS Case points ...

  6. node.js Study 03

    node.js Study 03 Solve the problem of garbled code after the browser receives the server information : The server is set by http Response header , Tell the browser to use the appropriate encoding To parse the web page . res.setHeader('Content','text/ ...

  7. Redis Study 03—— Store string (String)

    ---------------------  author : Angry Xiao Ming   source :CSDN  original text :https://blog.csdn.net/qiwenmingshiwo/article/details/78118 ...

  8. Mybatis Study 03

    title: Mybatis Study 03 date: 2020-01-19 13:03:20 tags: Mybatis The second day of study , There are logs and pagination . <!--more--> 1. journal 1. ...

  9. CBO Learning notes ( turn )

    Query Transformation Continue to study SQL Other operations of ( such as Join) Yes CBO Before the impact of , Let's talk about it Oracle Optimizer's Query Transformation characteristic . We are all used to depending on ...

Random recommendation

  1. C#.NET Large enterprise information system integration rapid development platform 4.2 edition - Increase the security, increase the limitation that computers in the Intranet can only call

    Large business application systems are vulnerable to various attacks , With each layer of protection, we can reduce 80%-90% The attack of . There are also some limitations in our core interface components , Some interface methods can only be called by intranet users , Prevent users from calling the Internet , In this way, the security will be greatly improved ...

  2. 2013 Changsha network competition J topic

    Ideas : This problem is similar to other situations where all values can be exited , The only thing you can't be sure about is XXOXXOXXOXX The sequence of this form , among XX It means unknown ,O It means known . We make num[1]=0, that num[4]=sum[3]-sum[2] ...

  3. Java-struts2 adopt MODEL How to receive form data

    There are always problems receiving data : 1. remember action = “” The path to , It's best to use the full path <a href="../Struts/user/hello?user.name=xxzzzzzzzz ...

  4. linux innode The illustration 2

    http://www.opsers.org/linux-home/base/the-knowledge-that-one-day-learn-linux-file-system.html The file system is operating ...

  5. Winform The application program realizes the general mask layer 2

    It's been published before :<Winform The application implements a universal mask layer >.<Winform The application program implements the general message window >, These two mask layers are actually based on pop-up windows , Today I would like to share with you a relatively simple but relatively friendly interface ...

  6. Spring shiro Summary of first use

    Let's start with a paragraph about shiro Introduction to : In the development system , You can't do without authority , at present java The authority framework in is SpringSecurity and Shiro( Formerly called jsecurity), about SpringSecurity: Too much functionality ...

  7. SpringMvc Random code problem in parameter passing

    Problem description : When passing Chinese parameters to controller Class time , No chaos is get The way is still post There's a mess in the way solve : 1. Make sure that all page coding is utf-8, Include jsp page , Browser encoding settings and eclipse The encoding settings for ...

  8. Virtualbox [The headers for the current running kernel were not found] ( After the operation, it still fails , Show the same question )

    Install... On your laptop Ubuntu11.04 Enhancement failed quote fuliang@fuliang-VirtualBox:~$ sudo /etc/init.d/vboxadd setup Removing exi ...

  9. JQ + CSS It's necessary to realize romantic expression

    JQ + CSS Realize the necessary page of romantic expression design sketch : Picture material : The code is as follows , Copy and use : <!DOCTYPE html> <html> <head> <meta ...

  10. Luogu P2480 [SDOI2010] Ancient pig writing Answer key 【 Euler theorem 】【CRT】【Lucas Theorem 】

    Number theory synthesis problem . Background The background of the title has nothing to do with the title, so it is omitted . Topic link Title Description The civilization of pig Kingdom has a long history , broad and profound . iPig Look up the materials in the library of big pig school , It is known that the total number of pig characters in ancient times is \(N\). Of course , A language ...