This discussion is archived
12 Replies Latest reply: Jan 24, 2013 12:17 AM by karan RSS

table analyze

Harsh_v Newbie
Currently Being Moderated
Hi folks..


Can you tell me we about analyse tables in oracle. please resolve my query...


1) why required table analyze.
2) how to do table analyze.
3) and after analyze what taking steps.
  • 1. Re: table analyze
    karan Pro
    Currently Being Moderated
    Use analyze command to only validate and chained rows, gather statistics by dbms_stats package, dbms_stats is the recommended method of collecting statisttics, with dbms_stats you can analyze external tables and analyze cannot. you should gather statistics like dbms_stats.gather_Table_stats('OWNER','table_name'); It woudl give optimizer the clear picture/state of your object so that it can produce a good/cheaper/fast execution plan.
  • 2. Re: table analyze
    karan Pro
    Currently Being Moderated
    Also dbms_stats would not the gather statistics for the number of chained rows, average free space, or number of unused data blocks for the use analyze command.
  • 3. Re: table analyze
    karan Pro
    Currently Being Moderated
    Get the complete details and this will answer your question from TOM see http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4347359891525
  • 4. Re: table analyze
    Harsh_v Newbie
    Currently Being Moderated
    thanks for good suggest.

    can you please give me simple example. if possible..
  • 5. Re: table analyze
    missymichi Newbie
    Currently Being Moderated
    ANALYZE TABLE FACULTY VALIDATE STRUCTURE;

    Edited by: missymichi on Jan 23, 2013 11:18 PM
  • 6. Re: table analyze
    karan Pro
    Currently Being Moderated
    For a corruption check

    The following statement analyzes the emp table:

    ANALYZE TABLE emp VALIDATE STRUCTURE;

    You can validate an object and all dependent objects (for example, indexes) by including the CASCADE option. The following statement validates the emp table and all associated indexes:

    ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;


    following statement inserts rows containing information about the chained rows in the emp_dept cluster into the CHAINED_ROWS table:

    ANALYZE CLUSTER emp_dept LIST CHAINED ROWS INTO CHAINED_ROWS;

    ____________________________________________________________________

    ANALYZE TABLE order_hist LIST CHAINED ROWS;

    SELECT * FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST';

    ____________________________________________________________________

    The following will gather statistics for scott's emp table and its dependent indexes as well

    exec dbms_stats.gather_table_stats('SCOTT','EMP',cascade=>true);

    Edited by: Karan on Jan 24, 2013 12:52 PM
  • 7. Re: table analyze
    Harsh_v Newbie
    Currently Being Moderated
    I am doing ..............


    SQL> exec dbms_stats.gather_table_stats('MCB','FTP_TAB',cascade=>true);

    PL/SQL procedure successfully completed.




    after that how to check it ?
  • 8. Re: table analyze
    KuljeetPalSingh Guru
    Currently Being Moderated
    select NUM_ROWS,SAMPLE_SIZE,last_analyzed from dba_tables where table_name='FTP_TAB';
  • 9. Re: table analyze
    karan Pro
    Currently Being Moderated
    check in ALL_TAB_STATISTICS and ALL_TAB_COL_STATISTICS views like for any column level details

    select NUM_DISTINCT,NUM_NULLS, last_analyzed, hitogram from ALL_TAB_COL_STATISTICS
  • 10. Re: table analyze
    Harsh_v Newbie
    Currently Being Moderated
    thanks for support Kuljeet Pal Singh and Karan.....

    can you please send some good link where i understand Analyze more deep.
  • 11. Re: table analyze
    Girish Sharma Guru
    Currently Being Moderated
    Harshit -{A> wrote:
    thanks for support Kuljeet Pal Singh and Karan.....

    can you please send some good link where i understand Analyze more deep.
    This link is as much deep as it requires by production DBAs :
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_4005.htm#SQLRF01105

    Regards
    Girish Sharma
  • 12. Re: table analyze
    karan Pro
    Currently Being Moderated
    Let oracle doc be your friend http://docs.oracle.com/cd/B28359_01/server.111/b28310/general002.htm

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points