Re: Clarification -- NUMERIC data type always takes up 22 bytes. Alternatives?

Are you missing something? No.

It's already been answered that not only are the columns which make up the index stored, but also headers, rowids.

Also, if you analyze the index then query dba_indexes, you'll see how many leaf blocks you have and what your blevel is.

Remember that these are b*tree indexes. Branch blocks duplicate some of the column data and contain pointer data.

The typical rule of thumb when sizing an index is to very carefully follow Oracle's algorithms, then double and double again. That estimate will probably be no more than 50% less than the real size of the index.

We had a DBA here for a while who was Oracle Certified but apparantly low on experience. I was explaining the construction of one of our production databases, saying the data took up 20 GB, indexes 35 GB. He looked at me, puzzled, ask how this could be. ----- Original Message -----
From: <Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com> To: Tom Pall _ <tom_at_cdproc.com>
Cc: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Monday, October 23, 2000 1:14 PM
Subject: Re: Clarification -- NUMERIC data type always takes up 22 bytes. Alternatives?

>
>
>
> Thank you for the script. Here is the output of the script for the table in
> question:
> Type Name Blocks Next Ext Unused
> Free
> --------------- ---------------------------- -------- -------- -------- ---
> -----
> TABLE AGYPKAR 31,360 640 200
> 3
> INDEX AGYPKAR_PK 5,120 640 153
> 0
>
> My DB_BLOCK_SIZE = 8K, thus it's telling me that I only have 1Mb unused (I
> can see that happenning) and
> it is also telling me that my index size approx. 40Mb (which matches my
> queries results) -- that I can not explain.
>
> I expect this index to be 14Mb given the data size stored in it and the
> number of redcords.
>
> Am I missing something?
>
> Thanks,
> Val Gamerman.
> To: Val Gamerman/Victoria Financial_at_VICTORIA FINANCIAL
> cc: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> From: "Tom Pall" <tom_at_cdproc.com> @ NOTES NET
> Date: 10/23/2000 05:26:39 PM GMT
> Subject: Re: Clarification -- NUMERIC data type always takes up 22 bytes.
> Alternatives?
>
>
>
>
> I have a script I run in sql*plus, stolen from Jonathan Lewis, which shows
> the
> actual amount of space taken up by objects and the amount of free space
> below the
> high water mark (in full blocks, of course). The parameter m_scan_limit
> determines how
> many many blocks on the free list Oracle walks. The larger the number, the
> more accurate
> if there are lots of blocks below the HWM. Of course, the higher the
> number, the load placed
> upon your database.
> If you have recently rebuilt the index which makes up your PK, the script
> will tell you just how
> big your PK index is.
> Make sure you replace MY_SCHEMA with the name of your schema and edit the
> in list for
> the segment types.
> rem this script show the number of blocks used an unused by each segment
> rem it shows the unused space under the high water mark as well
> rem must be run in sql*plus
> set serveroutput on size 100000
> declare
> cursor c1 is
> select owner, segment_name,segment_type,
> blocks, next_extent/8192 next_ext, partition_name
> from dba_segments
> where segment_type in ('INDEX','TABLE','CLUSTER', 'TABLE
> PARTITION')
> and owner in ('MY_SCHEMA')
> order by segment_name
> ;
> m_tot_blocks number;
> m_tot_bytes number;
> m_unused_blocks number;
> m_unused_bytes number;
> m_last_file_id number;
> m_last_block_id number;
> m_last_block number;
> m_on_free_list number;
> m_free_group number := 0;
> m_scan_limit number := 100;
> begin
> dbms_output.put_line(
> rpad('Type',15) || ' ' ||
> rpad('Name',28) || ' ' ||
> lpad('Blocks',8) || ' ' ||
> lpad('Next Ext',8) || ' ' ||
> lpad('Unused',8) || ' ' ||
> lpad('Free',8)
> );
> dbms_output.put_line(
> rpad('-',15,'-') || ' ' ||
> rpad('-',28,'-') || ' ' ||
> lpad('-',8,'-') || ' ' ||
> lpad('-',8,'-') || ' ' ||
> lpad('-',8,'-') || ' ' ||
> lpad('-',8,'-')
> );
> for r1 in c1 loop
> dbms_space.free_blocks(
> r1.owner,
> r1.segment_name,
> r1.segment_type,
> m_free_group,
> m_on_free_list,
> m_scan_limit, r1.partition_name
> );
> dbms_space.unused_space(
> r1.owner,
> r1.segment_name,
> r1.segment_type,
> m_tot_blocks,
> m_tot_bytes,
> m_unused_blocks,
> m_unused_bytes,
> m_last_file_id,
> m_last_block_id,
> m_last_block, r1.partition_name
> );
> dbms_output.put_line(
> rpad(substr(r1.segment_type,1,15),15) || ' ' ||
> rpad(substr(r1.segment_name,1,28),28) || ' ' ||
> lpad(to_char(m_tot_blocks,'999,990'),8) || ' ' ||
> lpad(to_char(r1.next_ext,'99,990'),8) || ' ' ||
> lpad(to_char(m_unused_blocks,'999,990'),8) || ' '
> ||
> lpad(to_char(m_on_free_list,'999,990'),8)
> );
> end loop;
> end;
> /
>
> ----- Original Message -----
> From: <Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com>
> To: Tom Pall _ <tom_at_cdproc.com>
> Cc: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent: Monday, October 23, 2000 11:09 AM
> Subject: Clarification -- NUMERIC data type always takes up 22 bytes.
> Alternatives?
>
> >
> >
> >
> > 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(*)
> > ---------
> > 7
> >
> >
> > 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?
Received on Mon Oct 23 2000 - 14:19:11 CDT