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

From: <Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com>
Date: Mon, 23 Oct 2000 12:09:40 -0400
Message-Id: <10658.119976@fatcity.com>

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 Received on Mon Oct 23 2000 - 11:09:40 CDT