oracle中与索引相关的视图---all_indexes - 数据库编程

DANT_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

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