index rebuild candidates oracle
CREATE TABLE index_log ( owner VARCHAR2(30), index_name VARCHAR2(30), last_inspected DATE, leaf_blocks NUMBER, target_size NUMBER, idx_layout CLOB); ALTER TABLE index_log ADD CONSTRAINT pk_index_log PRIMARY KEY (owner,index_name); CREATE TABLE index_hist ( owner VARCHAR2(30), index_name VARCHAR2(30), inspected_date DATE, leaf_blocks NUMBER, target_size NUMBER, idx_layout VARCHAR2(4000)); ALTER TABLE index_hist ADD CONSTRAINT pk_index_hist PRIMARY KEY (owner,index_name,inspected_date); CREATE OR REPLACE PACKAGE index_util AUTHID CURRENT_USER IS vMinBlks CONSTANT POSITIVE := 1000; vScaleFactor CONSTANT NUMBER := 0.6; vTargetUse CONSTANT POSITIVE := 90; -- equates to pctfree 10 vHistRet CONSTANT POSITIVE := 10; -- (#) records to keep in index_hist procedure inspect_schema (aSchemaName IN VARCHAR2); procedure inspect_index (aIndexOwner IN VARCHAR2, aIndexName IN VARCHAR2, aTableOwne