RE: Naming standards -- that lead to implicit/explicit constraint and index creation.

From: <Joel.Patterson_at_crowley.com>
Date: Thu, 17 Nov 2011 13:27:28 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1B8BB67CE0_at_JAXMSG01.crowley.com>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joel.Patterson_at_crowley.com Sent: Thursday, November 17, 2011 11:11 AM To: Freek.DHooge_at_uptime.be; oracle-l_at_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_at_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.

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.
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.
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 [mailto:Freek.DHooge_at_uptime.be] Sent: Thursday, November 17, 2011 8:23 AM To: Patterson, Joel; oracle-l_at_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_at_elin ~]$ sqlplus fdh

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

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_at_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

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_at_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

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_at_elin ~]$ sqlplus fdh99

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

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_at_elin ~]$

Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_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

Received on Thu Nov 17 2011 - 12:27:28 CST

This message: [ Message body ]
Next message: Jorgensen, Finn: "RE: Finding WLS Server Process connecting to Oracle"
Previous message: Taylor, Chris David: "RE: Strategies for dealing with (NOT EQUAL) conditions and indexes"
In reply to: Joel.Patterson_at_crowley.com: "RE: Naming standards -- that lead to implicit/explicit constraint and index creation."

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message