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: How many buckets

Re: How many buckets

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Wed, 21 Dec 2005 23:00:29 +0800
Message-ID: <43A96E0D.7DB4@yahoo.com>


Js wrote:
>
> Hello Experts,
>
> I need your comments on below topic.
>
> Oracle generating 2 buckets for each column if I am executing this
> command.
>
> SQL> analyze table t compute statistics;
>
> Table analyzed.
>
> SQL> select count(*),column_name from user_Tab_histograms where
> table_name = 'T' group by column_name;
>
> COUNT(*) COLUMN_NAME
> ---------- --------------------------------------------------
> 2 CREATED
> 2 DATA_OBJECT_ID
> 2 GENERATED
> 2 LAST_DDL_TIME
> 2 OBJECT_ID
> 2 OBJECT_NAME
> 2 OBJECT_TYPE
> 2 OWNER
> 2 SECONDARY
> 2 STATUS
> 2 SUBOBJECT_NAME
> 2 TEMPORARY
> 2 TIMESTAMP
>
> 13 rows selected.
>
> And If use this command ...
>
> SQL> analyze table t compute statistics for table for all columns;
>
> Table analyzed.
>
> SQL> select count(*),column_name from user_Tab_histograms where
> table_name = 'T' group by column_name;
>
> COUNT(*) COLUMN_NAME
> ---------- --------------------------------------------------
> 76 CREATED
> 75 DATA_OBJECT_ID
> 2 GENERATED
> 76 LAST_DDL_TIME
> 76 OBJECT_ID
> 76 OBJECT_NAME
> 24 OBJECT_TYPE
> 11 OWNER
> 1 SECONDARY
> 2 STATUS
> 2 SUBOBJECT_NAME
> 2 TEMPORARY
> 76 TIMESTAMP
>
> I am confused .. No of buckets is dependent upon what ?
>
> Regards.

Oracle "without" histograms, stores the max and min value for a column, hence you get a histogram of size 2.

When you explicitly ask for histograms, you'll get different numbers depending on the default for analyze, the size of your sample, and the number of distinct values in the columns.

btw - you probably want to be looking at dbms_stats

hth

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------
Received on Wed Dec 21 2005 - 09:00:29 CST

Original text of this message

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