Re: query performance -- another try

On 3 Jun 2003 11:57:57 -0700, dfult_at_econs.umass.edu (Doug) wrote:

>I did the following to interrogate the presence of a histogram on
>Country (from the Tuning doc):
>
> 1 select ENDPOINT_NUMBER, ENDPOINT_VALUE FROM DBA_HISTOGRAMS
> 2* WHERE TABLE_NAME = 'ADDRESS' AND COLUMN_NAME = 'COUNTRY'
>SQL> /
>
>ENDPOINT_NUMBER ENDPOINT_VALUE
>--------------- --------------
> 0 3.3917E+35
> 1 6.0984E+35
>
>It doesn't look like a meaningful histogram exists, here (even to my
>untrained eye). I tried the same thing with postalCode and got:
>
>ENDPOINT_NUMBER ENDPOINT_VALUE
>--------------- --------------
> 0 2.3469E+35
> 1 6.5525E+35
>
>This also looks unhelpful. Perhaps I am doing the wrong thing to
>verify the histograms.
>
>FYI: I created statistics with the following: analyze table Address
>compute statistics;

analyze table compute calculates the default histogram only. The default histogram consists of 2 end-points. You need to analyze compute statistics for table for address for country etc.
You should also rather use dbms_stats.

As for not having a composite index on country and postalcode: this is the well known usual US shortsigthedness.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Jun 03 2003 - 15:56:02 CDT