oracle中与索引相关的视图---all_indexes

最新推荐文章于 2023-05-21 14:07:33 发布

ezbit

最新推荐文章于 2023-05-21 14:07:33 发布

阅读量4.1k

点赞数 1

分类专栏： Oracle Database ORACLE索引研究文章标签： oracle index all_indexes analyze

本文链接：https://blog.csdn.net/yidian815/article/details/17347239

版权

Oracle Database 同时被 2 个专栏收录

50 篇文章 3 订阅

订阅专栏

ORACLE索引研究

9 篇文章 2 订阅

订阅专栏

ALL_INDEXES描述了与索引有关的信息，为了使部分字段的内容更准确，我们需要通过analyze或者dbms_stats包来收集与索引有关的统计信息（这部分字段标记为带*）。

首先看一下官网给出的各个字段的解释：

Column	Datatype	NULL	Description
`OWNER`	`VARCHAR2(30)`	`NOT NULL`	Owner of the index（索引拥有者）
`INDEX_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the index
`INDEX_TYPE`	`VARCHAR2(27)`		Type of the index: `NORMAL` `NORMAL/REV` `BITMAP` `FUNCTION-BASED NORMAL` `FUNCTION-BASED NORMAL/REV` `FUNCTION-BASED BITMAP` `CLUSTER` `IOT - TOP (索引组织表对应的索引，注意IOT - TOP 之间存有空格，个人观察：表的类型为IOT;对应索引类型为IOT - TOP;对应overflow表类型为IOT-OVERFLOW)` `DOMAIN（自定义索引类型）`
`TABLE_OWNER`	`VARCHAR2(30)`	`NOT NULL`	Owner of the indexed object
`TABLE_NAME`	`VARCHAR2(30)`	`NOT NULL`	Name of the indexed object
`TABLE_TYPE`	`CHAR(5)`		Type of the indexed object: `NEXT OBJECT` `INDEX` `TABLE` `CLUSTER` `VIEW` `SYNONYM` `SEQUENCE`
`UNIQUENESS`	`VARCHAR2(9)`		Indicates whether the index is unique (`UNIQUE`) or nonunique (`NONUNIQUE`)
`COMPRESSION`	`VARCHAR2(8)`		Indicates whether index compression is enabled (`ENABLED`) or not (`DISABLED`)
`PREFIX_LENGTH`	`NUMBER`		Number of columns in the prefix of the compression key（索引压缩中被压缩的字段数量，唯一索引最大值为索引列数-1，非唯一索引最大值为索引列数）
`TABLESPACE_NAME`	`VARCHAR2(30)`		Name of the tablespace containing the index
`INI_TRANS`	`NUMBER`		Initial number of transactions
`MAX_TRANS`	`NUMBER`		Maximum number of transactions
`INITIAL_EXTENT`	`NUMBER`		Size of the initial extent
`NEXT_EXTENT`	`NUMBER`		Size of secondary extents
`MIN_EXTENTS`	`NUMBER`		Minimum number of extents allowed in the segment
`MAX_EXTENTS`	`NUMBER`		Maximum number of extents allowed in the segment
`PCT_INCREASE`	`NUMBER`		Percentage increase in extent size
`PCT_THRESHOLD`	`NUMBER`		Threshold percentage of block space allowed per index entry
`INCLUDE_COLUMN`	`NUMBER`		Column ID of the last column to be included in index-organized table primary key (non-overflow) index. This column maps to the `COLUMN_ID` column of the `*_TAB_COLUMNS` view(该字段具体的含义参见下文的解释).
`FREELISTS`	`NUMBER`		Number of process freelists allocated to this segment
`FREELIST_GROUPS`	`NUMBER`		Number of freelist groups allocated to this segment
`PCT_FREE`	`NUMBER`		Minimum percentage of free space in a block
`LOGGING`	`VARCHAR2(3)`		Indicates whether or not changes to the index are logged: `YES` `NO`
`BLEVEL*`	`NUMBER`		B*-Tree level (depth of the index from its root block to its leaf blocks). A depth of `0` indicates that the root block and leaf block are the same.
`LEAF_BLOCKS*`	`NUMBER`		Number of leaf blocks in the index
`DISTINCT_KEYS*`	`NUMBER`		Number of distinct indexed values. For indexes that enforce `UNIQUE` and `PRIMARY KEY` constraints, this value is the same as the number of rows in the table (`*_TABLES.NUM_ROWS`)
`AVG_LEAF_BLOCKS_PER_KEY*`	`NUMBER`		Average number of leaf blocks in which each distinct value in the index appears, rounded to the nearest integer. For indexes that enforce `UNIQUE` and `PRIMARY KEY` constraints, this value is always `1`.
`AVG_DATA_BLOCKS_PER_KEY*`	`NUMBER`		Average number of data blocks in the table that are pointed to by a distinct value in the index rounded to the nearest integer. This statistic is the average number of data blocks that contain rows that contain a given value for the indexed columns.
`CLUSTERING_FACTOR*`	`NUMBER`		Indicates the amount of order of the rows in the table based on the values of the index. If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks. If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks. For bitmap indexes, this column is not applicable and is not used.
`STATUS`	`VARCHAR2(8)`		Indicates whether a nonpartitioned index is `VALID` or `UNUSABLE（对于分区索引，可以查看各个all_ind_partitions）`
`NUM_ROWS`	`NUMBER`		Number of rows in the index
`SAMPLE_SIZE`	`NUMBER`		Size of the sample used to analyze the index
`LAST_ANALYZED`	`DATE`		Date on which this index was most recently analyzed
`DEGREE`	`VARCHAR2(40)`		Number of threads per instance for scanning the index, or `DEFAULT`
`INSTANCES`	`VARCHAR2(40)`		Number of instances across which the indexes to be scanned, or `DEFAULT`
`PARTITIONED`	`VARCHAR2(3)`		Indicates whether the index is partitioned (`YES`) or not (`NO`)
`TEMPORARY`	`VARCHAR2(1)`		Indicates whether the index is on a temporary table (`Y`) or not (`N`)
`GENERATED`	`VARCHAR2(1)`		Indicates whether the name of the index is system-generated (`Y`) or not (`N`)
`SECONDARY`	`VARCHAR2(1)`		Indicates whether the index is a secondary object created by the `ODCIIndexCreate` method of the Oracle Data Cartridge (`Y`) or not (`N`)
`BUFFER_POOL`	`VARCHAR2(7)`		Buffer pool to be used for index blocks: `DEFAULT` `KEEP` `RECYCLE` NULL
`FLASH_CACHE`	`VARCHAR2(7)`		Database Smart Flash Cache hint to be used for index blocks: `DEFAULT` `KEEP` `NONE` Solaris and Oracle Linux functionality only.
`CELL_FLASH_CACHE`	`VARCHAR2(7)`		Cell flash cache hint to be used for index blocks: `DEFAULT` `KEEP` `NONE` See Also: Oracle Exadata Storage Server Software documentation for more information
`USER_STATS`	`VARCHAR2(3)`		Indicates whether statistics were entered directly by the user (`YES`) or not (`NO`)
`DURATION`	`VARCHAR2(15)`		Indicates the duration of a temporary table: `SYS$SESSION` - Rows are preserved for the duration of the session `SYS$TRANSACTION` - Rows are deleted after `COMMIT` NNUL - Permanent table
`PCT_DIRECT_ACCESS`	`NUMBER`		For a secondary index on an index-organized table, the percentage of rows with `VALID` guess（对于无效值较多的索引，可以使用alter index i2 update block references来更新索引结构）
`ITYP_OWNER`	`VARCHAR2(30)`		For a domain index, the owner of the indextype
`ITYP_NAME`	`VARCHAR2(30)`		For a domain index, the name of the indextype
`PARAMETERS`	`VARCHAR2(1000)`		For a domain index, the parameter string
`GLOBAL_STATS`	`VARCHAR2(3)`		For partitioned indexes, indicates whether statistics were collected by analyzing the index as a whole (`YES`) or were estimated from statistics on underlying index partitions and subpartitions (`NO`)（当使用analyze时，oracle会统计分区或者子分区的统计信息，然后依据这些信息计算出分区或者全局的统计信息，因此其结构有时是不正确的,此时取值为NO；DBMS_STATS默认会直接统计全局的统计信息，此时取值为YES,如果我们设置DBMS_STATS的granularity=>'PARTITION'，此时该字段的取值亦为NO）
`DOMIDX_STATUS`	`VARCHAR2(12)`		Status of a domain index: NULL - Index is not a domain index `VALID` - Index is a valid domain index `IDXTYP_INVLD` - Indextype of the domain index is invalid
`DOMIDX_OPSTATUS`	`VARCHAR2(6)`		Status of the operation on a domain index: NULL - Index is not a domain index `VALID` - Operation performed without errors `FAILED` - Operation failed with an error
`FUNCIDX_STATUS`	`VARCHAR2(8)`		Status of a function-based index: NULL - Index is not a function-based index `ENABLED` - Function-based index is enabled `DISABLED` - Function-based index is disabled
`JOIN_INDEX`	`VARCHAR2(3)`		Indicates whether the index is a join index (`YES`) or not (`NO`)
`IOT_REDUNDANT_PKEY_ELIM`	`VARCHAR2(3)`		Indicates whether redundant primary key columns are eliminated from secondary indexes on index-organized tables (`YES`) or not (`NO`)
`DROPPED`	`VARCHAR2(3)`		Indicates whether the index has been dropped and is in the recycle bin (`YES`) or not (`NO`); NULL for partitioned tables
`VISIBILITY`	`VARCHAR2(10)`		Indicates whether the index is `VISIBLE` or `INVISIBLE` to the optimizer（不可见的索引仅仅会导致优化器生成执行计划时忽略该索引，其他方面与正常索引一致。可以通过OPTIMIZER_USE_INVISIBLE_INDEXES参数来控制优化器是否可以利用肺可见索引）
`DOMIDX_MANAGEMENT`	`VARCHAR2(14)`		If this is a domain index, indicates whether the domain index is system-managed (`SYSTEM_MANAGED`) or user-managed (`USER_MANAGED`)
`SEGMENT_CREATED`	`VARCHAR2(3)`		Indicates whether the index segment has been created (`YES`) or not (`NO`)

索引组织表（ 请注意IOT_TYPE,IOT_NAME,INDEX_TYPE字段的取值）：

SQL> create table t1(c1 number,c2 number,c3 varchar2(20),constraint iot1 primary key(c1)) organization index pctthreshold 10 overflow tablespace example;

表已创建。


SQL> select table_name,tablespace_name,iot_type,iot_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME                IOT_TYPE     IOT_NAME
------------------------------ ------------------------------ ------------ ------------------------------
SYS_IOT_OVER_364758            EXAMPLE                        IOT_OVERFLOW T1
T1                                                            IOT

SQL> select index_name,index_type,pct_threshold,tablespace_name from user_indexes;

INDEX_NAME                     INDEX_TYPE                  PCT_THRESHOLD TABLESPACE_NAME
------------------------------ --------------------------- ------------- ------------------------------
IOT1                           IOT - TOP                              10 TS1

include_column:

在默认情况下，如果索引组织表中指定了overflow子句，非主键列会存储到overflow段中存储，那么到底会有哪些列存储到overflow段中那？

SQL> create table iot_over_inc(c1 varchar2(1000),c2 varchar2(1000),c3 varchar2(1000),c4 varchar2(1000),constraint pk primary key(c1,c2)) organization index pctthreshold 5 overflow;
create table iot_over_inc(c1 varchar2(1000),c2 varchar2(1000),c3 varchar2(1000),c4 varchar2(1000),constraint pk primary key(c1,c2)) organization index pctthreshold 5 overflow
*
第 1 行出现错误:
--ORA-01450: 超出最大的关键字长度 (309)


SQL> create table iot_over_inc(c1 varchar2(400),c2 varchar2(1000),c3 varchar2(1000),c4 varchar2(1000),constraint pk primary key(c1)) organization index pctthreshold 5 overflow;
create table iot_over_inc(c1 varchar2(400),c2 varchar2(1000),c3 varchar2(1000),c4 varchar2(1000),constraint pk primary key(c1)) organization index pctthreshold 5 overflow
*
第 1 行出现错误:
--ORA-01450: 超出最大的关键字长度 (309)


SQL> create table iot_over_inc(c1 varchar2(300),c2 varchar2(1000),c3 varchar2(1000),c4 varchar2(1000),constraint pk primary key(c1)) organization index pctthreshold 5 overflow;

表已创建。

SQL> insert into iot_over_inc values(1,dbms_random.string('u',20),dbms_random.string('u',20),dbms_random.string('u',20));

已创建 1 行。

SQL> insert into iot_over_inc values(2,dbms_random.string('u',20),dbms_random.string('u',20),dbms_random.string('u',400));

已创建 1 行。

SQL> insert into iot_over_inc values(3,dbms_random.string('u',400),dbms_random.string('u',20),dbms_random.string('u',40));

已创建 1 行。

SQL> alter table iot_over_inc including c2;

表已更改。

SQL> insert into iot_over_inc values(4,dbms_random.string('u',20),dbms_random.string('u',20),dbms_random.string('u',400));

已创建 1 行。

SQL> insert into iot_over_inc values(5,dbms_random.string('u',20),dbms_random.string('u',20),dbms_random.string('u',40));

已创建 1 行。

SQL> insert into iot_over_inc values(6,dbms_random.string('u',400),dbms_random.string('u',20),dbms_random.string('u',40));

已创建 1 行。

查看索引块的数据结构：

row#0[7962] flag: K-----, lock: 0, len=70
col 0; len 1; (1):  31
tl: 66 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [20]  54 50 46 52 56 4d 4a 53 4c 42 42 58 47 47 4c 4d 52 4c 4e 59
col  1: [20]  4a 43 4a 59 50 42 54 56 45 42 4f 57 4b 4d 4b 4f 4e 4f 4b 4c
col  2: [20]  50 57 59 44 4c 4a 4c 4c 53 52 49 54 58 41 47 4a 52 59 4d 41
row#1[7907] flag: K-----, lock: 0, len=55
col 0; len 1; (1):  32
tl: 51 fb: --H-F--- lb: 0x0  cc: 2
nrid:  0x0b80000a.0
col  0: [20]  4d 45 52 44 50 54 51 5a 46 59 56 55 50 4e 5a 4d 4d 4b 50 4f
col  1: [20]  49 4c 4d 54 49 41 43 57 48 44 46 55 54 58 56 52 4b 49 51 52
row#2[7894] flag: K-----, lock: 0, len=13
col 0; len 1; (1):  33
tl: 9 fb: --H-F--- lb: 0x0  cc: 0
nrid:  0x0b80000a.1
row#3[7860] flag: K-----, lock: 2, len=34
col 0; len 1; (1):  34
tl: 30 fb: --H-F--- lb: 0x0  cc: 1
nrid:  0x0b80000a.2
col  0: [20]  59 4c 56 44 4e 52 47 42 49 51 52 4e 4a 51 54 44 41 43 44 57
row#4[7826] flag: K-----, lock: 2, len=34
col 0; len 1; (1):  35
tl: 30 fb: --H-F--- lb: 0x0  cc: 1
nrid:  0x0b80000a.3
col  0: [20]  5a 4a 50 57 41 52 4b 55 4b 4f 48 58 53 5a 53 52 41 4b 52 56
row#5[7813] flag: K-----, lock: 2, len=13
col 0; len 1; (1):  36
tl: 9 fb: --H-F--- lb: 0x0  cc: 0
nrid:  0x0b80000a.4
----- end of leaf block dump -----
End dump data blocks tsn: 32 file#: 46 minblk 18 maxblk 18

综上，可以得出如下结论：

在索引组织表中，主键必须存储在索引结构中，即主键列的大小不可以超过pctthreshold的值。
在没有指定including子句的情况下，oracle会仅可能的将更多的列存储在iot-top段中，无法放在iot-top段中的内容存储在overflow段中。
在指定了including子句的情况下，oracle会尽可能的将从column_id小于等于including子句指定的列存放在iot-top段中，而其他的字段无条件存放在overflow段中
通过incluing子句指定的字段，在all_indexes视图的includ_column列中通过column id 的形式标出

VISIBILITY：

索引的可见性，可以用来测试删除某一索引可能带来的影响，也可以实现某些其他作用，例如：通过OPTIMIZER_USE_INVISIBLE_INDEXES参数，使得部分程序可以利用索引，而其他程序不可以利用索引。