Databases Reference
In-Depth Information
See also
F
We will talk about full table scans in
Chapter 4
in the recipe
Avoiding full table scans
,
and optimizer hints are explained in depth in
Chapter 7
in the recipe
Exploring the
optimizer hints
.
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 for the next operations:
SET TIMING ON
5.
Create an index on the table:
CREATE INDEX IX1_BIG_CUSTOMERS
ON BIG_CUSTOMERS (CUST_LAST_NAME, CUST_FIRST_NAME);
