[20170427]唯一索引与约束注意的地方.txt
--//昨天看书Apress.Expert.Oracle.Indexing.and.Access.Paths.Maximum.Performance.for.Your.Database.2nd.Edition.148421983X.pdf
--//Creating Only a Unique Index P60,提到建立唯一索引与约束需要注意的地方,做一个例子说明:
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table t as select rownum id,'test' name from dual connect by level<=10;
Table created.
SCOTT@book> create unique index i_t_id on t(id);
Index created.
2.测试:
SCOTT@book> insert into t values (10,'aaa');
insert into t values (10,'aaa')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.I_T_ID) violated
--//存在冲突,注意提示,ORA-00001: unique constraint (SCOTT.I_T_ID) violated.
SCOTT@book> select constraint_name from user_constraints where constraint_name='I_T_ID';
no rows selected
--//并不存在这样的约束.
SCOTT@book> select index_name, uniqueness from user_indexes where index_name='I_T_ID';
INDEX_NAME UNIQUENES
------------------------------ ---------
I_T_ID UNIQUE
SCOTT@book> drop index i_t_id ;
Index dropped.
SCOTT@book> alter table t add constraint i_t_id unique (id) enable validate;
Table altered.
--//这两种方式最大的不同,查询sys.ind$的PROPERTY=4097.而建立唯一索引的PROPERTY=1.
SELECT *
FROM sys.ind$
WHERE obj# IN (SELECT object_id
FROM dba_objects
WHERE owner = USER AND OBJECT_name = 'I_T_ID');
Record View
As of: 2017/4/27 8:55:34
OBJ#: 91658
DATAOBJ#: 91658
TS#: 4
FILE#: 4
BLOCK#: 554
BO#: 91655
INDMETHOD#: 0
COLS: 1
PCTFREE$: 10
INITRANS: 2
MAXTRANS: 255
PCTTHRES$:
TYPE#: 1
FLAGS: 2050
PROPERTY: 4097
BLEVEL: 0
LEAFCNT: 1
DISTKEY: 10
LBLKKEY: 1
DBLKKEY: 1
CLUFAC: 1
ANALYZETIME: 2017/4/27 8:53:24
SAMPLESIZE: 10
ROWCNT: 10
INTCOLS: 1
DEGREE:
INSTANCES:
TRUNCCNT:
SPARE1: 1
SPARE2:
SPARE3:
SPARE4:
SPARE5:
SPARE6: 2017/4/27 0:53:24
--//如果查询 /u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/dcore.bsq
property number 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 */
/* system managed domain index : 0x0800 */
/* The index was created by a constraint : 0x1000 */
/* The index was created by create MV : 0x2000 */
/* composite domain index : 0x8000 */
/* The following columns are used for index statistics such
* as # btree levels, # btree leaf blocks, # distinct keys,
* # distinct values of first key column, average # leaf blocks per key,
* clustering info, and # blocks in index segment.
*/
SCOTT@book> @ &r/10to16 4097
10 to 16 HEX REVERSE16
-------------- ------------------
0000000001001 0x01100000
--//第4位为1表示/* The index was created by a constraint : 0x1000 */.