Skip to Main Content
  • Questions

  • difference between dropping rebuilding indexes and using update global indexes

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, David.

Asked: December 20, 2003 - 10:18 am UTC

Last updated: December 23, 2003 - 11:10 am UTC

Version: 9.2.04

Viewed 10K+ times! This question is

You Asked

hi Tom

we have recently migrated to 9iR2 and wanted to use some new features to stop our indexes going valid.

What we have is a partitioned table called cs_actype_map with one partition called part.

Now we are loading reference data into a table called cs_actype_map_tmp and then using exchange partition to swap the data over.

Now in 8i the two indexes went unusable so we dropped and recreated them.

So now in 9i we added the update global indexes clause so now they indexes are ok, but what we saw was it was a lot slower than rebuilding,

here is my test

zhl4s002:22 % more cim_results.txt
17:52:21 SQL> ALTER TABLE CS_ACTYPE_MAP EXCHANGE PARTITION part WITH TABLE CS_ACTYPE_MAP_TMP;

Table altered.

Elapsed: 00:00:13.78

17:53:17 SQL> CREATE INDEX I1_CS_ACTYPE_MAP ON CS_ACTYPE_MAP (ACCOUNT_KEY) TABLESPACE mz_lcustom_ind UNRECOVERABLE;

Index created.

Elapsed: 00:01:20.55

17:54:51 SQL> CREATE INDEX I2_CS_ACTYPE_MAP ON CS_ACTYPE_MAP (TEXT1) TABLESPACE mz_lcustom_ind UNRECOVERABLE;

Index created.

Elapsed: 00:01:25.85

17:57:43 SQL> truncate table cs_actype_map;

Table truncated.

Elapsed: 00:00:00.47
17:58:07 SQL> truncate table cs_actype_map_tmp;

Table truncated.

Elapsed: 00:00:00.08
17:58:24 SQL> insert /*+APPEND*/ into cs_actype_map_tmp select * from ds;


5871902 rows created.

Elapsed: 00:01:15.33
17:59:46 SQL> 17:59:46 SQL> commit;

Commit complete.

Elapsed: 00:00:00.13
17:59:52 SQL> ALTER TABLE CS_ACTYPE_MAP EXCHANGE PARTITION part WITH TABLE CS_ACTYPE_MAP_TMP UPDATE GLOBAL INDEXES;

Table altered.

Elapsed: 00:25:21.39
18:25:37 SQL>

Overall took around 20 mins longer, I did increase my sort area size to around 15m and it went down about 5 mins, but overall time was much longer.

My question is should it take longer?

I understand there are better ways to get the data into the table in the first place but this is what I have to use at the moment

Thanks


and Tom said...

rebuild is a "bulk operation"

it is true that SOMETIMES, disabling an index, doing a large bulk load, re-enabling and rebuild the index is FASTER than "leaving the index in place during the load"

This is true for single partition tables (normal tables) as well as partitioned tables.

HOWEVER, during the load -- the indexes are "not there". So, you have to make the choice:

a) fast load, but no other work gets done

OR

b) slower load, but queries and all other work continues uninterrupted


This is only true sometimes -- if you add say 1,000 rows to 100,000,000 rows -- leaving the indexes on probably flies as compared to disable, load, rebuild.


So, as with everything in life -- it is a tradeoff. do you want:

a) continous operation
b) downtime

the decision, at the end of the day, is up to you. Maintaining the index in your case takes longer then a disable, load, and rebuild. BUT during the disable/load/rebuild time -- no work gets done other then the load. If that is acceptable -- that is the option you want. If you primary goal is continous operation -- the penalty is "longer load time" in your case (but not in EVERYONES case)


in your case, the real difference is 5 mins versus 3 mins -- forget that 25 minute one (you already figured that out on your own).

5 minutes
vs
3 minutes

it is not that large.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very nice explanation of tradeoffs

J.Nemec, December 21, 2003 - 6:46 pm UTC

Hi Tom,

I agree that the “UPDATE GLOBAL INDEXES” technique is very useful in case of global indices or unique constraints with lot of partitions (i.e. rolling window publishing).
In the case above there is only one partition in the table and no unique index. Apparently this is a total refresh of a table.
What about the approach
a) build the indexes on the CS_ACTYPE_MAP_TMP table
b) exchange the CS_ACTYPE_MAP_TMP table “INCLUDING INDEXES”

I guess it will perform similar to the disable/load/rebuild case above and the “downtime” (i.e time of exchange) will be very short. But I am not sure what is the influence on other processing. I total refresh of a table is performed, at one moment old data are there, on second moment new version of data is valid.

Can you please explain the difference of

UPDATE GLOBAL INDEXES
INCLUDING INDEXES
INCLUDING INDEXES WITH VALIDATION

