11 Replies Latest reply on Nov 10, 2009 11:15 PM by user503699

    Index Space Utilization

    user503699
      Hello,

      I am trying to understand why the index can not continue to utilize maximum space despite of setting PCTFREE for the index to 0. Can anybody please help?

      Here is the test case:

      Oracle Database Version: 10.2.0.4
      Tablespace Management: Local
      Segment Space Management: Automatic
      Database Block Size: 8 KB
      SQL> create table t2 (load_date date not null, extract_date date, extract_ind varchar2(1) default 'Y', cdata varchar2(30));
      
      Table created.
      
      SQL> create index t2_idx on t2(extract_ind) ;
      
      Index created.
      
      SQL> insert into t2(load_date, cdata) select trunc(sysdate), dbms_random.string('a', 30) from dual connect by level <= 10000 ;
      
      10000 rows created.
      
      SQL> commit ;
      
      Commit complete.
      
      SQL> exec dbms_stats.gather_table_stats(user, 'T2', cascade=>true) ;
      
      PL/SQL procedure successfully completed.
      
      SQL> analyze index t2_idx validate structure ;
      
      Index analyzed.
      
      SQL> select blocks, lf_rows, lf_blks, del_lf_rows, pct_used from index_stats ;
      
          BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS   PCT_USED
      ---------- ---------- ---------- ----------- ----------
              32      10000         25           0         63
      
      SQL> alter index t2_idx rebuild pctfree 0 ;
      
      Index altered.
      
      SQL> analyze index t2_idx validate structure ;
      
      Index analyzed.
      
      SQL> select blocks, lf_rows, lf_blks, del_lf_rows, pct_used from index_stats ;
      
          BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS   PCT_USED
      ---------- ---------- ---------- ----------- ----------
              24      10000         17           0         91
      
      SQL> update t2 set extract_date = trunc(sysdate)+1, extract_ind = null where load_date >= trunc(sysdate) ;
      
      10000 rows updated.
      
      SQL> commit ;
      
      Commit complete.
      
      SQL> exec dbms_stats.gather_index_stats(user, 'T2_IDX') ;
      
      PL/SQL procedure successfully completed.
      
      SQL> analyze index t2_idx validate structure ;
      
      Index analyzed.
      
      SQL> select blocks, lf_rows, lf_blks, del_lf_rows, pct_used from index_stats ;
      
          BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS   PCT_USED
      ---------- ---------- ---------- ----------- ----------
              24      10000         17       10000         91
      
      SQL> insert into t2(load_date, cdata) select trunc(sysdate), dbms_random.string('a', 30) from dual connect by level <= 20000 ;
      
      20000 rows created.
      
      SQL> commit ;
      
      Commit complete.
      
      SQL> exec dbms_stats.gather_table_stats(user, 'T2', cascade=>true) ;
      
      PL/SQL procedure successfully completed.
      
      SQL> analyze index t2_idx validate structure ;
      
      Index analyzed.
      
      SQL> select blocks, lf_rows, lf_blks, del_lf_rows, pct_used from index_stats ;
      
          BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS   PCT_USED
      ---------- ---------- ---------- ----------- ----------
              72      20000         58           0         56
      
      SQL> update t2 set extract_date = trunc(sysdate)+1, extract_ind = null where load_date >= trunc(sysdate) and extract_ind = 'Y' ;
      
      20000 rows updated.
      
      SQL> commit ;
      
      Commit complete.
      
      SQL> exec dbms_stats.gather_index_stats(user, 'T2_IDX') ;
      
      PL/SQL procedure successfully completed.
      
      SQL> analyze index t2_idx validate structure ;
      
      Index analyzed.
      
      SQL> select blocks, lf_rows, lf_blks, del_lf_rows, pct_used from index_stats ;
      
          BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS   PCT_USED
      ---------- ---------- ---------- ----------- ----------
              72      20000         58       20000         56
      
      SQL> insert into t2(load_date, cdata) select trunc(sysdate), dbms_random.string('a', 30) from dual connect by level <= 10000 ;
      
      10000 rows created.
      
      SQL> commit ;
      
      Commit complete.
      
      SQL> exec dbms_stats.gather_table_stats(user, 'T2', cascade=>true) ;
      
      PL/SQL procedure successfully completed.
      
      SQL> analyze index t2_idx validate structure ;
      
      Index analyzed.
      
      SQL> select blocks, lf_rows, lf_blks, del_lf_rows, pct_used from index_stats ;
      
          BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS   PCT_USED
      ---------- ---------- ---------- ----------- ----------
              72      16621         58        6621         46
      
      SQL> update t2 set extract_date = trunc(sysdate)+1, extract_ind = null where load_date >= trunc(sysdate) and extract_ind = 'Y' ;
      
      10000 rows updated.
      
      SQL> commit ;
      
      Commit complete.
      
      SQL> exec dbms_stats.gather_index_stats(user, 'T2_IDX') ;
      
      PL/SQL procedure successfully completed.
      
      SQL> analyze index t2_idx validate structure ;
      
      Index analyzed.
      
      SQL> select blocks, lf_rows, lf_blks, del_lf_rows, pct_used from index_stats ;
      
          BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS   PCT_USED
      ---------- ---------- ---------- ----------- ----------
              72      16621         58       16621         46
      
      SQL> select leaf_blocks, num_rows from user_indexes where index_name = 'T2_IDX' ;
      
      LEAF_BLOCKS   NUM_ROWS
      ----------- ----------
                0          0
      
      SQL> select leaf_blocks, num_rows, pct_free from user_indexes where index_name = 'T2_IDX' ;
      
      LEAF_BLOCKS   NUM_ROWS   PCT_FREE
      ----------- ---------- ----------
                0          0          0
      
      SQL> alter index t2_idx coalesce ;
      
      Index altered.
      
      SQL> analyze index t2_idx validate structure ;
      
      Index analyzed.
      
      SQL> select blocks, lf_rows, lf_blks, del_lf_rows, pct_used from index_stats ;
      
          BLOCKS    LF_ROWS    LF_BLKS DEL_LF_ROWS   PCT_USED
      ---------- ---------- ---------- ----------- ----------
              72          0          1           0
      I vaguly remember reading about something related but can not find actual contents.

      Thanks.
        • 1. Re: Index Space Utilization
          Centinul
          This may be useful, [PCTFREE for Indexes|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2424495760219] :
          *pctfree only counts when BUILDING an index*. It reserves X% of the block for subsequent
          inserts into that block (inserts into the block can arise from inserts or UPDATES to the
          base table).

          If you load and index and NEVER insert/update the table -- a pctfree of 0 would be just
          fine.

          All the pctfree does on an index is defer the splitting of an index block due to
          subsequent inserts/updates AFTER the index is build. 10% is just a good "starting
          point".
          >

          HTH!
          1 person found this helpful
          • 2. Re: Index Space Utilization
            Jonathan Lewis
            user503699 wrote:
            Hello,

            I am trying to understand why the index can not continue to utilize maximum space despite of setting PCTFREE for the index to 0. Can anybody please help?
            As indicated by Centinul, the PCTFREE for an index is only relevant for a create or rebuild. More generally, many of the oddities of B-tree index space management rely on two features - one is that an index entry has to go in the right leaf block, the other is that deletes, and the subsequent cleanouts, are subject to greater delay for indexes than they are for tables.

            For further comments on the "unexpected" behaviour of indexes a good place to browse (after checking the manuals) is [+*Richard Foote's blog*+|http://richardfoote.wordpress.com/] .

            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
            .
            
            
            "Science is more than a body of knowledge; it is a way of thinking" 
            Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
            • 3. Re: Index Space Utilization
              user503699
              Hello Jonathan/Centinul,

              Thanks for your reply.
              I understood that PCTFREE (for index) will be used only during index creation or rebuild.
              But in the example which I have mentioned, the index is populated using only a single value (i.e. 'Y')
              during INSERT and gets deleted during UPDATE. I thought that the UPDATE would empty all the index
              blocks and subsequent INSERT should populate the blocks as full as possible. But as you can see in the example,
              the PCT_USED (for index) after each combination of INSERT..UPDATE decreases. Why can not index blocks be
              populated completely? That is what I am trying to understand.

              Thanks
              • 4. Re: Index Space Utilization
                Jonathan Lewis
                user503699 wrote:
                Thanks for your reply.
                I understood that PCTFREE (for index) will be used only during index creation or rebuild.
                Why can not index blocks be populated completely? That is what I am trying to understand.
                That's a good indication of why it's important to ask the question you want answered rather than assuming that it's obvious from the SQL and results.

                The general guideline - when you delete the data, you mark rows for deletion; when you commit you don't clean out the blocks immediately but clean them out on demand for free space. Since your index is a non-unique one the rowids are appended to the key values to create index entries which is why some allocated space that has not yet been used can end up being used before freed blocks are re-used. This type of behaviour leads to small fluctuations in how effectively (and how rapidly) Oracle re-uses index leaf blocks.

                If you want to get a better handle on the effects of the timing, you'd be better off running tests with far more rows (if you want to emulate bulk processing) or lots of commits of small batches of rows. If you want to investigate and understand at the scale of your tests, then the number of blocks invovled is so small that you could simply dump all the leaf blocks and see which table rows appeared in which leaf blocks.

                Update+: And, I should have said - the fact that you are using ASSM (automatic segment space management) makes a difference to which blocks you use and the timing of their use. The big drop in block usage (and wastage) from the first insert to the first rebuild is a side effect of ASSM, and the scale of the drop is dependent on the process id of the session you were connected to combined with the relative large impact of ASSM overheads on small objects.


                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
                .
                
                "Science is more than a body of knowledge; it is a way of thinking" 
                Carl Sagan
                
                Edited by: Jonathan Lewis on Nov 9, 2009 10:07 PM
                Comment about ASSM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                1 person found this helpful
                • 5. Re: Index Space Utilization
                  user503699
                  Jonathan Lewis wrote:
                  user503699 wrote:
                  Thanks for your reply.
                  I understood that PCTFREE (for index) will be used only during index creation or rebuild.
                  Why can not index blocks be populated completely? That is what I am trying to understand.
                  That's a good indication of why it's important to ask the question you want answered rather than assuming that it's obvious from the SQL and results.
                  Thanks.
                  I was hoping to get my questions answered, one at a time. But seems the approach is not working. :)
                  Since your index is a non-unique one the rowids are appended to the key values to create index entries which is why some allocated space that has not yet been used can end up being used before freed blocks are re-used.
                  Ok. I did not know that. Is there a way to avoid this (without changing non-unique index to unique)?
                  Update+: And, I should have said - the fact that you are using ASSM (automatic segment space management) makes a difference to which blocks you use and the timing of their use. The big drop in block usage (and wastage) from the first insert to the first rebuild is a side effect of ASSM, and the scale of the drop is dependent on the process id of the session you were connected to combined with the relative large impact of ASSM overheads on small objects.
                  So does it mean using ASSM tablespace for this index is not the right approach?

                  Anyways, here is the description (or specification) of what I was trying to achieve with the code in my OP.
                  1. Need to have a database structure, as a staging area, to store (and retain) data - Table T2 in the example is a heap table used for this purpose. For the purpose of this example, I have included only a single column (CDATA) but in real-life there will be other columns.
                  2. Data will be loaded into this table by SINGLE batch process that will be scheduled to run at a pre-defined intervals - The INSERTs in the example represent the data load process. I have included LOAD_DATE column in order to store when the data was created.
                  3. Following the data load, it will be extracted by SINGLE session (but different from the loading session) - The UPDATEs in the example represent the skinned version of the extract process. I have included EXTRACT_DATE column in order to store when the data was extracted. In addition, I have included an indicator column (EXTRACT_IND) that controls what data is used by the EXTRACT process. The Load process will populate the EXTRACT_IND column with 'Y' value. The EXTRACT process, at the end of main processing, will update EXTRACT_DATE to SYSDATE and EXTRACT_IND to NULL.
                  4. Indicator column (EXTRACT_IND) - While the EXTRACT process can use EXTRACT_DATE column to identify what data needs to be extracted (e.g. EXTRACT_DATE IS NULL), any SELECTs and UPDATEs will always do full table scan (due to EXTRACT_DATE IS NULL) condition and hence may not be efficient. I do not know the extact retention period for the data in this staging table but I am expecting the table to grow quickly and hence do not want the EXTRACT process to be affected by the increase in the data. I am planning to create a B*Tree index on EXTRACT_IND column, which will help the EXTRACT process. The EXTRACT_IND column will have only one possible value (which is 'Y', set during Load) and will be set to NULL by Extract process. This will ensure that the index remains small thereby benefitting the EXTRACT process.

                  Now my questions:
                  1. Is there a better approach to achieve this?
                  2. Even when SINGLE session is modifying the index (in the example) and that too with the same value, why does the PCT_USED in the index drop considerably? In my example above, it dropeed from 91 (after rebuild with PCTFREE 0) for initial 10000 rows to 46 after only few INSERTs/UPDATEs. Even the number of blocks occupied by the index increased from 24 (after rebuild with PCTFREE 0) to 72. That is almost 50% reduction in space utilization(per index block). Is that expected (or "normal") in ASSM tablespace? Can it be avoided? How?
                  • 6. Re: Index Space Utilization
                    Jonathan Lewis
                    user503699 wrote:

                    Anyways, here is the description (or specification) of what I was trying to achieve with the code in my OP.
                    1. Need to have a database structure, as a staging area, to store (and retain) data - Table T2 in the example is a heap table used for this purpose. For the purpose of this example, I have included only a single column (CDATA) but in real-life there will be other columns.
                    2. Data will be loaded into this table by SINGLE batch process that will be scheduled to run at a pre-defined intervals - The INSERTs in the example represent the data load process. I have included LOAD_DATE column in order to store when the data was created.
                    3. Following the data load, it will be extracted by SINGLE session (but different from the loading session) - The UPDATEs in the example represent the skinned version of the extract process. I have included EXTRACT_DATE column in order to store when the data was extracted. In addition, I have included an indicator column (EXTRACT_IND) that controls what data is used by the EXTRACT process. The Load process will populate the EXTRACT_IND column with 'Y' value. The EXTRACT process, at the end of main processing, will update EXTRACT_DATE to SYSDATE and EXTRACT_IND to NULL.
                    4. Indicator column (EXTRACT_IND) - While the EXTRACT process can use EXTRACT_DATE column to identify what data needs to be extracted (e.g. EXTRACT_DATE IS NULL), any SELECTs and UPDATEs will always do full table scan (due to EXTRACT_DATE IS NULL) condition and hence may not be efficient. I do not know the extact retention period for the data in this staging table but I am expecting the table to grow quickly and hence do not want the EXTRACT process to be affected by the increase in the data. I am planning to create a B*Tree index on EXTRACT_IND column, which will help the EXTRACT process. The EXTRACT_IND column will have only one possible value (which is 'Y', set during Load) and will be set to NULL by Extract process. This will ensure that the index remains small thereby benefitting the EXTRACT process.
                    So far you've discovered that for a very small index with 100% delete/insert cycles the size and apparent efficiency of the index fluctuates wildly and strange things can happen when you rebuild the index. Extreme modification on index structure often causes oddities to appear.

                    In your case, though, you need to think about where the real use of resources is likely to be: how big are your rows, how many rows will go into the table before any get deleted, how are you going to delete them.. Your test should reflect the size and number of rows at something approaching steady state - then you may decide that a variation of 24 to 72 blocks in the index is not worth worrying about.

                    Your test also has a "concurrency flaw" - if you have one session loading and another session flagging (or "unflagging") then your test should emulate this behaviour.

                    Your basic strategy of using a flag column with a single column index to identify a fairly fixed volume of new data, then updating the flag to remove the entry from the index is sound; and if we assume that a large volume of data can accumulate before any data is deleted then the strategy makes very good sense from the viewpoiint of stability. A simple mechanism to reduce space anomalies after eliminating all the index entries is to use a coalesce on the index after you have updated the flag and committed.

                    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
                    .
                    
                    "Science is more than a body of knowledge; it is a way of thinking" 
                    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                    1 person found this helpful
                    • 7. Re: Index Space Utilization
                      user503699
                      Jonathan Lewis wrote:

                      In your case, though, you need to think about where the real use of resources is likely to be: how big are your rows, how many rows will go into the table before any get deleted, how are you going to delete them.. Your test should reflect the size and number of rows at something approaching steady state - then you may decide that a variation of 24 to 72 blocks in the index is not worth worrying about.
                      I did not quite get it. Are you suggesting that the size of the rows will affect the index usage. But if index is going to be on a VARCHAR2(1) column, how will the size of the row have any effect (on index)? Also, are you trying to suggest that the variation which I saw in my example, will not have any noticable impact on the way it is used? With the approach that I have described earlier, I am anticipating that the EXTRACT process will always range-scan this index. If index entries are scattered across more number of blocks as a result of INSERTs and DELETEs(caused by UPDATEs to the table), the index range scans will need to visit more blocks and hence more work.
                      Your test also has a "concurrency flaw" - if you have one session loading and another session flagging (or "unflagging") then your test should emulate this behaviour.
                      Again, did not understand what is the "concurrency flaw" is? Are you talking about possible overlap of the EXTRACT session with LOAD session?
                      The only probable issue that I saw with my example is unflagging / EXTRACT process being benefitted with most of the data being available in buffer cache. I agree that this may not hold true in real life. But will that need to be considered in my "test" ?
                      • 8. Re: Index Space Utilization
                        Jonathan Lewis
                        user503699 wrote:

                        I did not quite get it. Are you suggesting that the size of the rows will affect the index usage. But if index is going to be on a VARCHAR2(1) column, how will the size of the row have any effect (on index)?
                        There are two points to consider. First, while the index may vary between 24 and 72 blocks (say), how much does this matter if your rows are so long and the number so large that the part of the table you are interested in is (say) 20,000 blocks ? Secondly, remember that the rowid is appended to the non-unique key - the larger the table row the smaller the number of rowids you use from a single block, and this can actually make a difference to the way in which index leaf block splits can occur.
                        >
                        >
                        Again, did not understand what is the "concurrency flaw" is?
                        Concurrency was not an ideal word, but I couldn't think of a more appropriate one - the mechanisms of block usage, block cleanout, and so on, can be affected by process ids; so there is scope for an alternating pair of processes to cause a variation in the pattern of use and re-use of blocks. In fact, given the link between process ID and free block selection when using ASSM, you could find that the variation in the size of such a small index to be quite surprising if you simply start up a random number of "other" processes each time you do an insert.

                        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
                        .
                        
                        "Science is more than a body of knowledge; it is a way of thinking" 
                        Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                        1 person found this helpful
                        • 9. Re: Index Space Utilization
                          user503699
                          Jonathan Lewis wrote:
                          There are two points to consider. First, while the index may vary between 24 and 72 blocks (say), how much does this matter if your rows are so long and the number so large that the part of the table you are interested in is (say) 20,000 blocks ? Secondly, remember that the rowid is appended to the non-unique key - the larger the table row the smaller the number of rowids you use from a single block, and this can actually make a difference to the way in which index leaf block splits can occur.
                          Again, did not understand what is the "concurrency flaw" is?
                          Concurrency was not an ideal word, but I couldn't think of a more appropriate one - the mechanisms of block usage, block cleanout, and so on, can be affected by process ids; so there is scope for an alternating pair of processes to cause a variation in the pattern of use and re-use of blocks. In fact, given the link between process ID and free block selection when using ASSM, you could find that the variation in the size of such a small index to be quite surprising if you simply start up a random number of "other" processes each time you do an insert.
                          Thanks Jonathan.
                          But in that case, what changes should I do to my earlier test setup so that it becomes more reliable/realistic (but still remains a "test case")?

                          Edited by: user503699 on Nov 11, 2009 4:26 AM
                          • 10. Re: Index Space Utilization
                            Jonathan Lewis
                            user503699 wrote:

                            Thanks Jonathan.
                            But in that case, what changes should I do to my earlier test setup so that it becomes more reliable/realistic (but still remains a "test case")?
                            Create the table and index, add a "padding" column which is a varchar2() column long enough to give you rows of about the right size for your production system.

                            Create two scripts, one to insert a randomised number of rows that is around the right number - and commit; one to update all the rows in the 'extract' state - and commit. (I think that covers your two jobs).

                            Alternate the two scripts from two different sessions until you've got to a table with the number of rows that is about the number you expect to see at steady state. Check the state of the index after cycling a couple more inserts and deletes; throw in a coalesce after each delete and see what happens; compare the size of the table with the size of the index.

                            Now think about how you're going to clean out some of the old data - and how often, and what you're going to do about the "empty" space this leaves in the table, and whether you actually care that there may be big holes in the table if you've got an index that always allows you to pick up the bit you want while ignoring the rest. Throw the delete process into the pattern at a realistic frequency - see if that does anything nasty that you need to address.

                            Check where your CPU time and I/O time is going, and how much buffer space you're using on a typical cycle.

                            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
                            .
                            
                            "Science is more than a body of knowledge; it is a way of thinking" 
                            Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                            1 person found this helpful
                            • 11. Re: Index Space Utilization
                              user503699
                              Thanks Jonathan.
                              I will give it a try.
                              In the meantime, I will keep this thread open in case anybody else can share any past experience or other ideas.