jump to navigation

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

Comments»

1. Yas - December 12, 2007

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.

2. Richard Foote - December 12, 2007

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 :)

3. Nigel Thomas - December 13, 2007

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?

4. Hemant K Chitale - January 8, 2008

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.

5. Richard Foote - January 8, 2008

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 ;)

6. Naresh Bhandare - January 27, 2009

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

7. Richard Foote - February 5, 2009

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.

8. rlfuentes - June 12, 2015

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 ;
*/

Richard Foote - June 24, 2015

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 :)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,115 other followers

%d bloggers like this: