結論

1,如果在ind$刪除對應測試表的索引條目，查詢應用表不全再使用索引
2,ind$表的flags以及相關幾個列，控制索引是否可用
3,ind$的定義非常複雜，值得全面學習
4,ind$如下列控制從哪些表空間及資料檔案和資料塊開始讀取索引的真正資料
file# number not null, /* segment header file number */
block# number not null, /* segment header block number */ --索引的頭塊
6,如果上述列的資料不一致，可能會報
SQL> select * from t_ind_err where a=1;
select * from t_ind_err where a=1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [4], [5], [999724], [], [], [], [], [], [], [], []

7,經過對比分析，可知第2個引數就是表空間，第3個引數是資料檔案，第4個引數是索引的塊頭

測試

1,資料庫版本
SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

2，建立測試表，插入資料，構建索引
SQL> create table t_ind_err(a int,b int);

Table created.

SQL> insert into t_ind_err select level,level from dual connect by level<=10;

10 rows created.

SQL> commit;

Commit complete.

SQL> create index idx_t_ind_err on t_ind_err(a);

Index created.

3，查詢測試表SQL

SQL> set autot traceonly
SQL> select * from t_ind_err where a=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3275211715

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_IND_ERR | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_IND_ERR | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"=1)

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
589 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

4,刪除ind$的對應記錄
SQL> select obj#,name from obj$ where lower(name)='idx_t_ind_err';

OBJ# NAME
---------- ------------------------------------------------------------
75125 IDX_T_IND_ERR

ind$表的DDL定義
create table ind$ /* index table */
( obj# number not null, /* object number */
/* DO NOT CREATE INDEX ON DATAOBJ# AS IT WILL BE UPDATED IN A SPACE
* TRANSACTION DURING TRUNCATE */
dataobj# number, /* data layer object number */
ts# number not null, /* tablespace number */
file# number not null, /* segment header file number */
block# number not null, /* segment header block number */ --索引的頭塊
bo# number not null, /* object number of base table */
indmethod# number not null, /* object # for cooperative index method */
cols number not null, /* number of columns */ --索引包括幾個列
pctfree$ number not null, /* minimum free space percentage in a block */
initrans number not null, /* initial number of transaction */
maxtrans number not null, /* maximum number of transaction */
pctthres$ number, /* iot overflow threshold, null if not iot */
type# number not null, /* what kind of index is this? */ --索引的型別，普通還是點陣圖
/* normal : 1 */
/* bitmap : 2 */
/* cluster : 3 */
/* iot - top : 4 */
/* iot - nested : 5 */
/* secondary : 6 */
/* ansi : 7 */
/* lob : 8 */
/* cooperative index method : 9 */
flags number not null, ---索引的是否分析，是否可用，是否重建，諸多資訊，豐富很多
/* mutable flags: anything permanent should go into property */
/* unusable (dls) : 0x01 */
/* analyzed : 0x02 */
/* no logging : 0x04 */
/* index is currently being built : 0x08 */
/* index creation was incomplete : 0x10 */
/* key compression enabled : 0x20 */
/* user-specified stats : 0x40 */
/* secondary index on IOT : 0x80 */
/* index is being online built : 0x100 */
/* index is being online rebuilt : 0x200 */
/* index is disabled : 0x400 */
/* global stats : 0x800 */
/* fake index(internal) : 0x1000 */
/* index on UROWID column(s) : 0x2000 */
/* index with large key : 0x4000 */
/* move partitioned rows in base table : 0x8000 */
/* index usage monitoring enabled : 0x10000 */
/* 4 bits reserved for bitmap index version : 0x1E0000 */
/* Delayed Segment Creation: 0x4000000 */
property number not null, /* immutable flags for life of the index */ --索引的型別
/* unique : 0x01 */
/* partitioned : 0x02 */
/* reverse : 0x04 */
/* compressed : 0x08 */
/* functional : 0x10 */
/* temporary table index: 0x20 */
/* session-specific temporary table index: 0x40 */
/* index on embedded adt: 0x80 */
/* user said to check max length at runtime: 0x0100 */
/* domain index on IOT: 0x0200 */
/* join index : 0x0400 */
/* system managed domain index : 0x0800 */
/* The index was created by a constraint : 0x1000 */
/* The index was created by create MV : 0x2000 */
/* composite domain index : 0x8000 */
/* The following columns are used for index statistics such
* as # btree levels, # btree leaf blocks, # distinct keys,
* # distinct values of first key column, average # leaf blocks per key,
* clustering info, and # blocks in index segment.
*/
blevel number, /* btree level */
leafcnt number, /* # of leaf blocks */
distkey number, /* # distinct keys */
lblkkey number, /* avg # of leaf blocks/key */
dblkkey number, /* avg # of data blocks/key */
clufac number, /* clustering factor */
analyzetime date, /* timestamp when last analyzed */
samplesize number, /* number of rows sampled by Analyze */
rowcnt number, /* number of rows in the index */
intcols number not null, /* number of internal columns */
/* The following two columns are only valid for partitioned indexes */
/*
* Legal values for degree, instances:
* NULL (used to represent 1 on disk/dictionary and implies noparallel), or
* 2 thru EB2MAXVAL-1 (user supplied values), or
* EB2MAXVAL (implies use default value)
*/
degree number, /* number of parallel query slaves per instance */ --索引的並行度
instances number, /* number of OPS instances for parallel query */

trunccnt number, /* re-used for iots 'inclcol' */
spare1 number, /* number of columns depended on, >= intcols */
spare2 number, /* number of key columns in compressed prefix */
spare3 number,
spare4 varchar2(1000), /* used for parameter str for domain idx */
spare5 varchar2(1000),
spare6 date /* flashback timestamp */ --閃回技術
)
cluster c_obj#(bo#)

SQL> select obj#,type#,flags from ind$ where obj#=75125;

OBJ# TYPE# FLAGS
---------- ---------- ----------
75125 1 2

SQL> create table ind$_bak as select * from ind$ where obj#=75125;

Table created.

SQL> delete from ind$ where obj#=75125;

1 row deleted.

SQL> commit;

Commit complete.

奕成了全表掃描
SQL> alter system flush shared_pool;

System altered.

SQL> set autot traceonly
SQL> select * from t_ind_err where a=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3215397359

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_IND_ERR | 1 | 26 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=1)

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
289 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
585 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

恢復ind$
SQL> insert into ind$ select * from ind$_bak;

1 row created.

SQL> commit;

Commit complete.

恢復後又使用了索引
SQL> select * from t_ind_err where a=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3275211715

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_IND_ERR | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_IND_ERR | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"=1)

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
360 recursive calls
0 db block gets
61 consistent gets
0 physical reads
0 redo size
589 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

5,還下我們不刪除掉整條ind$記錄，我們更新其記錄對應列值，看會如何
SQL> select obj#,cols from ind$ where obj#=75125;

OBJ# COLS
---------- ----------
75125 1

SQL> update ind$ set cols=2 where obj#=75125;

1 row updated.

SQL> commit;

Commit complete.

雖說更新了ind$對應記錄的某列，仍可以使用索引
SQL> select * from t_ind_err where a=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3275211715

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_IND_ERR | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_IND_ERR | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"=1)

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
360 recursive calls
0 db block gets
61 consistent gets
0 physical reads
0 redo size
589 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

說明ind$僅有些重要列比如flags，會決定應用的索引是否有效
SQL> update ind$ set cols=1,flags=1 where obj#=75125;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t_ind_err where a=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3215397359

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_IND_ERR | 1 | 26 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=1)

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
356 recursive calls
0 db block gets
64 consistent gets
0 physical reads
0 redo size
585 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

6,還原ind$表資料
SQL> update ind$ set cols=1,flags=2 where obj#=75125;

1 row updated.

SQL> commit;

Commit complete.

7,繼續學習下ind$幾個重要的列含義
SQL> select obj#,ts#,file#,block# from ind$ where obj#=75125;

OBJ# TS# FILE# BLOCK#
---------- ---------- ---------- ----------
75125 4 4 100434

file# number not null, /* segment header file number */
block# number not null, /* segment header block number */ --索引的頭塊

SQL> update ind$ set block#=999724 where obj#=75125;

1 row updated.

SQL> commit;

Commit complete.

看到沒，查詢應用表報錯了
SQL> set autot traceonly
SQL> select * from t_ind_err where a=1;
select * from t_ind_err where a=1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [4], [4], [999724], [], [], [], [], [], [], [], []

由上可知，999724就是要訪問的索引的頭塊
2個4各是表空間和資料檔案

那麼哪個是表空間，哪個是資料檔案呢，我們對比下，即知

SQL> update ind$ set ts#=6,file#=3 where obj#=75125;

1 row updated.

SQL> commit;

Commit complete.

直接去找表空間6，即soe可知不存在
SQL> select * from t_ind_err where a=1;
select * from t_ind_err where a=1
*
ERROR at line 1:
ORA-00959: tablespace 'SOE' does not exist

SQL> update ind$ set ts#=4,file#=5 where obj#=75125;

1 row updated.

SQL> commit;

Commit complete.

可知第2個引數就是表空間，第3個引數是資料檔案，第4個引數是索引的塊頭
SQL> select * from t_ind_err where a=1;
select * from t_ind_err where a=1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [4], [5], [999724], [], [], [], [], [], [], [], []

恢復ind$
SQL> update ind$ set ts#=4,file#=4,block#=100434 where obj#=75125;

1 row updated.

SQL> commit;

執行計劃恢復正常
SQL> select * from t_ind_err where a=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3275211715

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_IND_ERR | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_IND_ERR | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"=1)

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
360 recursive calls
0 db block gets
61 consistent gets
0 physical reads
0 redo size
589 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
Commit complete.

oracle11g基於bootstrap$中的ind$表損壞系列五

結論

測試

相關文章