You have a Table that you use in your Package/Procedure and do a lot of DML (Insert/Update/Delete) operations on it.
You notice the performance of the SQL Statements is not as good as it used to be when you created the Indexes.
This happens because the DML operations, especially Update and Delete create "holes" or "gaps" in the Index.
Solution
You may consider using dbms_index_utl.build_table_indexes in your package
PROCEDURE dummy_schema.dummy_procedure
IS
BEGIN
DELETE FROM dummy_table
WHERE dummy_conditions = 'TRUE';
COMMIT;
-- REBUILD INDEX
dbms_index_utl.build_table_indexes
(LIST => 'DUMMY_SCHEMA.DUMMY_TABLE',
just_unusable => FALSE,
locality => 'ALL',
concurrent => TRUE,
cont_after_err => TRUE,
max_slaves => 8
);
END dummy_procedure;
Note: The procedure's schema should have INDEX privilege on the custom table.