from a point of view of concurrent queries.

Thanks

Jaromir Nemec




Tom Kyte
December 21, 2003 - 7:00 pm UTC

including indexes ONLY applies to local -- not global -- indexes. It is only useful for getting local indexes changed. will not affect globals at all.




including indexes

A reader, December 22, 2003 - 10:41 am UTC

Hi

Is including indexes a dictionary operation as exchange partition?

For the guy who asked, this is from the Documentation


The partition DDL statement takes longer to execute since indexes which were previously marked UNUSABLE are updated. However, this must be compared against the time it takes to execute DDL without updating indexes, and then rebuilding all indexes. A rule of thumb is that it is faster to update indexes if the size of the partition is less that 5% of the size of the table.

global index on table with one partition

Nemec, December 23, 2003 - 2:29 am UTC

If the design decision is that the table has only one partition (as described above), what sense it makes to define global index?

Tom Kyte
December 23, 2003 - 11:10 am UTC

to split the index out, decreasing contention perhaps. but in general, I don't see it being done.

What's wrong with this?

Developer, October 07, 2009 - 9:47 am UTC 

Tom,

I did a test using UPDATE GLOBAL INDEXES clause and without it and not only did it take a long time ( 85 seconds versus 6 seconds), the indexes took more space (two and a half times more) and redo generated was tremendous.

Here is the test. My question is why would anyone want to use UPDATE GLOBAL INDEXES option, HA or no HA, when the consequences are so bad?


$ sqlplus testuser/testpassword @test.sql

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Oct 6 12:27:28 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 
SQL> DROP TABLE t PURGE;

Table dropped.

Elapsed: 00:00:00.17
SQL> 
SQL> CREATE TABLE t TABLESPACE large_data01 PARTITION BY RANGE (created) (PARTITION p_maxvalue VALUES LESS THAN (maxvalue))
  2  AS SELECT * FROM all_objects
  3    UNION ALL SELECT * FROM all_objects
  4    UNION ALL SELECT * FROM all_objects
  5    UNION ALL SELECT * FROM all_objects
  6    UNION ALL SELECT * FROM all_objects
  7    UNION ALL SELECT * FROM all_objects
  8    UNION ALL SELECT * FROM all_objects
  9    UNION ALL SELECT * FROM all_objects
 10    UNION ALL SELECT * FROM all_objects;

Table created.

Elapsed: 00:00:28.49
SQL> 
SQL> CREATE INDEX i1 ON t (owner, object_name) TABLESPACE large_data01;

Index created.

Elapsed: 00:00:03.15
SQL> CREATE INDEX i2 ON t (object_id) TABLESPACE large_data01;

Index created.

Elapsed: 00:00:00.83
SQL> CREATE INDEX i3 ON t (object_type, created) TABLESPACE large_data01;

Index created.

Elapsed: 00:00:01.66
SQL> 
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TREXONE_DATA',TABNAME=>'T',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',DEGREE=>2, CASCADE=>TRUE,GRANULARITY=>'ALL');

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.04
SQL> SELECT segment_name, bytes, blocks, extents FROM user_segments WHERE segment_name = 'T';

SEGMENT_NAME                        BYTES     BLOCKS    EXTENTS
------------------------------ ---------- ---------- ----------
T                                61865984       7552         59

Elapsed: 00:00:00.73
SQL> SELECT segment_name, bytes, blocks, extents FROM user_segments WHERE segment_name IN ('I1','I2','I3');

SEGMENT_NAME                        BYTES     BLOCKS    EXTENTS
------------------------------ ---------- ---------- ----------
I1                               26214400       3200         25
I2                               11534336       1408         11
I3                               18874368       2304         18

Elapsed: 00:00:01.03
SQL> 
SQL> ALTER TABLE t SPLIT PARTITION p_maxvalue AT (TO_DATE('01-MAR-2009','DD-MON-YYYY')) INTO (PARTITION p_200902 NOLOGGING, PARTITION p_maxvalue NOLOGGING);

Table altered.

Elapsed: 00:00:01.51
SQL> 
SQL> ALTER INDEX I1 REBUILD NOLOGGING;

Index altered.

Elapsed: 00:00:03.16
SQL> ALTER INDEX I2 REBUILD NOLOGGING;

Index altered.

Elapsed: 00:00:00.75
SQL> ALTER INDEX I3 REBUILD NOLOGGING;

Index altered.

Elapsed: 00:00:01.18
SQL> 
SQL> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.11
SQL> exec runstats_pkg.rs_stop (1000);
Run1 ran in 661 hsecs
Run2 ran in 0 hsecs
run 1 ran in 66100% of the time

