This discussion is archived
10 Replies Latest reply: Jan 1, 2006 6:16 AM by The Human Fly RSS

"analyze index"  vs  "rebuild index"

373353 Newbie
Currently Being Moderated
Hi,
I don't undestand the difference between "analyze index" and "rebuild index".

I have a table where I do a lot of "insert" and "update" and "query", What is the best thing to do ??

thanks

Giordano
  • 1. Re: "analyze index"  vs  "rebuild index"
    The Human Fly Oracle ACE Director
    Currently Being Moderated
    well,

    rebuild index can be used to relocate the index from one tbs to another and also to change the storage parameters of index.
    Whenever you move table from one tbs to another tbs then you have to rebuild your indexed for that table as when you move tables associated indexes become invalid and you have to rebuild them.

    analyze indexes to get/delete the statistics (current) on the indexes, also, to check the structure validity.

    whenever you are getting new statistics, there is a chance of change in exection plan.

    Like wise, there are many situation where you have to do decide analyze or rebild.
  • 2. Re: "analyze index"  vs  "rebuild index"
    358102 Newbie
    Currently Being Moderated
    You may also want to rebuild an index when it is fragmented. You can analyze the index at the same time you are building it. Here is an example.

    alter index empname_idx rebuild compute statistics;
  • 3. Re: "analyze index"  vs  "rebuild index"
    17642 Newbie
    Currently Being Moderated
    Here's a link and a lengthy discussion on index rebuilds and the benefits (and lack of) doing so, by Howard Rogers, Richard Foote, et al

    http://groups.google.ca/groups?hl=en&lr=&ie=UTF-8&threadm=5Vtkc.5521%24TT.227%40news-server.bigpond.net.au&rnum=1&prev=/groups%3Fq%3Dg:thl3120676585d%26dq%3D%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D5Vtkc.5521%2524TT.227%2540news-server.bigpond.net.au

    Good place to start debunking some of the prevalent myths.
  • 4. Re: "analyze index"  vs  "rebuild index"
    226421 Newbie
    Currently Being Moderated
    Can we do this:

    alter index empname_idx rebuild estimate statistics sample 25 percent;

    Eamil: rshyu@scholle.com
  • 5. Re: "analyze index"  vs  "rebuild index"
    KuljeetPalSingh Guru
    Currently Being Moderated
    hi


    no,u can analyze the index with rebuild option with following syntax

    alter index <index_name> rebuild compute statistics;
    like
    alter index scott.PK_DEPT rebuild compute statistics;


    Thanks
    Kuljeet Pal Singh
  • 6. Re: "analyze index"  vs  "rebuild index"
    The Human Fly Oracle ACE Director
    Currently Being Moderated
    Collecting 100% stats (compute) on indexes is always recommended.

    Jaffar
  • 7. Re: "analyze index"  vs  "rebuild index"
    475167 Newbie
    Currently Being Moderated
    Collecting 100% stats (compute) on indexes is always recommended.
    Why? Any proofs? Did you ever work with a Database with tables containing billions of records and lots of indexes on it?

    By the way. Don't use analyze it's deprecated sice 8i. Use DBMS_STATS.GATHER_INDEX_STATS with DBMS_STATS.AUTO_SAMPLE_SIZE instead.
  • 8. Re: "analyze index"  vs  "rebuild index"
    The Human Fly Oracle ACE Director
    Currently Being Moderated
    Did you ever work with a Database with tables containing billions of records and lots of indexes on it?
    For your kind information, I do work with very large databases, our datawarehouse db size is around 1.7TB and one of the index size is 30+GB.
    Use DBMS_STATS.GATHER_INDEX_STATS with DBMS_STATS.AUTO_SAMPLE_SIZE instead.
    When you use dbms_stats.gather_schema_stats package with cascade=>true option, you are also collecting stats for the indexes, no need to collects stats separately using dbms_stats.gather_index_stats.
    DBMS_STATS.AUTO_SAMPLE_SIZE instead. .
    Auto_sample_size has many problems/bugs in 9i,
    ==
    When auto_sample_size is used to gather stats with dbms_stats,the SAMPLE_SIZE
    in dba_tables is almost equal to the NUM_ROWS.
    In 9iR2, one can sepcify any percentage and it will generate stats using that percentage. Prior to oracle 9i R2, however, if the percentage was 25% or greater, then DBMS_STATS would perform 100% compute. But now in Oracle 9iR2 it can be any percentange that one need to meet your requirements.
    == Search in metalink for information(for bugs about sample_size)
    Why? Any proofs?
    I had read it at many places, including metalink. I dont remember the note number. When I, I will definately let you know the URL.

    Jaffar
  • 9. Re: "analyze index"  vs  "rebuild index"
    475167 Newbie
    Currently Being Moderated
    When you use dbms_stats.gather_schema_stats package with cascade=>true option, you are also collecting stats for the indexes, no need to collects stats separately using dbms_stats.gather_index_stats.
    Of course, but I refered to the rebuild index question. Therefore I only mentioned the GATHER_INDEX_STATS.
    Auto_sample_size has many problems/bugs in 9i
    Ok didn't know that - I'm using 10gR2.

    But this discussion made me curious. So I tried something (10gR2):

    CREATE TABLE BIG NOLOGGING AS
    WITH GEN AS (
    SELECT ROWNUM ID FROM ALL_OBJECTS WHERE ROWNUM <=10000)
    SELECT V1.ID,RPAD('A',10) C FROM GEN V1,GEN V2
    WHERE ROWNUM <=10000000;

    SELECT COUNT(*) FROM BIG;

    COUNT(*)
    ----------
    10000000

    So I had a Table containing 10 Million rows. Now I indexed ID:

    CREATE INDEX BIG_IDX ON BIG(ID)

    I tested two different methods:

    1.) GATHER_TABLE_STATS with estimate 10%

    EXEC DBMS_STATS.GATHER_TABLE_STATS(TABNAME=>'BIG',OWNNAME=>'DIMITRI',CASCADE=>TRUE,ESTIMATE_PERCENT=>10);

    It took about 6 seconds (I only set timing on in sqlplus, no 10046 trace) Now I checked the estimated values:

    SELECT NUM_ROWS,SAMPLE_SIZE,ABS(10000000-NUM_ROWS)/100000 VARIANCE,'TABLE' OBJECT FROM USER_TABLES WHERE TABLE_NAME='BIG'
    UNION ALL
    SELECT NUM_ROWS,SAMPLE_SIZE,ABS(10000000-NUM_ROWS)/100000 VARIANCE,'INDEX' OBJECT FROM USER_INDEXES WHERE INDEX_NAME='BIG_IDX';

    NUM_ROWS SAMPLE_SIZE VARIANCE OBJEC
    ---------- ----------- ---------- -----
    9985220 998522 ,1478 TABLE
    9996210 999621 ,0379 INDEX

    2.) GATHER_TABLE_STATS with DBMS_STATS.AUTO_SAMPLE_SIZE

    EXEC DBMS_STATS.DELETE_TABLE_STATS(OWNNAME=>'DIMITRI',TABNAME=>'BIG');

    EXEC DBMS_STATS.GATHER_TABLE_STATS(TABNAME=>'BIG',OWNNAME=>'DIMITRI',CASCADE=>TRUE,ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE);

    It took about 1,5 seconds. Now the results:

    NUM_ROWS SAMPLE_SIZE VARIANCE OBJEC
    ---------- ----------- ---------- -----
    9826851 4715 1,73149 TABLE
    10262432 561326 2,62432 INDEX

    The estimate 10% was more exact - also a 1,7 and 2,6 percent variance is still ok. It's also very interesting, that using AUTO_SAMPLE_SIZE
    causes oracle to execute a estimate 5% for the index and a estimate 0.5 for the table.
    I tried again with a table containing only 1 Million records and oracle did an estimate with 100% for the index.

    So for me I will continue using AUTO_SAMPLE_SIZE. Its very flexible, fast and accurate.

    Dim

    PS: Is there a way to format code like one can do in HTML using <code> or <pre>?
  • 10. Re: "analyze index"  vs  "rebuild index"
    The Human Fly Oracle ACE Director
    Currently Being Moderated
    Problems/Bugs with dbms_stats.auto_sample_size in 9i (9205) are:

    When auto_sample_size is used to gather stats with dbms_stats,the SAMPLE_SIZE in dba_tables is almost equal to the NUM_ROWS.

    Bug 3150750 DBMS_STATS auto_sample_size can produce poor estimated NDV.
    It's also very interesting, that using AUTO_SAMPLE_SIZE
    causes oracle to execute a estimate 5% for the index and a estimate 0.5 for the table.

    In 10g, the defination for auto_sample_size

    AUTO_SAMPLE_SIZE lets Oracle determine the best sample size necessary for good statistics, based on the statistical property of the object. Because each type of statistics has different requirements, the size of the actual sample taken may not be the same across the table, columns, or indexes.