# vb.net barcode component SINGLE TABLE SELECTIVITY in Java Paint ANSI/AIM Code 39 in Java SINGLE TABLE SELECTIVITY

CHAPTER 3 SINGLE TABLE SELECTIVITY
ANSI/AIM Code 39 Creation In Java
Using Barcode maker for Java Control to generate, create ANSI/AIM Code 39 image in Java applications.
www.OnBarcode.com
Code-39 Recognizer In Java
Using Barcode reader for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
15 countries, so only 1 in 15 of that group is likely to come from Italy. Since 100 / 15 = 6.5, I expect 6 or 7 people to raise their hands. In SQL terms, I have predicate1 as month_no = 12 (selectivity = 1/12) and predicate2 as eu_country = 'Italy' (selectivity = 1/15). The formula says that the combined selectivity is (1 / 12) * (1 / 15). The formula is simply stating algebraically the arithmetic we ve just worked out intuitively for our audience. What about the or case Let s ask for people born in December or born in Italy to raise their hands. Of these, 1 in 12 were born in December (100), and 1 in 15 were born in Italy (80), so a first approximation says that 180 hands go up. But if we stop there, we are going to count some people twice the people who were born in December in Italy so we have to subtract them from our total. That s easy because we ve just worked out that the fraction of the audience born in December in Italy is (1 / 12) * (1 / 15). So the fraction of the audience we want is (1 / 12) + (1 / 15) (1 / 12) * (1 / 15): just as the formula says. Finally, how about the people who were not born in December Since 1/12 of the audience was born in December, it s obvious that 11/12 of the audience was not born in December. And again, our formula is simply stating the normal mental arithmetic: fraction not born in December = 1 fraction born in December. Those of you who are familiar with probability theory will have recognized the three formulae as the standard formulae for calculating the combined probabilities of independent events: Probability(A AND B occur) = Probability(A occurs) * Probability(B occurs) Probability(A OR B occurs) = Probability(A occurs) + Probability(B occurs) Probability (A AND B occur) Probability(NOT(A occurs)) = 1 Probability(A occurs) This equivalence is not really surprising. The probability of an event occurring is (loosely) the fraction of times it has occurred in previous tests; the selectivity of a predicate is (loosely) the fraction of rows in the table that match the predicate.
Make GS1 - 12 In Java
Using Barcode generation for Java Control to generate, create UPC Symbol image in Java applications.
www.OnBarcode.com
Encode Data Matrix 2d Barcode In Java
Using Barcode generator for Java Control to generate, create Data Matrix ECC200 image in Java applications.
www.OnBarcode.com
STRANGE BIND SELECTIVITIES
European Article Number 13 Drawer In Java
Using Barcode maker for Java Control to generate, create UPC - 13 image in Java applications.
www.OnBarcode.com
Print Data Matrix ECC200 In Java
Using Barcode creation for Java Control to generate, create DataMatrix image in Java applications.
www.OnBarcode.com
You may have wondered why the selectivity of month_no between :b1 and :b2 is fixed at 0.0025. It s because the optimizer treats it as two predicates with an AND. The selectivity invented for month_no > :b1 is fixed at 0.05, the selectivity invented for month_no < :b2 is the same; so the selectivity of both predicates being true is: 0.05 * 0.05 = 0.0025 (see script bind_between.sql in the online code suite). The selectivity of not (column > :b1) is a special case as well: it is 0.05, not 0.95. The optimizer respects the intention that any test of an unbounded range scan on a column should return 5% of the data. This problem gets even stranger when you use not(month_no between :b1 and :b2). The value that the optimizer uses is 9.75% because the predicate is equivalent to (month_no < :b1 or month_no > :b2) which should be 5% plus 5%, except the optimizer subtracts 0.25%, emulating the subtract the overlap strategy of a more general OR clause.
Code 128B Printer In Java
Using Barcode creation for Java Control to generate, create USS Code 128 image in Java applications.
www.OnBarcode.com
UCC - 14 Drawer In Java
Using Barcode drawer for Java Control to generate, create ITF14 image in Java applications.
www.OnBarcode.com
CHAPTER 3 SINGLE TABLE SELECTIVITY
Reading Code-39 In Visual C#
Using Barcode scanner for .NET framework Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
Code39 Encoder In None
Using Barcode generation for Online Control to generate, create Code 3 of 9 image in Online applications.
www.OnBarcode.com
Problems with Multiple Predicates
UPC-A Supplement 5 Decoder In .NET Framework
Using Barcode decoder for Visual Studio .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
Barcode Generation In None
Using Barcode printer for Word Control to generate, create Barcode image in Word applications.
www.OnBarcode.com
I d like to finish this chapter by doing two things with the general formulae for combining predicates. First I d like to walk through the arithmetic that lets the optimizer calculate a cardinality of 986 on a clause that clearly covers exactly 100% of the rows in our 1,200 row table. Then I d like to show you why the formulae that the optimizer uses are guaranteed to produce the wrong figures in other, more realistic, cases. Let s dissect the where clause: where or month_no > 8 month_no <= 8 -- (predicate 1) -- (predicate 2)
Scan Barcode In None
Using Barcode recognizer for Software Control to read, scan read, scan image in Software applications.
www.OnBarcode.com
Drawing Barcode In Java
Using Barcode generator for Android Control to generate, create Barcode image in Android applications.
www.OnBarcode.com
From our single selectivity formula, (required range / total range), we calculate the selectivity for predicate1, month_no > 8, as (12 8) / (12 1) = 4 / 11 = 0.363636. Similarly, the selectivity for predicate2, month_no <= 8, is (8 1) / (12 1) + 1/12 = 7/11 + 1/12 = 0.719696. Our formula for selectivity(P1 OR P2) is selectivity(P1) + selectivity(P2) selectivity(P1 AND P2), so the combined selectivity is 0.363636 + 0.719696 (0.363636 * 0.719696) = 0.8216. Multiply this selectivity by the 1,200 rows in the table, round up, and you get 986, exactly as we saw in Table 3-6. It s clearly the wrong answer for reasons that are intuitively obvious to the human mind. But the machine is not human it follows the code, it doesn t understand the situation. In fact, this error is just a special case of a more general problem, which I will introduce by going back one last time to my audience of 1,200 people. Assume everyone in the audience knows which star sign they were born under (I know that astrology is not a topic that should appear in a book that s trying to be scientific, but it does provide a convenient demonstration). If I ask all the people born under Aries to raise their hands, I expect to see 100 hands there are 12 star signs, and we assume uniform distribution of data selectivity is 1/12, cardinality is 1,200/12 = 100. If I ask all the people born in December to raise their hands, I expect to see 100 hands there are 12 months, and we assume uniform distribution of data selectivity is 1/12, cardinality is 1,200/12 = 100. How many people will raise their hands if I ask for all the people born under Aries and in December to raise their hands What about all the people born under Aries in March What about all the people born under Aries in April According to Oracle, the answer will be the same for all three questions: Selectivity (month AND star sign) = selectivity (month) * selectivity (star sign) = 1/12 * 1/12 = 1/144 Cardinality = 1,200 * 1/144 = 8.5 (rounded to 8 or 9 depending on version of Oracle) But the star sign Aries extends from 21 March to 19 April so there can t be any people born in December under Aries; about 35 of the 100 people born in March will be under Aries, and about 65 of the 100 people born in April will be under Aries. Star signs and calendar dates
GS1 - 13 Creation In Visual Studio .NET
Using Barcode generation for VS .NET Control to generate, create GS1 - 13 image in VS .NET applications.
www.OnBarcode.com
DataMatrix Generator In C#.NET
Using Barcode generation for VS .NET Control to generate, create Data Matrix image in .NET applications.
www.OnBarcode.com
Generate Barcode In .NET Framework
Using Barcode creator for Reporting Service Control to generate, create Barcode image in Reporting Service applications.
www.OnBarcode.com
EAN-13 Generation In None
Using Barcode generation for Font Control to generate, create GS1 - 13 image in Font applications.
www.OnBarcode.com
Code 3/9 Encoder In None
Using Barcode printer for Microsoft Word Control to generate, create Code 39 image in Microsoft Word applications.
www.OnBarcode.com
Data Matrix Creator In None
Using Barcode drawer for Online Control to generate, create ECC200 image in Online applications.
www.OnBarcode.com
Copyright © OnBarcode.com . All rights reserved.