INDEX SIZING

[skdas]

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..?

[jmodic]

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.

[skdas]

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...?

[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.

[skdas]

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.

[SANJAY_G]

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.

select count(distinct(substr(rowid,7,3)||substr(rowid,10,6)))
from :your_table_name;

Sanjay

[nagarjuna]

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()

[oravijay]

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.

[nagarjuna]

Later method is faster

[jmodic]

Originally posted by SANJAY_G

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:

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.