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 11.2.0.4, 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.

2 Comments »

  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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Blog at WordPress.com.