[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 */ 表示索引建立透過約束建立。