 Home
 Products
 Integration
 Tutorial
 Barcode FAQ
 Purchase
 Company
vb.net barcode component HISTOGRAMS in Java
CHAPTER 7 HISTOGRAMS Code 39 Extended Generator In Java Using Barcode drawer for Java Control to generate, create ANSI/AIM Code 39 image in Java applications. www.OnBarcode.comCode39 Reader In Java Using Barcode decoder for Java Control to read, scan read, scan image in Java applications. www.OnBarcode.comrow count) and not be noticed by Oracle as a popular value. Worse still, every very popular value takes up more than its fair share of buckets a small number of very popular rows could ensure that Oracle misses dozens of rows that you would like to classify as popular. There are times when you may have to fall back on creating an artificial frequency histogram, because that s the best you can do to describe your data set to Oracle. Data Matrix 2d Barcode Encoder In Java Using Barcode encoder for Java Control to generate, create Data Matrix image in Java applications. www.OnBarcode.comLinear 1D Barcode Printer In Java Using Barcode maker for Java Control to generate, create Linear 1D Barcode image in Java applications. www.OnBarcode.comThe Arithmetic
Code39 Drawer In Java Using Barcode maker for Java Control to generate, create Code39 image in Java applications. www.OnBarcode.comGS1 RSS Creation In Java Using Barcode creator for Java Control to generate, create DataBar image in Java applications. www.OnBarcode.comWhen the optimizer takes advantage of a height balanced histogram to work out cardinality, it adopts one of three major strategies. Strategy 1 for calculating selectivity/cardinality: For column = constant, the constant may be a popular value. A popular value is one that fills at least one bucket, and therefore results in a gap in user_tab_histograms view. If Oracle spots such a value, it does its arithmetic by buckets. For example, in the preceding, the value 77 appears at endpoint 69, and the previous value (76) appears at endpoint 67: consequently Oracle considers 77 to be a popular value that spans 2 buckets out of 75. The selectivity of 77 is therefore 2/75, and the cardinality is 3,240 * 2/75 = 86.4 and with autotrace we see Execution Plan (9.2.0.6  select count(*) from t1 where skew = 77) 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3) 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NONUNIQUE) (Cost=1 Card=86 Bytes=258) Strategy 2 for calculating selectivity/cardinality: We have already noted that 75 is not a special value, so what happens when we execute a query to select all rows for that value Oracle uses the density (not 1/num_distinct, note). We have the density recorded as 0.013885925, so we expect a cardinality of 3,240 * 0.013885925 = 44.99, and again the plan shows Execution Plan (9.2.0.6  select count(*) from t1 where skew = 75) 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3) 1 0 SORT (AGGREGATE) 2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NONUNIQUE) (Cost=1 Card=45 Bytes=135) Of course, you might ask at this point how Oracle works out a density it is a subject I don t really want to spend much time on, as it needs a fairly lengthy example to help me explain it and the added value is negligible. So I ll give you the short answer now, and save the long answer for Volume 2. In purely descriptive terms, the density is as follows: sum of the square of the frequency of the nonpopular values / (number of nonnull rows * number of nonpopular nonnull rows) By virtue of the way the columns have been constructed, this can be calculated from the big histogram query at the end of the Getting Started section as follows: ( sum(sumrepsq)  sum(maxrep(i) * maxrep(i) ) / ( sum(sumrep) * ( sum(sumrep)  sum(maxrep(i) ) ) Paint Code 3 Of 9 In Java Using Barcode generator for Java Control to generate, create Code 3/9 image in Java applications. www.OnBarcode.comPaint Universal Product Code Version E In Java Using Barcode creation for Java Control to generate, create UPC  E1 image in Java applications. www.OnBarcode.comCHAPTER 7 HISTOGRAMS
Code 3/9 Printer In None Using Barcode generation for Software Control to generate, create Code 39 Extended image in Software applications. www.OnBarcode.comGenerating ANSI/AIM Code 39 In None Using Barcode maker for Online Control to generate, create USS Code 39 image in Online applications. www.OnBarcode.comThe subscripts (i) are there to indicate that only certain rows are to be selected from the query results to contribute to their maxrep value. These rows are the one meeting the following condition: maxbkt > min(minbkt) + 1 or min(val) = max(val) In the special case where there are no popular values (or at least, none that have been detected by the query), this formula degenerates to the simpler form sum(sumrepsq) / (sum(sumrep) * (sum(sumrep)) This could be verbalized as: the sum of the squared frequencies over the square of the summed frequencies. If you analyze the formula carefully, you will realize that there is a potential trap that could give you problems if you happen to be unlucky with your data set. The idea of this density calculation is to factor out the impact of popular values. For example, if you have 2,000 rows of which 1,000 hold the same value, then a suitably adjusted density would be 1/2,000 rather than 1/1,001 because Oracle should be able to notice queries against the popular value and act accordingly. In principle, then, we want the formula for the adjusted density to reduce the density that would appear in the absence of a histogram. In practice, this works well for extreme cases like the one I have just described. However, if you have popular values in your data and Oracle does not notice them when building the histogram, the formula will produce an increased density. Yet again, you have to know your data, and know what you are expecting in order to be sure that the optimizer will be able to do the right job. Strategy 3 for calculating selectivity/cardinality: After that brief digression, we can investigate the issue of range scans. There are many possible variants on the theme, of course so we will stick with just two indicative cases. Since the data set I have been using is a fairly odd one, I have created another data set for these tests, to make sure that the results showed very clearly what Oracle is doing. The script is hist_sel.sql in the online code suite. The script generates 10,000 rows using a scaled normal distribution centered on the value 3,000 ranging from 5,000 to 11,000. Superimposed on this distribution, I have 500 rows each of the values 500, 1,000, 1,500, and so on up to 10,000 a list of 20 popular values. After analyzing this table with 250 buckets (80 rows per bucket), I get a histogram that shows the spikes from my 20 popular values. The number of distinct values is 5,626 and the column density is 0.000119361 (a convincing drop from 1 / 5,626 = 0.000177746). My first test case is a range scan across buckets with no popular values: n1 between 100 and 200. If we check the histogram, we find that the required values fall across three buckets with endpoints 17, 117, and 251. ENDPOINT_NUMBER ENDPOINT_VALUE  8 120 9 17 10 117 11 251 12 357 So we apply the standard formula bearing in mind that we have to break our required range across two separate buckets so that we examine ranges 100 117 and 117 200: Encoding Code39 In None Using Barcode creator for Office Word Control to generate, create ANSI/AIM Code 39 image in Office Word applications. www.OnBarcode.comReading UPC A In Visual C# Using Barcode recognizer for .NET framework Control to read, scan read, scan image in .NET framework applications. www.OnBarcode.comMake QR Code In None Using Barcode creation for Software Control to generate, create Quick Response Code image in Software applications. www.OnBarcode.comBarcode Creation In VS .NET Using Barcode creation for Reporting Service Control to generate, create Barcode image in Reporting Service applications. www.OnBarcode.comCreating PDF417 In None Using Barcode maker for Online Control to generate, create PDF 417 image in Online applications. www.OnBarcode.comPDF 417 Maker In Java Using Barcode encoder for Android Control to generate, create PDF 417 image in Android applications. www.OnBarcode.comPrint Code39 In .NET Framework Using Barcode creation for Visual Studio .NET Control to generate, create Code 3/9 image in VS .NET applications. www.OnBarcode.comCode 128C Creator In ObjectiveC Using Barcode maker for iPad Control to generate, create Code 128 image in iPad applications. www.OnBarcode.comGTIN  13 Reader In None Using Barcode decoder for Software Control to read, scan read, scan image in Software applications. www.OnBarcode.comGTIN  13 Maker In ObjectiveC Using Barcode printer for iPad Control to generate, create EAN13 image in iPad applications. www.OnBarcode.com 
