Stats tool for planning

Section Navigation. Facebook Twitter LinkedIn Syndicate. Minus Related Pages. Get Email Updates. To receive email updates about this page, enter your email address: Email Address.

What's this? However, in practice many data sets are not fully normalized for various reasons; intentional denormalization for performance reasons is a common example. Even in a fully normalized database, there may be partial correlation between some columns, which can be expressed as partial functional dependency. The existence of functional dependencies directly affects the accuracy of estimates in certain queries. If a query contains conditions on both the independent and the dependent column s , the conditions on the dependent columns do not further reduce the result size; but without knowledge of the functional dependency, the query planner will assume that the conditions are independent, resulting in underestimating the result size.

Assessing the degree of dependency between all sets of columns would be prohibitively expensive, so data collection is limited to those groups of columns appearing together in a statistics object defined with the dependencies option. It is advisable to create dependencies statistics only for column groups that are strongly correlated, to avoid unnecessary overhead in both ANALYZE and later query planning.

Here it can be seen that column 1 zip code fully determines column 5 city so the coefficient is 1. When computing the selectivity for a query involving functionally dependent columns, the planner adjusts the per-condition selectivity estimates using the dependency coefficients so as not to produce an underestimate.

Functional dependencies are currently only applied when considering simple equality conditions that compare columns to constant values, and IN clauses with constant values. They are not used to improve estimates for equality conditions comparing two columns or comparing a column to an expression, nor for range clauses, LIKE or any other type of condition.

When estimating with functional dependencies, the planner assumes that conditions on the involved columns are compatible and hence redundant. If they are incompatible, the correct estimate would be zero rows, but that possibility is not considered.

For example, given a query like. However, it will make the same assumption about. Functional dependency statistics do not provide enough information to conclude that, however. In many practical situations, this assumption is usually satisfied; for example, there might be a GUI in the application that only allows selecting compatible city and ZIP code values to use in a query.

But if that's not the case, functional dependencies may not be a viable option. Single-column statistics store the number of distinct values in each column. Estimates of the number of distinct values when combining more than one column for example, for GROUP BY a, b are frequently wrong when the planner only has single-column statistical data, causing it to select bad plans.

As before, it's impractical to do this for every possible column grouping, so data is collected only for those groups of columns appearing together in a statistics object defined with the ndistinct option. Data will be collected for each possible combination of two or more columns from the set of listed columns. Continuing the previous example, the n-distinct counts in a table of ZIP codes might look like the following:.

This indicates that there are three combinations of columns that have distinct values: ZIP code and state; ZIP code and city; and ZIP code, city and state the fact that they are all equal is expected given that ZIP code alone is unique in this table.

On the other hand, the combination of city and state has only distinct values. Common statistical tools used in research and their uses. Embed Size px. Start on. Show related SlideShares at end. WordPress Shortcode. Share Email. Top clipped slide. Download Now Download Download to read offline.

Cheryl Asia Follow. Continuining education in singapore. Developing a winning team. Critical theory. Educational reforms on mismatch in teaching and skills. Emergence in mathematics. Z test asia. Role of participative management. Related Books Free with a 30 day trial from Scribd. Dry: A Memoir Augusten Burroughs. Related Audiobooks Free with a 30 day trial from Scribd. Empath Up! I don't have enough time write it by myself.

Genelyn Baluyos. Muhammad Atif. Nouroz Liaquat. James Adeolu , Student at University of Ibadan.