Oracle索引

dba_indexes

当前索引状态：
SQL> select OWNER,INDEX_NAME,STATUS from dba_indexes where INDEX_NAME='&1';

索引有效性查询：
（1）select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS from dba_indexes where INDEX_NAME='&1';
（2）select sum(bytes)/1024/1024 mb from dba_segments where segment_name=upper('&1');

索引查询：
select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS from dba_indexes where INDEX_NAME='&1';
select owner,object_name,status,object_type,created from dba_objects where object_name in ('&1');
col TABLE_OWNER for a15
col TABLE_NAME for a25
col INDEX_NAME for a30
col COLUMN_NAME for a25
select TABLE_OWNER,table_name,index_name,column_name,column_position from dba_ind_columns where table_name=upper('&table_name') order by 3,5;
select INDEX_owner,INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME from dba_ind_columns where table_owner=upper('&1') and table_name=upper('&2') and INDEX_NAME='&3';

---

1：非分区全局索引查询
select table_owner,table_name,owner index_owner,index_name,status,partitioned from dba_indexes where status !='N/A' and table_owner='&1' and table_name='&2';

2：一级本地分区索引和全局分区索引查询
select i.table_owner,i.table_name,t.index_owner,t.index_name,t.status,g.locality,t.partition_name 
from dba_ind_partitions t,dba_indexes i,dba_part_indexes g 
where status !='N/A' 
and i.table_owner='&1' 
and i.table_name='&2' 
and i.owner=t.index_owner 
and i.index_name=t.index_name 
and g.owner=i.owner 
and g.index_name=i.index_name 
and g.table_name=i.table_name 
order by index_name,partition_name;

3：二级分区索引查询--一定是LOCAL的
select i.table_owner,i.table_name,t.index_owner,t.index_name,t.partition_name,t.subpartition_name,t.status 
from dba_ind_subpartitions t,dba_indexes i 
where status !='N/A' 
and i.table_owner='&1' 
and i.table_name='&2' 
and i.owner=t.index_owner 
and i.index_name=t.index_name 
order by index_name,partition_name,subpartition_name;

---

分区表索引失效查询
select t.index_owner,t.status ,t.index_name,g.locality,t.partition_name,t.subpartition_count
from dba_ind_partitions t,dba_indexes i,dba_part_indexes g
where i.table_owner='CHANNEL' and i.table_name='RPT_COGNOS_U11_COMMON'
and i.owner=t.index_owner and i.index_name=t.index_name
and g.owner=i.owner and g.index_name=i.index_name and g.table_name=i.table_name
order by index_name,partition_name;

select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,STATUS from dba_indexes where INDEX_NAME='&1';
select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,STATUS from dba_ind_partitions where index_name='&1' and status not in ('USABLE','N/A');

---

新建索引
create index owner_name.index_name on owner_name.table_name (column) online ;
ALTER TABLE 'table_name' ADD INDEX index_name('column');

例1：
create index owner_name.index_name on owner_name.table_name (column) parallel 4 online;
alter index owner_name.index_name noparallel;

例2：
create index owner_name.index_name on owner_name.table_name (column)
tablespace tablespace_name
initrans 2
maxtrans 255
storage
(
  initial 64K
  next 1M
  minextents 1
  maxextents unlimited
) online ;

例3：
create index owner_name.index_name on owner_name.table_name(column) tablespace tablespace_name;

---

失效索引重建
select 'alter index ' || index_owner || '.' ||index_name ||' rebuild partition ' || PARTITION_NAME || ' ;' from dba_ind_partitions where index_owner='&1' and index_name='&2' and status ='UNUSABLE';

---

失效索引修复
set pagesize 0
set long 90000
set feedback off
set echo off
select dbms_metadata.get_ddl('TABLE','tablenameBB','ownernameAA') from dual;

  CREATE TABLE "ownernameAA"."tablenameBB"
   (    "LOGID" NUMBER(20,0) NOT NULL ENABLE,
        ......
         CONSTRAINT "indexnameCC" PRIMARY KEY ("LOGID")
        ....... )

SQL> alter table ownernameAA.tablenameBB drop CONSTRAINT indexnameCC;
SQL> create index ownernameAA.indexnameCC on ownernameAA.tablenameBB (logid) online parallel 8;

ORA-01502: 索引或这类索引的分区处于不可用状态

原因：出现这个问题，可能有人move过表，或者disable过索引
1. alter table xxxxxx move tablespace xxxxxxx 命令后，索引就会失效。
2. alter index index_name  unusable，命令使索引失效。

解决办法：
1. 重建索引才是解决这类问题的完全的方法。
   alter index index_name rebuild (online);
或者
   alter index index_name rebuild;
2. 如果是分区索引只需要重建那个失效的分区
   alter index index_name rebuild partition partition_name (online);
或者
   alter index index_name rebuild partition partition_name ;
3. 或者改变当前索引的名字。

说明：
1. alter session set skip_unusable_indexes=true;就可以在session级别跳过无效索引作查询。
2. 分区索引应适用user_ind_partitions。
3. 状态分4种：
    N/A       说明这个是分区索引需要查user_ind_partitions或者user_ind_subpartitions来确定每个分区是否可用；
    VAILD     说明这个索引可用；
    UNUSABLE  说明这个索引不可用；
    USABLE    说明这个索引的分区是可用的。
4. 查询当前索引的状态：select distinct status from user_indexes;
5. 查询那个索引无效：select index_name from  user_indexes where status <> 'VALID';
6. 批量rebuild下：select 'alter index '||index_name||' rebuild online;' from  user_indexes where status <> 'VALID' and index_name not like'%$$'; 

---

SQL> create index ownernameAA.indexnameBB on ownernameAA.tablenameCC (CUSTOMER_ID_CODE, CUSTOMER_NAME) online;                                             *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

分析原因：
ORA-01450: maximum key length (3215) exceeded
9i之后每个index key最大可以为block size的80%,所以理论上来说，是可以创建最大长度为block size=8096*80%约为6400左右长度的index,但因为online创建的过程中会生成一个中间表，用来记录创建过程中的变化，而这个表是IOT表。经过测试，发现IOT表的限制比较严格，8k的block size，最大长度只能有3215，所以普通创建可以成功，而online创建则不行。

检查确认：
CUSTOMER_ID_CODE         VARCHAR2(300)
CUSTOMER_NAME            VARCHAR2(3000)
加起来超过3215，符合分析情况；

问题处理：
1.检查当前数据库会话情况
2.当前数据库内对该表无访问，去掉online参数，采用普通创建索引方式，继续添加索引，成功
SQL> create index ownernameAA.indexnameBB on ownernameAA.tablenameCC (CUSTOMER_ID_CODE, CUSTOMER_NAME);
Index created.
3.检查当前索引情况
SQL> select OWNER,INDEX_NAME,TABLE_NAME,STATUS,INCLUDE_COLUMN from dba_indexes where TABLE_NAME='tablenameCC';