Oracle Scratchpad

July 8, 2015

PK Index

Filed under: Indexing,Infrastructure,Oracle — Jonathan Lewis @ 6:08 pm BST Jul 8,2015

Here’s one of those little details that I might have known once, or maybe it wasn’t true in earlier versions of oracle, or maybe I just never noticed it and it’s “always” been true; and it’s a detail I’ll probably have forgotten again a couple of years from now.  Consider the following two ways of creating a table with primary key:

Option 1:

create table orders (
        order_id        number(10,0) not null,
        customer_id     number(10,0) not null,
        date_ordered    date         not null,
        other_bits      varchar2(250),
--      constraint ord_fk_cus foreign key(customer_id) references customers,
        constraint ord_pk primary key(order_id)
tablespace TS_ORD

Option 2:

create table orders (
        order_id        number(10,0) not null,
        customer_id     number(10,0) not null,
        date_ordered    date         not null,
        other_bits      varchar2(250)
tablespace TS_OP_DATA

alter table orders add constraint ord_pk primary key(order_id);

There’s a significant difference between the two strategies (at least in, I haven’t gone back to check earlier versions): in the first form the implicit primary key index is created in the tablespace of the table, in the second form it’s created in the default tablespace of the user. To avoid the risk of putting something in the wrong place you can always add the “using index” clause, for example:

alter table order add constraint ord_pk primary key (order_id) using index tablespace TS_OP_INDX;

Having noticed / reminded myself of this detail I now have on my todo list a task to check the equivalent behaviour when creating partitioned (or composite partitioned) tables – but that’s a task with a very low priority.


  1. Just in case someone mistakes your example as supposing you cannot be specific in the table creation version, this also works:

    create table t13 (
       id number not null,
       payload varchar2(250),
       constraint t13_pk primary key(id) using index tablespace EXAMPLE
    tablespace USERS

    (putting the index in EXAMPLE and the table in USERS).

    Comment by rsiz — July 8, 2015 @ 6:35 pm BST Jul 8,2015 | Reply

  2. I can see how a bug like this (if the discrepancy is really a bug… sure doesn’t seem intentional) could have gone undetected. Until somewhat recently, the common practice/fashion was to put indexes in a separate index tablespace that was parallel to the data tablespace where the table itself was stashed.

    Under that practice, one would always explicitly have to specify the index’s tablespace regardless–using either your syntax or Mark Farnham’s syntax, or creating the index explicitly and imposing the constraint later.

    It’s only in the past several years that the meme has started to the effect of, “It’s all on a SAN anyway, so leave the SAN to do its own I/O balancing and save us the hassle,” or its up-and-coming cousin, “It’s all SSD now anyway, so it absolutely doesn’t matter.” With this meme, I think it’s more likely that this discrepancy will be noticed since more people will want the index to share a tablespace with its table, and will want the default to work to avoid specifying the same tablespace twice.

    Comment by Jason Bucata — July 10, 2015 @ 2:39 pm BST Jul 10,2015 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by