DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: INDEX SIZING

  1. #1
    Join Date
    Jul 2002
    Posts
    205
    Hi,

    The actual space occupied by table data is = blocks * db_block_size

    Where blocks we get from dba_tables.

    Also the Total size we get from the field, bytes from DBA_SEGMENTS, initial_extent and next_extent from DBA_TABLES


    In the similar way how to find , how much space occupied by the index in a table..? There is no field blocks in DBA_INDEXES..?


  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Its as simple as that:

    SELECT bytes FROM dba_segments WHERE segment_name = :your_index_name;

    You'll get the exact and accurate size of an index.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jul 2002
    Posts
    205
    If I am wrong please rectify me.

    If I give the following command

    SELECT bytes FROM dba_segments WHERE segment_name = :your_table_name;

    It gives me the space occupied by the table. Not the actually occupied by the data. To find actually occupied by the data we need to multiply blocks from DBA_TABLES with DB_BLOCK_SIZE.

    But for INDEX if I give the following command

    SELECT bytes FROM dba_segments WHERE segment_name = :your_index_name;

    Will it give me the actual space occupied by index.

    For more specific example, if I specify initial and next as 2MB for table creation, and after that I insert some records in the table, then the following command gives me

    SELECT bytes FROM dba_segments WHERE segment_name = :your_table_name;

    the result 2MB.

    But actually space occupied by data is .01 MB which is blocks (which is 1) * 8k (db_block_size)

    In the similar way if I create an index with initial and next as 2MB.

    SELECT bytes FROM dba_segments WHERE segment_name = :your_index_name;

    command gives me 2MB. Are you telling me that the Index it self has taken complete space of 2MB...?






  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    If I understand you correctly, you want to find the number of blocks that actually contain any data of a particular table/index.

    Well, for tables I know for sure: there is no *general* method to get the exact answer, short of dumping all the data blocks below the highwatter mark of a table. None. Multiplying dba_tables.blocks*blocksize does not give you any accurate answer.

    I don't think there is anything different with indexes.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jul 2002
    Posts
    205
    Hi ,

    You are right.

    But if I import the table which was exported with compress, then it will exactly take the number of blocks, may be some short fall will be there, I mean last block may not be filled up completely.

    In this case if it takes 1 block, then I will be sure that the data is not more than 8K which is my DB_BLOCK_SIZE.

    But if I select bytes from DBA_SEGMENT it still will show 2MB , which is INITIAL extent size.

    Any way hope there is no way out, to find the same for Index. Thanks for your reply.

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Neither DBA_SEGMENTS nor DBA_TABLES would give you the actual no. of blocks used.
    Here is the work around to get the actual no. of blocks used.
    This is written for Oracle8 and will not work for Oracle7 because the ROWID format is different, however it can be easily modified to use with Oracle7 also.
    Code:
    select count(distinct(substr(rowid,7,3)||substr(rowid,10,6)))
    from :your_table_name;
    Sanjay




  7. #7
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by jmodic
    If I understand you correctly, you want to find the number of blocks that actually contain any data of a particular table/index.

    Well, for tables I know for sure: there is no *general* method to get the exact answer, short of dumping all the data blocks below the highwatter mark of a table. None. Multiplying dba_tables.blocks*blocksize does not give you any accurate answer.

    I don't think there is anything different with indexes.
    Try DBMS_SPACE.UNUSED_SPACE()
    -nagarjuna

  8. #8
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    You can do the following:

    1. Create a table:
    create table vj_test(no number, name varchar2(20))
    storage(initial 1m next 1m pctincrease 0 minextents 3) ;

    2. Issue a select statement from user_segments.

    select blocks, bytes, extents from user_segments where
    segment_name='VJ_TEST'
    SQL> /

    BLOCKS BYTES EXTENTS
    ---------- ---------- ----------
    384 3145728 3

    3. Analyze the table.
    SQL> analyze table vj_test compute statistics ;

    4. Insert some records into the table and reanalyze the
    table and then query USER_TABLES:

    SQL> begin
    2 for i in 1..100 loop
    3 insert into vj_test values(i,'Name'||i) ;
    4 end loop ;
    5 end ;
    6 /

    PL/SQL procedure successfully completed.

    SQL> select count(*) from vj_test ;

    COUNT(*)
    ----------
    100

    SQL> analyze table vj_test compute statistics ;

    Table analyzed.

    SQL> select blocks, empty_blocks from user_tables where table_name='VJ_TEST' ;

    BLOCKS EMPTY_BLOCKS
    ---------- ------------
    1 382

    5. Issue the following query:

    SQL> select bytes, blocks, extents from user_segments where segment_name='VJ_TEST_NO_PK' ;

    BYTES BLOCKS EXTENTS
    ---------- ---------- ----------
    16384 2 1

    This shows that for the index Oracle shows the no. of blocks allocated(since it took the default values for the storage parameters).

    You can also check the same thing using DBMS_SPACE.UNUSED_SPACE package:

    SQL> var total_blocks number ;
    SQL> var total_bytes number ;
    SQL> var unused_blocks number ;
    SQL> var unused_bytes number ;
    SQL> var file_id number ;
    SQL> var block_id number ;
    SQL> var last_used_block number ;

    SQL> exec dbms_space.unused_space('SYSTEM','VJ_TEST','TABLE',:total_blocks,:total_bytes,:unused_bloc
    ks,:unused_bytes,:file_id,:block_id,:last_used_block) ;


    SQL> select :total_blocks, :total_bytes, :unused_blocks, :unused_bytes, :file_id, :block_id, :last_u
    sed_block from dual ;

    :TOTAL_BLOCKS :TOTAL_BYTES :UNUSED_BLOCKS :UNUSED_BYTES :FILE_ID :BLOCK_ID
    ------------- ------------ -------------- ------------- ---------- ----------
    :LAST_USED_BLOCK
    ----------------
    384 3145728 382 3129344 1 18405
    2


    HTH.

    Vijay.

    Say No To Plastics

  9. #9
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Later method is faster
    -nagarjuna

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by SANJAY_G
    Code:
    select count(distinct(substr(rowid,7,3)||substr(rowid,10,6)))
    from :your_table_name;
    This gives the number of used blocks only under certain conditions. Consider the following example:
    Code:
    SQL> show parameter db_block_size
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    db_block_size                        integer     8192
    SQL> CREATE TABLE bla (c1 VARCHAR2(4000), 
      2                    c2 VARCHAR2(4000),
      3                    c3 VARCHAR2(4000),
      4                    c4 VARCHAR2(4000));
    
    Table created.
    
    SQL> INSERT INTO bla(c1) VALUES (RPAD('1',4000,'1'));
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> SELECT COUNT(DISTINCT(SUBSTR(ROWID,7,3)||SUBSTR(ROWID,10,6)))
      2  FROM bla;
    
    COUNT(DISTINCT(SUBSTR(ROWID,7,3)||SUBSTR(ROWID,10,6)))
    ------------------------------------------------------
                                                         1
    
    SQL> UPDATE bla SET c2 = RPAD('2',4000,'2'),
      2                 c3 = RPAD('3',4000,'3'),
      3                 c4 = RPAD('4',4000,'4');
    
    1 row updated.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> SELECT COUNT(DISTINCT(SUBSTR(ROWID,7,3)||SUBSTR(ROWID,10,6)))
      2  FROM bla;
    
    COUNT(DISTINCT(SUBSTR(ROWID,7,3)||SUBSTR(ROWID,10,6)))
    ------------------------------------------------------
                                                         1
    SQL>
    See, your query still reports that only one single block is occupied by table data. But it is more than clear that you can't stuff that row consisting of 4x4000 bytes into one block! It is because that row is chained into 2 or even 3 database blocks, yet it still have only one rowid.

    And chaned/migrated rows are not the only cases where the above query gives incorrect results - there are quite a few other cases as well.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width