Optimizing Application Design - Oracle Database 11gR2 Performance Tuning - page 67

INSERT INTO PHONEKIND(ID, DESCRIPTION) VALUES (1, 'BUSINESS');

INSERT INTO PHONEKIND(ID, DESCRIPTION) VALUES (2, 'MOBILE-HOME');

INSERT INTO PHONEKIND(ID, DESCRIPTION) VALUES (3,

'MOBILE-BUSINESS');

INSERT INTO PHONEKIND(ID, DESCRIPTION) VALUES (4, 'OTHER');

INSERT INTO AVAILABILITY(ID, WHENAVAILABLE) VALUES (0, 'ALWAYS');

INSERT INTO AVAILABILITY(ID, WHENAVAILABLE) VALUES (1,

'WORK-HOURS');

INSERT INTO AVAILABILITY(ID, WHENAVAILABLE) VALUES (2,

'AFTERNOON');

INSERT INTO AVAILABILITY(ID, WHENAVAILABLE) VALUES (3, 'EVENING');

INSERT /*+APPEND */ INTO PHONE (ID, PHONEKINDID, AVAILABILITYID,

PHONENUMBER)

SELECT ROWNUM, MOD(ROWNUM, 5), MOD(ROWNUM, 4),

CUST_MAIN_PHONE_NUMBER FROM SH.CUSTOMERS ORDER BY CUST_ID;

INSERT /*+APPEND */ INTO FRIEND_PHONE (FRIENDID, PHONEID)

SELECT CUST_ID, ROWNUM FROM SH.CUSTOMERS ORDER BY CUST_ID;

COMMIT;

3. Gather statistics related to the tables using the following statements:

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'FRIEND_PHONE',

estimate_percent => 100,

method_opt => 'for all columns size 1');

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'PHONE',

estimate_percent => 100,

method_opt => 'for all columns size 1');

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'AVAILABILITY',

estimate_percent => 100,

method_opt => 'for all columns size 1');

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'PHONEKIND',

estimate_percent => 100,

method_opt => 'for all columns size 1');

EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'FRIEND',

estimate_percent => 100,

method_opt => 'for all columns size 1');