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) - 已找到子记录