Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How many buckets
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