oracle估算一个索引所需要的空间

在创建一个索引之前，可以使用 DBMS_SPACE.CREATE_INDEX_COST 存储过程来估算所需的空间

set serveroutput on
exec dbms_stats.gather_table_stats(user,'CUST');
var used_bytes number
var alloc_bytes number
exec dbms_space.create_index_cost('create index cust_idx2 on cust(first_name)', :used_bytes,:alloc_bytes);
print :used_bytes
print :alloc_bytes

used_bytes 给出索引数据需要的空间。alloc_bytes 提供了在表空间中将分配多少空间的估计信息

 

在同样一组列上创建多个索引

在12c之前，不能在一张表中的同一组列上创建多个索引，12c之后可以。但只有当索引在某些物理属性上不同时才能这么做，比如一个B树索引，第二个是位图索引

并且 对于同一组列，只能有一条可见索引。在创建第二条索引的时候必须声明为不可见(invisible)

create        index idx1 on t(id);
create bitmap index idx2 on t(id) invisible;

 

为啥要在同一组列上创建两条索引呢？（不可见也需要维护）,设置初始化参数 alter session set optimizer_use_invisible_indexes=true(默认FALSE); 会指示优化器考虑不可见索引

　　如果刚开始创建的是B树索引，但现在需要修改为位图索引，就需要这样做，其中的想法是：将新索引创建为不可见，然后将原索引删除，再将新索引设为可见。在一个大型数据库环境中，这使得修改更快

 

不可见索引的影响

尽管索引可能对优化器不可见，但还是可以通过以下途径影响性能

1. 不可见索引会消耗有记录增加、更新或删除的表的空间和资源。这将会影响性能（使DML操作变慢）
2. 在进行基数计算时，优化器可能会考虑不可见索引（可能会改变接下来对执行计划的选择）
3. 当索引建立在外键列上时，Oracle仍然可以使用不可见索引来阻止锁定问题的发生
4. 如果创建惟一不可见索引，列上的值将强制具有唯一性，而不管该索引是否可见

 

没有数据段的索引

可以通过 NOSEGMENT 子句命令创建一个永远不会使用且不会为其分配任何盘区的索引

create        index idx1 on t(id) nosegment;

即使这个索引永远也不会使用，仍然可以通过 初始化参数： _USE_NOSEGMENT_INDEXES 来确定它是否会被优化器所使用
alter session set "_use_nosegment_indexes" = true;

 

为什么要使用 NOSEGMEMT 来创建索引？如果想要创建一个很大的索引，但并不想给它分配空间，而是要先确认优化器是否会选择使用该索引，就可以使用 NOSEGMENT 来创建索引就可以先进行测试，如果确定这个索引是有用的，可以删除该索引，然后使用不包含 NOSEGMENT 的语句重建它

 

是否所有的外键都有索引

select
    case when ind.INDEX_NAME is not null then
        case when ind.INDEX_TYPE in('BITMAP') then
            '** Bitmap idx**'
        else
            'indexed'
        end
    else
        '** Check idx **'
    end checker,
    ind.INDEX_TYPE,
    cons.owner,cons.table_name,ind.INDEX_NAME,cons.constraint_name,cons.cols
FROM (SELECT
          c.OWNER,c.TABLE_NAME,c.CONSTRAINT_NAME,
          listagg(cc.COLUMN_NAME,',') within group ( order by cc.COLUMN_NAME) cols
      FROM DBA_CONSTRAINTS c,DBA_CONS_COLUMNS cc
      WHERE c.OWNER=cc.OWNER
      AND c.OWNER = upper('&&schema')
      and c.CONSTRAINT_NAME=cc.CONSTRAINT_NAME
      and c.CONSTRAINT_TYPE = 'R'
      group by c.OWNER, c.TABLE_NAME, c.CONSTRAINT_NAME) cons
left outer join (
    select table_owner,table_name,index_name,index_type,cbr,
           listagg(column_name,',') within group ( order by column_name) cols
    from(select ic.TABLE_OWNER,ic.TABLE_NAME,ic.INDEX_NAME,ic.COLUMN_NAME,ic.COLUMN_POSITION,i.INDEX_TYPE,
                connect_by_root (ic.COLUMN_NAME) cbr
             from DBA_IND_COLUMNS ic,dba_indexes i
             where ic.TABLE_OWNER=upper('&schema')
             and ic.TABLE_OWNER=i.TABLE_OWNER
             and ic.TABLE_NAME = i.TABLE_NAME
             and ic.INDEX_NAME=i.INDEX_NAME
             connect by prior ic.COLUMN_POSITION-1 = ic.COLUMN_POSITION
             and prior ic.INDEX_NAME=ic.INDEX_NAME)
    group by table_owner, table_name,INDEX_NAME, index_type, cbr) ind
on cons.cols=ind.cols
and cons.TABLE_NAME=ind.TABLE_NAME
and cons.OWNER=ind.TABLE_OWNER
order by checker,cons.OWNER,cons.TABLE_NAME;

 

这个查询需要输入 schema ，然后显示没有对应索引的外键约束。此外还会检查索引类型，如果外键约束列上可能存在位图索引，但并不能阻止锁定问题

 

在外键上没有索引导致锁定问题的案例

create table emp(emp_id number primary key ,dept_id number);
create table dept(dept_id number primary key );
alter table emp add constraint emp_fk1 foreign key (dept_id) references dept(dept_id);

-- 插入数据
insert into dept values(10);
insert into dept values(20);
insert into dept values(30);
insert into emp values (1,10);
insert into emp values (2,20);
insert into emp values (3,30);
commit;

-- 打开两个终端。一个中删除字表的一条记录（不提交）
delete from emp where dept_id=10;
-- 然后尝试从父表中删除一些与字表中被删除数据不相关的数据行
delete from dept where dept_id=30;
-- 在字表中的删除提交前，父表中国的删除操作是被挂起的。在字表的外键列上没有正常的B树索引，每当你尝试在字表中进行插入或删除操作时，就会在父表上加上表锁。这在字表中的事物完成之前，阻止对父表的删除和更新操作

--如果在字表的外键列上创建索引
alter table EMP add constraint EMP_FK1 foreign key (DEPT_ID) references DEPT on delete set null;

-- 现在应该可以独立运行前两个删除语句了。当在外键列上有索引时，如果字表中删除记录，就不会再锁定父表中的数据行