Hi, Madhavan, Thanks very much for your invaluable input.
Chuan -----Original Message----- Sent: Saturday, 24 August 2002 5:46 AM To: Multiple recipients of list ORACLE-L Hi Chuan, > Just wonder whether "analyze table <name> estimate statistics" > generates the stats at partition level. But for sure, it does not > generate the stats at table level. Please correct me if wrong. Yes it does here is a sample.... (sorry for the long post) SQL> analyze table atest delete statistics 2 / Table analyzed. SQL> select table_name,num_rows,to_char(last_analyzed,'DD-MON-YY-HH-MI-SS') 2 from user_tables 3 where table_name = 'ATEST' 4 / TABLE_NAME NUM_ROWS TO_CHAR(LAST_ANALY ------------------------------ ---------- ------------------ ATEST SQL> select table_name,partition_name,num_rows,to_char(last_analyzed,'DD-MON-YY-HH-MI-SS') 2 from user_tab_partitions 3 where table_name = 'ATEST' 4 / TABLE_NAME PARTITION_NAME NUM_ROWS ------------------------------ ------------------------------ ---------- TO_CHAR(LAST_ANALY ------------------ ATEST P1 ATEST P2 SQL> analyze table atest estimate statistics sample 10 percent 2 / Table analyzed. SQL> select table_name,num_rows,to_char(last_analyzed,'DD-MON-YY-HH-MI-SS') 2 from user_tables 3 where table_name = 'ATEST' 4 / TABLE_NAME NUM_ROWS TO_CHAR(LAST_ANALY ------------------------------ ---------- ------------------ ATEST 199 23-AUG-02-11-33-21 SQL> select table_name,partition_name,num_rows,to_char(last_analyzed,'DD-MON-YY-HH-MI-SS') 2 from user_tab_partitions 3 where table_name = 'ATEST' 4 / TABLE_NAME PARTITION_NAME NUM_ROWS ------------------------------ ------------------------------ ---------- TO_CHAR(LAST_ANALY ------------------ ATEST P1 99 23-AUG-02-11-33-21 ATEST P2 100 23-AUG-02-11-33-21 SQL> analyze table atest partition (p1) estimate statistics sample 10 percent 2 / Table analyzed. SQL> exec dbms_lock.sleep(10) PL/SQL procedure successfully completed. SQL> / Table analyzed. SQL> select table_name,partition_name,num_rows,to_char(last_analyzed,'DD-MON-YY-HH-MI-SS') 2 from user_tab_partitions 3 where table_name = 'ATEST' 4 / TABLE_NAME PARTITION_NAME NUM_ROWS ------------------------------ ------------------------------ ---------- TO_CHAR(LAST_ANALY ------------------ ATEST P1 99 23-AUG-02-11-33-31 ATEST P2 100 23-AUG-02-11-33-21 > As Connor said, dbms_stats runs a lot heavier than analyze, I think, > it's because it gathers the stats at table and partition level and > related indexes. analyze also generates statistics for at table, partition and indexes. If you specify analyze table <name> compute statistics (generates for all associated objects) If you use for table , for all indexes, then it does only table level and index level. > In brief, I prefer to choose "analyze .. estimate statistics" for > non-partition table and dbms_stats for partitioned table. dbms_stats in my opinion executes much better and the parallelism is a big help. Hope this helps. Regards, Madhavan http://www.dpapps.com -- Madhavan Amruthur DecisionPoint Applications -- http://fastmail.fm -- Does exactly what it says on the tin -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
