alter index rebuild

[rubenr]

Hi Tim,

Currently I am archiving data (100,000 rows app.) from a table on a regular basis (delete from source table, insert into archive table). After this deletion I intend to rebuild the indexes as my understanding is that when you delete data from table it leaves empty "leaf" on your indexes (and this "leaf" can not be refilled, unlike, when you delete data from a table then empty space can be refilled as you insert into the same table), and if you do not rebuild your index it may affect the performance of your application, also, if you rebuild your indexes after deleting rows from the concerned table then it reduces the size of your index.

I was looking for ways to do this task in an automated way (via a scheduled procedure, etc.), because sometimes you get an ora- error when you try to rebuild indexes and someone is trying to insert into that table at the same time, so i wanted to know what would be the proper way of rebuilding indexes via a scheduled job. And, I saw your responses on similar issues as below:

topic subject: Online Index Rebuild fails with ORA-08104 error
your response: "Note. Scheduling online rebuilds of indexes is very wasteful of resources. For the most part you shouldn't need to rebuild Oracle indexes."

topic subject: Dynamic SQL
your response: "Of course, the big question is why would you want to rebuild the indexes? If they are unusable then fine. If you are doing it in a hope to gain better performance, you are probably on to a looser. The conventional wisdone is that there is no need to rebuild indexes unless they are marked unusable."

So, it is evident from your responses that you are not a big fan of rebuilding indexes to gain performance improvement. Could you please explain why or direct me to a link that explains?

secondly, can you please state your opinion as far as the space reclamation concerned when you rebuild indexes?

Ruben

[Tim...]

Hi.

Why? The reason why is most of the time the rebuilt index does not actually give you better performance. It's one of those common myths. You'll understand why if you read the two articles pointed to by this link:

http://richardfoote.wordpress.com/categ ... x-rebuild/

Richard is a well renowned expert on indexing ane he's layed the maths out for you.

Space retention? Oracle is really good at managing indexes, which is why the vast majority of experts say don't rebuild them unless you can identify a specific performance problem that means you should. Just doing a rebuild because you think you should is not a sensible way to work.

You mention you delete 100,000 rows, but don't say what percentage of the table this represents. If this is 1% or the rows, who cares. If this is 90% of the rows, then it would be more efficient to do a CTAS or online table redefinition. Context is very important as far as these matters go.

Cheers

Tim...

[rubenr]

Hi Tim,

100,000 rows represent 25% of the table. I will probably monitor the performance of the concerned sqls and see how things go. And, as far the space concerned, I will monitor the size of the indexes, and see if/how they are growing.

Thank you for your response.

Ruben

[Tim...]

Hi.

The nature of the work and the type of index has a big impact. Check this out:

Create and populates a test table with a PK and two non-unique indexed columns.

Code: Select all

DROP TABLE t1;

CREATE TABLE t1 (
  id          NUMBER(10),
  num         NUMBER(10),
  str         VARCHAR2(10),
  description VARCHAR2(50),
  CONSTRAINT  t1_pk PRIMARY KEY (id)
);

CREATE INDEX t1_num_idx ON t1(num);
CREATE INDEX t1_str_idx ON t1(str);

BEGIN
  -- row-by-row insert rather than bulk on purpose.
  FOR i IN 1 .. 400000 LOOP
    INSERT INTO t1 (id, num, str, description)
    VALUES (i, i, TO_CHAR(i), 'Description for ' || TO_CHAR(i));
  END LOOP;
  COMMIT;
END;
/

EXEC DBMS_STATS.gather_table_stats(USER, 'T1', cascade => TRUE);

Get information about the index.

Code: Select all

SET LINESIZE 120

SELECT index_name,
       blevel,
       leaf_blocks,
       distinct_keys,
       avg_leaf_blocks_per_key AS avg_lbpk,
       avg_data_blocks_per_key AS avg_dbpk,
       clustering_factor
FROM   user_indexes
ORDER BY index_name;

INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS   AVG_LBPK   AVG_DBPK CLUSTERING_FACTOR
------------------------------ ---------- ----------- ------------- ---------- ---------- -----------------
T1_NUM_IDX                              2         799        400000          1          1              2451
T1_PK                                   2         750        400000          1          1              2451
T1_STR_IDX                              2        1666        398144          1          1             77980

SQL>

SELECT segment_name,
       blocks
