O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Rebuilding index

In the previous recipe, we saw that using indexes leads to performance improvements; however, we need to take care that DML operations are slower due to the operations involved to keep the index synchronized with table data.

Rebuilding an index is an operation that can provide performance benefits because it reduces intra-block fragmentation.

Getting ready

The following steps have to be carried out initially:

  1. Open a SQL*Plus session and connect to the SH schema:
    CONNECT sh@TESTDB/sh
    
  2. Create a table to test:
    CREATE TABLE BIG_CUSTOMERS AS SELECT * FROM CUSTOMERS;
    
  3. Insert more than 5 million records:
    BEGIN
    FOR j IN 1..100 LOOP
    INSERT INTO BIG_CUSTOMERS SELECT * FROM CUSTOMERS;
    END LOOP;
    COMMIT;
    END;
    
  4. Instruct SQL*Plus to show the timings ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required