Re: Usefulness of histograms when lots of nulls

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<is8S8.20998$Hj3.65876_at_newsfeeds.bigpond.com>...
> Hi Thomas,
>
> A histogram would be of no benefit in your case as you have a perfect
> distribution of data. The nulls should have no effect as they aren't indexed
> anyway. Are you sure the column is indexed and the stats are accurate ?
>
> Post the statement and pkprof output and give someone a chance to help.
>
> Cheers
>
> Richard
> "Thomas Gaines" <Thomas.Gaines_at_noaa.gov> wrote in message
> news:3D18C83F.FACEC721_at_noaa.gov...
> > All -
> >
> > I'm having a devil of a time working through a long-running
> > query, and I'm at a loss about where I should go next. The
> > query in question was taken from a log of a third-party application
> > (bind variables were present in the original query, and I've
> > filled in realistic values for my testing), and so I have very
> > limited recourse here. As I see it, about all I can do is
> > build indexes, gather statistics, and set database parameters.
> > I can't even use my own hints!
> >
> > Perhaps I'll place the individual SQL and tkprof output
> > later, but right now I'm interested
> > in whether or not a histogram on a particular column will help
> > matters. This column is in a table of some 57,000 rows and
> > is of type number(38). About
> > 10% of the column's values are null. The remaining are unique
> > and range from 1 to about 51300 consecutively.
> >
> > I'd like to give the CBO as much information as I can, but I have
> > this feeling that the nulls in a particular column are simply ignored
> > when CBO does its work. But I can't find this noted anywhere.
> >
> > Q: Given what you know, do you think that presence of a histogram
> > would help the CBO in my case? If so, how many buckets would you
> > recommend?
> >
> > Thanks very much for your assistance,
> > Tom
> >

I agree that histograms would be of no value since the original query uses bind variables and histograms are only used with constants through at least version 8.1. With version 9 having the ability to peek at the bind variable value the first time a query with bind values is executed I am not sure if the information in histograms would be used or not in version 9.

If this is version 8.1 has any consideration been given to outlines? I keep planning to work with the feature but I have not, but I believe that you can capture the sql being ran in one outline table (OL$) and via the second outline table (OL$HINTS) provide hints to change the plan. See the outln_pkg.

HTH -- Mark D Powell -- Received on Wed Jun 26 2002 - 08:36:42 CDT