This post is to explain a little more about a topic I touched on when describing how to generate ddl, which is why creating indexes and constraints separate from your table definition is a good idea.  If you just want to achieve similar functionality in a new environment this probably isn’t a big deal, but it’s something to be aware of and will be necessary if you need an exact copy for testing or migrating.

The idea here is that in order to have full control over your table/index/constraint structure you need to create all of these objects individually.  Allowing Oracle to choose the “best” implementation may not give the desired result, particularly if you’re creating these structures prior to loading potentially large amounts of data.  Below is an example of how things can get changed around.

–Let’s start by creating a table, index, and constraint with the preferred method, which is to create them all individually:

SQL> create table people (name varchar(20), age number(2));
Table created.

SQL> create index people_idx on people (age);
Index created.

SQL> alter table people add constraint people_pk primary key (age) using index people_idx;
Table altered.

–Now go ahead and create the ddl for the table, but set it aside for now:

SQL> set pagesize 0
SQL> set long 150000
SQL> select dbms_metadata.get_ddl('TABLE','PEOPLE','CHARLOTTE') from dual;
CREATE TABLE "CHARLOTTE"."PEOPLE"
( "NAME" VARCHAR2(20),
"AGE" NUMBER(2,0),
CONSTRAINT "PEOPLE_PK" PRIMARY KEY ("AGE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "EORDERS_BLOB" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CHARLOTTE_DATA"

–Verify the index and constraint relationship:

SQL> select index_name from user_indexes where table_name='PEOPLE';
PEOPLE_IDX
SQL> select index_name from user_constraints where constraint_name='PEOPLE_PK';
PEOPLE_IDX

**You can see that the people_idx index we created is the only index on the people table, and that it is being used to enforce the people_pk constraint that we created.  If we drop the constraint, the index still exists:

SQL> alter table people drop constraint people_pk;
Table altered.

SQL> select index_name from user_indexes where table_name='PEOPLE';
PEOPLE_IDX

–Now drop the table, and lets recreate it using the ddl that we generated.  Again, I don’t typically specify all of the storage information when creating objects, but for the sake of the example we’ll use the entire ddl as in theory this should give us a structural copy of the table.

SQL> drop table people;
Table dropped.

SQL> CREATE TABLE "CHARLOTTE"."PEOPLE"
2  (    "NAME" VARCHAR2(20),
3  "AGE" NUMBER(2,0),
4  CONSTRAINT "PEOPLE_PK" PRIMARY KEY ("AGE")
5  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
6  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
7  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
8  TABLESPACE "EORDERS_BLOB"  ENABLE
9  ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
10  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
11  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
12  TABLESPACE "CHARLOTTE_DATA";
Table created.

–And take a look at the index and constraint relationship on the new table:

SQL> select index_name from user_indexes where table_name='PEOPLE';
PEOPLE_PK

SQL> select index_name from user_constraints where constraint_name='PEOPLE_PK';
PEOPLE_PK

You can see that the index has the same name as the constraint here, and..

–If you drop the constraint the index gets dropped too:

SQL> alter table people drop constraint people_pk;
Table altered.

SQL> select index_name from user_indexes where table_name='PEOPLE';
no rows selected

WHY??

In order for unique and primary key constraints to be enforced, there must be an index on the constrained columns.  If an index exists, it can/will be used.  But if there is no existing index (which is the case when you create the constraint as part of the table definition) Oracle will create an underlying index as part of the constraint definition.  This index will “belong to” the constraint, have the same name as the constraint and as demonstrated above, will be dropped when the constraint is dropped.

Keep this in mind when you’re creating objects, particularly when you’re creating a large number of objects in a critical environment.  I’ve done a ton of database migrations, many of them involving data pump and structural changes so this was always something I paid a lot of attention to.

Leave a comment and let me know if you have any questions about this.