RE: Clarification -- NUMERIC data type always takes up 22 bytes.

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Mon, 23 Oct 2000 22:14:08 +0100
Message-Id: <10658.120005@fatcity.com>

Val,

Here are my ideas based on the data you have provided:

You have 2,092,487 entries
Calculate the size of each index entry - Add a 2 byte header per index entry and 6 for the rowid plus the index column length + 2 for the number of columns. Given the sum of the columns sizes (14626070) divided by the number of rows you get an average "columns" length of 7, so the result is 17 bytes "average".
The bytes available per block is 8192(Block Size) minus the block header of 113 bytes and the variable size block header if 24 bytes * the default index initrans of 2 (total 161) giving you 8031 bytes available.
The number of entries per block is bytes available/entry length, therefore 8031/17 = 472
The number of blocks calculation takes in the factor of 1.05 for the additional blocks required for non-leaf blocks - so total blocks is 1.05 * (entries/entries per block) = 1.05 * (2092487/472)=4655
The size of the index in kbytes will be (blocks * block size) / 1024

4655 * 8192 = 38,133,760
38,133,760 / 1024 = 37,240,000 bytes - which takes you into your seventh extent.....

So you have about 750k of block headers
About 20 megabytes of index header, rowid etc.. ...plus your indexed columns....
and you soon see how to use up disk space!

Hope this helps you out.

Regards

Mark

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com Sent: 23 October 2000 18:15
To: Multiple recipients of list ORACLE-L Subject: Clarification -- NUMERIC data type always takes up 22 bytes.

Alright,

My bad - I should have selected vsize(field) to see how much is actully allocated for the data in the column.

However I still have something that I can not explain. I have a table that has a PRIMARY KEY that is too big in my opinion:

The recap:

1. Actual size of the index is ~ 40Mb
2. Actual size of the data that should be stored in the index is ~ 14Mb
3. Index INIT_EXTENT = 10Mb, NEXT = 5Mb, PCT_FREE=0, MIN_EXTENTS = 1
4. Index has just been rebuilt.

Below you can find the results of queries that I ran trying to identify what the problem might be.

If anybody has enough time to spend to look at these I would be very appreciative. It's just it's been driving me nuts ...

Thanks,
Val Gamerman.

Here is the description of the table:

 Name                            Null?    Type
 ------------------------------- -------- ----
 D8POLN                          NOT NULL NUMBER(9)
 D8SEQN                          NOT NULL NUMBER(3)
 D8PACK                                   VARCHAR2(2000)
 D8DSTS                                   VARCHAR2(1)
 D8LCUS                                   VARCHAR2(10)
 D8LCDT                                   DATE
 D8LCTM                                   VARCHAR2(8)

Or more detailed one:

  1 select SUBSTR(TABLE_NAME,1,8), substr(COLUMN_NAME,1,8), DATA_TYPE, DATA_LENGTH, DATA_PRECISION,
  2 from all_tab_columns
  3* where table_name = 'AGYPKAR'
SQL> / SUBSTR(T SUBSTR(C DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE COLUMN_ID -------- -------- --------- ----------- -------------- ---------- ---------

AGYPKAR  D8POLN   NUMBER             22              9          0         1
AGYPKAR  D8SEQN   NUMBER             22              3          0         2
AGYPKAR  D8PACK   VARCHAR2         2000                                   3
AGYPKAR  D8DSTS   VARCHAR2            1                                   4
AGYPKAR  D8LCUS   VARCHAR2           10                                   5
AGYPKAR  D8LCDT   DATE                7                                   6
AGYPKAR  D8LCTM   VARCHAR2            8                                   7


===========================================================================

==============================

Number of records:

SQL> select count(*) from agypkar;

COUNT(*)

2092487

The first two fields constitute the PRIMARY KEY:

1 select INDEX_NAME, COLUMN_NAME,COLUMN_POSITION, COLUMN_LENGTH 2 from all_ind_columns
3* where table_name = 'AGYPKAR'
SQL> /

INDEX_NAME                     COLUMN_NAME

COLUMN_POSITION COLUMN_LENGTH

------------------------------ ------------------------------ -------------
-- -------------
AGYPKAR_PK                     D8POLN
                                       1            22
AGYPKAR_PK                     D8SEQN
                                       2            22


===========================================================================

==============================

===========================================================================

==============================

===========================================================================

==============================

===========================================================================

==============================

===========================================================================

==============================

What I do NOT understand is why the size of the index is 1 select sum(bytes) from dba_extents
2* where segment_name = 'AGYPKAR_PK'
SQL> / SUM(BYTES)

41943040

------------------------------------------------------------- OR  7 EXTENTS
 ! ---------------------------------------------------------------

1 select count(*) from dba_extents
2* where segment_name = 'AGYPKAR_PK'
SQL> / COUNT(*)

That's roughly 40Mb for 2 million records. I would think that it should be approx. 14Mb according to the data below.

1* select sum(vsize(d8poln)+vsize(d8seqn)) from agypkar SQL> / SUM(VSIZE(D8POLN)+VSIZE(D8SEQN))

14626070

The MIN extents are fine and PCT_FREE is set to 0. 1 select index_name, initial_extent, min_extents, next_extent, pct_free 2 from all_indexes
3* where index_name = 'AGYPKAR_PK'
SQL> /

INDEX_NAME                     INITIAL_EXTENT MIN_EXTENTS NEXT_EXTENT

PCT_FREE

------------------------------ -------------- ----------- ----------- -----
----
AGYPKAR_PK                           10485760           1     5242880
         0

What am I missing?

The RECAP:

1. Actual size of the index is ~ 40Mb
2. Actual size of the data that should be stored in the index is ~ 14Mb
3. Index INIT_EXTENT = 10Mb, NEXT = 5Mb, PCT_FREE=0, MIN_EXTENTS = 1
4. Index has just been rebuilt.

Thanks,
Val Gamerman.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

Received on Mon Oct 23 2000 - 16:14:08 CDT