Oracle数据库索引

--创建索引

CREATE INDEX IDX_ZSQ ON ZSQ01(ID);

--将索引置为无效
ALTER INDEX   IDX_ZSQ   UNUSABLE;

--重新启用索引(三种方式)

ALTER INDEX   IDX_ZSQ   REBUILD;

ALTER INDEX   IDX_ZSQ   REBUILD NOLOGGING;

ALTER INDEX   IDX_ZSQ   REBUILD ONLINE NOLOGGING;

--查询索引当前状态

SELECT * FROM User_Ind_Partitions a WHERE a.index_name='IDX_ZSQ' ;

--查询表索引（两种方式）

SELECT * FROM User_Ind_Columns WHERE table_name = UPPER('ZSQ01');

select i.index_name,
       i.index_type,
       i.table_owner,
       i.table_name,
       i.uniqueness,
       i.tablespace_name,
       i.status,
       c.column_name,
       c.column_position,
       c.column_length
from user_indexes i, user_ind_columns c
where i.index_name = c.index_name
and c.table_name = 'ZSQ01'
and i.index_name = 'IDX_ZSQ'

and i.uniqueness <> 'UNIQUE';

--生成“置psr索引无效”语句
SELECT DISTINCT 'ALTER INDEX ' AS f, i.index_name,' UNUSABLE; ' AS a FROM user_indexes i,user_ind_columns b
WHERE i.index_name=b.INDEX_NAME AND b.table_name = 'ZSQ01' AND i.uniqueness <> 'UNIQUE';
--生成“置psr索引可用”语句
SELECT DISTINCT 'ALTER INDEX ' AS f, i.index_name,' REBUILD; ' AS a FROM user_indexes i,user_ind_columns b
WHERE i.index_name=b.INDEX_NAME AND b.table_name = 'ZSQ01' AND i.uniqueness <> 'UNIQUE';