[索引相关] 唯一索引 与 非唯一索引

-- 非唯一索引是允许存在重复的索引键值，根据索引键查询可能得到多行，默认创建的索引为非唯一索引
-- 唯一索引则不允许创建重复的索引键值，根据索引键查询只能得到单行
-- 主键约束，唯一键约束创建隐藏索引均为非唯一键索引

go@DEMO> create table t (a number);

Table created.

-- 创建索引
go@DEMO> create index idx_t on t (a);

Index created.

-- 默认为非唯一索引
go@DEMO> select index_name,index_type,uniqueness from user_indexes where table_name='T';
INDEX_NAME     INDEX_TYPE     UNIQUENESS
----------     ----------     -------------
IDX_T          NORMAL         NONUNIQUE

1 row selected.

go@DEMO> select index_name,index_type,uniqueness from user_indexes where table_name='T';
INDEX_NAME     INDEX_TYPE    UNIQUENESS
----------     ----------    -------------
IDX_T          NORMAL        NONUNIQUE

1 row selected.

go@DEMO> insert into t values (1);

1 row created.

go@DEMO> insert into t values (1);

1 row created.

go@DEMO> commit;

Commit complete.

-- 允许插入索引键的重复值
go@DEMO> select * from t;
         A
----------
         1
         1

2 rows selected.

go@DEMO> analyze index idx_t validate structure;

Index analyzed.

go@DEMO> select name,lf_rows from index_stats;
NAME                                        LF_ROWS
---------------------------------------- ----------
IDX_T                                             2

1 row selected.

go@DEMO> drop table t purge;

Table dropped.

go@DEMO> create table t (a number);

Table created.

go@DEMO> create unique index idx_t on t(a);

Index created.

go@DEMO> select index_name,index_type,uniqueness from user_indexes where table_name='T';
INDEX_NAME    INDEX_TYPE    UNIQUENESS
----------    ----------    -----------
IDX_T         NORMAL        UNIQUE

1 row selected.

go@DEMO> insert into t values (1);

1 row created.

-- 不允许插如重复的索引键值
go@DEMO> insert into t values (1);
insert into t values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (GO.IDX_T) violated

go@DEMO> commit;

Commit complete.

go@DEMO> select * from t;
         A
----------
         1

1 row selected.

go@DEMO> analyze index idx_t validate structure;

Index analyzed.

go@DEMO> select name,lf_rows from index_stats;
NAME                                        LF_ROWS
---------------------------------------- ----------
IDX_T                                             1

1 row selected.