Re: optimum datatype for primary key column O9i

From: mcstock <>
Date: Thu, 30 Oct 2003 09:28:58 -0500
Message-ID: <>


You're focusing much to much attention on what really is a non-issue. For many years, any type of NUMBER datatype has been the recommended datatype for PKs. Overhead of NUMBER(10) vs NUMBER vs NUMBER(10,0) etc, etc. is in reality not relevant.

What has a far greater impact on performance are 1) poorly written PL/SQL (typically excessive loops that spawn excessive recursive SQL). 2)poorly indexed tables and 3) poorly written SQL (partial joins, joins to multiple tables rather than natural PK/FK joins, columns wrapped in expressions).

Use NUMBER datatypes, constrain them if you like, and move on to some more important issues.

  • MCS
"Sanjay Minni" <> wrote in message
> > How can anyone give such a rude and incompetent answer to a sensible
> > question?
> >
> > Nobody claimed that leading zeroes can be stored in a number datatype.
> >
> > The question is:
> > Does declaring a column as number(10,0) make it need less memory
> > storage than declaring it simply as number?
> >
> > Are search operations which use the index faster when the index column
> > is declared as number(10,0) as compared to when it is declared as
> > number? [...<added>or as any other type whatsoever given you are using
> > only digits for values]
> > regards,
> > Max
> Max you have summarised to the exact question which I was trying to
> pose
> all along, I have just added the note in [...] for further
> clarification
> Of the above, the second issue on joins is vital specially as there
> are large tables and large joins. A typical SQL for a complex report
> in a substantially normalised database can be a nightmare and
> every bit of optimisation helps (sometimes we have over 10 joins
> and at least 2-3 tables have over 1,000,000 rows, while other
> hover around 100,000 rows). In fact that is where we use Oracle
> Space is not the issue, only its implication in speed is, Infact all
> focus is on optimisation in SQL for Selects with large joins, rows
> sets
> and possible group by clauses
> In response to the observations in the other posts, i would like to
> state that I have been thru the manuals but the could not find
> sufficient material to the issues as summarised above, specially the
> second.
> I suppose "Senior Oracle DBA's" have a right to be ...
Received on Thu Oct 30 2003 - 15:28:58 CET

Original text of this message