7 Replies Latest reply: Jul 7, 2014 4:55 PM by rp0428 RSS

    checking size of indexes

    981609

      Can somebody explain why after I rebuild my index the space recovered is a negative number? Here is my code
      and output. BTW, I am using Tom Kytes show space to measure the buckets and that does not show any problems.


      I started with 100K records, deleted 65K records than ran my procedure. I am using start size - end size for my
      calculation on the index.


      ALTER TABLE N546830.T2
      DROP PRIMARY KEY CASCADE;

      DROP TABLE N546830.T2 CASCADE CONSTRAINTS;

      CREATE TABLE N546830.T2
      (
        ID           NUMBER                           NOT NULL,
        S1           VARCHAR2(30 BYTE),
        S2           VARCHAR2(30 BYTE),
        S3           VARCHAR2(30 BYTE),
        S4           VARCHAR2(30 BYTE),
        S5           VARCHAR2(30 BYTE),
        S6           VARCHAR2(30 BYTE),
        S7           VARCHAR2(30 BYTE),
        S8           VARCHAR2(30 BYTE),
        S9           VARCHAR2(30 BYTE),
        S10          VARCHAR2(30 BYTE),
        S11          VARCHAR2(30 BYTE),
        S12          VARCHAR2(30 BYTE),
        S13          VARCHAR2(30 BYTE),
        S14          VARCHAR2(30 BYTE),
        S15          VARCHAR2(30 BYTE),
        S16          VARCHAR2(30 BYTE),
        S17          VARCHAR2(30 BYTE),
        S18          VARCHAR2(30 BYTE),
        S19          VARCHAR2(30 BYTE),
        S20          VARCHAR2(30 BYTE),
        N1           NUMBER(20),
        N2           NUMBER(20),
        N3           NUMBER(20),
        N4           NUMBER(20),
        N5           NUMBER(20),
        N6           NUMBER(20),
        N7           NUMBER(20),
        N8           NUMBER(20),
        N9           NUMBER(20),
        N10          NUMBER(20),
        N11          NUMBER(20),
        N12          NUMBER(20),
        N13          NUMBER(20),
        N14          NUMBER(20),
        N15          NUMBER(20),
        N16          NUMBER(20),
        N17          NUMBER(20),
        N18          NUMBER(20),
        N19          NUMBER(20),
        N20          NUMBER(20),
        INSERT_DATE  DATE                             DEFAULT sysdate
      )
      TABLESPACE PBH_DAT
      PCTUSED    0
      PCTFREE    10
      INITRANS   1
      MAXTRANS   255
      STORAGE    (
                  INITIAL          64K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                 )
      LOGGING
      NOCOMPRESS
      NOCACHE
      NOPARALLEL
      MONITORING;


      CREATE INDEX N546830.T2_COL3_IDX ON N546830.T2
      (S1, S2, S3)
      LOGGING
      TABLESPACE PBH_IDX
      PCTFREE    10
      INITRANS   2
      MAXTRANS   255
      STORAGE    (
                  INITIAL          64K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                 )
      PARALLEL ( DEGREE 8 INSTANCES 1 );


      CREATE UNIQUE INDEX N546830.T2_IDX ON N546830.T2
      (ID)
      LOGGING
      TABLESPACE PBH_IDX
      PCTFREE    10
      INITRANS   2
      MAXTRANS   255
      STORAGE    (
                  INITIAL          64K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                 )
      PARALLEL ( DEGREE 8 INSTANCES 1 );


      ALTER TABLE N546830.T2 ADD (
        CONSTRAINT T2_IDX
        PRIMARY KEY
        (ID)
        USING INDEX N546830.T2_IDX);

       


      FUNCTION GET_OBJECT_SIZE (F_SCHEMA VARCHAR2, F_TABLE_NAME VARCHAR2, F_TYPE VARCHAR2) RETURN NUMBER
      AS
      F_SIZE NUMBER;
      BEGIN
          SELECT BYTES/1024/1024 INTO F_SIZE FROM SYS.DBA_SEGMENTS
          WHERE OWNER=F_SCHEMA AND SEGMENT_NAME=F_TABLE_NAME AND SEGMENT_TYPE=F_TYPE;

          RETURN F_SIZE;
      END;


      FOR IC_REC IN INDEX_CURSOR LOOP
              V_START_SIZE := GET_OBJECT_SIZE(F_SCHEMA,IC_REC.INDEX_NAME,'INDEX');

              begin
               dba_maint.show_space(IC_REC.INDEX_NAME, F_SCHEMA, 'INDEX');
              end;
              DBMS_OUTPUT.PUT_LINE(CHR(13) || CHR(10));


              DBMS_OUTPUT.PUT_LINE (TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') || ' ' || ' START REBUILDING INDEX ' || F_SCHEMA || '.' || IC_REC.INDEX_NAME);
              V_STMT := 'ALTER INDEX ' || F_SCHEMA || '.' || IC_REC.INDEX_NAME || ' REBUILD PARALLEL 8';
              DBMS_OUTPUT.PUT_LINE(V_STMT);
              EXECUTE IMMEDIATE V_STMT;
              DBMS_OUTPUT.PUT_LINE (TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') || ' ' || ' END REBUILDING INDEX ' || F_SCHEMA || '.' || IC_REC.INDEX_NAME);
              DBMS_OUTPUT.PUT_LINE(CHR(13) || CHR(10));
             
              begin
               dba_maint.show_space(IC_REC.INDEX_NAME, F_SCHEMA, 'INDEX');
              end;
              DBMS_OUTPUT.PUT_LINE(CHR(13) || CHR(10)); 
             
              V_END_SIZE := GET_OBJECT_SIZE(F_SCHEMA,IC_REC.INDEX_NAME,'INDEX');

              V_TOT_SPACE_RECOVERED := V_TOT_SPACE_RECOVERED + (V_START_SIZE-V_END_SIZE);

              DBMS_OUTPUT.PUT_LINE (TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') || ' ' || 'SPACE RECOVERED FOR INDEX ' || F_SCHEMA || '.' || IC_REC.INDEX_NAME || ' = ' || TO_CHAR(V_START_SIZE-V_END_SIZE));

          END LOOP;

       

       

       


      07/06/2014 14:29:02  START REBUILDING INDEX N546830.T2_COL3_IDX
      ALTER INDEX N546830.T2_COL3_IDX REBUILD PARALLEL 8
      07/06/2014 14:29:05  END REBUILDING INDEX N546830.T2_COL3_IDX


      Unformatted Blocks .....................               0
      FS1 Blocks (0-25)  .....................               0
      FS2 Blocks (25-50) .....................              23
      FS3 Blocks (50-75) .....................               0
      FS4 Blocks (75-100).....................               0
      Full Blocks        .....................             318
      Total Blocks............................             392
      Total Bytes.............................       3,211,264
      Total MBytes............................               3
      Unused Blocks...........................               0
      Unused Bytes............................               0
      Last Used Ext FileId....................               9
      Last Used Ext BlockId...................             952
      Last Used Block.........................               8


      07/06/2014 14:29:05 SPACE RECOVERED FOR INDEX N546830.T2_COL3_IDX = -.4375  ????????????????????????
      Unformatted Blocks .....................               0
      FS1 Blocks (0-25)  .....................               0
      FS2 Blocks (25-50) .....................               1
      FS3 Blocks (50-75) .....................               0
      FS4 Blocks (75-100).....................               0
      Full Blocks        .....................              94
      Total Blocks............................             104
      Total Bytes.............................         851,968
      Total MBytes............................               0
      Unused Blocks...........................               0
      Unused Bytes............................               0
      Last Used Ext FileId....................               9
      Last Used Ext BlockId...................             232
      Last Used Block.........................               8


      07/06/2014 14:29:05  START REBUILDING INDEX N546830.T2_IDX
      ALTER INDEX N546830.T2_IDX REBUILD PARALLEL 8
      07/06/2014 14:29:07  END REBUILDING INDEX N546830.T2_IDX


      Unformatted Blocks .....................               0
      FS1 Blocks (0-25)  .....................               0
      FS2 Blocks (25-50) .....................              36
      FS3 Blocks (50-75) .....................               0
      FS4 Blocks (75-100).....................               0
      Full Blocks        .....................              97
      Total Blocks............................             168
      Total Bytes.............................       1,376,256
      Total MBytes............................               1
      Unused Blocks...........................               0
      Unused Bytes............................               0
      Last Used Ext FileId....................               9
      Last Used Ext BlockId...................             648
      Last Used Block.........................               8


      07/06/2014 14:29:07 SPACE RECOVERED FOR INDEX N546830.T2_IDX = -.5

      Thanks to all who answer.

        • 1. Re: checking size of indexes
          JustinCave

          Where are you showing us how you are inserting 100k rows and deleting 65k rows?

          Are you missing one set of show_space output?  I would expect 2 befores and 2 afters but I only see 3 total outputs.  I'm not sure which I should regard as a "before" and which as an "after"

          You've posted code that uses a variable V_TOT_SPACE_RECOVERED that has never been declared so it will fail.  I'm not sure why you would add to the prior value of this variable unless you are also initializing it to 0 in the loop in code that you also haven't included.

           

          That said, rebuilding an index does not necessarily make it smaller.  Particularly when you have a very small index that you rebuild with a large number of parallel threads.  Each of the 8 threads would need its own extent which would likely cause the final size to be much bigger than you would expect.  I can't imagine a reason to rebuild a 1 MB index in parallel let alone with 8 parallel threads.

           

          Justin

          • 2. Re: checking size of indexes
            981609

            Justin,

             

            Thanks for your respone. Let me give you the full story and hopefully it does not bore you. We have several poorly designed tables( 200 cols, which are very highly fragmented). My goal is is to use the command alter table schema.tab shrink space cascade to compress the data.  This part of the code I did not show you becuase it seems to work fine.

             

            So what I did is use sqlldr to load 100K records into the table than deleted a range of records 20K-80K to simulate fragmentation.

            This is part of my setup which I dont show either because I was trying to keep to the issue.

             

            So once I run alter table schema.tab shrink space cascade I want to go throw all the indexes associated with the table and rebuild them. The output I posted is just to test the functionality first before I try this with 50 million plus records.. Yes, I know it should be partitoned but this is between my AD team and thier vendor so I will not go there.

             

            In summary the 1MB index is really just for testing purposes and not a true indication of my live data. Any other input you may have on this issue would be greatly appreciated and thanks for the comments especially the one about the parallel threads I did not know that.

             


            • 3. Re: checking size of indexes
              JustinCave

              Define "fragmented".  For most definitions of "fragmented", it is either impossible for or practically incredibly unlikely for a table in Oracle to be "fragmented".  But there are roughly as many possible definitions of "fragmentation" as people using the term so it's hard to know what you mean by that.

               

              Normally, you don't need to rebuild indexes in Oracle.  Deleting rows from a table does not generally mean that you should rebuild your indexes.  If you are not already very, very familiar with it, I'd suggest going through RIchard Foote's Index Internals: Rebuilding the Truth.  You'll see that being linked to any time anyone here talks about rebuilding indexes.

               

              If we assume that you actually have a problem for which rebuilding an index is the right solution (very uncommon, I don't think I can recall coming across a situation where I've ever wanted to rebuild an index), if you want to test, you'd have to create a representative test.  Rebuilding a 1 MB index is not a good test for what will happen if you try to rebuild a 100 GB index.  You just can't draw that many conclusions from one that apply to the other.

               

              Justin

              • 4. Re: checking size of indexes
                Martin Preiss

                do I understand your question correctly that you want to know why your function shows a (small) negative value though Tom Kyte's code shows a significant difference in size before and after the rebuild? If that's the case then I would suggest you debug the code (or just add additional dbms_output.put_line calls) to check the result for V_START_SIZE and V_END_SIZE. The next step would be to compare these results with the show_space output. Maybe I would have done this check already - but it seems that your function code is missing some lines.

                • 5. Re: checking size of indexes
                  981609

                  This is the case I running into, where doing many deletions without subsequent inserts. Therefore I am running alter table schema.table shrink space cascade.

                   

                  I tried looking for a query that would help me determine if the table really needs to be shrunk or not but was not successful. If I can find such a query, this would be a great time saver for me and I can avoid all this hassle for many tables

                   

                  Thanks for sharing you expertise on this matter. Any queries or other comments you may have and care to share regarding this issue would be greatly appreciated.

                  • 6. Re: checking size of indexes
                    JustinCave

                    If you're just looking for a tool that can show you tables that can be shrunk, I'd use the segment advisor

                     

                    So you're saying that the table will be permanently smaller?  You're never going to insert data again (or at least you'll be deleting as much data going forward as you insert)?  Most of the time, when people delete a large number of rows, they plan on inserting at least that many new rows in the future (though it may take time, of course).  Expending the time and effort to shrink a table only to let it grow again is rarely useful.

                     

                    Justin

                    • 7. Re: checking size of indexes
                      rp0428
                      I tried looking for a query that would help me determine if the table really needs to be shrunk or not but was not successful.

                      Ok - that is an easy question to answer.

                       

                      No - your table(s) do NOT need to be shrunk.

                      If I can find such a query, this would be a great time saver for me and I can avoid all this hassle for many tables

                      Super! Now you can avoid ALL of that 'hassle'.

                       

                      Shrinking, and similar operations, are a SOLUTION to a PROBLEM.

                       

                      If you don't have a problem then you do NOT need a solution.

                       

                      If you have a problem in your database you will know it. There will be symptoms of some sort.

                       

                      If you have symtoms that you think indicate a problem of some sort then post them so we can see what they are.

                       

                      Start with the problem, not the solution you think you want to use.