** histograms

  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 20 Nov 2006 18:35:45 -0000

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Wolfgang Breitling
>>
>>Is it advisable to use histograms just for some tables and some 
>>specific columns or is it OK to just set database wide?
>No.
>At least in my opinion. I have seen an unnecessary histogram - on a
column with perfectly uniform data distribution - destroy ( and I mean
destroy, by a factor of ~ 9000 ) the performance of a sql statement. See
also the recent post by Fuad Arshad and I quote: "it is generating
histograms which doesnt work well with the application".

It would be interesting to set conditions under which the statement
"more statistics mean a better plan" is true.
I would start with this:

-Columns data distribution is even
-Columns correlation is zero
-Clustering is zero meaning all rows perfectly mixed.

Then provided a) all permutations are scanned and b) floating point
calculations are used this may be true.

Now if we start relaxing the conditions:

- data distribution uneven but it still can be accurately described by
254(or so) frequency histogramms. This implies no more than 254 distinct
column values I believe.

Then I think all depends on bind variables. If all sql is literal then I
think the statement is still true. If not then it is a matter of luck I
think.

 


Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--
//www.freelists.org/webpage/oracle-l


Other related posts: