4 Ответы Последний ответ: 26.05.2009 7:32, автор: 703459

    Estimate Index size in 10G

    587557
      Hi Gurus

      My db is on 10.2.0.1.I want to know how can I estimate space taken by indexes on a table if I have number of rows.

      To get table size I did

      Execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'USERDB1A',TABNAME => 'TIFF',ESTIMATE_PERCENT =>NULL,method_opt => 'FOR ALL COLUMNS SIZE 1',CASCADE => TRUE);

      select num_rows, blocks, empty_blocks, avg_row_len, sample_size, last_analyzed, degree ,AVG_SPACE
      from dba_tables where table_name='TIFF' and owner=upper('USERDB1A');

      avg_row_len * number of expected rows * 20% overhead

      WHat is the formula to use for indexes ?


      Thanks
        • 1. Re: Estimate Index size in 10G
          181444
          Calculate the average size of each of the columns that make up the index key and sum the columns plus one rowid and add 2 bytes for the index row header to get the average row size. Now add just a little to the pctfree value for the index to come up with an overhead factor, maybe 1.125 for pctfree of 10.

          number of indexed table rows X avg row len X 1.125

          Note - if the index contains nullable columns then every table row may not appear in the index. On a single column index where 90% of the columns are null only 10% would go into the index.

          Compare estimate to tablespace extent allocation method and adjust final answer if necessary.

          Also a larger overhead factor may be better as the index gets bigger since the more data indexed the more branch blocks necessary to support the index structure and the calculation really just figures for leaf blocks.

          HTH -- Mark D Powell --
          • 2. Re: Estimate Index size in 10G
            587557
            Thank Mark for responding But I was wondering if I have sample data and I do

            14:41:34 PLMBOMDV.WORLD-KARORA> ANALYZE INDEX USERDB1A.TIFF_FITOWNNAM VALIDATE STRUCTURE;

            Index analyzed.

            14:49:12 PLMBOMDV.WORLD-KARORA> select name, blocks, lf_blks, br_blks, blocks-(lf_blks+br_blks) empt
            y
            15:00:39 2 from index_stats;

            NAME BLOCKS LF_BLKS BR_BLKS EMPTY
            ------------------------------ ---------- ---------- ---------- ----------
            TIFF_FITOWNNAM 20 8 1 11

            1 row selected.

            15:00:40 PLMBOMDV.WORLD-KARORA>

            Based upon number of rows in the index can't I estimate space using above information ?.
            • 3. Re: Estimate Index size in 10G
              181444
              Yes, you could if you equate the number of indexed rows to the 9 blocks used for the index you could project the number of blocks for a different number of indexed rows. However, I would want a larger sample size to project from unless ball park (large margin of error) estimates are good enough.

              -- Mark D Powell --
              • 4. Re: Estimate Index size in 10G
                703459
                test