2 Replies Latest reply: Mar 17, 2014 8:49 PM by Bono RSS

    index Rebuilding

    9876564

      Dear All,

       

      I want to understand what do we mean by index rebuilding ?

       

      If i drop an existing index and recreate it , will it be same as rebuilding the same?

       

      Thanks in Advance

        • 1. Re: index Rebuilding
          Hoek

          They're not  the same.

          A lot of myths exist regarding rebuilding an index.

          I suggest you explore the blogs of Richard Foote:

          Index Internals – Rebuilding The Truth | Richard Foote's Oracle Blog

          and Jonathan Lewis:

          Index Rebuilds | Oracle Scratchpad

          and have some good reads on the subject.

          • 2. Re: index Rebuilding
            Bono

            Hi,

            What is difference recreate and rebuild?

             

            I want to introduce below's simple test. (Ver. 11.2.0.1)

            drop table t1 purge;

             

            create table t1

            (c1 number, c2 number, c3 number);

             

            insert into t1

            select

            level as c1,

            level as c2,

            level as c3

            from dual

            connect by level<=10000;

             

            commit;

             

            Create Index

            explain plan for

            create index t1_x1 on t1(c1);

             

            set linesize 200

            select * from table(dbms_xplan.display);

             

            --------------------------------------------------------------------------------

            | Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

            --------------------------------------------------------------------------------

            |   0 | CREATE INDEX STATEMENT |       |    82 |  1066 |     3   (0)| 00:00:01 |

            |   1 |  INDEX BUILD NON UNIQUE| T1_X1 |       |       |            |          |

            |   2 |   SORT CREATE INDEX    |       |    82 |  1066 |            |          |

            |   3 |    TABLE ACCESS FULL   | T1    |    82 |  1066 |     2   (0)| 00:00:01 |

            --------------------------------------------------------------------------------

            create index t1_x1 on t1(c1);

             

            offline rebuild

            explain plan for

            alter index t1_x1 rebuild;

             

            set linesize 200

            select * from table(dbms_xplan.display);

             

             

            --------------------------------------------------------------------------------

            | Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

            --------------------------------------------------------------------------------

            |   0 | ALTER INDEX STATEMENT  |       |    82 |  1066 |     2   (0)| 00:00:01 |

            |   1 |  INDEX BUILD NON UNIQUE| T1_X1 |       |       |            |          |

            |   2 |   SORT CREATE INDEX    |       |    82 |  1066 |            |          |

            |   3 |    INDEX FAST FULL SCAN| T1_X1 |       |       |            |          |

            --------------------------------------------------------------------------------

             

            online rebuild

            explain plan for

            alter index t1_x1 rebuild online;


            set linesize 200

            select * from table(dbms_xplan.display);

            --------------------------------------------------------------------------------

            | Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

            --------------------------------------------------------------------------------

            |   0 | ALTER INDEX STATEMENT  |       |    82 |  1066 |     2   (0)| 00:00:01 |

            |   1 |  INDEX BUILD NON UNIQUE| T1_X1 |       |       |            |          |

            |   2 |   SORT CREATE INDEX    |       |    82 |  1066 |            |          |

            |   3 |    TABLE ACCESS FULL   | T1    |    82 |  1066 |     2   (0)| 00:00:01 |

            --------------------------------------------------------------------------------

             

            Generally Speaking,

             

            TABLE FULL SCAN

            create index

            offline rebuild

             

            INDEX FAST FULL SCAN

            online rebuild

             

             

            Regards eqon