1、分别用两种方法创建主键
createtabletest1(idnumber,namevarchar2(10));
insertintotest1values(1,'t1');
insertintotest1values(2,'t2');
commit;
altertabletest1addconstraintpk_test1primarykey(id);
createtabletest2(idnumber,namevarchar2(10));
insertintotest2values(3,'t1');
insertintotest2values(4,'t2');
commit;
createuniqueindexpk_test2
ontest2(id);
altertabletest2addconstraintpk_test2primarykey(id);
1)查看约束及索引
selecttable_name,constraint_name,constraint_type,index_namefromuser_constraints
ucwhereuc.table_namein('TEST1','TEST2');
TEST1PK_TEST1PPK_TEST1
TEST2PK_TEST2PPK_TEST2
证明目前两个表上都有一个主键约束,而且都有一个索引。
selecttable_name,index_name,index_type,uniquenessfromuser_indexes uiwhereui.table_namein('TEST1','TEST2');
TEST1PK_TEST1NORMALUNIQUE
TEST2PK_TEST2NORMALUNIQUE
两个索引也都是唯一索引。
此时似乎很难区分两个索引的区别。
2、区别:
1)高可用分析:第一种情况下是Oracle在创建主键约束的自动创建索引,后一种是手动创建索引,然后再基于此唯一索引创建主键约束。在并发事务量较高的情况下,后一种情况可以先以online方式创建索引,减少表的读写阻塞。
2)删除方式不同:第一种情况可通过删除约束进而删除索引。
altertabletest1dropconstraintpk_test1;
altertabletest2dropconstraintpk_test2;
selecttable_name,index_name,index_type,uniqueness
fromuser_indexes uiwhereui.table_namein('TEST1','TEST2');
1TEST2PK_TEST2NORMALUNIQUE
发现删除约束并不能删除通过第二种方式建的索引,Oracle提供添加drop
index。
altertabletest2dropconstraintpk_test2dropindex
3)区分存在的主键的创建方式
可通过查询sys.ind$视图的PROPERTY字段获得,此字段定义可通过$ORACLE_HOME/rdbms/admin/sql.bsp
propertynumber not null,/*
immutable flags for life of the index */
/* unique : 0x01 */
/* partitioned : 0x02 */
/* reverse : 0x04 */
/* compressed : 0x08 */
/* functional : 0x10 */
/* temporary table index: 0x20 */
/* session-specific temporary table index: 0x40 */
/* index on embedded adt: 0x80 */
/* user said to check max length at runtime: 0x0100 */
/* domain index on IOT: 0x0200 */
/* join index : 0x0400 */
/* functional index expr contains a PL/SQL function : 0x0800 */
/* The index was created by a constraint : 0x1000 */
/* The index was created by create MV : 0x2000 */
索引类型分别用对应的16进制来表示,而property存储的是十进制,可通过进制转换获得索引的真正类型。
selectsi.PROPERTY,ui.index_name
fromsys.ind$si,user_indexes ui,user_objects uo
wheresi.obj#=uo.OBJECT_ID
andui.index_name=uo.OBJECT_NAME
andui.index_namein('PK_TEST1','PK_TEST2')
PK_TEST14097
PK_TEST21