FAQ
There has been discussion on this before on Oracle-L as I have googled and come across some of it.
I am having trouble recreating the cases for using Explicit indexes, over implicit (as shown by query three in time).

Specifically for case 1, creating and index that 'could' be used by an unique or primary key constraint. Thus expdp/impdp would drop the implicit index and use the 'could' one. I wonder if you could suggest one.

For case 2, dropping the unique or primary key constraint would drop the associated index. (I wonder if that is also true for a Foreign Key constraint that is using a unique index - Theoretical muse)


If it matters, then maybe PK,PX, UK,UX is worth it, and the standard would be expanded to create these in a certain way.

Can someone suggest an Index, or show why this behavior is not happening with my below example?


set echo off

EXAMPLE ONE


DROP TABLE IMPLICIT_TABLE;

Table dropped.

DROP TABLE EXPLICIT_TABLE;

Table dropped.


CREATE TABLE IMPLICIT_TABLE (
2 PRIM_COL number constraint IMPLICIT_TABLE_PK PRIMARY KEY
3 using index ( CREATE UNIQUE INDEX IMPLICIT_TABLE_PX ON
4 IMPLICIT_TABLE(PRIM_COL)),
5 UNIQ_COL number constraint IMPLICIT_TABLE_UNIQ_COL_UK UNIQUE
6 using index ( create UNIQUE index IMPLICIT_TABLE_UNIQ_COL_UX on
7 IMPLICIT_TABLE(UNIQ_COL)),
8 junk varchar2(10)
9 );

Table created.


CREATE INDEX IMPLICIT_NON_UNIQUE_IX
2 ON IMPLICIT_TABLE(PRIM_COL,UNIQ_COL);

Index created.


INSERT INTO IMPLICIT_TABLE values (1,1,'Implicit');

1 row created.

commit;

Commit complete.



set echo off

EXAMPLE TWO


CREATE TABLE EXPLICIT_TABLE (
2 PRIM_COL number ,
3 UNIQ_COL number,
4 junk varchar2(10)
5 );

Table created.


CREATE UNIQUE INDEX EXPLICIT_TABLE_PX
2 on EXPLICIT_TABLE (PRIM_COL);

Index created.


ALTER TABLE EXPLICIT_TABLE add (
2 CONSTRAINT EXPLICIT_TABLE_PK PRIMARY KEY (PRIM_COL)
3 USING INDEX EXPLICIT_TABLE_PX);

Table altered.


CREATE UNIQUE INDEX EXPLICIT_TABLE_UNIQ_COL_UX
2 ON EXPLICIT_TABLE (UNIQ_COL);

Index created.


ALTER TABLE EXPLICIT_TABLE add
2 CONSTRAINT EXPLICIT_TABLE_UNIQ_COL_UK UNIQUE (UNIQ_COL)
3 USING INDEX EXPLICIT_TABLE_UNIQ_COL_UX;

Table altered.


CREATE INDEX EXPLICIT_NON_UNIQUE_IX
2 ON EXPLICIT_TABLE(PRIM_COL,UNIQ_COL);

Index created.


INSERT INTO EXPLICIT_TABLE VALUES (1,1,'Explicit');

1 row created.

commit;

Commit complete.


set echo off

################ RESULTS ########################



select index_name, index_type, uniqueness, generated
2 from user_indexes
3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE')
4 order by index_name;

INDEX_NAME INDEX_TYPE UNIQUENES G
------------------------------ --------------------------- --------- -
EXPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N
EXPLICIT_TABLE_PX NORMAL UNIQUE N
EXPLICIT_TABLE_UNIQ_COL_UX NORMAL UNIQUE N
IMPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N
IMPLICIT_TABLE_PX NORMAL UNIQUE N
IMPLICIT_TABLE_UNIQ_COL_UX NORMAL UNIQUE N

6 rows selected.


select constraint_name, constraint_type, index_name, generated
2 from user_constraints
3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE')
4 order by constraint_name;

CONSTRAINT_NAME C INDEX_NAME GENERATED
------------------------------ - ------------------------------ --------------
EXPLICIT_TABLE_PK P EXPLICIT_TABLE_PX USER NAME
EXPLICIT_TABLE_UNIQ_COL_UK U EXPLICIT_TABLE_UNIQ_COL_UX USER NAME
IMPLICIT_TABLE_PK P IMPLICIT_TABLE_PX USER NAME
IMPLICIT_TABLE_UNIQ_COL_UK U IMPLICIT_TABLE_UNIQ_COL_UX USER NAME


select
2 obj_t.owner table_owner, obj_t.object_name table_name,
3 obj_i.owner index_owner, obj_i.object_name index_name,
4 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
5 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
6 from
7 dba_objects obj_t,
8 dba_objects obj_i,
9 sys.ind$ ind
10 where
11 ind.bo# = obj_t.object_id
12 and ind.obj# = obj_i.object_id
13 and obj_i.owner = 'DBMON'
14 and obj_i.object_name in
15 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX','EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX',
16 'EXPLICIT_NON_UNIQUE_IX','IMPLICIT_NON_UNIQUE_IX')
17 order by index_name, table_name;

TABLE_OWNE TABLE_NAME INDEX_OWNE INDEX_NAME IMPLICIT UNIQUE
---------- --------------- ---------- ------------------------------ -------- ------
DBMON EXPLICIT_TABLE DBMON EXPLICIT_NON_UNIQUE_IX NO NO
DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_PX NO YES
DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_UNIQ_COL_UX NO YES
DBMON IMPLICIT_TABLE DBMON IMPLICIT_NON_UNIQUE_IX NO NO
DBMON IMPLICIT_TABLE DBMON IMPLICIT_TABLE_PX YES YES
DBMON IMPLICIT_TABLE DBMON IMPLICIT_TABLE_UNIQ_COL_UX YES YES

6 rows selected.


set echo off

Step 1) expdp, impdp
EXPORT TABLES, DROP TABLES, IMPORT TABLES

Hit any key to continue



select index_name, index_type, uniqueness, generated
2 from user_indexes
3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE')
4 order by index_name;

INDEX_NAME INDEX_TYPE UNIQUENES G
------------------------------ --------------------------- --------- -
EXPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N
EXPLICIT_TABLE_PX NORMAL UNIQUE N
EXPLICIT_TABLE_UNIQ_COL_UX NORMAL UNIQUE N
IMPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N
IMPLICIT_TABLE_PX NORMAL UNIQUE N
IMPLICIT_TABLE_UNIQ_COL_UX NORMAL UNIQUE N

6 rows selected.


select constraint_name, constraint_type, index_name, generated
2 from user_constraints
3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE')
4 order by constraint_name;

CONSTRAINT_NAME C INDEX_NAME GENERATED
------------------------------ - ------------------------------ --------------
EXPLICIT_TABLE_PK P EXPLICIT_TABLE_PX USER NAME
EXPLICIT_TABLE_UNIQ_COL_UK U EXPLICIT_TABLE_UNIQ_COL_UX USER NAME
IMPLICIT_TABLE_PK P IMPLICIT_TABLE_PX USER NAME
IMPLICIT_TABLE_UNIQ_COL_UK U IMPLICIT_TABLE_UNIQ_COL_UX USER NAME


select
2 obj_t.owner table_owner, obj_t.object_name table_name,
3 obj_i.owner index_owner, obj_i.object_name index_name,
4 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
5 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
6 from
7 dba_objects obj_t,
8 dba_objects obj_i,
9 sys.ind$ ind
10 where
11 ind.bo# = obj_t.object_id
12 and ind.obj# = obj_i.object_id
13 and obj_i.owner = 'DBMON'
14 and obj_i.object_name in
15 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX','EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX',
16 'EXPLICIT_NON_UNIQUE_IX','IMPLICIT_NON_UNIQUE_IX')
17 order by index_name, table_name;

TABLE_OWNE TABLE_NAME INDEX_OWNE INDEX_NAME IMPLICIT UNIQUE
---------- --------------- ---------- ------------------------------ -------- ------
DBMON EXPLICIT_TABLE DBMON EXPLICIT_NON_UNIQUE_IX NO NO
DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_PX NO YES
DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_UNIQ_COL_UX NO YES
DBMON IMPLICIT_TABLE DBMON IMPLICIT_NON_UNIQUE_IX NO NO
DBMON IMPLICIT_TABLE DBMON IMPLICIT_TABLE_PX YES YES
DBMON IMPLICIT_TABLE DBMON IMPLICIT_TABLE_UNIQ_COL_UX YES YES

6 rows selected.



set echo off

Step 2) Drop Constraints, check Indexes

Hit any key to continue



ALTER TABLE EXPLICIT_TABLE DROP CONSTRAINT EXPLICIT_TABLE_PK;

Table altered.

ALTER TABLE EXPLICIT_TABLE DROP CONSTRAINT EXPLICIT_TABLE_UNIQ_COL_UK;

Table altered.

ALTER TABLE IMPLICIT_TABLE DROP CONSTRAINT IMPLICIT_TABLE_PK;

Table altered.

ALTER TABLE IMPLICIT_TABLE DROP CONSTRAINT IMPLICIT_TABLE_UNIQ_COL_UK;

Table altered.


select index_name, index_type, uniqueness, generated
2 from user_indexes
3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE')
4 order by index_name;

INDEX_NAME INDEX_TYPE UNIQUENES G
------------------------------ --------------------------- --------- -
EXPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N
EXPLICIT_TABLE_PX NORMAL UNIQUE N
EXPLICIT_TABLE_UNIQ_COL_UX NORMAL UNIQUE N
IMPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N


select constraint_name, constraint_type, index_name, generated
2 from user_constraints
3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE')
4 order by constraint_name;

no rows selected


select
2 obj_t.owner table_owner, obj_t.object_name table_name,
3 obj_i.owner index_owner, obj_i.object_name index_name,
4 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
5 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
6 from
7 dba_objects obj_t,
8 dba_objects obj_i,
9 sys.ind$ ind
10 where
11 ind.bo# = obj_t.object_id
12 and ind.obj# = obj_i.object_id
13 and obj_i.owner = 'DBMON'
14 and obj_i.object_name in
15 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX','EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX',
16 'EXPLICIT_NON_UNIQUE_IX','IMPLICIT_NON_UNIQUE_IX')
17 order by index_name, table_name;

TABLE_OWNE TABLE_NAME INDEX_OWNE INDEX_NAME IMPLICIT UNIQUE
---------- --------------- ---------- ------------------------------ -------- ------
DBMON EXPLICIT_TABLE DBMON EXPLICIT_NON_UNIQUE_IX NO NO
DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_PX NO YES
DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_UNIQ_COL_UX NO YES
DBMON IMPLICIT_TABLE DBMON IMPLICIT_NON_UNIQUE_IX NO NO


spool off

Joel Patterson
Database Administrator
904 727-2546

