Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Usefulness of histograms when lots of nulls

Re: Usefulness of histograms when lots of nulls

From: Thomas Gaines <Thomas.Gaines_at_noaa.gov>
Date: Wed, 26 Jun 2002 09:53:47 -0600
Message-ID: <3D19E38B.C1DA072A@noaa.gov>


Mark and Richard and Vladimir -

Thanks very much for the words of encouragement and advice. Late yesterday, I was successful in getting the canned third-party query to return its results in about 6 seconds as opposed to the 6 minutes it was taking before. My change was to an underlying view that was produced locally before I came on the scene. A bit of old-fashioned "driving table" thought to eliminate as many rows as possible did the trick.

I'll always have my statistics up to date, and I'll keep your words at the ready when they're needed.

Thanks,
Tom

Mark D Powell wrote:

> "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 - 10:53:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US