Re: What makes difference by creating an index or add a primary key to a table

cschang wrote:

> Daniel:
> I have searched such subject on the web. Many concluded the same thing
> as you mentioned, but not much about the performance comparsion. According
> to the closest conclusion, does that mean I only need to create an index (
> or indexes) for speedy performance and I can ignore to create the primary key
> constraint provided that I do not care about the null value of the primary
> key. Besides, there is never be NULL to both column in my case, so whether
> or not the parimary key by two columns does not matter. I did found a
> response that claimed to be faster with the primary key verse the index (very
> similar case as my, but it is on SQL server). That's what I consern.
>
> Daniel Morgan wrote:
>
> > cschang wrote:
> >
> > > System: 8.1.7
> > > Platform: NT 4 w/sp6a
> > > I have a table that I decided to add a primary key by combining two
> > > columns. But what that difference to create an index of the table by
> > > using these two columns?
> > >
> > > C Chang
> >
> > The two objects serve entirely different purposes. The purpose of a
> > constraint is to enforce data integrity. The only purpose of an index is
> > to enhance performance.
> >
> > If one looks more specifically at the differences between a primary key
> > constraint and a unique constraint (reasonably similar to a unique index)
> > there are still differences. Most obviously ... a table can have only a
> > single primary key but many unique constraints ... and a unique
> > constraint allows NULLs whereas you can not have a NULL primary key.
> >
> > --
> > Daniel Morgan
> > http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> > damorgan_at_x.washington.edu
> > (replace 'x' with a 'u' to reply)

I said it once ... I'll say it again. Constraints are for data integrity ... indexes for performane. They serve very different purposes. That is all there is to it.

But before you go running out building indexes you need to understanding of Oracle and the tools to know whether the indexes are helping or hurting performance. Be sure you learn how to use EXPLAIN PLAN and validate each and every index is being used or don't waste your time.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)