Re: Simple Questions

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.

>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.

>
>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.

>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 Received on Thu May 20 1999 - 06:59:32 CDT