FROM   user_segments
WHERE  segment_type = 'INDEX'
ORDER BY segment_name;

SEGMENT_NAME                                                                          BLOCKS
--------------------------------------------------------------------------------- ----------
T1_NUM_IDX                                                                               896
T1_PK                                                                                    896
T1_STR_IDX                                                                              1792

SQL>

Reusing Index Keys

Delete 100,000 rows (25%) of table and repopulate using the same keys we deleted (1 to 100,000).

Code: Select all

DELETE FROM t1 WHERE id BETWEEN 1 AND 100000;
COMMIT;

BEGIN
  -- row-by-row insert rather than bulk on purpose.
  FOR i IN 1 .. 100000 LOOP
    INSERT INTO t1 (id, num, str, description)
    VALUES (i, i, TO_CHAR(i), 'Description for ' || TO_CHAR(i));
  END LOOP;
  COMMIT;
END;
/

EXEC DBMS_STATS.gather_table_stats(USER, 'T1', cascade => TRUE);

Get information about the index.

Code: Select all

SET LINESIZE 120

SELECT index_name,
       blevel,
       leaf_blocks,
       distinct_keys,
       avg_leaf_blocks_per_key AS avg_lbpk,
       avg_data_blocks_per_key AS avg_dbpk,
       clustering_factor
FROM   user_indexes
ORDER BY index_name;

INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS   AVG_LBPK   AVG_DBPK CLUSTERING_FACTOR
------------------------------ ---------- ----------- ------------- ---------- ---------- -----------------
T1_NUM_IDX                              2         799        400000          1          1              2453
T1_PK                                   2         750        400000          1          1              2453
T1_STR_IDX                              2        1666        398144          1          1             77982

SQL>

SELECT segment_name,
       blocks
FROM   user_segments
WHERE  segment_type = 'INDEX'
ORDER BY segment_name;

SEGMENT_NAME                                                                          BLOCKS
--------------------------------------------------------------------------------- ----------
T1_NUM_IDX                                                                               896
T1_PK                                                                                    896
T1_STR_IDX                                                                              1792

SQL>

Not Reusing Index Keys

Delete 100,000 rows (25%) of table and repopulate using different keys to those that were deleted (1 to 100,000).

Code: Select all

DELETE FROM t1 WHERE id BETWEEN 1 AND 100000;
COMMIT;

BEGIN
  -- row-by-row insert rather than bulk on purpose.
  FOR i IN 400001 .. 500000 LOOP
    INSERT INTO t1 (id, num, str, description)
    VALUES (i, i, TO_CHAR(i), 'Description for ' || TO_CHAR(i));
  END LOOP;
  COMMIT;
END;
/

EXEC DBMS_STATS.gather_table_stats(USER, 'T1', cascade => TRUE);

Get information about the index.

Code: Select all

SET LINESIZE 120

SELECT index_name,
       blevel,
       leaf_blocks,
       distinct_keys,
       avg_leaf_blocks_per_key AS avg_lbpk,
       avg_data_blocks_per_key AS avg_dbpk,
       clustering_factor
FROM   user_indexes
ORDER BY index_name;

INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS   AVG_LBPK   AVG_DBPK CLUSTERING_FACTOR
------------------------------ ---------- ----------- ------------- ---------- ---------- -----------------
T1_NUM_IDX                              2         999        500000          1          1              3075
T1_PK                                   2         937        500000          1          1              3075
T1_STR_IDX                              2        2092        497536          1          1             97489

SQL>

SELECT segment_name,
       blocks
FROM   user_segments
WHERE  segment_type = 'INDEX'
ORDER BY segment_name;

SEGMENT_NAME                                                                          BLOCKS
--------------------------------------------------------------------------------- ----------
T1_NUM_IDX                                                                              1152
T1_PK                                                                                   1024
T1_STR_IDX                                                                              2176

SQL>

Conclusions:

- If the data loaded into an indexed column is similar to the previously deleted data, the index will not alter drastically.
- If the data loaded is different, the affect on the index can be substantial.
- Unique and non-unique indexes are different, but react similarly (in terms of proportions).
- Indexes on VARCHAR2 and NUMBER columns are different, but react similarly (in terms of proportions).
- Changes to clustering factor and unique keys should be considered.

In total, having a plan to do the same maintenance scheme for every index in your system is a waste of resources. Monitor your indexes and only do COALESE and REBUILD operations on those that need them.

Cheers

Tim...