Name                                            Run1        Run2        Diff
<< deleted to keep it short >>
STAT...redo size                             574,072       4,224    -569,848
<< deleted to keep it short >>
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
162,655         796    -161,859 20,434.05%

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.35
SQL> 
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TREXONE_DATA',TABNAME=>'T',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',DEGREE=>2, CASCADE=>TRUE,GRANULARITY=>'ALL');

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.45
SQL> SELECT segment_name, bytes, blocks, extents FROM user_segments WHERE segment_name = 'T';

SEGMENT_NAME                        BYTES     BLOCKS    EXTENTS
------------------------------ ---------- ---------- ----------
T                                54525952       6656         52
T                                 7340032        896          7

Elapsed: 00:00:01.02
SQL> SELECT segment_name, bytes, blocks, extents FROM user_segments WHERE segment_name IN ('I1','I2','I3');

SEGMENT_NAME                        BYTES     BLOCKS    EXTENTS
------------------------------ ---------- ---------- ----------
I1                               26214400       3200         25
I2                               11534336       1408         11
I3                               18874368       2304         18

Elapsed: 00:00:01.03
SQL> --------------------------------------
SQL> --reset
SQL> ALTER TABLE t MERGE PARTITIONS p_200902, p_maxvalue INTO PARTITION p_maxvalue nologging;

Table altered.

Elapsed: 00:00:01.50
SQL> ALTER INDEX I1 REBUILD NOLOGGING;

Index altered.

Elapsed: 00:00:03.20
SQL> ALTER INDEX I2 REBUILD NOLOGGING;

Index altered.

Elapsed: 00:00:00.66
SQL> ALTER INDEX I3 REBUILD NOLOGGING;

Index altered.

Elapsed: 00:00:01.20
SQL> SELECT segment_name, bytes, blocks, extents FROM user_segments WHERE segment_name = 'T';

SEGMENT_NAME                        BYTES     BLOCKS    EXTENTS
------------------------------ ---------- ---------- ----------
T                                61865984       7552         59

Elapsed: 00:00:00.69
SQL> SELECT segment_name, bytes, blocks, extents FROM user_segments WHERE segment_name IN ('I1','I2','I3');

SEGMENT_NAME                        BYTES     BLOCKS    EXTENTS
------------------------------ ---------- ---------- ----------
I1                               26214400       3200         25
I2                               11534336       1408         11
I3                               18874368       2304         18

Elapsed: 00:00:01.05
SQL> --------------------------------------
SQL> -- Test with UPDATE GLOBAL INDEXES
SQL> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.22
SQL> 
SQL> ALTER TABLE t SPLIT PARTITION p_maxvalue AT (TO_DATE('01-MAR-2009','DD-MON-YYYY'))
  2       INTO (PARTITION p_200902 NOLOGGING, PARTITION p_maxvalue NOLOGGING) UPDATE GLOBAL INDEXES;

Table altered.

Elapsed: 00:01:25.18
SQL> 
SQL> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.11
SQL> exec runstats_pkg.rs_stop (1000);
Run1 ran in 8518 hsecs
Run2 ran in 1 hsecs
run 1 ran in 851800% of the time

Name                                            Run1        Run2        Diff
<< deleted to keep it short >>
STAT...redo size                         320,566,140       4,584-320,561,556
<< deleted to keep it short >>
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
90,900,946         371 -90,900,575##########%

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.35
SQL> 
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TREXONE_DATA',TABNAME=>'T',METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',DEGREE=>2, CASCADE=>TRUE,GRANULARITY=>'ALL');

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.56
SQL> SELECT segment_name, bytes, blocks, extents FROM user_segments WHERE segment_name = 'T';

SEGMENT_NAME                        BYTES     BLOCKS    EXTENTS
------------------------------ ---------- ---------- ----------
T                                54525952       6656         52
T                                 7340032        896          7

Elapsed: 00:00:01.02
SQL> SELECT segment_name, bytes, blocks, extents FROM user_segments WHERE segment_name IN ('I1','I2','I3');

SEGMENT_NAME                        BYTES     BLOCKS    EXTENTS
------------------------------ ---------- ---------- ----------
I1                               59768832       7296         57
I2                               25165824       3072         24
I3                               51380224       6272         49

Elapsed: 00:00:01.03

...continued

Developer, October 07, 2009 - 9:52 am UTC

The last query results got truncated. So here it is again:

SQL> SELECT segment_name, bytes, blocks, extents FROM user_segments WHERE segment_name IN ('I1','I2','I3');

SEGMENT_NAME                        BYTES     BLOCKS    EXTENTS
------------------------------ ---------- ---------- ----------
I1                               59768832       7296         57
I2                               25165824       3072         24
I3                               51380224       6272         49

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.