Re: Creating new database.

Certainly a single-field, non concatenated, index of any type is more efficient. But PKs are generally defined by the business requirement. If there is a performance issue at hand perhaps that should be explored and using a surrogate key considered as one of many possible solutions. But I wouldn't go out there trying to fix what isn't broken.

PS: The problem with surrogate keys is that often they allow in dupicate data when you consider natural keys. And adding a unique constraint only adds more overhead, not less.

Daniel Morgan

Richard Nield wrote:

> Daniel....thanks for that! I must admit I had not thought of the task in
> that way....everything seems to be coming together at the end of the FY, I
> just wanted to get started with the new Db....but you're right, the db has
> not changed much, apart from a few new tables, since it was created.
>
> this may sound like an odd question but, would you agree that having tables
> with a single field PK is more efficient than using more than one field as
> pk? For example - the pk of our stock table is the unique combination of
> manufacture code, county code and stock id code. Which I have always though
> of as inefficient, especially as every related table holds the same pk.
> When I was involved with a similar project a few years ago, in ingress.,
> the stock number only appear in one table,, all the other related table
> just reference the id of the item.
>
> thanks
>
> Richard
>
> "damorgan" <dan.morgan_at_ci.seattle.wa.us> wrote in message
> news:3C742ABE.CDE97561_at_ci.seattle.wa.us...
> > Any DBA can clone an existing instance. But there are some terrible prices
> to
> > pay:
> >
> > 1. Inability to resize and redistribute objects based on historical
> experience.
> > 2. Inability to convert tablespaces from dictionary managed to locally
> managed
> > 3. Inability to learn new skills
> > 4. A stagnant resume that will make it harder for you to get your next
> job.
> >
> > You are being handed a plum. Eat it!
> >
> > Daniel Morgan
> >
> >
> >
> > Richard Nield wrote:
> >
> > > Hi...
> > >
> > > we have an oracle database in which we hold stock, transaction and
> contract
> > > data for several large stock holding warehouses, for accounting
> purposes.
> > > Each year we freeze the old db and start a new one. This is the first
> year
> > > I have been involved in the creation of the new db....the dba has said
> he
> > > will create the database, but I will have to provide the scripts to
> create
> > > the tables, indexes and constraints. my question is, should he not have
> a
> > > way of cloning the old database to make the new - especially as most of
> the
> > > data is copied from the old to the new?
> > >
> > > We are using oracle 8i.....I original database was created using
> designer in
> > > 1999.
> > >
> > > Hope that make sense! many thanks for any advise.
> >
Received on Thu Feb 21 2002 - 15:53:55 CST