How can we estimate the index size

  1. TABKH

    TABKH Premium Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Mumbai
    Dear Experts.
    We are in process of creating new index on one of the big table in our database. But before this we would like to estimate the index size.

    With Regards
    TABKH
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,745
    Likes Received:
    382
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    SET serveroutput ON SIZE 1000000 verify off feedback off linesize 200 head off pagesize 0 termout off

    --
    -- Table for row count
    --
    CREATE TABLE tab_rows
    (t_name         varchar2(30),
    t_rows          NUMBER)
    tablespace tools
    storage(initial 5M NEXT 5M pctincrease 0);

    --
    -- Populate table with row count from desired table
    --
    SELECT 'insert into tab_rows select '''||TABLE_NAME||''', count(*) from '||TABLE_NAME||';'
    FROM user_tables
    WHERE TABLE_NAME = UPPER('&&1')

    spool /tmp/tabrows.SQL
    /
    spool off

    @/tmp/tabrows

    SET linesize 80 termout ON
    DECLARE
            tbl_nm          user_tables.table_name%TYPE;
            ini_tr          user_tables.ini_trans%TYPE;
            p_free          user_tables.pct_free%TYPE;
            blsz            sys.ts$.blocksize%TYPE;
            iblock          NUMBER;
            isize           NUMBER;
            irows           NUMBER;
            rwz             NUMBER;
            --
            -- Get database block size
            --
            cursor get_bsize IS        SELECT DISTINCT blocksize
            FROM sys.ts$;
            --
            -- Get table_name, ini_trans and pct_free
            -- for desired table to calculate block
            -- usage for indexes
            --
            cursor get_tab_info (tname IN varchar2) IS
            SELECT TABLE_NAME, ini_trans, pct_free
            FROM user_tables
            WHERE TABLE_NAME = UPPER(tname);
            --
            -- Get row count
            --
            cursor get_rows IS
            SELECT t_rows FROM tab_rows;
    BEGIN
            OPEN get_bsize;
            fetch get_bsize INTO blsz;
            close get_bsize;
            OPEN get_tab_info('&&1');
            fetch get_tab_info INTO tbl_nm, ini_tr, p_free;
            close get_tab_info;
            OPEN get_rows;
            fetch get_rows INTO rwz;
            close get_rows;
            --
            -- Calculate free bytes per block
            --
            -- Will determine the number of rows per block
            -- for the index
            --
            iblock := FLOOR(blsz - (113 + (23*ini_tr))*(p_free/100));
            --
            -- Calculate average number of blocks each indes will
            -- consume
            --
            isize := CEIL(1.05*((rwz*25)/(FLOOR(iblock/25)*25)));
            --        -- Rows of index per block
            --
            irows := FLOOR(1 / (isize/rwz));
            --
            -- Spit out the results
            --
            dbms_output.put_line('Available space per block : '||iblock);
            dbms_output.put_line('Approx. blocks per index  : '||isize);
            dbms_output.put_line('Approx. rows per block    : '||irows);
    END;
    /

    DROP TABLE tab_rows
    /
    If you don't have a tablespace named TOOLS then edit the script to create the table in the desired tablespace. Pass in the table name and it will produce output like this:

    Code (SQL):
    Available SPACE per block : 8178
    Approx. blocks per INDEX  : 1
    Approx. ROWS per block      : 115
     
  3. TABKH

    TABKH Premium Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Mumbai
    Dear Zargon
    Thanks for your update, Could you please let me how can we calculate the size on the basis of the query output.
    Additionally if we are creating indexes on few table columns, how to estimate the size of column?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,745
    Likes Received:
    382
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You can use the dump() command to dump column information, such as length, etc. from SQL*Plus:

    Code (SQL):
    SCOTT @ orcl > SELECT rowid, dump(dump_str) string_dump FROM dumptest WHERE rownum <= 128;

    ROWID           STRING_DUMP
    ------------------ --------------------------------------------------------------------------------
    AAAZc7AAFAAAADXAAA Typ=1 Len=3: 0,1,2
    AAAZc7AAFAAAADXAAB Typ=1 Len=6: 0,1,239,191,189,2
    AAAZc7AAFAAAADXAAC Typ=1 Len=3: 1,2,3
    AAAZc7AAFAAAADXAAD Typ=1 Len=3: 2,3,4
    AAAZc7AAFAAAADXAAE Typ=1 Len=3: 3,4,5
    AAAZc7AAFAAAADXAAF Typ=1 Len=3: 4,5,6
    AAAZc7AAFAAAADXAAG Typ=1 Len=3: 5,6,7
    AAAZc7AAFAAAADXAAH Typ=1 Len=3: 6,7,8
    AAAZc7AAFAAAADXAAI Typ=1 Len=3: 7,8,9
    AAAZc7AAFAAAADXAAJ Typ=1 Len=3: 8,9,10
    AAAZc7AAFAAAADXAAK Typ=1 Len=3: 9,10,11
    dump() will give you the chr() code for each letter in a string (as shown above).

    Explain plan for a create index statement should provide the estimated index size:

    Code (SQL):
    SCOTT @ orcl > EXPLAIN plan FOR CREATE INDEX empdeptidx ON emp(empno, deptno);

    Explained.

    SCOTT @ orcl > SELECT * FROM TABLE(dbms_xplan.display)
      2  /

    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    Plan hash VALUE: 1359622775

    -------------------------------------------------------------------------------------
    | Id  | Operation           | Name        | ROWS  | Bytes | Cost (%CPU)| TIME     |
    -------------------------------------------------------------------------------------
    |   0 | CREATE INDEX STATEMENT |        |    115 |    805 |      3   (0)| 00:00:01 |
    |   1 |  INDEX BUILD NON UNIQUE| EMPDEPTIDX |        |        |         |        |
    |   2 |   SORT CREATE INDEX    |        |    115 |    805 |         |        |
    |   3 |    TABLE ACCESS FULL   | EMP        |    115 |    805 |      2   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------


    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------
    Note
    -----
       - estimated INDEX SIZE: 65536  bytes

    14 ROWS selected.

    SCOTT @ orcl >