Hi Anurag Varma, thanks for writing this:
>
> .. and why not create this table as an IOT.
>
> Anurag
> "Paul Brewer" <paul_at_paul.brewers.org.uk> wrote in message
> news:3f217acd$1_1_at_mk-nntp-1.news.uk.worldonline.com...
> > Karsten,
> >
> > Sorry. Disagree strongly.
> > Apart from anything else, PK constraint prevents someone from accidentally
> > duplicating an existing 2 char state code. And who on earth would use a
> > sequence, when the US Postal State Code is *intended* to be unique?
> > And what about when you need a FK constraint from your Address table to
> the
> > state table?
> >
> > Regards,
> > Paul
> >
> >
> > "Karsten Farrell" <kfarrell_at_belgariad.com> wrote in message
> > news:MPG.198b01e758561e9f989807_at_news.la.sbcglobal.net...
> > > Hi delavega, thanks for writing this:
> > > > Hi. I´m a MySql programmer, but i need make a application with Oracle
> > > > 8.1 Server. I have 2 doubts (for the moment)
> > > >
> > > > 1- Can i have a Oracle table without primary Key?
> > > > 2- In a sql, the date fields are with quotas ("")? Exists the
> > > > direct comparation between dates (<, >)?
> > > >
> > > > Thanks!
> > > >
> > >
> > > Several posts have asked why you want a table without a primary key. In
> a
> > > pure "data design" sense, that's a good question. However, in
> implementing
> > > a physical representation of your logical design, it's sometimes prudent
> > > to have a table without a primary key.
> > >
> > > For example, we have a table that contains the two-character state
> > > abbreviation and its spelled-out (long) name. Unless something very
> > > unusual happens in the USA, there are only 52 rows in this table. Of
> > > course, I could use the state abbreviation as the PK (since it will be
> > > unique), or even use a sequence, but doing so would cause Oracle to
> > > generate an index for it. Now I know that all 52 states will easily fit
> in
> > > an Oracle block, so the optimizer will never use the index ... which
> means
> > > I've wasted space (not a lot of space, mind you).
> > >
> > > We have other lookup tables that have things like status codes and
> > > descriptions. They also don't have a PK. A full table scan (in memory)
> on
> > > these small tables is not a serious performance hit. So, in conclusion,
> > > the answer is "it depends."
> > >
> > > --
> > > [:%s/Karsten Farrell/Oracle DBA/g]
> >
>
If I remember correctly, IOTs are only available in the Enterprise
Edition. On our development box, we use the Standard Edition (was that way
before I came on board). We don't want one kind of schema on our
development and another on our QA and prod. Flimsy excuse, I know, but
there you have it...
--
[:%s/Karsten Farrell/Oracle DBA/g]
Received on Fri Jul 25 2003 - 14:42:53 CDT
