Index | SysDBA

Checking the validity of the index

ANALYZE INDEX orders_region_id_idx VALIDATE STRUCTURE; ===&gt; index_stats

1	ANALYZE INDEX orders_region_id_idx VALIDATE STRUCTURE; ===> index_stats

 SELECT blocks, pct_used, distinct_keys

lf_rows, del_lf_rows

FROM index_stats;

SELECT blocks, pct_used, distinct_keys

lf_rows, del_lf_rows

FROM index_stats;

BLOCKS PCT_USED LF_ROWS DEL_LF_ROWS

- -- -- --

25 11 14 0

1 row selected.

Note: If the percentage of LF_ROWS within the DEL_LF_ROWS passes 30% then the index should be analyzed and recreated after being checked.

DROP INDEX hr.deptartments_name_idx;

1	DROP INDEX hr.deptartments_name_idx;

To view the index usage

Start with

ALTER INDEX summit.orders_id_idx MONITORING USAGE;

Followed by retrieving the following column information from V$OBJECT_USAGE:

INDEX_NAME

TABLE_NAME

MONITORING: Monitoring status (ON/OFF)

USED: YES / NO Whether the index has been used whilst monitoring was active

START_MONITORING: The start monitoring time

END_MONITORING: The end monitoring time

To end the monitoring

 ALTER INDEX summit.orders_id_idx NOMONITORING USAGE;

1	ALTER INDEX summit.orders_id_idx NOMONITORING USAGE;

Freeing some space by compressing the indexes

create index emp_last_name_idx on employees (last_name, first_name) compress;

alter index emp_last_name_idx rebuild compress;

Getting detailed information about indexes by reviewing the views/tables below

DBA_INDEXES

DBA_IND_COLUMNS

DBA_IND_EXPRESSIONS

V$OBJECT_USAGE

DBA_INDEXES

DBA_IND_COLUMNS

DBA_IND_EXPRESSIONS

V$OBJECT_USAGE

select index_name, last_analyzed, num_rows, sample_size from user_indexes where table_name = ‘table_name’ ;

1	select index_name, last_analyzed, num_rows, sample_size from user_indexes where table_name = ‘table_name’ ;

It is important to collect statistical information for Cost Based Optimizers (CBO). There are several ways to accomplish this:

The first is to retrieve information about a particular time:

DBMS_STATS.GATHER_INDEX_STATS (‘SCHEMA_NAME’,’INDEX_NAME’);

Another way is by performing the following while creating the index

create index INDEX_NAME ………. compute statistics;

1	create index INDEX_NAME ………. compute statistics;

It may be necessary to rebuild the indexes from time to time. Index statistics can be gathered during this rebuilding process.

alter index INDEX_NAME rebuild compute statistics;

1	alter index INDEX_NAME rebuild compute statistics;

With 10g, the DBMS gathers statistics while building the index.

To see an example of this:

drop table ttest;

create table ttest as select object_id, object_name, object_type from all_objects;

create index ttest_id_idx on ttest (object_id);

drop table ttest;

create table ttest as select object_id, object_name, object_type from all_objects;

create index ttest_id_idx on ttest (object_id);

select index_name, last_analyzed, num_rows, sample_size from user_indexes where table_name = ‘TTEST’ ;

1	select index_name, last_analyzed, num_rows, sample_size from user_indexes where table_name = ‘TTEST’ ;

index_name last_analyzed num_rows sample_size

TTEST_ID_IDX 2/12/2007 3:59:01 PM 12332 12332

Statistics aren’t updated as transactions are carried out on the tables. To check:

insert into ttest select object_id, object_name, object_type from all_objects;

1	insert into ttest select object_id, object_name, object_type from all_objects;

select index_name, last_analyzed, num_rows, sample_size from user_indexes where table_name = ‘TTEST’ ;

index_name last_analyzed num_rows sample_size

TTEST_ID_IDX 2/12/2007 3:59:01 PM 12332 12332

select index_name, last_analyzed, num_rows, sample_size from user_indexes where table_name = ‘TTEST’ ;

index_name last_analyzed num_rows sample_size

TTEST_ID_IDX 2/12/2007 3:59:01 PM 12332 12332

An insert operation (or any operation) performed on a table doesn’t automatically update the "last_analyzed" field. To compute statistics run analyze index ttest_id_idx compute statistics.

Viewing the results after running the command:

analyze index ttest_id_idx compute statistics;

select index_name, last_analyzed, num_rows, sample_size from user_indexes where table_name = ‘TTEST’ ;

index_name last_analyzed num_rows sample_size

TTEST_ID_IDX 2/12/2007 3:59:53 PM 36998 36998

analyze index ttest_id_idx compute statistics;

select index_name, last_analyzed, num_rows, sample_size from user_indexes where table_name = ‘TTEST’ ;

index_name last_analyzed num_rows sample_size

TTEST_ID_IDX 2/12/2007 3:59:53 PM 36998 36998

By running ”GATHER_STATS_JOB” the DBMS can automatically gather statistics whenever necessary. To view the status of the this setting, run this command: (TRUE indicates that it’s active)

SELECT owner, job_name, enabled

FROM dba_scheduler_jobs

WHERE job_name = ‘GATHER_STATS_JOB’ =&gt; if inactive =&gt; “dbms_scheduler.enable(’GATHER_STATS_JOB’)

SELECT owner, job_name, enabled

FROM dba_scheduler_jobs

WHERE job_name = ‘GATHER_STATS_JOB’ => if inactive => “dbms_scheduler.enable(’GATHER_STATS_JOB’)

Deleting an index necessitates creating a new one. Creating a new and updated index would increase a database’s performance.

Rebuilding indexes would normally lock the tables, this method shows how to set the lock to a minimum:

 ALTER INDEX orders_id_idx DEALLOCATE UNUSED

 ALTER INDEX orders_region_id_idx REBUILD TABLESPACE indx02;

 ALTER INDEX orders_id_idx REBUILD ONLINE;

 ALTER INDEX orders_id_idx COALESCE;

ALTER INDEX orders_id_idx DEALLOCATE UNUSED

ALTER INDEX orders_region_id_idx REBUILD TABLESPACE indx02;

ALTER INDEX orders_id_idx REBUILD ONLINE;

ALTER INDEX orders_id_idx COALESCE;

Viewing the amount of index fragmentation

validate index index_name ; =&gt; By committing this statement information gets written to index_stats

1	validate index index_name ; => By committing this statement information gets written to index_stats

Reviewed using the following query

select name "index name",

del_LF_rows "rows that have not be refilled after being deleted" ,

LF_rows "total index rows" ,

round ((del_LF_rows / (LF_rows + 0.0000000001)) * 100) "fragmentation percentage"

from index_stats;

select name "index name",

del_LF_rows "rows that have not be refilled after being deleted" ,

LF_rows "total index rows" ,

round ((del_LF_rows / (LF_rows + 0.0000000001)) * 100) "fragmentation percentage"

from index_stats;

If the fragmentation percentage is high

alter index index_name rebuild ; to rebuild the index.

1	alter index index_name rebuild ; to rebuild the index.

To find the index tables belonging to this user

select a.index_name ,

b.index_type ,

a.column_name

from user_ind_columns a , user_indexes b

where a.table_name = 'hr.employees'

and a.index_name = b.index_name

order by a.index_name ;

select a.index_name ,

b.index_type ,

a.column_name

from user_ind_columns a , user_indexes b

where a.table_name = 'hr.employees'

and a.index_name = b.index_name

order by a.index_name ;