Creating and validating a histogram

From: <tdry_at_yet2.com>
Date: Wed, 05 Jan 2000 19:03:25 GMT
Message-ID: <8504hi$aei$1@nnrp1.deja.com>

I have a column that is holding true/false type values and 99% of the values are true and yet we are regularly looking for those which are false. Of course, the answer is to create a histogram which will let the cost based optimizer intelligently do the correct thing. My problem is that the execution plan is not changing after I analyze the column. I'm also not sure how to interpret the contents of user_histograms and user_tab_columns to validate that the histogram really exists.

Here are the two different commands that I've used to generate the histograms:

      analyze index tablea_idx compute statistics
	for columns true_false size 2;

      analyze table tablea compute statistics
	for columns true_false size 2;

Both statements generate the same data in user_tab_columns with a "num_buckets" value of 1. Below are the values in user_histograms after running the two analyze commands respectively:

table		column		endpoint number
TABLEA          TRUE_FALSE      0
TABLEA          TRUE_FALSE      1

TABLEA          TRUE_FALSE      15
TABLEA          TRUE_FALSE      1371

The database version is 7.3.4. Any thoughts?

-Tim

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 05 2000 - 13:03:25 CST