RE: 10g gather_table_stats + method_opt +histograms

Using "for all columns size 1" does not create histograms. But still you see 2 rows for each column in user_histograms. It creates one bucket for all the values and shows it in user_histograms. By looking at the endpoint_number you can see that there is only one bucket, endpoint_value shows the last column value in that bucket. One bucket means no histograms.

SQL> create table t as select * from all_objects;

Table created.

SQL> exec
dbms_stats.gather_table_stats(ownname=>null,tabname=>'T',method_opt=>'fo r all columns size 1');

PL/SQL procedure successfully completed.

SQL> select column_name,endpoint_number ,endpoint_value   2 from user_histograms
  3 where table_name='T' and column_name='OBJECT_ID';

COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ --------------- --------------
OBJECT_ID                                    0            222
OBJECT_ID                                    1         121492

SQL> select min(object_id) from t;

MIN(OBJECT_ID)

           194

SQL> select max(object_id) from t;

MAX(OBJECT_ID)

        121645

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Fedock, John (KAM.RHQ)
Sent: Tuesday, February 21, 2006 11:26 PM To: oracle-l_at_freelists.org
Subject: 10g gather_table_stats + method_opt +histograms

Looking at Cost Based Oracle Fundamentals by Johnathan Lewis, page 454. It discusses how by default in 10g, gather_table_stats will get histograms on all tables.

I verified that was true by looking in dba_tab_histograms.

I also read in MetaLink, that by setting "method_opt => 'FOR ALL COLUMNS SIZE 1", histograms will NOT be created.

While testing, I see that running as I have below, the histograms are still being created. Am I missing something or am I misinformed?

System is Oracle 10.1.0.3, on HP-UX.

exec DBMS_STATS.DELETE_TABLE_STATS('rf','state_table');

select * from dba_tab_histograms where table_name = 'STATE_TABLE'
< no rows returned - as expected >

exec dbms_stats.gather_table_stats ('rf','state_table', method_opt => 'FOR ALL COLUMNS SIZE 1',CASCADE => TRUE); select * from dba_tab_histograms where table_name = 'STATE_TABLE'
< rows returned - NOT as I expected >

Thanks for any advice
John Fedock
john.fedock [AT] us.kline.com

--
http://www.freelists.org/webpage/oracle-l





Bu mesaj ve onunla iletilen tum ekler gonderildigi kisi ya da kuruma ozel ve Bankalar Kanunu geregince, gizlilik yukumlulugu tasiyor olabilir. Bu mesaj, hicbir sekilde, herhangi bir amac icin cogaltilamaz, yayinlanamaz ve para karsiligi satilamaz; mesajin yetkili alicisi veya alicisina iletmekten sorumlu kisi degilseniz, mesaj icerigini ya da eklerini kopyalamayiniz, yayinlamayiniz, baska kisilere yonlendirmeyiniz ve mesaji gonderen kisiyi derhal uyararak bu mesaji siliniz. Bu mesajin iceriginde ya da eklerinde yer alan bilgilerin dogrulugu, butunlugu ve guncelligi Bankamiz tarafindan garanti edilmemektedir ve bilinen viruslere karsi kontrolleri yapilmis olarak yollanan mesajin sisteminizde yaratabilecegi zararlardan Bankamiz sorumlu tutulamaz.

This message and the files attached to it are under the privacy liability in accordance with the Banking Law and confidential to the use of the individual or entity to whom they are addressed. This message cannot be copied, disclosed or sold monetary consideration for any purpose. If you are not the intended recipient of this message, you should not copy, distribute, disclose or forward the information that exists in the content and in the attachments of this message; please notify the sender immediately and delete all copies of this message. Our Bank does not warrant the accuracy, integrity and currency of the information transmitted with this message. This message has been detected for all known computer viruses thence our Bank is not liable for the occurrence of any system corruption caused by this message
--
http://www.freelists.org/webpage/oracle-l