Search Discussions

  • Joel Patterson at Nov 16, 2011 at 3:12 pm
    OK. Hold on. I focused to much and somehow did not create the implicit indexes.... Duh
    I'll be back (maybe).
    ... Arnold S.

    Joel Patterson
    Database Administrator
    904 727-2546

    _____________________________________________
    From: Patterson, Joel
    Sent: Wednesday, November 16, 2011 9:54 AM
    To: Freek.DHooge@uptime.be; oracle-l@freelists.org
    Subject: Naming standards -- that lead to implicit/explicit constraint and index creation.


    There has been discussion on this before on Oracle-L as I have googled and come across some of it.

    I am having trouble recreating the cases for using Explicit indexes, over implicit (as shown by query three in time).

    Specifically for case 1, creating and index that 'could' be used by an unique or primary key constraint. Thus expdp/impdp would drop the implicit index and use the 'could' one. I wonder if you could suggest one.

    For case 2, dropping the unique or primary key constraint would drop the associated index. (I wonder if that is also true for a Foreign Key constraint that is using a unique index - Theoretical muse)


    If it matters, then maybe PK,PX, UK,UX is worth it, and the standard would be expanded to create these in a certain way.

    Can someone suggest an Index, or show why this behavior is not happening with my below example?


    set echo off

    EXAMPLE ONE


    DROP TABLE IMPLICIT_TABLE;

    Table dropped.

    DROP TABLE EXPLICIT_TABLE;

    Table dropped.


    CREATE TABLE IMPLICIT_TABLE (
    2 PRIM_COL number constraint IMPLICIT_TABLE_PK PRIMARY KEY
    3 using index ( CREATE UNIQUE INDEX IMPLICIT_TABLE_PX ON
    4 IMPLICIT_TABLE(PRIM_COL)),
    5 UNIQ_COL number constraint IMPLICIT_TABLE_UNIQ_COL_UK UNIQUE
    6 using index ( create UNIQUE index IMPLICIT_TABLE_UNIQ_COL_UX on
    7 IMPLICIT_TABLE(UNIQ_COL)),
    8 junk varchar2(10)
    9 );

    Table created.


    CREATE INDEX IMPLICIT_NON_UNIQUE_IX
    2 ON IMPLICIT_TABLE(PRIM_COL,UNIQ_COL);

    Index created.


    INSERT INTO IMPLICIT_TABLE values (1,1,'Implicit');

    1 row created.

    commit;

    Commit complete.



    set echo off

    EXAMPLE TWO


    CREATE TABLE EXPLICIT_TABLE (
    2 PRIM_COL number ,
    3 UNIQ_COL number,
    4 junk varchar2(10)
    5 );

    Table created.


    CREATE UNIQUE INDEX EXPLICIT_TABLE_PX
    2 on EXPLICIT_TABLE (PRIM_COL);

    Index created.


    ALTER TABLE EXPLICIT_TABLE add (
    2 CONSTRAINT EXPLICIT_TABLE_PK PRIMARY KEY (PRIM_COL)
    3 USING INDEX EXPLICIT_TABLE_PX);

    Table altered.


    CREATE UNIQUE INDEX EXPLICIT_TABLE_UNIQ_COL_UX
    2 ON EXPLICIT_TABLE (UNIQ_COL);

    Index created.


    ALTER TABLE EXPLICIT_TABLE add
    2 CONSTRAINT EXPLICIT_TABLE_UNIQ_COL_UK UNIQUE (UNIQ_COL)
    3 USING INDEX EXPLICIT_TABLE_UNIQ_COL_UX;

    Table altered.


    CREATE INDEX EXPLICIT_NON_UNIQUE_IX
    2 ON EXPLICIT_TABLE(PRIM_COL,UNIQ_COL);

    Index created.


    INSERT INTO EXPLICIT_TABLE VALUES (1,1,'Explicit');

    1 row created.

    commit;

    Commit complete.


    set echo off

    ################ RESULTS ########################



    select index_name, index_type, uniqueness, generated
    2 from user_indexes
    3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE')
    4 order by index_name;

    INDEX_NAME INDEX_TYPE UNIQUENES G
    ------------------------------ --------------------------- --------- -
    EXPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N
    EXPLICIT_TABLE_PX NORMAL UNIQUE N
    EXPLICIT_TABLE_UNIQ_COL_UX NORMAL UNIQUE N
    IMPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N
    IMPLICIT_TABLE_PX NORMAL UNIQUE N
    IMPLICIT_TABLE_UNIQ_COL_UX NORMAL UNIQUE N

    6 rows selected.


    select constraint_name, constraint_type, index_name, generated
    2 from user_constraints
    3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE')
    4 order by constraint_name;

    CONSTRAINT_NAME C INDEX_NAME GENERATED
    ------------------------------ - ------------------------------ --------------
    EXPLICIT_TABLE_PK P EXPLICIT_TABLE_PX USER NAME
    EXPLICIT_TABLE_UNIQ_COL_UK U EXPLICIT_TABLE_UNIQ_COL_UX USER NAME
    IMPLICIT_TABLE_PK P IMPLICIT_TABLE_PX USER NAME
    IMPLICIT_TABLE_UNIQ_COL_UK U IMPLICIT_TABLE_UNIQ_COL_UX USER NAME


    select
    2 obj_t.owner table_owner, obj_t.object_name table_name,
    3 obj_i.owner index_owner, obj_i.object_name index_name,
    4 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
    5 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
    6 from
    7 dba_objects obj_t,
    8 dba_objects obj_i,
    9 sys.ind$ ind
    10 where
    11 ind.bo# = obj_t.object_id
    12 and ind.obj# = obj_i.object_id
    13 and obj_i.owner = 'DBMON'
    14 and obj_i.object_name in
    15 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX','EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX',
    16 'EXPLICIT_NON_UNIQUE_IX','IMPLICIT_NON_UNIQUE_IX')
    17 order by index_name, table_name;

    TABLE_OWNE TABLE_NAME INDEX_OWNE INDEX_NAME IMPLICIT UNIQUE
    ---------- --------------- ---------- ------------------------------ -------- ------
    DBMON EXPLICIT_TABLE DBMON EXPLICIT_NON_UNIQUE_IX NO NO
    DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_PX NO YES
    DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_UNIQ_COL_UX NO YES
    DBMON IMPLICIT_TABLE DBMON IMPLICIT_NON_UNIQUE_IX NO NO
    DBMON IMPLICIT_TABLE DBMON IMPLICIT_TABLE_PX YES YES
    DBMON IMPLICIT_TABLE DBMON IMPLICIT_TABLE_UNIQ_COL_UX YES YES

    6 rows selected.


    set echo off

    Step 1) expdp, impdp
    EXPORT TABLES, DROP TABLES, IMPORT TABLES

    Hit any key to continue



    select index_name, index_type, uniqueness, generated
    2 from user_indexes
    3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE')
    4 order by index_name;

    INDEX_NAME INDEX_TYPE UNIQUENES G
    ------------------------------ --------------------------- --------- -
    EXPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N
    EXPLICIT_TABLE_PX NORMAL UNIQUE N
    EXPLICIT_TABLE_UNIQ_COL_UX NORMAL UNIQUE N
    IMPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N
    IMPLICIT_TABLE_PX NORMAL UNIQUE N
    IMPLICIT_TABLE_UNIQ_COL_UX NORMAL UNIQUE N

    6 rows selected.


    select constraint_name, constraint_type, index_name, generated
    2 from user_constraints
    3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE')
    4 order by constraint_name;

    CONSTRAINT_NAME C INDEX_NAME GENERATED
    ------------------------------ - ------------------------------ --------------
    EXPLICIT_TABLE_PK P EXPLICIT_TABLE_PX USER NAME
    EXPLICIT_TABLE_UNIQ_COL_UK U EXPLICIT_TABLE_UNIQ_COL_UX USER NAME
    IMPLICIT_TABLE_PK P IMPLICIT_TABLE_PX USER NAME
    IMPLICIT_TABLE_UNIQ_COL_UK U IMPLICIT_TABLE_UNIQ_COL_UX USER NAME


    select
    2 obj_t.owner table_owner, obj_t.object_name table_name,
    3 obj_i.owner index_owner, obj_i.object_name index_name,
    4 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
    5 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
    6 from
    7 dba_objects obj_t,
    8 dba_objects obj_i,
    9 sys.ind$ ind
    10 where
    11 ind.bo# = obj_t.object_id
    12 and ind.obj# = obj_i.object_id
    13 and obj_i.owner = 'DBMON'
    14 and obj_i.object_name in
    15 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX','EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX',
    16 'EXPLICIT_NON_UNIQUE_IX','IMPLICIT_NON_UNIQUE_IX')
    17 order by index_name, table_name;

    TABLE_OWNE TABLE_NAME INDEX_OWNE INDEX_NAME IMPLICIT UNIQUE
    ---------- --------------- ---------- ------------------------------ -------- ------
    DBMON EXPLICIT_TABLE DBMON EXPLICIT_NON_UNIQUE_IX NO NO
    DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_PX NO YES
    DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_UNIQ_COL_UX NO YES
    DBMON IMPLICIT_TABLE DBMON IMPLICIT_NON_UNIQUE_IX NO NO
    DBMON IMPLICIT_TABLE DBMON IMPLICIT_TABLE_PX YES YES
    DBMON IMPLICIT_TABLE DBMON IMPLICIT_TABLE_UNIQ_COL_UX YES YES

    6 rows selected.



    set echo off

    Step 2) Drop Constraints, check Indexes

    Hit any key to continue



    ALTER TABLE EXPLICIT_TABLE DROP CONSTRAINT EXPLICIT_TABLE_PK;

    Table altered.

    ALTER TABLE EXPLICIT_TABLE DROP CONSTRAINT EXPLICIT_TABLE_UNIQ_COL_UK;

    Table altered.

    ALTER TABLE IMPLICIT_TABLE DROP CONSTRAINT IMPLICIT_TABLE_PK;

    Table altered.

    ALTER TABLE IMPLICIT_TABLE DROP CONSTRAINT IMPLICIT_TABLE_UNIQ_COL_UK;

    Table altered.


    select index_name, index_type, uniqueness, generated
    2 from user_indexes
    3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE')
    4 order by index_name;

    INDEX_NAME INDEX_TYPE UNIQUENES G
    ------------------------------ --------------------------- --------- -
    EXPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N
    EXPLICIT_TABLE_PX NORMAL UNIQUE N
    EXPLICIT_TABLE_UNIQ_COL_UX NORMAL UNIQUE N
    IMPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N


    select constraint_name, constraint_type, index_name, generated
    2 from user_constraints
    3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE')
    4 order by constraint_name;

    no rows selected


    select
    2 obj_t.owner table_owner, obj_t.object_name table_name,
    3 obj_i.owner index_owner, obj_i.object_name index_name,
    4 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
    5 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
    6 from
    7 dba_objects obj_t,
    8 dba_objects obj_i,
    9 sys.ind$ ind
    10 where
    11 ind.bo# = obj_t.object_id
    12 and ind.obj# = obj_i.object_id
    13 and obj_i.owner = 'DBMON'
    14 and obj_i.object_name in
    15 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX','EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX',
    16 'EXPLICIT_NON_UNIQUE_IX','IMPLICIT_NON_UNIQUE_IX')
    17 order by index_name, table_name;

    TABLE_OWNE TABLE_NAME INDEX_OWNE INDEX_NAME IMPLICIT UNIQUE
    ---------- --------------- ---------- ------------------------------ -------- ------
    DBMON EXPLICIT_TABLE DBMON EXPLICIT_NON_UNIQUE_IX NO NO
    DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_PX NO YES
    DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_UNIQ_COL_UX NO YES
    DBMON IMPLICIT_TABLE DBMON IMPLICIT_NON_UNIQUE_IX NO NO


    spool off

    Joel Patterson
    Database Administrator
    904 727-2546
  • Joel Patterson at Nov 16, 2011 at 7:48 pm
    Oracle version 10.2.0.4

    I would appreciate an example where an Index is created such that by creating an implicit primary key or unique index on the table, would cause one of the indexes not to be imported.

    The below scenario shows three ways to create the tables, constraints and indexes.

    The First time the unique and primary key constraints are dropped:
    IMPLICIT TABLES LOOSE THEIR UNIQUE INDEXES, and PRIMARY KEY INDEXES.
    i.e. All IMPLICIT INDEXES Dropped.

    After Exporting and importing.
    All Indexes, IMPLICIT AND EXPLICIT where imported Successfully.
    (This is the scenario where I would like an example were all would not be imported).
    NOW HOWEVER NOTE, ALL NINE OF THEM ARE EXPLICIT INDEXES!

    The Second time the unique and primary key constraints are dropped, (after import):
    ALL INDEXES REMAIN -- and REMAIN EXPLICIT,
    Because they are now Explicit, they do not drop when dropping the constraints.

    This is leading to a standard akin to the EXPLICIT_TABLE. I would like to document all the reasons I find.

    (Script is available)



    set echo off

    EXAMPLE ONE


    DROP TABLE IMPLICIT_TABLE1;

    Table dropped.

    DROP TABLE IMPLICIT_TABLE;

    Table dropped.


    DROP TABLE EXPLICIT_TABLE;

    Table dropped.


    CREATE TABLE IMPLICIT_TABLE1 (
    2 PRIM_COL number constraint IMPLICIT_TABLE1_PK PRIMARY KEY
    3 using index ( CREATE UNIQUE INDEX IMPLICIT_TABLE1_PX ON
    4 IMPLICIT_TABLE1(PRIM_COL)),
    5 UNIQ_COL number constraint IMPLICIT_TABLE1_UNIQ_COL_UK UNIQUE
    6 using index ( create UNIQUE index IMPLICIT_TABLE1_UNIQ_COL_UX on
    7 IMPLICIT_TABLE1(UNIQ_COL)),
    8 junk varchar2(10)
    9 );

    Table created.


    CREATE INDEX IMPLICIT_TABLE1_NON_UNIQUE_IX
    2 ON IMPLICIT_TABLE1(PRIM_COL,UNIQ_COL);

    Index created.


    INSERT INTO IMPLICIT_TABLE1 values (1,1,'Implicit1');

    1 row created.



    set echo off

    EXAMPLE TWO


    CREATE TABLE EXPLICIT_TABLE (
    2 PRIM_COL number ,
    3 UNIQ_COL number,
    4 junk varchar2(10)
    5 );

    Table created.


    CREATE UNIQUE INDEX EXPLICIT_TABLE_PX
    2 on EXPLICIT_TABLE (PRIM_COL);

    Index created.


    ALTER TABLE EXPLICIT_TABLE add (
    2 CONSTRAINT EXPLICIT_TABLE_PK PRIMARY KEY (PRIM_COL)
    3 USING INDEX EXPLICIT_TABLE_PX);

    Table altered.


    CREATE UNIQUE INDEX EXPLICIT_TABLE_UNIQ_COL_UX
    2 ON EXPLICIT_TABLE (UNIQ_COL);

    Index created.


    ALTER TABLE EXPLICIT_TABLE add
    2 CONSTRAINT EXPLICIT_TABLE_UNIQ_COL_UK UNIQUE (UNIQ_COL)
    3 USING INDEX EXPLICIT_TABLE_UNIQ_COL_UX;

    Table altered.


    CREATE INDEX EXPLICIT_TABLE_NON_UNIQUE_IX
    2 ON EXPLICIT_TABLE(PRIM_COL,UNIQ_COL);

    Index created.


    INSERT INTO EXPLICIT_TABLE values (1,1,'Explicit');

    1 row created.

    set echo off

    EXAMPLE THREE


    CREATE TABLE IMPLICIT_TABLE (
    2 PRIM_COL number ,
    3 UNIQ_COL number,
    4 junk varchar2(10)
    5 );

    Table created.


    ALTER TABLE IMPLICIT_TABLE
    2 ADD CONSTRAINT IMPLICIT_TABLE_PK PRIMARY KEY (PRIM_COL)
    3 USING INDEX (CREATE UNIQUE INDEX IMPLICIT_TABLE_PX ON IMPLICIT_TABLE(PRIM_COL));

    Table altered.


    ALTER TABLE IMPLICIT_TABLE
    2 ADD CONSTRAINT IMPLICIT_TABLE_UNIQ_COL_UK UNIQUE (UNIQ_COL)
    3 USING INDEX (CREATE UNIQUE INDEX IMPLICIT_TABLE_UNIQ_COL_UX ON IMPLICIT_TABLE(UNIQ_COL));

    Table altered.


    CREATE INDEX IMPLICIT_TABLE_NON_UNIQUE_IX
    2 ON IMPLICIT_TABLE(PRIM_COL,UNIQ_COL);

    Index created.


    INSERT INTO IMPLICIT_TABLE values (1,1,'Implicit');

    1 row created.

    commit;

    Commit complete.


    set echo off

    ################ RESULTS ########################



    select index_name, table_name, index_type, uniqueness, generated
    2 from user_indexes
    3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
    4 order by table_name, index_name;

    INDEX_NAME TABLE_NAME INDEX_TYPE UNIQUENES G
    ------------------------------ --------------- --------------------------- --------- -
    EXPLICIT_TABLE_NON_UNIQUE_IX EXPLICIT_TABLE NORMAL NONUNIQUE N
    EXPLICIT_TABLE_PX EXPLICIT_TABLE NORMAL UNIQUE N
    EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE_NON_UNIQUE_IX IMPLICIT_TABLE NORMAL NONUNIQUE N
    IMPLICIT_TABLE_PX IMPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE_UNIQ_COL_UX IMPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE1_NON_UNIQUE_IX IMPLICIT_TABLE1 NORMAL NONUNIQUE N
    IMPLICIT_TABLE1_PX IMPLICIT_TABLE1 NORMAL UNIQUE N
    IMPLICIT_TABLE1_UNIQ_COL_UX IMPLICIT_TABLE1 NORMAL UNIQUE N

    9 rows selected.


    select constraint_name, constraint_type, index_name, table_name, generated
    2 from user_constraints
    3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
    4 order by table_name, constraint_name;

    CONSTRAINT_NAME C INDEX_NAME TABLE_NAME GENERATED
    ------------------------------ - ------------------------------ --------------- --------------
    EXPLICIT_TABLE_PK P EXPLICIT_TABLE_PX EXPLICIT_TABLE USER NAME
    EXPLICIT_TABLE_UNIQ_COL_UK U EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE USER NAME
    IMPLICIT_TABLE_PK P IMPLICIT_TABLE_PX IMPLICIT_TABLE USER NAME
    IMPLICIT_TABLE_UNIQ_COL_UK U IMPLICIT_TABLE_UNIQ_COL_UX IMPLICIT_TABLE USER NAME
    IMPLICIT_TABLE1_PK P IMPLICIT_TABLE1_PX IMPLICIT_TABLE1 USER NAME
    IMPLICIT_TABLE1_UNIQ_COL_UK U IMPLICIT_TABLE1_UNIQ_COL_UX IMPLICIT_TABLE1 USER NAME

    6 rows selected.


    select
    2 obj_t.object_name table_name, obj_i.object_name index_name,
    3 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
    4 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
    5 from
    6 dba_objects obj_t,
    7 dba_objects obj_i,
    8 sys.ind$ ind
    9 where
    10 ind.bo# = obj_t.object_id
    11 and ind.obj# = obj_i.object_id
    12 and obj_i.owner = 'DBMON'
    13 and obj_i.object_name in
    14 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX', 'IMPLICIT_TABLE_NON_UNIQUE_IX',
    15 'IMPLICIT_TABLE1_PX', 'IMPLICIT_TABLE1_UNIQ_COL_UX', 'IMPLICIT_TABLE1_NON_UNIQUE_IX',
    16 'EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX', 'EXPLICIT_TABLE_NON_UNIQUE_IX')
    17 order by table_name, index_name;

    TABLE_NAME INDEX_NAME IMPLICIT UNIQUE
    --------------- ------------------------------ -------- ------
    EXPLICIT_TABLE EXPLICIT_TABLE_NON_UNIQUE_IX NO NO
    EXPLICIT_TABLE EXPLICIT_TABLE_PX NO YES
    EXPLICIT_TABLE EXPLICIT_TABLE_UNIQ_COL_UX NO YES
    IMPLICIT_TABLE IMPLICIT_TABLE_NON_UNIQUE_IX NO NO
    IMPLICIT_TABLE IMPLICIT_TABLE_PX YES YES
    IMPLICIT_TABLE IMPLICIT_TABLE_UNIQ_COL_UX YES YES
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_NON_UNIQUE_IX NO NO
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_PX YES YES
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_UNIQ_COL_UX YES YES

    9 rows selected.




    set echo off

    Steps:
    1) EXPORT TABLES
    2) DROP CONSTRAINTS and Look.
    3) DROP TABLES from another session and IMPORT TABLES
    4) Look
    5) DROP CONSTRAINTS again and look.

    AFTER EXPORTING TABLES, Hit any key to continue.
    Now we will see what happens after dropping constraints.

    Step 2 drop constraints and look.
    " IMPLICIT TABLES WILL LOOSE THEIR UNIQUE INDEXES, and PRIMARY KEY INDEXES."
    " i.e. All IMPLICIT INDEXES Dropped."


    ALTER TABLE IMPLICIT_TABLE1 DROP CONSTRAINT IMPLICIT_TABLE1_PK;

    Table altered.

    ALTER TABLE IMPLICIT_TABLE1 DROP CONSTRAINT IMPLICIT_TABLE1_UNIQ_COL_UK;

    Table altered.

    ALTER TABLE EXPLICIT_TABLE DROP CONSTRAINT EXPLICIT_TABLE_PK;

    Table altered.

    ALTER TABLE EXPLICIT_TABLE DROP CONSTRAINT EXPLICIT_TABLE_UNIQ_COL_UK;

    Table altered.

    ALTER TABLE IMPLICIT_TABLE DROP CONSTRAINT IMPLICIT_TABLE_PK;

    Table altered.

    ALTER TABLE IMPLICIT_TABLE DROP CONSTRAINT IMPLICIT_TABLE_UNIQ_COL_UK;

    Table altered.


    select index_name, table_name, index_type, uniqueness, generated
    2 from user_indexes
    3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
    4 order by table_name, index_name;

    INDEX_NAME TABLE_NAME INDEX_TYPE UNIQUENES G
    ------------------------------ --------------- --------------------------- --------- -
    EXPLICIT_TABLE_NON_UNIQUE_IX EXPLICIT_TABLE NORMAL NONUNIQUE N
    EXPLICIT_TABLE_PX EXPLICIT_TABLE NORMAL UNIQUE N
    EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE_NON_UNIQUE_IX IMPLICIT_TABLE NORMAL NONUNIQUE N
    IMPLICIT_TABLE1_NON_UNIQUE_IX IMPLICIT_TABLE1 NORMAL NONUNIQUE N


    select constraint_name, constraint_type, index_name, table_name, generated
    2 from user_constraints
    3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
    4 order by table_name, constraint_name;

    no rows selected


    select
    2 obj_t.object_name table_name, obj_i.object_name index_name,
    3 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
    4 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
    5 from
    6 dba_objects obj_t,
    7 dba_objects obj_i,
    8 sys.ind$ ind
    9 where
    10 ind.bo# = obj_t.object_id
    11 and ind.obj# = obj_i.object_id
    12 and obj_i.owner = 'DBMON'
    13 and obj_i.object_name in
    14 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX', 'IMPLICIT_TABLE_NON_UNIQUE_IX',
    15 'IMPLICIT_TABLE1_PX', 'IMPLICIT_TABLE1_UNIQ_COL_UX', 'IMPLICIT_TABLE1_NON_UNIQUE_IX',
    16 'EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX', 'EXPLICIT_TABLE_NON_UNIQUE_IX')
    17 order by index_name, table_name;

    TABLE_NAME INDEX_NAME IMPLICIT UNIQUE
    --------------- ------------------------------ -------- ------
    EXPLICIT_TABLE EXPLICIT_TABLE_NON_UNIQUE_IX NO NO
    EXPLICIT_TABLE EXPLICIT_TABLE_PX NO YES
    EXPLICIT_TABLE EXPLICIT_TABLE_UNIQ_COL_UX NO YES
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_NON_UNIQUE_IX NO NO
    IMPLICIT_TABLE IMPLICIT_TABLE_NON_UNIQUE_IX NO NO


    set echo off

    Step 3 drop tables from another session and import. Then look.

    AFTER IMPORTING TABLES,
    All Indexes, IMPLICIT AND EXPLICIT have been imported Sucessfully
    EXCEPT NOW ALL NINE OF THEM ARE EXPLICIT!
    --- Need that example index that will cause one not to import.

    AFTER IMPORTING TABLES, Hit any key to continue.



    select index_name, table_name, index_type, uniqueness, generated
    2 from user_indexes
    3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
    4 order by table_name, index_name;

    INDEX_NAME TABLE_NAME INDEX_TYPE UNIQUENES G
    ------------------------------ --------------- --------------------------- --------- -
    EXPLICIT_TABLE_NON_UNIQUE_IX EXPLICIT_TABLE NORMAL NONUNIQUE N
    EXPLICIT_TABLE_PX EXPLICIT_TABLE NORMAL UNIQUE N
    EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE_NON_UNIQUE_IX IMPLICIT_TABLE NORMAL NONUNIQUE N
    IMPLICIT_TABLE_PX IMPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE_UNIQ_COL_UX IMPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE1_NON_UNIQUE_IX IMPLICIT_TABLE1 NORMAL NONUNIQUE N
    IMPLICIT_TABLE1_PX IMPLICIT_TABLE1 NORMAL UNIQUE N
    IMPLICIT_TABLE1_UNIQ_COL_UX IMPLICIT_TABLE1 NORMAL UNIQUE N

    9 rows selected.


    select constraint_name, constraint_type, index_name, table_name, generated
    2 from user_constraints
    3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
    4 order by table_name, constraint_name;

    CONSTRAINT_NAME C INDEX_NAME TABLE_NAME GENERATED
    ------------------------------ - ------------------------------ --------------- --------------
    EXPLICIT_TABLE_PK P EXPLICIT_TABLE_PX EXPLICIT_TABLE USER NAME
    EXPLICIT_TABLE_UNIQ_COL_UK U EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE USER NAME
    IMPLICIT_TABLE_PK P IMPLICIT_TABLE_NON_UNIQUE_IX IMPLICIT_TABLE USER NAME
    IMPLICIT_TABLE_UNIQ_COL_UK U IMPLICIT_TABLE_UNIQ_COL_UX IMPLICIT_TABLE USER NAME
    IMPLICIT_TABLE1_PK P IMPLICIT_TABLE1_PX IMPLICIT_TABLE1 USER NAME
    IMPLICIT_TABLE1_UNIQ_COL_UK U IMPLICIT_TABLE1_UNIQ_COL_UX IMPLICIT_TABLE1 USER NAME

    6 rows selected.


    select
    2 obj_t.object_name table_name, obj_i.object_name index_name,
    3 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
    4 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
    5 from
    6 dba_objects obj_t,
    7 dba_objects obj_i,
    8 sys.ind$ ind
    9 where
    10 ind.bo# = obj_t.object_id
    11 and ind.obj# = obj_i.object_id
    12 and obj_i.owner = 'DBMON'
    13 and obj_i.object_name in
    14 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX', 'IMPLICIT_TABLE_NON_UNIQUE_IX',
    15 'IMPLICIT_TABLE1_PX', 'IMPLICIT_TABLE1_UNIQ_COL_UX', 'IMPLICIT_TABLE1_NON_UNIQUE_IX',
    16 'EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX', 'EXPLICIT_TABLE_NON_UNIQUE_IX')
    17 order by table_name, index_name;

    TABLE_NAME INDEX_NAME IMPLICIT UNIQUE
    --------------- ------------------------------ -------- ------
    EXPLICIT_TABLE EXPLICIT_TABLE_NON_UNIQUE_IX NO NO
    EXPLICIT_TABLE EXPLICIT_TABLE_PX NO YES
    EXPLICIT_TABLE EXPLICIT_TABLE_UNIQ_COL_UX NO YES
    IMPLICIT_TABLE IMPLICIT_TABLE_NON_UNIQUE_IX NO NO
    IMPLICIT_TABLE IMPLICIT_TABLE_PX NO YES
    IMPLICIT_TABLE IMPLICIT_TABLE_UNIQ_COL_UX NO YES
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_NON_UNIQUE_IX NO NO
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_PX NO YES
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_UNIQ_COL_UX NO YES

    9 rows selected.


    set echo off

    Step 5)
    Drop Constraints again, but this time AFTER IMPORT of original tables, Look.
    ALL INDEXES REMAIN -- and REMAIN EXPLICIT, therefore none drop.


    ALTER TABLE IMPLICIT_TABLE1 DROP CONSTRAINT IMPLICIT_TABLE1_PK;

    Table altered.

    ALTER TABLE IMPLICIT_TABLE1 DROP CONSTRAINT IMPLICIT_TABLE1_UNIQ_COL_UK;

    Table altered.

    ALTER TABLE EXPLICIT_TABLE DROP CONSTRAINT EXPLICIT_TABLE_PK;

    Table altered.

    ALTER TABLE EXPLICIT_TABLE DROP CONSTRAINT EXPLICIT_TABLE_UNIQ_COL_UK;

    Table altered.

    ALTER TABLE IMPLICIT_TABLE DROP CONSTRAINT IMPLICIT_TABLE_PK;

    Table altered.

    ALTER TABLE IMPLICIT_TABLE DROP CONSTRAINT IMPLICIT_TABLE_UNIQ_COL_UK;

    Table altered.



    select index_name, table_name, index_type, uniqueness, generated
    2 from user_indexes
    3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
    4 order by table_name, index_name;

    INDEX_NAME TABLE_NAME INDEX_TYPE UNIQUENES G
    ------------------------------ --------------- --------------------------- --------- -
    EXPLICIT_TABLE_NON_UNIQUE_IX EXPLICIT_TABLE NORMAL NONUNIQUE N
    EXPLICIT_TABLE_PX EXPLICIT_TABLE NORMAL UNIQUE N
    EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE_NON_UNIQUE_IX IMPLICIT_TABLE NORMAL NONUNIQUE N
    IMPLICIT_TABLE_PX IMPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE_UNIQ_COL_UX IMPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE1_NON_UNIQUE_IX IMPLICIT_TABLE1 NORMAL NONUNIQUE N
    IMPLICIT_TABLE1_PX IMPLICIT_TABLE1 NORMAL UNIQUE N
    IMPLICIT_TABLE1_UNIQ_COL_UX IMPLICIT_TABLE1 NORMAL UNIQUE N

    9 rows selected.


    select constraint_name, constraint_type, index_name, table_name, generated
    2 from user_constraints
    3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
    4 order by table_name, constraint_name;

    no rows selected


    select
    2 obj_t.object_name table_name, obj_i.object_name index_name,
    3 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
    4 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
    5 from
    6 dba_objects obj_t,
    7 dba_objects obj_i,
    8 sys.ind$ ind
    9 where
    10 ind.bo# = obj_t.object_id
    11 and ind.obj# = obj_i.object_id
    12 and obj_i.owner = 'DBMON'
    13 and obj_i.object_name in
    14 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX', 'IMPLICIT_TABLE_NON_UNIQUE_IX',
    15 'IMPLICIT_TABLE1_PX', 'IMPLICIT_TABLE1_UNIQ_COL_UX', 'IMPLICIT_TABLE1_NON_UNIQUE_IX',
    16 'EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX', 'EXPLICIT_TABLE_NON_UNIQUE_IX')
    17 order by table_name, index_name;

    TABLE_NAME INDEX_NAME IMPLICIT UNIQUE
    --------------- ------------------------------ -------- ------
    EXPLICIT_TABLE EXPLICIT_TABLE_NON_UNIQUE_IX NO NO
    EXPLICIT_TABLE EXPLICIT_TABLE_PX NO YES
    EXPLICIT_TABLE EXPLICIT_TABLE_UNIQ_COL_UX NO YES
    IMPLICIT_TABLE IMPLICIT_TABLE_NON_UNIQUE_IX NO NO
    IMPLICIT_TABLE IMPLICIT_TABLE_PX NO YES
    IMPLICIT_TABLE IMPLICIT_TABLE_UNIQ_COL_UX NO YES
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_NON_UNIQUE_IX NO NO
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_PX NO YES
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_UNIQ_COL_UX NO YES

    9 rows selected.


    spool off

    Joel Patterson
    Database Administrator
    904 727-2546
  • Joel Patterson at Nov 17, 2011 at 12:23 pm
    Thanks, that makes sense and is intuitive. However, I was told by someone in this group to watch out for creating an implicit index, because it might disappear upon exp/imp if oracle has another index available that it can use.
    I would like to demonstrate that, just like I have demonstrated that they become Explicit after import (didn't know that would happen before), and that dropping the constraints affect the implicit indexes... but not the explicit ones.

    So, what index can I put on the implicit tables that would cause this to happen? (one is lead to wonder why if they 'turn' into explicit indexes why they would be ignored anyway... but that doesn't really matter as much as knowing the behavior, and proceed accordingly).

    Joel Patterson
    Database Administrator
    904 727-2546
    From: Subodh Deshpande
    Sent: Thursday, November 17, 2011 6:59 AM
    To: Patterson, Joel
    Cc: Freek.DHooge@uptime.be; oracle-l@freelists.org
    Subject: Re: Naming standards -- that lead to implicit/explicit constraint and index creation.

    while importing if you choose only data to be imported and not definations then newly created index will not be imported..provided you have exported..

    thanks..subodh
    On 17 November 2011 01:17, wrote:
    Oracle version 10.2.0.4

    I would appreciate an example where an Index is created such that by creating an implicit primary key or unique index on the table, would cause one of the indexes not to be imported.

    The below scenario shows three ways to create the tables, constraints and indexes.

    The First time the unique and primary key constraints are dropped:
    IMPLICIT TABLES LOOSE THEIR UNIQUE INDEXES, and PRIMARY KEY INDEXES.
    i.e. All IMPLICIT INDEXES Dropped.

    After Exporting and importing.
    All Indexes, IMPLICIT AND EXPLICIT where imported Successfully.
    (This is the scenario where I would like an example were all would not be imported).
    NOW HOWEVER NOTE, ALL NINE OF THEM ARE EXPLICIT INDEXES!

    The Second time the unique and primary key constraints are dropped, (after import):
    ALL INDEXES REMAIN -- and REMAIN EXPLICIT,
    Because they are now Explicit, they do not drop when dropping the constraints.

    This is leading to a standard akin to the EXPLICIT_TABLE. I would like to document all the reasons I find.

    (Script is available)



    set echo off

    EXAMPLE ONE

    DROP TABLE IMPLICIT_TABLE1;

    Table dropped.

    DROP TABLE IMPLICIT_TABLE;

    Table dropped.


    DROP TABLE EXPLICIT_TABLE;

    Table dropped.

    CREATE TABLE IMPLICIT_TABLE1 (
    2 PRIM_COL number constraint IMPLICIT_TABLE1_PK PRIMARY KEY
    3 using index ( CREATE UNIQUE INDEX IMPLICIT_TABLE1_PX ON
    4 IMPLICIT_TABLE1(PRIM_COL)),
    5 UNIQ_COL number constraint IMPLICIT_TABLE1_UNIQ_COL_UK UNIQUE
    6 using index ( create UNIQUE index IMPLICIT_TABLE1_UNIQ_COL_UX on
    7 IMPLICIT_TABLE1(UNIQ_COL)),
    8 junk varchar2(10)
    9 );

    Table created.

    CREATE INDEX IMPLICIT_TABLE1_NON_UNIQUE_IX
    2 ON IMPLICIT_TABLE1(PRIM_COL,UNIQ_COL);

    Index created.


    INSERT INTO IMPLICIT_TABLE1 values (1,1,'Implicit1');

    1 row created.



    set echo off

    EXAMPLE TWO


    CREATE TABLE EXPLICIT_TABLE (
    2 PRIM_COL number ,
    3 UNIQ_COL number,
    4 junk varchar2(10)
    5 );

    Table created.


    CREATE UNIQUE INDEX EXPLICIT_TABLE_PX
    2 on EXPLICIT_TABLE (PRIM_COL);

    Index created.


    ALTER TABLE EXPLICIT_TABLE add (
    2 CONSTRAINT EXPLICIT_TABLE_PK PRIMARY KEY (PRIM_COL)
    3 USING INDEX EXPLICIT_TABLE_PX);

    Table altered.


    CREATE UNIQUE INDEX EXPLICIT_TABLE_UNIQ_COL_UX
    2 ON EXPLICIT_TABLE (UNIQ_COL);

    Index created.


    ALTER TABLE EXPLICIT_TABLE add
    2 CONSTRAINT EXPLICIT_TABLE_UNIQ_COL_UK UNIQUE (UNIQ_COL)
    3 USING INDEX EXPLICIT_TABLE_UNIQ_COL_UX;

    Table altered.

    CREATE INDEX EXPLICIT_TABLE_NON_UNIQUE_IX
    2 ON EXPLICIT_TABLE(PRIM_COL,UNIQ_COL);

    Index created.

    INSERT INTO EXPLICIT_TABLE values (1,1,'Explicit');

    1 row created.

    set echo off

    EXAMPLE THREE


    CREATE TABLE IMPLICIT_TABLE (
    2 PRIM_COL number ,
    3 UNIQ_COL number,
    4 junk varchar2(10)
    5 );

    Table created.

    ALTER TABLE IMPLICIT_TABLE
    2 ADD CONSTRAINT IMPLICIT_TABLE_PK PRIMARY KEY (PRIM_COL)
    3 USING INDEX (CREATE UNIQUE INDEX IMPLICIT_TABLE_PX ON IMPLICIT_TABLE(PRIM_COL));

    Table altered.


    ALTER TABLE IMPLICIT_TABLE
    2 ADD CONSTRAINT IMPLICIT_TABLE_UNIQ_COL_UK UNIQUE (UNIQ_COL)
    3 USING INDEX (CREATE UNIQUE INDEX IMPLICIT_TABLE_UNIQ_COL_UX ON IMPLICIT_TABLE(UNIQ_COL));

    Table altered.


    CREATE INDEX IMPLICIT_TABLE_NON_UNIQUE_IX
    2 ON IMPLICIT_TABLE(PRIM_COL,UNIQ_COL);

    Index created.


    INSERT INTO IMPLICIT_TABLE values (1,1,'Implicit');

    1 row created.

    commit;

    Commit complete.


    set echo off
    ################ RESULTS ########################



    select index_name, table_name, index_type, uniqueness, generated
    2 from user_indexes
    3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
    4 order by table_name, index_name;

    INDEX_NAME TABLE_NAME INDEX_TYPE UNIQUENES G
    ------------------------------ --------------- --------------------------- --------- -
    EXPLICIT_TABLE_NON_UNIQUE_IX EXPLICIT_TABLE NORMAL NONUNIQUE N
    EXPLICIT_TABLE_PX EXPLICIT_TABLE NORMAL UNIQUE N
    EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE_NON_UNIQUE_IX IMPLICIT_TABLE NORMAL NONUNIQUE N
    IMPLICIT_TABLE_PX IMPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE_UNIQ_COL_UX IMPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE1_NON_UNIQUE_IX IMPLICIT_TABLE1 NORMAL NONUNIQUE N
    IMPLICIT_TABLE1_PX IMPLICIT_TABLE1 NORMAL UNIQUE N
    IMPLICIT_TABLE1_UNIQ_COL_UX IMPLICIT_TABLE1 NORMAL UNIQUE N

    9 rows selected.


    select constraint_name, constraint_type, index_name, table_name, generated
    2 from user_constraints
    3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
    4 order by table_name, constraint_name;

    CONSTRAINT_NAME C INDEX_NAME TABLE_NAME GENERATED
    ------------------------------ - ------------------------------ --------------- --------------
    EXPLICIT_TABLE_PK P EXPLICIT_TABLE_PX EXPLICIT_TABLE USER NAME
    EXPLICIT_TABLE_UNIQ_COL_UK U EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE USER NAME
    IMPLICIT_TABLE_PK P IMPLICIT_TABLE_PX IMPLICIT_TABLE USER NAME
    IMPLICIT_TABLE_UNIQ_COL_UK U IMPLICIT_TABLE_UNIQ_COL_UX IMPLICIT_TABLE USER NAME
    IMPLICIT_TABLE1_PK P IMPLICIT_TABLE1_PX IMPLICIT_TABLE1 USER NAME
    IMPLICIT_TABLE1_UNIQ_COL_UK U IMPLICIT_TABLE1_UNIQ_COL_UX IMPLICIT_TABLE1 USER NAME

    6 rows selected.


    select
    2 obj_t.object_name table_name, obj_i.object_name index_name,
    3 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
    4 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
    5 from
    6 dba_objects obj_t,
    7 dba_objects obj_i,
    8 sys.ind$ ind
    9 where
    10 ind.bo#<http://ind.bo> = obj_t.object_id
    11 and ind.obj# = obj_i.object_id
    12 and obj_i.owner = 'DBMON'
    13 and obj_i.object_name in
    14 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX', 'IMPLICIT_TABLE_NON_UNIQUE_IX',
    15 'IMPLICIT_TABLE1_PX', 'IMPLICIT_TABLE1_UNIQ_COL_UX', 'IMPLICIT_TABLE1_NON_UNIQUE_IX',
    16 'EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX', 'EXPLICIT_TABLE_NON_UNIQUE_IX')
    17 order by table_name, index_name;

    TABLE_NAME INDEX_NAME IMPLICIT UNIQUE
    --------------- ------------------------------ -------- ------
    EXPLICIT_TABLE EXPLICIT_TABLE_NON_UNIQUE_IX NO NO
    EXPLICIT_TABLE EXPLICIT_TABLE_PX NO YES
    EXPLICIT_TABLE EXPLICIT_TABLE_UNIQ_COL_UX NO YES
    IMPLICIT_TABLE IMPLICIT_TABLE_NON_UNIQUE_IX NO NO
    IMPLICIT_TABLE IMPLICIT_TABLE_PX YES YES
    IMPLICIT_TABLE IMPLICIT_TABLE_UNIQ_COL_UX YES YES
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_NON_UNIQUE_IX NO NO
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_PX YES YES
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_UNIQ_COL_UX YES YES

    9 rows selected.




    set echo off
    Steps:
    1) EXPORT TABLES
    2) DROP CONSTRAINTS and Look.
    3) DROP TABLES from another session and IMPORT TABLES
    4) Look
    5) DROP CONSTRAINTS again and look.

    AFTER EXPORTING TABLES, Hit any key to continue.
    Now we will see what happens after dropping constraints.

    Step 2 drop constraints and look.
    " IMPLICIT TABLES WILL LOOSE THEIR UNIQUE INDEXES, and PRIMARY KEY INDEXES."
    " i.e. All IMPLICIT INDEXES Dropped."


    ALTER TABLE IMPLICIT_TABLE1 DROP CONSTRAINT IMPLICIT_TABLE1_PK;

    Table altered.

    ALTER TABLE IMPLICIT_TABLE1 DROP CONSTRAINT IMPLICIT_TABLE1_UNIQ_COL_UK;

    Table altered.

    ALTER TABLE EXPLICIT_TABLE DROP CONSTRAINT EXPLICIT_TABLE_PK;

    Table altered.

    ALTER TABLE EXPLICIT_TABLE DROP CONSTRAINT EXPLICIT_TABLE_UNIQ_COL_UK;

    Table altered.

    ALTER TABLE IMPLICIT_TABLE DROP CONSTRAINT IMPLICIT_TABLE_PK;

    Table altered.

    ALTER TABLE IMPLICIT_TABLE DROP CONSTRAINT IMPLICIT_TABLE_UNIQ_COL_UK;

    Table altered.

    select index_name, table_name, index_type, uniqueness, generated
    2 from user_indexes
    3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
    4 order by table_name, index_name;

    INDEX_NAME TABLE_NAME INDEX_TYPE UNIQUENES G
    ------------------------------ --------------- --------------------------- --------- -
    EXPLICIT_TABLE_NON_UNIQUE_IX EXPLICIT_TABLE NORMAL NONUNIQUE N
    EXPLICIT_TABLE_PX EXPLICIT_TABLE NORMAL UNIQUE N
    EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE_NON_UNIQUE_IX IMPLICIT_TABLE NORMAL NONUNIQUE N
    IMPLICIT_TABLE1_NON_UNIQUE_IX IMPLICIT_TABLE1 NORMAL NONUNIQUE N


    select constraint_name, constraint_type, index_name, table_name, generated
    2 from user_constraints
    3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
    4 order by table_name, constraint_name;

    no rows selected


    select
    2 obj_t.object_name table_name, obj_i.object_name index_name,
    3 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
    4 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
    5 from
    6 dba_objects obj_t,
    7 dba_objects obj_i,
    8 sys.ind$ ind
    9 where
    10 ind.bo#<http://ind.bo> = obj_t.object_id
    11 and ind.obj# = obj_i.object_id
    12 and obj_i.owner = 'DBMON'
    13 and obj_i.object_name in
    14 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX', 'IMPLICIT_TABLE_NON_UNIQUE_IX',
    15 'IMPLICIT_TABLE1_PX', 'IMPLICIT_TABLE1_UNIQ_COL_UX', 'IMPLICIT_TABLE1_NON_UNIQUE_IX',
    16 'EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX', 'EXPLICIT_TABLE_NON_UNIQUE_IX')
    17 order by index_name, table_name;
    TABLE_NAME INDEX_NAME IMPLICIT UNIQUE
    --------------- ------------------------------ -------- ------
    EXPLICIT_TABLE EXPLICIT_TABLE_NON_UNIQUE_IX NO NO
    EXPLICIT_TABLE EXPLICIT_TABLE_PX NO YES
    EXPLICIT_TABLE EXPLICIT_TABLE_UNIQ_COL_UX NO YES
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_NON_UNIQUE_IX NO NO
    IMPLICIT_TABLE IMPLICIT_TABLE_NON_UNIQUE_IX NO NO


    set echo off

    Step 3 drop tables from another session and import. Then look.

    AFTER IMPORTING TABLES,
    All Indexes, IMPLICIT AND EXPLICIT have been imported Sucessfully
    EXCEPT NOW ALL NINE OF THEM ARE EXPLICIT!
    --- Need that example index that will cause one not to import.

    AFTER IMPORTING TABLES, Hit any key to continue.



    select index_name, table_name, index_type, uniqueness, generated
    2 from user_indexes
    3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
    4 order by table_name, index_name;

    INDEX_NAME TABLE_NAME INDEX_TYPE UNIQUENES G
    ------------------------------ --------------- --------------------------- --------- -
    EXPLICIT_TABLE_NON_UNIQUE_IX EXPLICIT_TABLE NORMAL NONUNIQUE N
    EXPLICIT_TABLE_PX EXPLICIT_TABLE NORMAL UNIQUE N
    EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE_NON_UNIQUE_IX IMPLICIT_TABLE NORMAL NONUNIQUE N
    IMPLICIT_TABLE_PX IMPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE_UNIQ_COL_UX IMPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE1_NON_UNIQUE_IX IMPLICIT_TABLE1 NORMAL NONUNIQUE N
    IMPLICIT_TABLE1_PX IMPLICIT_TABLE1 NORMAL UNIQUE N
    IMPLICIT_TABLE1_UNIQ_COL_UX IMPLICIT_TABLE1 NORMAL UNIQUE N

    9 rows selected.


    select constraint_name, constraint_type, index_name, table_name, generated
    2 from user_constraints
    3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
    4 order by table_name, constraint_name;

    CONSTRAINT_NAME C INDEX_NAME TABLE_NAME GENERATED
    ------------------------------ - ------------------------------ --------------- --------------
    EXPLICIT_TABLE_PK P EXPLICIT_TABLE_PX EXPLICIT_TABLE USER NAME
    EXPLICIT_TABLE_UNIQ_COL_UK U EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE USER NAME
    IMPLICIT_TABLE_PK P IMPLICIT_TABLE_NON_UNIQUE_IX IMPLICIT_TABLE USER NAME
    IMPLICIT_TABLE_UNIQ_COL_UK U IMPLICIT_TABLE_UNIQ_COL_UX IMPLICIT_TABLE USER NAME
    IMPLICIT_TABLE1_PK P IMPLICIT_TABLE1_PX IMPLICIT_TABLE1 USER NAME
    IMPLICIT_TABLE1_UNIQ_COL_UK U IMPLICIT_TABLE1_UNIQ_COL_UX IMPLICIT_TABLE1 USER NAME

    6 rows selected.


    select
    2 obj_t.object_name table_name, obj_i.object_name index_name,
    3 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
    4 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
    5 from
    6 dba_objects obj_t,
    7 dba_objects obj_i,
    8 sys.ind$ ind
    9 where
    10 ind.bo#<http://ind.bo> = obj_t.object_id
    11 and ind.obj# = obj_i.object_id
    12 and obj_i.owner = 'DBMON'
    13 and obj_i.object_name in
    14 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX', 'IMPLICIT_TABLE_NON_UNIQUE_IX',
    15 'IMPLICIT_TABLE1_PX', 'IMPLICIT_TABLE1_UNIQ_COL_UX', 'IMPLICIT_TABLE1_NON_UNIQUE_IX',
    16 'EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX', 'EXPLICIT_TABLE_NON_UNIQUE_IX')
    17 order by table_name, index_name;

    TABLE_NAME INDEX_NAME IMPLICIT UNIQUE
    --------------- ------------------------------ -------- ------
    EXPLICIT_TABLE EXPLICIT_TABLE_NON_UNIQUE_IX NO NO
    EXPLICIT_TABLE EXPLICIT_TABLE_PX NO YES
    EXPLICIT_TABLE EXPLICIT_TABLE_UNIQ_COL_UX NO YES
    IMPLICIT_TABLE IMPLICIT_TABLE_NON_UNIQUE_IX NO NO
    IMPLICIT_TABLE IMPLICIT_TABLE_PX NO YES
    IMPLICIT_TABLE IMPLICIT_TABLE_UNIQ_COL_UX NO YES
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_NON_UNIQUE_IX NO NO
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_PX NO YES
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_UNIQ_COL_UX NO YES

    9 rows selected.


    set echo off
    Step 5)
    Drop Constraints again, but this time AFTER IMPORT of original tables, Look.
    ALL INDEXES REMAIN -- and REMAIN EXPLICIT, therefore none drop.


    ALTER TABLE IMPLICIT_TABLE1 DROP CONSTRAINT IMPLICIT_TABLE1_PK;

    Table altered.

    ALTER TABLE IMPLICIT_TABLE1 DROP CONSTRAINT IMPLICIT_TABLE1_UNIQ_COL_UK;

    Table altered.

    ALTER TABLE EXPLICIT_TABLE DROP CONSTRAINT EXPLICIT_TABLE_PK;

    Table altered.

    ALTER TABLE EXPLICIT_TABLE DROP CONSTRAINT EXPLICIT_TABLE_UNIQ_COL_UK;

    Table altered.

    ALTER TABLE IMPLICIT_TABLE DROP CONSTRAINT IMPLICIT_TABLE_PK;

    Table altered.

    ALTER TABLE IMPLICIT_TABLE DROP CONSTRAINT IMPLICIT_TABLE_UNIQ_COL_UK;

    Table altered.


    select index_name, table_name, index_type, uniqueness, generated
    2 from user_indexes
    3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
    4 order by table_name, index_name;

    INDEX_NAME TABLE_NAME INDEX_TYPE UNIQUENES G
    ------------------------------ --------------- --------------------------- --------- -
    EXPLICIT_TABLE_NON_UNIQUE_IX EXPLICIT_TABLE NORMAL NONUNIQUE N
    EXPLICIT_TABLE_PX EXPLICIT_TABLE NORMAL UNIQUE N
    EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE_NON_UNIQUE_IX IMPLICIT_TABLE NORMAL NONUNIQUE N
    IMPLICIT_TABLE_PX IMPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE_UNIQ_COL_UX IMPLICIT_TABLE NORMAL UNIQUE N
    IMPLICIT_TABLE1_NON_UNIQUE_IX IMPLICIT_TABLE1 NORMAL NONUNIQUE N
    IMPLICIT_TABLE1_PX IMPLICIT_TABLE1 NORMAL UNIQUE N
    IMPLICIT_TABLE1_UNIQ_COL_UX IMPLICIT_TABLE1 NORMAL UNIQUE N

    9 rows selected.


    select constraint_name, constraint_type, index_name, table_name, generated
    2 from user_constraints
    3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
    4 order by table_name, constraint_name;

    no rows selected


    select
    2 obj_t.object_name table_name, obj_i.object_name index_name,
    3 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
    4 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
    5 from
    6 dba_objects obj_t,
    7 dba_objects obj_i,
    8 sys.ind$ ind
    9 where
    10 ind.bo#<http://ind.bo> = obj_t.object_id
    11 and ind.obj# = obj_i.object_id
    12 and obj_i.owner = 'DBMON'
    13 and obj_i.object_name in
    14 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX', 'IMPLICIT_TABLE_NON_UNIQUE_IX',
    15 'IMPLICIT_TABLE1_PX', 'IMPLICIT_TABLE1_UNIQ_COL_UX', 'IMPLICIT_TABLE1_NON_UNIQUE_IX',
    16 'EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX', 'EXPLICIT_TABLE_NON_UNIQUE_IX')
    17 order by table_name, index_name;

    TABLE_NAME INDEX_NAME IMPLICIT UNIQUE
    --------------- ------------------------------ -------- ------
    EXPLICIT_TABLE EXPLICIT_TABLE_NON_UNIQUE_IX NO NO
    EXPLICIT_TABLE EXPLICIT_TABLE_PX NO YES
    EXPLICIT_TABLE EXPLICIT_TABLE_UNIQ_COL_UX NO YES
    IMPLICIT_TABLE IMPLICIT_TABLE_NON_UNIQUE_IX NO NO
    IMPLICIT_TABLE IMPLICIT_TABLE_PX NO YES
    IMPLICIT_TABLE IMPLICIT_TABLE_UNIQ_COL_UX NO YES
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_NON_UNIQUE_IX NO NO
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_PX NO YES
    IMPLICIT_TABLE1 IMPLICIT_TABLE1_UNIQ_COL_UX NO YES

    9 rows selected.


    spool off

    Joel Patterson
    Database Administrator
    904 727-2546



    --
    http://www.freelists.org/webpage/oracle-l




    --
    =============================================
    TRUTH WINS AT LAST, DO NOT FORGET TO SMILE TODAY
    =============================================

    --
    http://www.freelists.org/webpage/oracle-l
  • D'Hooge Freek at Nov 17, 2011 at 1:15 pm
    Hey
    In attach you will find an example which shows an implicit unique index not being recreated during import because the constraint is using an explicit index instead.
    Also note the failed import of some of the index_statistics, which can act as a red flag to detect such a case.


    Regards,


    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge@uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer: www.uptime.be/disclaimer
    ---
    From: Joel.Patterson@crowley.com
    Sent: woensdag 16 november 2011 20:47
    To: D'Hooge Freek; oracle-l@freelists.org
    Subject: Naming standards -- that lead to implicit/explicit constraint and index creation.


    Oracle version 10.2.0.4

    I would appreciate an example where an Index is created such that by creating an implicit primary key or unique index on the table, would cause one of the indexes not to be imported.
    ...
  • D'Hooge Freek at Nov 17, 2011 at 1:22 pm
    It seems attachments are silently removed when sending to the list, so I resend it with the output inline.


    [oracle1@elin ~]$ sqlplus fdh

    SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 17 13:53:11 2011

    Copyright (c) 1982, 2010, Oracle. All rights reserved.

    Enter password:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> drop table t;

    Table dropped.

    SQL> create table t (veld1 number(10,0), veld2 number(10,0));

    Table created.

    SQL> alter table t add primary key (veld1);

    Table altered.

    SQL> create index t_i_nonunique on t (veld1, veld2);

    Index created.

    SQL> insert into t values (1, 2);

    1 row created.

    SQL> insert into t values (2, 3);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select index_name, index_type, uniqueness from user_indexes where table_name = 'T';

    INDEX_NAME INDEX_TYPE UNIQUENES
    ------------------------------ --------------------------- ---------
    T_I_NONUNIQUE NORMAL NONUNIQUE
    SYS_C0014660 NORMAL UNIQUE

    SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name = 'T';

    CONSTRAINT_NAME C INDEX_NAME
    ------------------------------ - ------------------------------
    SYS_C0014660 P SYS_C0014660

    SQL> column index_owner format a20
    SQL> column index_name format a20
    SQL> column is_system_generated format a5 heading GEN
    SQL> column is_unique format a5 heading UNQ
    SQL>
    SQL> select
    2 obj_i.owner index_owner,
    3 obj_i.object_name index_name,
    4 decode(bitand(ind.property,4096),4096, 'YES', 'NO') is_system_generated,
    5 decode(bitand(ind.property,1),1, 'YES', 'NO') is_unique
    6 from
    7 dba_objects obj_t,
    8 dba_objects obj_i,
    9 sys.ind$ ind
    10 where
    11 ind.bo# = obj_t.object_id
    12 and ind.obj# = obj_i.object_id
    13 and obj_t.owner = 'FDH'
    14 and obj_t.object_name = 'T'
    15 order by
    16 index_owner, index_name;

    INDEX_OWNER INDEX_NAME GEN UNQ
    -------------------- -------------------- ----- -----
    FDH SYS_C0014660 YES YES
    FDH T_I_NONUNIQUE NO NO


    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    [oracle1@elin ~]$ expdp system schemas=FDH dumpfile=implicit_test.dmp

    Export: Release 11.2.0.2.0 - Production on Thu Nov 17 13:56:00 2011

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
    Password:

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=FDH dumpfile=implicit_test.dmp
    Estimate in progress using BLOCKS method...
    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type SCHEMA_EXPORT/USER
    Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
    Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
    Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    . . exported "FDH"."T" 5.507 KB 2 rows
    Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
    /u01/oracle/oracle1/admin/gunnar/dpdump/implicit_test.dmp
    Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:56:23

    [oracle1@elin ~]$ impdp system remap_schema=FDH:FDH99 dumpfile=implicit_test.dmp

    Import: Release 11.2.0.2.0 - Production on Thu Nov 17 13:57:55 2011

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
    Password:

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
    Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** remap_schema=FDH:FDH99 dumpfile=implicit_test.dmp
    Processing object type SCHEMA_EXPORT/USER
    Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
    Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
    Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    . . imported "FDH99"."T" 5.507 KB 2 rows
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    ORA-39083: Object type INDEX_STATISTICS failed to create with error:
    ORA-01403: no data found
    ORA-01403: no data found
    Failing sql is:
    DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';BEGIN DELET
    Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 13:58:00

    [oracle1@elin ~]$ sqlplus fdh99

    SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 17 13:58:26 2011

    Copyright (c) 1982, 2010, Oracle. All rights reserved.

    Enter password:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> set linesize 150
    set pages 9999
    SQL> SQL>
    SQL> select index_name, index_type, uniqueness from user_indexes where table_name = 'T';

    INDEX_NAME INDEX_TYPE UNIQUENES
    ------------------------------ --------------------------- ---------
    T_I_NONUNIQUE NORMAL NONUNIQUE

    SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name = 'T';

    CONSTRAINT_NAME C INDEX_NAME
    ------------------------------ - ------------------------------
    SYS_C0014675 P T_I_NONUNIQUE

    SQL> column index_owner format a20
    SQL> column index_name format a20
    SQL> column is_system_generated format a5 heading GEN
    SQL> column is_unique format a5 heading UNQ
    SQL>
    SQL> select
    2 obj_i.owner index_owner,
    3 obj_i.object_name index_name,
    4 decode(bitand(ind.property,4096),4096, 'YES', 'NO') is_system_generated,
    5 decode(bitand(ind.property,1),1, 'YES', 'NO') is_unique
    6 from
    7 dba_objects obj_t,
    8 dba_objects obj_i,
    9 sys.ind$ ind
    10 where
    11 ind.bo# = obj_t.object_id
    12 and ind.obj# = obj_i.object_id
    13 and obj_t.owner = 'FDH99'
    14 and obj_t.object_name = 'T'
    15 order by
    16 index_owner, index_name;

    INDEX_OWNER INDEX_NAME GEN UNQ
    -------------------- -------------------- ----- -----
    FDH99 T_I_NONUNIQUE NO NO

    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    [oracle1@elin ~]$


    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge@uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer: www.uptime.be/disclaimer
  • Joel Patterson at Nov 17, 2011 at 4:12 pm
    Thanks Freek,

    Your query heading changed from implicit to system generated which I appreciate. All comments and additions are welcomed.

    I'm going to refine my script that generated the output in email sent 11/17@7:25am. My head needs to clear a bit. If anyone wants it when I'm done, let me know.

    I changed your example below to name the index (via the constraint create). ind$.property still returns YES, (because the index is implicitly created). The index does import in this case -- and changes to NO upon import (because name exists in export file -- note user assumption).

    So it appears that the ideal scenario is for the index to be created first (named), 'and' then explicitly attach the constraint to the index. (I don't think you can create an index on a table first with a system generated name).

    This is the only way for the ind$.property to return NO before export/import (that I have discovered so far).

    In your below example I made one change: (except also on 10.2.0.4)

    alter table t add constraint named_pk primary key (veld1);

    Before Export
    INDEX_NAME GEN UNQ
    -------------------- ----- -----
    NAMED_PK YES YES -- index was created with create constraint stmt. (if not named, this index disappears).
    T_I_NONUNIQUE NO NO

    After Import
    INDEX_NAME GEN UNQ
    -------------------- ----- -----
    NAMED_PK NO YES -- index already had name... so no longer system generated.
    T_I_NONUNIQUE NO NO



    Here is a couple results for now just to try and get them down quickly.

    a. If the index is named via the create constraint statement (implicitly creating an index with the same name), it will not be dropped. It is not necessary to 'explicitly' attach the constraint to the index to prevent this -- however dropping constraints will still drop the index.

    b. The corollary to 'a.' is, if the constraint is not named, the constraint name is system generated and the index name takes on the same name as the constraint. Then the index can be lost upon import given there is an index laying around it can use.
    b1. If no other index is laying around that can be used, the index will not be technically 'lost', but recreated with a 'new' system generated name.

    c. If you 'do' explicitly attach the constraint to the index, then you can drop the constraint and the index will not drop. Statistics will not be lost, and no indexes will be lost.


    (Caveat here, I did not see a statistics error in import log, but no statistics where imported -- 10g vs. 11g, or parameter difference).
    d. It appears the statistics are lost because either the new name is different, or there is one less index, -- thus invalidating them.
    d1. For example: when only the primary key index existed with and the name was system generated. That name changed to a new system generated number (name) -- I'm assuming the statistics where attached to the old name, and thus caused the error, and/or did not import.



    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: D'Hooge Freek
    Sent: Thursday, November 17, 2011 8:23 AM
    To: Patterson, Joel; oracle-l@freelists.org
    Subject: RE: Naming standards -- that lead to implicit/explicit constraint and index creation.

    It seems attachments are silently removed when sending to the list, so I resend it with the output inline.


    [oracle1@elin ~]$ sqlplus fdh

    SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 17 13:53:11 2011

    Copyright (c) 1982, 2010, Oracle. All rights reserved.

    Enter password:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> drop table t;

    Table dropped.

    SQL> create table t (veld1 number(10,0), veld2 number(10,0));

    Table created.

    SQL> alter table t add primary key (veld1);

    Table altered.

    SQL> create index t_i_nonunique on t (veld1, veld2);

    Index created.

    SQL> insert into t values (1, 2);

    1 row created.

    SQL> insert into t values (2, 3);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select index_name, index_type, uniqueness from user_indexes where table_name = 'T';

    INDEX_NAME INDEX_TYPE UNIQUENES
    ------------------------------ --------------------------- ---------
    T_I_NONUNIQUE NORMAL NONUNIQUE
    SYS_C0014660 NORMAL UNIQUE

    SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name = 'T';

    CONSTRAINT_NAME C INDEX_NAME
    ------------------------------ - ------------------------------
    SYS_C0014660 P SYS_C0014660

    SQL> column index_owner format a20
    SQL> column index_name format a20
    SQL> column is_system_generated format a5 heading GEN
    SQL> column is_unique format a5 heading UNQ
    SQL>
    SQL> select
    2 obj_i.owner index_owner,
    3 obj_i.object_name index_name,
    4 decode(bitand(ind.property,4096),4096, 'YES', 'NO') is_system_generated,
    5 decode(bitand(ind.property,1),1, 'YES', 'NO') is_unique
    6 from
    7 dba_objects obj_t,
    8 dba_objects obj_i,
    9 sys.ind$ ind
    10 where
    11 ind.bo# = obj_t.object_id
    12 and ind.obj# = obj_i.object_id
    13 and obj_t.owner = 'FDH'
    14 and obj_t.object_name = 'T'
    15 order by
    16 index_owner, index_name;

    INDEX_OWNER INDEX_NAME GEN UNQ
    -------------------- -------------------- ----- -----
    FDH SYS_ed C0014660 YES YES
    FDH T_I_NONUNIQUE NO NO


    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    [oracle1@elin ~]$ expdp system schemas=FDH dumpfile=implicit_test.dmp

    Export: Release 11.2.0.2.0 - Production on Thu Nov 17 13:56:00 2011

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
    Password:

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=FDH dumpfile=implicit_test.dmp
    Estimate in progress using BLOCKS method...
    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type SCHEMA_EXPORT/USER
    Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
    Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
    Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    . . exported "FDH"."T" 5.507 KB 2 rows
    Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
    /u01/oracle/oracle1/admin/gunnar/dpdump/implicit_test.dmp
    Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:56:23

    [oracle1@elin ~]$ impdp system remap_schema=FDH:FDH99 dumpfile=implicit_test.dmp

    Import: Release 11.2.0.2.0 - Production on Thu Nov 17 13:57:55 2011

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
    Password:

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
    Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** remap_schema=FDH:FDH99 dumpfile=implicit_test.dmp
    Processing object type SCHEMA_EXPORT/USER
    Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
    Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
    Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    . . imported "FDH99"."T" 5.507 KB 2 rows
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    ORA-39083: Object type INDEX_STATISTICS failed to create with error:
    ORA-01403: no data found
    ORA-01403: no data found
    Failing sql is:
    DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';BEGIN DELET
    Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 13:58:00

    [oracle1@elin ~]$ sqlplus fdh99

    SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 17 13:58:26 2011

    Copyright (c) 1982, 2010, Oracle. All rights reserved.

    Enter password:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> set linesize 150
    set pages 9999
    SQL> SQL>
    SQL> select index_name, index_type, uniqueness from user_indexes where table_name = 'T';

    INDEX_NAME INDEX_TYPE UNIQUENES
    ------------------------------ --------------------------- ---------
    T_I_NONUNIQUE NORMAL NONUNIQUE

    SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name = 'T';

    CONSTRAINT_NAME C INDEX_NAME
    ------------------------------ - ------------------------------
    SYS_C0014675 P T_I_NONUNIQUE

    SQL> column index_owner format a20
    SQL> column index_name format a20
    SQL> column is_system_generated format a5 heading GEN
    SQL> column is_unique format a5 heading UNQ
    SQL>
    SQL> select
    2 obj_i.owner index_owner,
    3 obj_i.object_name index_name,
    4 decode(bitand(ind.property,4096),4096, 'YES', 'NO') is_system_generated,
    5 decode(bitand(ind.property,1),1, 'YES', 'NO') is_unique
    6 from
    7 dba_objects obj_t,
    8 dba_objects obj_i,
    9 sys.ind$ ind
    10 where
    11 ind.bo# = obj_t.object_id
    12 and ind.obj# = obj_i.object_id
    13 and obj_t.owner = 'FDH99'
    14 and obj_t.object_name = 'T'
    15 order by
    16 index_owner, index_name;

    INDEX_OWNER INDEX_NAME GEN UNQ
    -------------------- -------------------- ----- -----
    FDH99 T_I_NONUNIQUE NO NO

    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    [oracle1@elin ~]$


    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge@uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer: www.uptime.be/disclaimer

    --
    http://www.freelists.org/webpage/oracle-l
  • Joel Patterson at Nov 17, 2011 at 6:30 pm
    This create table example appears that one is explicitly creating an index for the purpose of handling the constraints, (but is not).

    Akin to create index, then separately, create constraint using index. Yet oracle treats this as if it the indexes were implicitly created, and the ind$property field indicates that the name is system generated.

    Does anyone know why that is? Seems like many could look at it that way mistakenly.

    CREATE TABLE IMPLICIT_TABLE1 (
    PRIM_COL number constraint IMPLICIT_TABLE1_PK PRIMARY KEY
    using index ( CREATE UNIQUE INDEX IMPLICIT_TABLE1_PX ON
    IMPLICIT_TABLE1(PRIM_COL)),
    UNIQ_COL number constraint IMPLICIT_TABLE1_UNIQ_COL_UK UNIQUE
    using index ( create UNIQUE index IMPLICIT_TABLE1_UNIQ_COL_UX on
    IMPLICIT_TABLE1(UNIQ_COL)),
    junk varchar2(10)
    );

    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Joel.Patterson@crowley.com
    Sent: Thursday, November 17, 2011 11:11 AM
    To: Freek.DHooge@uptime.be; oracle-l@freelists.org
    Subject: RE: Naming standards -- that lead to implicit/explicit constraint and index creation.

    Thanks Freek,

    Your query heading changed from implicit to system generated which I appreciate. All comments and additions are welcomed.

    I'm going to refine my script that generated the output in email sent 11/17@7:25am. My head needs to clear a bit. If anyone wants it when I'm done, let me know.

    I changed your example below to name the index (via the constraint create). ind$.property still returns YES, (because the index is implicitly created). The index does import in this case -- and changes to NO upon import (because name exists in export file -- note user assumption).

    So it appears that the ideal scenario is for the index to be created first (named), 'and' then explicitly attach the constraint to the index. (I don't think you can create an index on a table first with a system generated name).

    This is the only way for the ind$.property to return NO before export/import (that I have discovered so far).

    In your below example I made one change: (except also on 10.2.0.4)

    alter table t add constraint named_pk primary key (veld1);

    Before Export
    INDEX_NAME GEN UNQ
    -------------------- ----- -----
    NAMED_PK YES YES -- index was created with create constraint stmt. (if not named, this index disappears).
    T_I_NONUNIQUE NO NO

    After Import
    INDEX_NAME GEN UNQ
    -------------------- ----- -----
    NAMED_PK NO YES -- index already had name... so no longer system generated.
    T_I_NONUNIQUE NO NO



    Here is a couple results for now just to try and get them down quickly.

    a. If the index is named via the create constraint statement (implicitly creating an index with the same name), it will not be dropped. It is not necessary to 'explicitly' attach the constraint to the index to prevent this -- however dropping constraints will still drop the index.

    b. The corollary to 'a.' is, if the constraint is not named, the constraint name is system generated and the index name takes on the same name as the constraint. Then the index can be lost upon import given there is an index laying around it can use.
    b1. If no other index is laying around that can be used, the index will not be technically 'lost', but recreated with a 'new' system generated name.

    c. If you 'do' explicitly attach the constraint to the index, then you can drop the constraint and the index will not drop. Statistics will not be lost, and no indexes will be lost.


    (Caveat here, I did not see a statistics error in import log, but no statistics where imported -- 10g vs. 11g, or parameter difference).
    d. It appears the statistics are lost because either the new name is different, or there is one less index, -- thus invalidating them.
    d1. For example: when only the primary key index existed with and the name was system generated. That name changed to a new system generated number (name) -- I'm assuming the statistics where attached to the old name, and thus caused the error, and/or did not import.



    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: D'Hooge Freek
    Sent: Thursday, November 17, 2011 8:23 AM
    To: Patterson, Joel; oracle-l@freelists.org
    Subject: RE: Naming standards -- that lead to implicit/explicit constraint and index creation.

    It seems attachments are silently removed when sending to the list, so I resend it with the output inline.


    [oracle1@elin ~]$ sqlplus fdh

    SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 17 13:53:11 2011

    Copyright (c) 1982, 2010, Oracle. All rights reserved.

    Enter password:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> drop table t;

    Table dropped.

    SQL> create table t (veld1 number(10,0), veld2 number(10,0));

    Table created.

    SQL> alter table t add primary key (veld1);

    Table altered.

    SQL> create index t_i_nonunique on t (veld1, veld2);

    Index created.

    SQL> insert into t values (1, 2);

    1 row created.

    SQL> insert into t values (2, 3);

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select index_name, index_type, uniqueness from user_indexes where table_name = 'T';

    INDEX_NAME INDEX_TYPE UNIQUENES
    ------------------------------ --------------------------- ---------
    T_I_NONUNIQUE NORMAL NONUNIQUE
    SYS_C0014660 NORMAL UNIQUE

    SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name = 'T';

    CONSTRAINT_NAME C INDEX_NAME
    ------------------------------ - ------------------------------
    SYS_C0014660 P SYS_C0014660

    SQL> column index_owner format a20
    SQL> column index_name format a20
    SQL> column is_system_generated format a5 heading GEN
    SQL> column is_unique format a5 heading UNQ
    SQL>
    SQL> select
    2 obj_i.owner index_owner,
    3 obj_i.object_name index_name,
    4 decode(bitand(ind.property,4096),4096, 'YES', 'NO') is_system_generated,
    5 decode(bitand(ind.property,1),1, 'YES', 'NO') is_unique
    6 from
    7 dba_objects obj_t,
    8 dba_objects obj_i,
    9 sys.ind$ ind
    10 where
    11 ind.bo# = obj_t.object_id
    12 and ind.obj# = obj_i.object_id
    13 and obj_t.owner = 'FDH'
    14 and obj_t.object_name = 'T'
    15 order by
    16 index_owner, index_name;

    INDEX_OWNER INDEX_NAME GEN UNQ
    -------------------- -------------------- ----- -----
    FDH SYS_ed C0014660 YES YES
    FDH T_I_NONUNIQUE NO NO


    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    [oracle1@elin ~]$ expdp system schemas=FDH dumpfile=implicit_test.dmp

    Export: Release 11.2.0.2.0 - Production on Thu Nov 17 13:56:00 2011

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
    Password:

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=FDH dumpfile=implicit_test.dmp
    Estimate in progress using BLOCKS method...
    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type SCHEMA_EXPORT/USER
    Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
    Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
    Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    . . exported "FDH"."T" 5.507 KB 2 rows
    Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
    ******************************************************************************
    Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
    /u01/oracle/oracle1/admin/gunnar/dpdump/implicit_test.dmp
    Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:56:23

    [oracle1@elin ~]$ impdp system remap_schema=FDH:FDH99 dumpfile=implicit_test.dmp

    Import: Release 11.2.0.2.0 - Production on Thu Nov 17 13:57:55 2011

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
    Password:

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
    Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** remap_schema=FDH:FDH99 dumpfile=implicit_test.dmp
    Processing object type SCHEMA_EXPORT/USER
    Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
    Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
    Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
    Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    Processing object type SCHEMA_EXPORT/TABLE/TABLE
    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
    . . imported "FDH99"."T" 5.507 KB 2 rows
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    ORA-39083: Object type INDEX_STATISTICS failed to create with error:
    ORA-01403: no data found
    ORA-01403: no data found
    Failing sql is:
    DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); NV VARCHAR2(1); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';BEGIN DELET
    Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 13:58:00

    [oracle1@elin ~]$ sqlplus fdh99

    SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 17 13:58:26 2011

    Copyright (c) 1982, 2010, Oracle. All rights reserved.

    Enter password:

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> set linesize 150
    set pages 9999
    SQL> SQL>
    SQL> select index_name, index_type, uniqueness from user_indexes where table_name = 'T';

    INDEX_NAME INDEX_TYPE UNIQUENES
    ------------------------------ --------------------------- ---------
    T_I_NONUNIQUE NORMAL NONUNIQUE

    SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name = 'T';

    CONSTRAINT_NAME C INDEX_NAME
    ------------------------------ - ------------------------------
    SYS_C0014675 P T_I_NONUNIQUE

    SQL> column index_owner format a20
    SQL> column index_name format a20
    SQL> column is_system_generated format a5 heading GEN
    SQL> column is_unique format a5 heading UNQ
    SQL>
    SQL> select
    2 obj_i.owner index_owner,
    3 obj_i.object_name index_name,
    4 decode(bitand(ind.property,4096),4096, 'YES', 'NO') is_system_generated,
    5 decode(bitand(ind.property,1),1, 'YES', 'NO') is_unique
    6 from
    7 dba_objects obj_t,
    8 dba_objects obj_i,
    9 sys.ind$ ind
    10 where
    11 ind.bo# = obj_t.object_id
    12 and ind.obj# = obj_i.object_id
    13 and obj_t.owner = 'FDH99'
    14 and obj_t.object_name = 'T'
    15 order by
    16 index_owner, index_name;

    INDEX_OWNER INDEX_NAME GEN UNQ
    -------------------- -------------------- ----- -----
    FDH99 T_I_NONUNIQUE NO NO

    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    [oracle1@elin ~]$


    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge@uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer: www.uptime.be/disclaimer

    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 16, '11 at 3:11p
activeNov 17, '11 at 6:30p
posts8
users2
websiteoracle.com

2 users in discussion

Joel Patterson: 6 posts D'Hooge Freek: 2 posts

People

Translate

site design / logo © 2022 Grokbase