Skip to main content

Search

Welcome to Tibero GTS!

Index Consistency Verification Mechanism

Document TypeㅣTechnical Information

CategoryㅣMonitoring/Inspection

Applicable Product VersionsㅣTibero6, Tibero7

Document NumberㅣTMOTI049

 

Overview

This document describes the internal logic used for index consistency verification (DBMS_VERIFY).
 
 

Method

Mechanism

When dbms_verify.table_index is executed, the internal queries below are executed.
 
1.Table Full Scan rows - Index Full scan rows
select index_col_name,rid
from ( 
select /*+full(t) t.index_col_name, t.rowid as rid
from table_name t
where t.index_col_name is not null
minus
select /*+index(t t_idx01) */ t.index_col_name, t.rowid
from table_name t
where t.index_col_name is not null
);

2. Index Full scan rows - Table Full Scan rows 
select index_col_name,rid
from ( 
select /*+full(t) t.index_col_name, t.rowid as rid
from table_name t
where t.index_col_name is not null
minus
select /*+index(t t_idx01) */ t.index_col_name, t.rowid
from table_name t
where t.index_col_name is not null
);


If the results of both queries are 0 rows, the index is considered normal.

If values exist, it is considered abnormal (Inconsistant).

  • table has more rows than index. When the table has more rows than the index
  • index has more rows than table. When the index has more rows than the table

The causes of index consistency corruption vary depending on the version. For analysis, extract the required logs and dumps, and then contact technical support.

Caution

exec dbms_verify.table_index('schema name','table name'):
Or, when using the method above, if the table and index are large
it is essential to secure sufficient undo space to guarantee read consistency while the query is being executed, and
to secure sufficient temp space for the large-scale sorting and duplicate removal involved in the MINUS operation

 

 

 

Related to