Re: Number Size on NT

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/07
Message-ID: <34ba0039.21160076@inet16>#1/1

On Wed, 7 Jan 1998 12:08:02 -0800, "Stephen Ferracioli" <stephen.ferracioli_at_ingrammicr.com> wrote:

>Where you take the hit is on indexes.
>Be careful in using NUMBER datatypes in indexes. They always take reserve
>22 bytes in indexes. That's why it's possible for indexes to be larger than
>tables.
>

If that was the case, I would expect an index on a couple of thousand 1's to be

the same size as the index on a couple of thousand 9999999999999999999999999's

but I don't see that to be the case. The index on the

9999999999999999999999999's is much larger then the index on the 1's.  Try the

following script in your database (i ran it in 7.1, 7.2, 7.3, and 8.0 getting pretty much the same results -- the index on 9999's was 2x bigger then the index on 1's).

Looking at an index on 1's and then one on 9999999999's I saw:

TEXT               BLOCKS    LF_ROWS LF_ROWS_LEN ROWS_PER_KEY
----------     ---------- ---------- ----------- ------------
Index on 1's           20       9080      127120         9080
Index on 9....         35       9084      236184         9084

The script I used was:

drop table foo;
drop table foo_stats;  

create table foo ( x number );  

insert into foo select 1 from all_objects;

/
/
/

commit;  

create index foo_idx on foo(x) storage (initial 10k next 10k pctincrease 0 );  

analyze index foo_idx validate structure;  

create table foo_stats as
select 'Index on 1' text, a.* from index_stats a;  

column segment_name format a10
column segment_type format a10
select segment_name, segment_type, bytes, blocks, extents

from user_segments where segment_name like 'FOO%';  

truncate table foo;
drop index foo_idx;  

insert into foo select 9999999999999999999999999 from all_objects;
/

/
/
commit;  

create index foo_idx on foo(x) storage (initial 10k next 10k pctincrease 0 );  

analyze index foo_idx validate structure;  

insert into foo_stats
select 'Index on 9', a.* from index_stats a;  

select text, blocks, lf_rows, lf_rows_len, rows_per_key from foo_stats;  

column segment_name format a10
column segment_type format a10
select segment_name, segment_type, bytes, blocks, extents

from user_segments where segment_name like 'FOO%';

---

try it out, i think you'll find similar results.

btw: Its possible for indexes to be larger then tables for many reasons (the index includes the rowid for example, the rowid consumes 6 extra bytes in the index that it doesn't in the table). In the above example, since the number of columns in the index = the number of columns in the table, the index will always need MORE space then the table does since the index has an extra column (rowid) that the table does not....

>
>Josh Blatt wrote in message <68u1f9$l1v$1_at_news.fas.harvard.edu>...
>>We have several tables where some number fields are set to way too high.
>>I'm wondering how much I should worry about sizing them precisely. How
 does
>>Oracle store these? Is there a performance hit to having variables too
>>large? If so, and where are the break points?
>>
>>Thanks much,
>>
>>Josh
>>jblatt_at_harvard.edu
>>
>>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jan 07 1998 - 00:00:00 CST