估算B树索引大小、B树索引外键列

1、估算索引的大小

SQL> set serveroutput on;
SQL> exec dbms_stats.gather_table_stats(user,'t1');

PL/SQL 过程已成功完成。

SQL> variable used_bytes number;
SQL> variable alloc_bytes number;
SQL> exec dbms_space.create_index_cost('create index idx_t1_v2 on t1(v2)',:used_bytes,:alloc_bytes);

PL/SQL 过程已成功完成。

SQL> print :used_bytes;

USED_BYTES
----------
   3145728       --索引数据需要多少空间

SQL> print :alloc_bytes;

ALLOC_BYTES
-----------
   25165824     --将在表空间内分配多大的空间

SQL> create index idx_t1_v2 on t1(v2);

索引已创建。

SQL> select bytes from user_segments where segment_name='IDX_T1_V2';

     BYTES
----------
  17825792      --索引大小

2、显示创建索引代码

SQL> set long 1000000;
SQL> select dbms_metadata.get_ddl('INDEX','IDX_T1_V2') from dual;  

DBMS_METADATA.GET_DDL('INDEX','IDX_T1_V2')
--------------------------------------------------------------------------------

  CREATE INDEX "U1"."IDX_T1_V2" ON "U1"."T1" ("V2")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_
CACHE DEFAULT)
  TABLESPACE "USERS"



SQL> select dbms_metadata.get_ddl('INDEX',index_name) from user_indexes;

DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME)
--------------------------------------------------------------------------------

  CREATE INDEX "U1"."IDX_T1_V2" ON "U1"."T1" ("V2")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_
CACHE DEFAULT)
  TABLESPACE "USERS"

3、索引外键列

外键约束确保插入数据到子表时，相应的父表记录存在。不同于主键和唯一键约束。oracle不会自动创建外键列上的索引。因此，必须在定义为外键约束的列的基础上手动创建一个外键索引。
oracle经常可以利用外键列上的索引，来改善使用外键列来连接父表和子表的查询性能。
如果外键列上没有B树索引存在，在往子表插入数据或从子表删除数据时，它会锁定父表中的所有行。对于频繁修改父表和子表的应用程序，这将导致锁定和死锁问题。

SQL> create table emp(emp_id number primary key,dept_id number);

表已创建。

SQL> create table dept(dept_id number primary key);

表已创建。

SQL> alter table emp add constraint emp_fk1 foreign key (dept_id) references dept(dept_id);

表已更改。

SQL> insert into dept values(10);

已创建 1 行。

SQL> insert into dept values(20);

已创建 1 行。

SQL> insert into dept values(30);

已创建 1 行。

SQL> insert into emp values(1,10);

已创建 1 行。

SQL> insert into emp values(2,20);

已创建 1 行。

SQL> insert into emp values(3,30);

已创建 1 行。

SQL> commit;

提交完成。

在一个会话中从子表删除记录但不提交：

SQL> select sid from v$mystat where rownum=1;

       SID
----------
        25

SQL> delete from emp where dept_id = 10;

已删除 1 行。

在另一个会话中从父表中删除一些不受影响的数据会被阻塞：

SQL> select sid from v$mystat where rownum=1;

       SID
----------
       146

SQL> 
SQL> delete from dept where dept_id = 30;

可以看见会话25锁住了两个对象：

SQL> select p.spid,
  2         c.object_name,
  3         c.subobject_name,
  4         b.session_id,
  5         b.oracle_username,
  6         b.os_user_name
  7    from gv$process p, gv$session a, gv$locked_object b, all_objects c
  8   where p.addr = a.paddr
  9     and a.process = b.process
 10     and c.object_id = b.object_id
 11     and object_name != 'OBJ$';

SPID                     OBJECT_NAME                    SUBOBJECT_NAME                 SESSION_ID ORACLE_USERNAME                OS_USER_NAME
------------------------ ------------------------------ ------------------------------ ---------- ------------------------------ ------------------------------
452                      EMP                                                                   25 U1                             oracle
452                      DEPT                                                                  25 U1                             oracle

SQL> select * from v$locked_object; 

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME                OS_USER_NAME                   PROCESS                  LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------ -----------
         9         19       2432     102700         25 U1                             oracle                         451                                3
         9         19       2432     102698         25 U1                             oracle                         451                                3
         0          0          0     102698        146 U1                             oracle                         488                                0

如果你认为某个子表会由于外键未加索引而被锁住，而且希望证明这一点，可以发出以下命令：

u1@ORCL> alter table emp disable table lock;

表已更改。

u1@ORCL> delete from dept where dept_id = 30;
delete from dept where dept_id = 30
            *
第 1 行出现错误:
ORA-00069: 无法获得锁 -- EMP 禁用了表锁定


u1@ORCL> alter table emp enable table lock;

表已更改。

u1@ORCL> delete from dept where dept_id = 30;
delete from dept where dept_id = 30
*
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (U1.EMP_FK1) - 已找到子记录