Forum Stats

  • 3,815,804 Users
  • 2,259,089 Discussions
  • 7,893,248 Comments

Discussions

Calculate disk space needed for INDEX

guruparan
guruparan Member Posts: 150
edited Apr 8, 2010 11:38AM in General Database Discussions
Hi,

How to calculate disk space needed for creating an INDEX? Please point me to resources where I can get detail.

For Example:
I need to create an non UNIQUE INDEX on the following columns, a rough size would do.

<PRE>
EVENT_DATE NOT NULL DATE
APPT_DATE_SLOT VARCHAR2(15)
EVENT_STATUS VARCHAR2(4)
</PRE>

The table has some 7,300,000 rows. Let me know how to calculate too.

All helps are greatly appreciated.
Tagged:
eaede5c0-4a95-4b0e-b366-55d6b0dd6ac3

Best Answer

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    Answer ✓
    A rough estimate of the space the index will need can be made by

    adding the expected actual length of each column plus 6 for the rowid plus 2 for the header X number of table rows that will have an entry so

    Date are 7 bytes internal + 15 + 4 + 2 + 6 = 34 X 7.3M = 248200000 X overhead for block header, initrans, etc...

    we will use 20% for block overhead or 1.2 X 248200000 = 297840000 which is about 285M

    The actual allocation will vary depending on your tablespace extent allocation method.

    HTH -- Mark D Powell --
    eaede5c0-4a95-4b0e-b366-55d6b0dd6ac3

Answers

  • 448389
    448389 Member Posts: 27
    Hi

    dbms_space.create_index_cost can help you
    448389
  • guruparan
    guruparan Member Posts: 150
    I haven't created yet! But I can still run this proc? BTW, I am in Oracle 9.2
  • guruparan
    guruparan Member Posts: 150
    Looks like Oracle 9.2 doesn't have it,

    <pre>
    SQL> desc dbms_space.create_index_cost
    ERROR:
    ORA-04043: object dbms_space.create_index_cost does not exist
    </pre>
  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    Answer ✓
    A rough estimate of the space the index will need can be made by

    adding the expected actual length of each column plus 6 for the rowid plus 2 for the header X number of table rows that will have an entry so

    Date are 7 bytes internal + 15 + 4 + 2 + 6 = 34 X 7.3M = 248200000 X overhead for block header, initrans, etc...

    we will use 20% for block overhead or 1.2 X 248200000 = 297840000 which is about 285M

    The actual allocation will vary depending on your tablespace extent allocation method.

    HTH -- Mark D Powell --
    eaede5c0-4a95-4b0e-b366-55d6b0dd6ac3
  • guruparan
    guruparan Member Posts: 150
    Thanks Mark. That's about it. :)
  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    You are welcome. Thank you for the points. If you get to build the index soon and can remember post back with the actual results.

    -- mark --
  • 448389
    448389 Member Posts: 27
    edited Apr 8, 2010 11:03AM
    Yes is it to known the future size BEFORE create index by using statistics

    look the Ask Tom http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1833377500346797242

    for psoug this package exist since 7.3.4. I use it on 10.2.0[2-4] and 11g ({ORACLE_HOME}/rdbms/admin/dbmsspu.sql)
    http://psoug.org/reference/dbms_space.html

    my test also :
    SQL*Plus: Release 11.1.0.6.0
    [email protected]>create table tsygale as select level num from dual connect by level<=100000;

    Table created.
    [email protected]>@seg_tab
    Enter value for segment_name: TSYGALE
    old 3: where segment_name in ('&segment_name')
    new 3: where segment_name in ('TSYGALE')

    TABLESPACE_NAME SEGMENT_NAME MO
    -------------------- ----------------------------------- ----------
    SYSTEM TSYGALE 2
    [email protected]>exec dbms_stats.gather_table_stats(user,'TSYGALE');

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:02.95

    [email protected]>variable used number
    [email protected]>variable alloc number
    exec dbms_space.create_index_cost( 'create index isygale on tsygale(object_name,object_type,owner)', :used, :alloc );

    PL/SQL procedure successfully completed.

    Elapsed: 00:00:00.25
    16:56:38 [email protected]>print used

    USED
    ----------
    2761720

    16:56:49 [email protected]>print alloc

    ALLOC
    ----------
    4194304
    [email protected]>create index isygale on tsygale(object_name,object_type,owner);

    Index created.

    Elapsed: 00:00:00.50

    [email protected]>@seg_tabind
    Segment name ?TSYGALE
    old 3: where (segment_name in ('&&segment_name')
    new 3: where (segment_name in ('TSYGALE')
    old 5: segment_name in (select index_name from dba_indexes where table_name in ('&&segment_name'))
    new 5: segment_name in (select index_name from dba_indexes where table_name in ('TSYGALE'))

    TABLESPACE_NAME SEGMENT_TYPE SEGMENT_NAME MO
    -------------------- ------------------ ----------------------------------- ----------
    SYSTEM TABLE TSYGALE 8
    SYSTEM INDEX ISYGALE 4

    Elapsed: 00:01:22.31


    So package estimate to 4 194 304 Octets and after the index created the size is to 4MO, so ....

    Edited by: Sygale on Apr 8, 2010 5:02 PM
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,950 Blue Diamond
    edited Apr 8, 2010 11:10AM
    Sygale wrote:

    for psoug this package exist since 7.3.4. I use it on 10.2.0[2-4] and 11g ({ORACLE_HOME}/rdbms/admin/dbmsspu.sql)
    http://psoug.org/reference/dbms_space.html
    Unfortunately the OP says he is using 9.2, and although the package has been around since 7.3 that particular procedure was not introduced until 10g - and it has a couple of flaws built in, see: http://jonathanlewis.wordpress.com/2009/05/22/index-size/

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
    
    +"I believe in evidence. I believe in observation, measurement, and reasoning, confirmed by independent observers. I'll believe anything, no matter how wild and ridiculous, if there is evidence for it. The wilder and more ridiculous something is, however, the firmer and more solid the evidence will have to be."+
    Isaac Asimov                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 448389
    448389 Member Posts: 27
    Jonathan Lewis wrote:
    Unfortunately the OP says he is using 9.2, and although the package has been around since 7.3 that particular procedure was not introduced until 10g - and it has a couple of flaws built in, see: http://jonathanlewis.wordpress.com/2009/05/22/index-size/
    Sorry for this mistake, have you a method to define on which version a functionality is delivered ?

    I read your article, I see some errors in this package,
    but it can help us to define if we have enough space on disk to create index or table in Clone environment. Before create it on production ;)

    A real test is always better than an estimate test

    I recommend to add 10% margin from development to production (the production database is always run ;))

    Thanks
This discussion has been closed.