This content has been marked as final.
Show 10 replies

1. Re: "analyze index" vs "rebuild index"
The Human Fly May 19, 2004 4:39 AM (in response to 373353)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 May 19, 2004 9:20 AM (in response to The Human Fly)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 May 19, 2004 3:16 PM (in response to 358102)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=UTF8&threadm=5Vtkc.5521%24TT.227%40newsserver.bigpond.net.au&rnum=1&prev=/groups%3Fq%3Dg:thl3120676585d%26dq%3D%26hl%3Den%26lr%3D%26ie%3DUTF8%26selm%3D5Vtkc.5521%2524TT.227%2540newsserver.bigpond.net.au
Good place to start debunking some of the prevalent myths. 
4. Re: "analyze index" vs "rebuild index"
226421 Dec 30, 2005 1:24 PM (in response to 358102)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"
kuljeet singh  Dec 31, 2005 12:05 AM (in response to 373353)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 Dec 31, 2005 12:29 AM (in response to 226421)Collecting 100% stats (compute) on indexes is always recommended.
Jaffar 
7. Re: "analyze index" vs "rebuild index"
Dr.Dimitri Dec 31, 2005 6:51 AM (in response to The Human Fly)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 Jan 1, 2006 2:37 AM (in response to Dr.Dimitri)
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.Did you ever work with a Database with tables containing billions of records and lots of indexes on it?
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.Use DBMS_STATS.GATHER_INDEX_STATS with DBMS_STATS.AUTO_SAMPLE_SIZE instead.
Auto_sample_size has many problems/bugs in 9i,DBMS_STATS.AUTO_SAMPLE_SIZE instead. .
==
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)
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.Why? Any proofs?
Jaffar 
9. Re: "analyze index" vs "rebuild index"
Dr.Dimitri Jan 1, 2006 5:20 AM (in response to The Human Fly)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(10000000NUM_ROWS)/100000 VARIANCE,'TABLE' OBJECT FROM USER_TABLES WHERE TABLE_NAME='BIG'
UNION ALL
SELECT NUM_ROWS,SAMPLE_SIZE,ABS(10000000NUM_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 Jan 1, 2006 8:16 AM (in response to Dr.Dimitri)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.
causes oracle to execute a estimate 5% for the index and a estimate 0.5 for the table.It's also very interesting, that using AUTO_SAMPLE_SIZE
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.