Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, uma.

Asked: March 31, 2003 - 4:09 pm UTC

Last updated: August 09, 2004 - 3:50 pm UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Hi Tom,

Which is the better way(performance wise) to define the uniqueness in a table for datawarehouse application where there are bulk inserts with no upadtes - Is it with table with primary keys defined on it or in the table with unique index on the keys? For fact tables the primary keys are always composite columns of tables.

When should we go for unique index definition against the primary key definition?

I find Most of the s/w packages (like SIEBEL) are now coming with unique index on tables without primary keys.


Thanks,
Uma





and Tom said...

a primary key is a unique constraint PLUS not null constraints.

all NOT NULL columns should be marked as NOT NULL -- hence, if it is a true primary key -- use it. And mark every possible column as NOT NULL -- that gives the optimizer more information about the nature of the data and opens up access plans that might not otherwise be possible.

If it is a primary key (unique+not null) -- use that.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Siebel Doesn't use PK and FK !

A reader, August 09, 2004 - 2:23 pm UTC

Hi Tom,

I've heard that Siebel doesn't use PK & FK constraints and they use COM to support these. Do you know why?

Don't they lose the Oracle Optimizer feature in this way?

Thanks,
Arash


Tom Kyte
August 09, 2004 - 2:36 pm UTC

tell me, how can you use "COM" to support that.

they do their own application logic, replicating many features of a database in their own code in order to be "database independent" meaning they work somewhat ok on maybe one database and horribly on the others and not good on any.



A reader, August 09, 2004 - 3:50 pm UTC

You are great!

Thanks,
Arash


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library