Using Histogram

Using Histograms

Histograms record the distribution of data when you analyze a table or index. With this information in hand, the cost-based optimizer can decide to use an index for conditions it knows will return a small number of rows and bypass the index when the condition will return many rows based on the limiting condition. The use of histograms is not limited to indexes. Any column of a table can have a histogram built on it.

When creating histograms, specify a size. This size relates to the number of buckets for the histogram. Each bucket will contain information about the value of the column(s) and the number of rows.


EXECUTE DBMS_STATS.GATHER_TABLE_STATS
('scott','company', METHOD_OPT => 'FOR COLUMNS SIZE 10 company_code');
PL/SQL procedure successfully completed.

The preceding query creates a ten-bucket histogram on the COMPANY table, as shown in figure. The values for the COMPANY_CODE column are divided into the ten buckets as displayed in the figure. This example shows a large number (80 percent) of the COMPANY_CODE is equal to 1430. As is also shown in the figure, most of the width-balanced buckets contain only 3 rows; a single bucket contains 73 rows. In the height-balanced version of this distribution, each bucket has the same number of rows and most of the bucket endpoints are 1430, reflecting the skewed distribution of the data.

Image from book
Advertisements

About qainterviews

Software Geek,QA Expert,Blogger
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s