[20170427]唯一索引与约束注意的地方.txt

最新推荐文章于 2024-07-11 21:23:24 发布

cuixun7780

最新推荐文章于 2024-07-11 21:23:24 发布

阅读量101

点赞数

[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.
   */