Re: Simple Questions

A copy of this was sent to mkares_at_my-dejanews.com (if that email address didn't require changing) On Thu, 20 May 1999 14:56:56 GMT, you wrote:

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

the number type holds integers. If you want to make it so that only integers will be there try

SQL> create table t ( x number(38,0) );
SQL> insert into t values ( 1.1 );
SQL> select * from t;

         X
----------
         1

there are no 'native integer' column types (although you could stuff them in a RAW but then comparing them for SORTS or <, > would not always work).

[snip]

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

not necessarily. Its just a general fact that a number column will be smaller storage wise then a varchar2 will be. The way we pack numbers -- we get 2 digits per byte pretty much. So, if we have a varchar2(10) with the values:

1
2
3
...
9999999999

in it, the same column with a NUMBER type would be half the size in general. The actual comparision would be faster since we compare half the bytes most of the time.

If the number surrogate key is smaller byte wise then varchar column -- then yes it would be faster to compare.

On the other hand -- adding a number column and an extra index to the table will increase your storage needs and decrease performance of loads, inserts, updates, deletes and the like.

Its good and bad. I'm not aware of any technical papers that have explored this in Oracle tho.

[snip]

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 - 12:13:13 CDT