Re: 2 Oracle doubts

.. 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]
>
>
Received on Fri Jul 25 2003 - 13:57:09 CDT