[20210520]关于主键索引问题.txt

[20210520]关于主键索引问题.txt --//链接问的问题：http://www.itpub.net/thread-2141807-1-1.html ORACLE 11204 1  先创建个B树索引，然后再对该索引字段创建主键约束，此时，主键约束会自动使用之前创建好的B树索引， 2  或者，若该字段没有B树索引，在对该字段创建主键约束时，ORACLE自动以该字段创建一个唯一性的B树索引。 问：哪个数据字典能够识别出，该索引是事先创建的，还是创建主键约束时自动创建的？ --//我记忆里有一个标识记录这样的情况。测试看看。 1.环境： SCOTT@book> @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 create table t1 (id number ,name varchar2(20) , constraint  pk_t1 primary key(id)); create table t2 (id number ,name varchar2(20)); create unique index pk_t2 on t2(id); alter table t2 add constraint pk_t2 primary key (id) ; --//分析表略。 2.测试： SCOTT@book> select object_name,object_id,data_object_id,object_type from dba_objects where owner='SCOTT' and object_name in ('PK_T1','PK_T2','T1','T2') order by 2; OBJECT_NAME           OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE -------------------- ---------- -------------- ------------------- T1                        92621          92621 TABLE PK_T1                     92622          92622 INDEX T2                        92623          92623 TABLE PK_T2                     92624          92624 INDEX SELECT *   FROM sys.ind$  WHERE (obj#, dataobj#) IN (SELECT OBJ#, dataobj#                               FROM sys.obj$                              WHERE name LIKE 'PK_T%'); SCOTT@book> @ prxx ============================== OBJ#                          : 92624 DATAOBJ#                      : 92624 TS#                           : 4 FILE#                         : 0 BLOCK#                        : 0 BO#                           : 92623 INDMETHOD#                    : 0 COLS                          : 1 PCTFREE$                      : 10 INITRANS                      : 2 MAXTRANS                      : 255 PCTTHRES$                     : TYPE#                         : 1 FLAGS                         : 67110914 PROPERTY                      : 1 BLEVEL                        : 0 LEAFCNT                       : 0 DISTKEY                       : 0 LBLKKEY                       : 0 DBLKKEY                       : 0 CLUFAC                        : 0 ANALYZETIME                   : 2021-05-20 09:14:26 SAMPLESIZE                    : 0 ROWCNT                        : 0 INTCOLS                       : 1 DEGREE                        : INSTANCES                     : TRUNCCNT                      : SPARE1                        : 1 SPARE2                        : SPARE3                        : SPARE4                        : SPARE5                        : SPARE6                        : 2021-05-20 01:13:27 ============================== OBJ#                          : 92622 DATAOBJ#                      : 92622 TS#                           : 4 FILE#                         : 0 BLOCK#                        : 0 BO#                           : 92621 INDMETHOD#                    : 0 COLS                          : 1 PCTFREE$                      : 10 INITRANS                      : 2 MAXTRANS                      : 255 PCTTHRES$                     : TYPE#                         : 1 FLAGS                         : 67110914 PROPERTY                      : 4097 BLEVEL                        : 0 LEAFCNT                       : 0 DISTKEY                       : 0 LBLKKEY                       : 0 DBLKKEY                       : 0 CLUFAC                        : 0 ANALYZETIME                   : 2021-05-20 09:14:26 SAMPLESIZE                    : 0 ROWCNT                        : 0 INTCOLS                       : 1 DEGREE                        : INSTANCES                     : TRUNCCNT                      : SPARE1                        : 1 SPARE2                        : SPARE3                        : SPARE4                        : SPARE5                        : SPARE6                        : 2021-05-20 01:12:39 PL/SQL procedure successfully completed. --//PROPERTY 标识不同。 --//4097 = 0x1001 --//查看/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/dcore.bsq的定义。 create table ind$ ...   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.    */ --//可以发现 /* The index was created by a constraint : 0x1000 */ 表示索引建立通过约束建立。