Index Internals – Rebuilding The Truth December 11, 2007
Posted by Richard Foote in Index Coalesce, Index Height, Index Internals, Index Rebuild, Index Shrink, Index statistics, Oracle Indexes, Oracle Myths, Oracle Opinion, Richard's Musings.trackback
The issue of when to rebuild indexes crops up again and again and again. Unfortunately, so do the same incorrect, myth-filled uninspired responses which considering how important and “key” (no pun intended) indexes are to database design and performance generally, is very puzzling indeed.
In the coming days I’ll post why I believe these index related myths simply refuse to go away …
This presentation was originally written several years ago but is still as relevant today as it’s always been.
Recently updated version: Index Internals – Rebuilding The Truth
Richard Foote's Oracle Blog 
Richard, first of all, thanks for starting a blog and welcome.
I see a question about rebuilding indexes everyday in several forums and mailing lists. Even Oracle’s own people suggest rebuilding indexes based on some percentage numbers or based on the blevel of an index in the reports they prepare for the customers. I think what they say is also not helping to destroy these myths.
LikeLike
Hi Yas
Yes, I agree totally.
I’ve just written a new blog entry that explains my take on all this.
Hopefully, this blog and the various things I’ll post over time will help that teeny weeny bit 🙂
LikeLike
Thanks for that very clear presentation. The main news for me was the 90/10 (or 99-1) split for monotonically increasing keys. Just out of interest, when did that optimisation get made?
LikeLike
Excellent Material !
You don’t just write “Indexes NEVER need to be rebuilt” but do
go through cases when they can be COALESCEd or REBUILDed and,
along the way, also cover a lot of ground.
LikeLike
Hi Hemant
Thanks for your kind comments.
One of the big mistakes some people seem to make when reading the presentation is that I somehow claim indexes should never be rebuilt.
I can only assume the thing is just too long, they give up at about the halfway mark and make the wrong assumption.
Thanks for managing to make it all the way through !!
Cheers 😉
LikeLike
hi Richard,
one slides 78 and 79 (“Spot the difference” – why is the index space usage less efficient in the case that you commit after every record?
Sorry if you have already explined it in the presentation – I could not find it.
Thanks,
Naresh
LikeLike
HI Naresh
It’s simply due to a bug with the 9i release that caused more indexes to potentially be fragmented than designed due to 90-10 splits not being performed when they should. Now fixed.
LikeLike
To automate this task , I created a simple and practical procedure , which meets all the all indices ( partitioned , sub_particionados or single) .
To use it , use the example below:
BEGIN DBA_PS.PS_UNUSABLEIDX_PROC ( ‘ OWNER ‘ DEGREE ) ; END;
where:
Owner = is the owner of the object, for example : HRITPRD . Required inform !
Degree = is the degree of parallelism to be used in the Rebuild . The largest valid value is 30 !
If they try to use more parallel , an error ” ORA- 20010 ” will be displayed .
This clause is therefore not required can be omitted.
the procedure of code follows:
—
CREATE OR REPLACE PROCEDURE DBA_PS.PS_UNUSABLEIDX_PROC(OWNER_IN VARCHAR2,
S_DEGREE NUMBER DEFAULT 1) AS
s_erroSQL VARCHAR2(2000) := ‘ ‘;
err_msg VARCHAR2(2000) := ‘ ‘;
s_lock VARCHAR2(10);
s_command VARCHAR2(300);
s_command2 VARCHAR2(300);
s_command3 VARCHAR2(300);
BEGIN
IF (S_DEGREE > 30) THEN
raise_application_error (-20010, ‘Use menos paralelismo. Limite de 30.’);
END IF;
BEGIN
FOR n_idx IN (SELECT *
FROM (SELECT /*+ no_cpu_costing */
a.OWNER,
a.INDEX_NAME,
a.PARTITIONED,
case
when c.subpartition_name is null then
‘N’
else
‘S’
end as IDX_SUBPART,
b.partition_name as nome_particao,
b.status as status_particao,
c.subpartition_name as nome_subparticao,
c.status as status_subparticao
FROM ALL_INDEXES a
left join all_ind_partitions b on a.owner =
b.index_owner
and a.index_name =
b.index_name
left join all_ind_subpartitions c on c.index_owner =
b.index_owner
and c.index_name =
b.index_name
and c.partition_name =
b.partition_name
WHERE a.OWNER = OWNER_IN
AND a.STATUS = ‘UNUSABLE’
OR B.STATUS = ‘UNUSABLE’
OR C.STATUS = ‘UNUSABLE’) A) LOOP
—
— PART 1
IF upper(n_idx.PARTITIONED) = ‘NO’ THEN
s_command := ‘alter index ‘ || n_idx.OWNER || ‘.’ || n_idx.index_name || ‘ rebuild compress parallel ‘|| s_degree ;
dbms_output.put_line(s_command);
EXECUTE IMMEDIATE s_command;
s_command3 := ‘alter index ‘ || n_idx.OWNER || ‘.’ || n_idx.index_name || ‘ ‘ || ‘ noparallel’;
dbms_output.put_line(s_command3);
EXECUTE IMMEDIATE s_command3;
s_command :=”;
s_command3 :=”;
dbms_output.put_line(‘PART 1’);
END IF;
— ** PART 2
IF upper(n_idx.PARTITIONED) = ‘YES’ AND upper(n_idx.status_particao) = ‘UNUSABLE’ AND upper(n_idx.IDX_SUBPART) =’N’ THEN
s_command := ‘alter index ‘ || n_idx.OWNER || ‘.’ || n_idx.index_name || ‘ ‘ || ‘ rebuild partition ‘|| n_idx.nome_particao || ‘ parallel ‘|| s_degree ;
EXECUTE IMMEDIATE s_command;
dbms_output.put_line(s_command);
s_command2 := ‘alter index ‘ || n_idx.owner || ‘.’ || n_idx.index_name || ‘ ‘ || ‘ noparallel’; — END LOOP;
EXECUTE IMMEDIATE s_command2;
dbms_output.put_line(s_command2);
s_command :=”;
s_command3 :=”;
dbms_output.put_line(‘PART 2’);
END IF;
— ** PART 3
IF upper(n_idx.PARTITIONED) = ‘YES’ AND upper(n_idx.IDX_SUBPART) =’S’ AND upper(n_idx.STATUS_SUBPARTICAO) = ‘UNUSABLE’ THEN
s_command := ‘alter index ‘ || n_idx.OWNER || ‘.’ || n_idx.index_name || ‘ ‘ || ‘ rebuild subpartition ‘|| n_idx.nome_subparticao || ‘ parallel ‘|| s_degree ;
EXECUTE IMMEDIATE s_command;
dbms_output.put_line(s_command);
s_command3 := ‘alter index ‘ || n_idx.owner || ‘.’ || n_idx.index_name || ‘ ‘ || ‘ noparallel’; — END LOOP;
EXECUTE IMMEDIATE s_command3;
dbms_output.put_line(s_command3);
s_command :=”;
s_command3 :=”;
dbms_output.put_line(‘PART 3’);
END IF;
—
END LOOP;
END;
END;
/*
GRANT ACCESS VIEWS IN READING BELOW FOR THE OWNER OF THIS PROCEDURE
GRANT SELECT ON V_ $ SESSION TO ;
GRANT SELECT ON TO sys.dba_dml_locks ;
grant alter any index to ;
grant execute any procedure to ;
grant create any index to ;
*/
LikeLike
Hi rlfuentes
I have to say, I’ve not ever worked in a database environment where the number of unusable indexes would warrant such a script 🙂
LikeLike
When we create a table has 1000 rows and contain 200 distinct values and also create a index on that column contain 1000 rows and 200 distinct values so does affect clustering factor?
If affect so clustering factor is bad or good?
LikeLike
Hi Sunny
Sorry, the question you’re asking isn’t clear to me.
But if the question you’re asking is what is the CF of an index with 200 distinct values on a 1000 row table, then it depends on the ordering of that column within the table.
If the column values are distributed randomly throughout the table, such that as you read through the index entries, they continually access different table blocks, then CF could be as high as 1000. The CF could be improved in this case if you have set table_cached_blocks table preference:
If the column is sorted within the table then the CF could be as low as the number of table blocks that store the 1000 rows.
Perform a search on the blog for Clustering Factor for various articles that explains how the CF is calculated.
LikeLike
[…] Not 2: Rebuilding indexes is not something that needs to be done in the normal course of things. Richard Foote is probably the foremost authority on the internals of oracle indexes, and he has this to say: https://richardfoote.wordpress.com/2007/12/11/index-internals-rebuilding-the-truth/ […]
LikeLike
[…] Index Internals- Rebuilding The Truth […]
LikeLike
[…] https://richardfoote.wordpress.com/2007/12/11/index-internals-rebuilding-the-truth/ […]
LikeLike