Re: Simple Questions

In article <3743f789.3402823_at_newshost.us.oracle.com>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to mkares_at_my-dejanews.com
> (if that email address didn't require changing)
> On Wed, 19 May 1999 18:34:59 GMT, you wrote:
>
> >Hi, I have a few easy questions for any Oracle Gurus out there:
> >
> >1. Does the data type PLS_INTEGER use four bytes to store a value
(it's
> >possible ranges in value are -+ 2 Gigabytes, which is the same as a
> >signed integer in a 32 bit programming environment)?
> >
>
> yes -- but you cannot store a pls_integer in a table. pls_integer is
just a
> 'fast integer' in plsql.

First of all, thanks for your very knowledgeable reply. I have a few more questions as a result of your answers, though...

Is there any data type that will create a column of integer numbers in Oracle?

>
> >2. If you declare a column with a data type of NUMBER(10), are there
> >only ten bytes taken for storage, or is it still 22 bytes?
Conversely,
> >declaring the same column as VARCHAR2(10) is it or is it not more
> >efficient than numeric? (In the case of using the column as a primary
> >key).
>
> it depends. it depends on the value in the number. You can use
vsize to see
> some of this, for example:
>
> SQL> select vsize(1) from dual union all
> 2 select vsize(1000) from dual union all
> 3 select vsize(9999) from dual;
>
> VSIZE(1)
> ----------
> 2
> 2
> 3
>
> so the number 9999 takes more storage then 1000.
>
> Server concepts manual, chapter 6, section on "Number Datatype"
includes this
> (and much more) about the number datatype:
>
> <quote>
> Oracle stores numeric data in variable–length format. Each value is
> stored in scientific notation, with one byte used to store the
exponent
> and up to 20 bytes to store the mantissa. (However, there are only 38
> digits of precision.) Oracle does not store leading and trailing
zeros. For
> example, the number 412 is stored in a format similar to 4.12 x 10^2,
> with one byte used to store the exponent (2) and two bytes used to
> store the three significant digits of the mantissa (4, 1, 2).
> <quote>
>
> So, the number 412 in a number(3) or a number(38) will consume the
same exact
> amount of storage. Storage wise -- setting the scale and precision
is not
> meaningful. Application wise -- setting the scale and precision is
very
> meaningful. Fix the numbers not for the storage but for the fact
that a
> number(3) is just that -- a number with 3 digits of precision.
Consider the
> scale and precision to be constraints, they can edit your data.
>

Thanks, this clears up a fair bit for me. (It doesn't help my problem, but that's another topic altogether).

> >
> >3. Does anybody have any concrete proof (or know where I could find
> >some) that would substantiate (or disprove) this statement: "Oracle
> >processes faster when all primary keys are integer data types as
> >opposed to variable character columns."
> >
>
> well, numbers are variable character columns actually.
>
> I think this stems from "instead of having a varchar2(80) as a
primary key or a
> set of varchar columns (i.e. primary key = a, b, c) lets use a single
surrogate
> key".
>
> The number column is typically smaller then a varchar2 key would be
(in
> general). the compare is faster.
>
> The number column is a single column, for a table with a true
compound key using
> a single surrogate key like this can improve join performance.
>

Yes, that would make sense. But I was looking for performance metrics dealing specifically with individual columns, not primary keys that are composed of multiple columns.

Is it thus fair to say that your statement supports the case that number datatype columns are a little bit faster than varchar2?

> >4. I would really appreciate any recommendations for good reading
> >materials on performance tuning Oracle.
> >
> >If you could be so kind as to post replies to the newsgroup, as well
as
> >forwarding to this email: mike_kares_at_pre-print.com (please change the
> >underscore to a period), I would be in your debt!
> >
> >Thanks!
> >
> >Mike Kares
> >
> >
> >--== Sent via Deja.com http://www.deja.com/ ==--
> >---Share what you know. Learn what you don't.---
>
> See http://www.oracle.com/ideveloper/ for my column 'Digging-in to
Oracle8i'...
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>

Once again, I appreciate your response. To give a little more detail as to the database I am dealing with, it is rather large, and when fully in production will be somewhere in the four to six hundred gigabyte size range (I'm not the dba on this project, but rather have been given the task of performance tuning the database and both the middle tier and the front end application, which are written in PL/SQL (business layer) and C++ (presentation layer) )

Thanks!
Mike Kares

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Thu May 20 1999 - 09:56:56 CDT