 Home
 Products
 Integration
 Tutorial
 Barcode FAQ
 Purchase
 Company
vb.net barcode component SINGLE TABLE SELECTIVITY in Java
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.comCode39 Recognizer In Java Using Barcode reader for Java Control to read, scan read, scan image in Java applications. www.OnBarcode.com15 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.comEncode Data Matrix 2d Barcode In Java Using Barcode generator for Java Control to generate, create Data Matrix ECC200 image in Java applications. www.OnBarcode.comSTRANGE 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.comPrint Data Matrix ECC200 In Java Using Barcode creation for Java Control to generate, create DataMatrix image in Java applications. www.OnBarcode.comYou 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.comUCC  14 Drawer In Java Using Barcode drawer for Java Control to generate, create ITF14 image in Java applications. www.OnBarcode.comCHAPTER 3 SINGLE TABLE SELECTIVITY
Reading Code39 In Visual C# Using Barcode scanner for .NET framework Control to read, scan read, scan image in .NET applications. www.OnBarcode.comCode39 Encoder In None Using Barcode generation for Online Control to generate, create Code 3 of 9 image in Online applications. www.OnBarcode.comProblems with Multiple Predicates
UPCA 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.comBarcode Generation In None Using Barcode printer for Word Control to generate, create Barcode image in Word applications. www.OnBarcode.comI 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.comDrawing 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 36. 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.comDataMatrix Generator In C#.NET Using Barcode generation for VS .NET Control to generate, create Data Matrix image in .NET applications. www.OnBarcode.comGenerate Barcode In .NET Framework Using Barcode creator for Reporting Service Control to generate, create Barcode image in Reporting Service applications. www.OnBarcode.comEAN13 Generation In None Using Barcode generation for Font Control to generate, create GS1  13 image in Font applications. www.OnBarcode.comCode 3/9 Encoder In None Using Barcode printer for Microsoft Word Control to generate, create Code 39 image in Microsoft Word applications. www.OnBarcode.comData Matrix Creator In None Using Barcode drawer for Online Control to generate, create ECC200 image in Online applications. www.OnBarcode.com 
