Databases Reference
In-Depth Information
How to do it...
In this recipe, we will see how to rebuild an index minimizing the effects to the users who are
using the database. Follow these steps:
1.
Open another session of SQL*Plus and connect as user SH. The newly opened
session is referred to as SESSION B from here on. The initial connection made in step
1 is SESSION A; set the timing in SESSION B too:
CONNECT sh@TESTDB/sh
SET TIMING ON
2.
Start rebuilding the index in SESSION A:
ALTER INDEX IX1_MYCUSTOMERS REBUILD ONLINE PARALLEL;
3.
Insert a row in SESSION B while the operation in SESSION A is still in execution:
DECLARE
MAX_CUST NUMBER;
BEGIN
SELECT MAX(CUST_ID) INTO MAX_CUST FROM BIG_CUSTOMERS;
INSERT INTO BIG_CUSTOMERS(
CUST_ID, CUST_FIRST_NAME, CUST_LAST_NAME, CUST_GENDER,
CUST_YEAR_OF_BIRTH,
CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY,
CUST_CITY_ID, CUST_STATE_PROVINCE,
CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER,
CUST_TOTAL, CUST_TOTAL_ID)
SELECT
CUST_ID + MAX_CUST + 1, CUST_FIRST_NAME, CUST_LAST_NAME,
CUST_GENDER, CUST_YEAR_OF_BIRTH,
CUST_STREET_ADDRESS, CUST_POSTAL_CODE, CUST_CITY,
CUST_CITY_ID, CUST_STATE_PROVINCE,
CUST_STATE_PROVINCE_ID, COUNTRY_ID, CUST_MAIN_PHONE_NUMBER,
CUST_TOTAL, CUST_TOTAL_ID
FROM CUSTOMERS
WHERE CUST_ID = 1;
COMMIT;
END;
/
4.
Wait until the operations in SESSION A and SESSION B are finished.
5.
Rebuild the index in SESSION A with a different option:
ALTER INDEX IX1_MYCUSTOMERS REBUILD PARALLEL;
