2

I just created a new table on 11gR2 and loaded it with data with no index. After the loading was completed, I created several indexes on the new table including primary constraint. 

CREATE TABLE xxxx (col1 varchar2(20), ...., coln varhcar2(10));
INSERT INTO xxxx SELECT * FROM another_table;
ALTER TABLE xxxx ADD CONSTRAINT xxxc PRIMARY KEY(col_list);
CREATE INDEX xxxx_idx1 ON xxxx (col3,col4);

AT this point do I still need to use DBMS_STATS.GATHER_TABLE_STATS(v_owner,'XXXX') to gather table stats? If yes, why? since Oracle says in docs "Oracle Database now automatically collects statistics during index creation and rebuild".

I don't want to wait for automatic stats gathering over night because I need to report the actual size of the table and its index immediately after the above operations. I think running DBMS_STATS.GATHER_TABLE_STATS may give me a more accurate usage data. I could be wrong though.

Thanks in advance,

Shawn
  • 5,130
  • 13
  • 66
  • 109

1 Answers1

4

In Oracle 11gR2 you still need to gather table statistics. I guess you read documentation for Oracle 12c, which automatically collects the statistics but only for direct path inserts, which is not your case, your insert is conventional. Also if you gather statistics (with default options) for brand new table that hasn't been used for queries no histograms will be generated.

Index statistics are gathered when index is built so it's not needed to gather its statistics explicitly. When you later gather table statistics you should use the DBMS_STATS.GATHER_TABLE_STATS option cascade => false so that index statistics aren't gathered twice.

You can simply check the statistics using

SELECT * FROM ALL_TAB_COL_STATISTICS WHERE TABLE_NAME = 'XXXX';
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
Husqvik
  • 5,669
  • 1
  • 19
  • 29
  • Hi, Husqvik, thanks for the quick reply. If I don't run table stats can I get accurate space usage size? – Shawn Sep 20 '15 at 22:22
  • Sure, just check this thread - http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle – Husqvik Sep 20 '15 at 22:25
  • Got it. without running DBMS_STATS.GATHER_TABLE_STATS, ALL_TAB_COL_STATISTICS is empty. Thanks again. – Shawn Sep 20 '15 at 23:09