SQL表索引创建/删除/修改/查询

-- SQL表索引创建/删除/修改/查询

-- Oracle索引创建
-- 索引的基本信息，存储在下面视图中：all_indexes / dba_indexes / user_indexes
-- 索引对应的字段信息，存储再下面视图中：all_ind_columns / dba_ind_columns / user_ind_columns
-- 创建索引的基本语法：
语法
CREATE INDEX
CREATE [unique] INDEX [user.]index
ON [user.]table (column [ASC | DESC] [,column
[ASC | DESC] ] ... )
[CLUSTER [scheam.]cluster]
[INITRANS n]
[MAXTRANS n]
[PCTFREE n]
[STORAGE storage]
[TABLESPACE tablespace]
[NO SORT]
Advanced

常用举例：

-- Oracle：创建表的单列唯一索引
CREATE UNIQUE INDEX idx_table01_name ON MY_TEST_TABLE01('COL_NAME');

-- Oracle：创建表的联合唯一索引
CREATE UNIQUE INDEX idx_table01_name_class ON MY_TEST_TABLE01('COL_NAME', 'COL_CLASS');

-- Oracle：创建表的单列普通索引
CREATE INDEX idx_table01_name ON MY_TEST_TABLE01('COL_NAME');

-- Oracle：创建表的联合普通索引
CREATE INDEX idx_table01_name_class ON MY_TEST_TABLE01('COL_NAME', 'COL_CLASS');

-- Oracle索引删除
-- 语法：DROP INDEX [schema.]indexname;
DROP INDEX idx_tb01_name;

-- Oracle：修改索引
ALTER [UNIQUE] INDEX [user.]index
[INITRANS n]
[MAXTRANS n] 
REBUILD 
[STORAGE n]

-- Oracle：修改条添加表的单主键约束
ALTER TABLE MY_TEST_TABLE01 ADD CONSTRAINT pk_table01_id PRIMARY KEY(ID);

-- Oracle：修改条添加表的联合主键约束
ALTER TABLE MY_TEST_TABLE01 ADD CONSTRAINT pk_table01_name_class PRIMARY KEY(COL_NAME,COL_CLASS);

-- Oracle：删除表的主键约束
ALTER TABLE MY_TEST_TABLE01 DROP CONSTRAINT pk_table01_name;

-- 如果删除失败，可以尝试：
ALTER TABLE MY_TEST_TABLE01 DROP CONSTRAINTS COLUMN CASCADE; --删除约束
ALTER TABLE MY_TEST_TABLE01 DISABLE primary_column ; --设置被设置为主键的列为无效
DROP INDEX index_name; --删除主键索引

-- Oracle：查看指定表的索引（UNIQUENESS INDEX_NAME TABLE_NAME COLUMN_NAME，联合索引会分多行显示）
SELECT b.uniqueness, a.index_name, a.table_name, a.column_name 
FROM all_ind_columns a, all_indexes b
WHERE a.index_name=b.index_name 
AND a.table_name = upper('MY_TEST_TABLE01')
ORDER BY a.table_name, a.index_name, a.column_position;

-- Oracle：查看指定表的索引（联合索引只显示一行记录）
SELECT * FROM user_indexes WHERE table_name=upper('MY_TEST_TABLE01');
SELECT * FROM all_indexes  WHERE table_name=upper('MY_TEST_TABLE01');

-- Oracle：查看素有索引
SELECT * FROM user_indexes;
SELECT * FROM all_indexes;