Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: orace sql query

Re: orace sql query

From: <>
Date: Tue, 26 Jun 2007 06:20:39 -0000
Message-ID: <>

On Jun 23, 7:50 am, "" <> wrote:
> On Jun 22, 3:45 pm, DA Morgan <> wrote:
> > EdStevens wrote:
> > > On Jun 22, 12:15 pm, wrote:
> > >> i want to create table with primary key but with out index
> > > How do you think Oracle enforces the PK?
> > Not with an index. Indexes have nothing to do with primary key
> > enforcement. Consider, for example, a deferred constraint which
> > is built with a non-unique index.
> > --
> > Daniel A. Morgan
> > University of Washington
> > (replace x with u to respond)
> > Puget Sound Oracle Users
> >From the documentation:
> "Oracle enforces all PRIMARY KEY constraints using indexes. In Figure
> 21-5, the primary key constraint created for the deptno column is
> enforced by the implicit creation of:
> A unique index on that column
> A NOT NULL constraint for that column
> Composite primary key constraints are limited to 32 columns, which is
> the same limitation imposed on composite indexes. The name of the
> index is the same as the name of the constraint. Also, you can specify
> the storage options for the index by including the ENABLE clause in
> the CREATE TABLE or ALTER TABLE statement used to create the
> constraint. If a usable index exists when a primary key constraint is
> created, then the primary key constraint uses that index rather than
> implicitly creating a new one."
> According to Oracle it does.
> David Fitzjarrell

How old is that document? It's not been true since version 8.0.

That's when they invented deferrable constraints -and a deferred primary key (or unique key) constraint is enforced with a NON-unique index.

What I think you are confusing is the concept of "enforcing" an index and assisting in its enforcement. Two different things. The thing which enforces a primary key constraint is... the primary key constraint. It's the TABLE which enforces its own uniqueness/primary key-ness.

The more subtle question is: how does it do that? And the answer in the absence of an index is: by doing a full table scan every time you propose to insert or update a new record. It needs to scan every record to make sure there are no duplicates and absent an index, a FTS is the only way to do it.

That is obviously a no-no as far as reasonable performance is concerned and therefore Oracle uses an index (whether unique or not is irrelevant). A check of uniqueness can be done very efficiently if you scan an index: spot an existing entry that has the same key value as the one you're trying to insert and you can immediately stop scanning: you know the thing's not unique.

The index makes enforcing the constraint a bearable proposition, therefore. But that does not mean the index is "enforcing" the constraint.

Therefore, it's perfectly possible, theoretically, to create a primary key without an index: you just have to agree to full table scans for every insert and update. But that means you'd be pretty weird to actually really want it to happen -and it's certainly not possible in Oracle because Oracle won't let you be that silly. Received on Tue Jun 26 2007 - 01:20:39 CDT

Original text of this message