Databases Reference
In-Depth Information
4.
Empty the cache:
CONNECT /@TESTDB AS SYSDBA
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
5.
Execute a query to retrieve the data (a simple list of friends with their phone
type/number/availability):
CONNECT sh@TESTDB/sh
SET AUTOTRACE TRACEONLY
SELECT F.FIRSTNAME, F.LASTNAME, PK.DESCRIPTION AS PHONEKIND,
PA.WHENAVAILABLE AS AVAILABILITY, P.PHONENUMBER
FROM FRIEND F
INNER JOIN FRIEND_PHONE FP ON FP.FRIENDID = F.ID
INNER JOIN PHONE P ON P.ID = FP.PHONEID
INNER JOIN PHONEKIND PK ON PK.ID = P.PHONEKINDID
LEFT OUTER JOIN AVAILABILITY PA ON PA.ID = P.AVAILABILITYID
WHERE F.ID = 29912;
SET AUTOTRACE OFF
6.
Denormalization, alter the database schema, adjust the data, and gather
statistics again:
ALTER TABLE PHONE ADD PHONEKIND VARCHAR2(20);
ALTER TABLE PHONE ADD AVAILABILITY VARCHAR2(30);
UPDATE PHONE SET PHONEKIND = (SELECT DESCRIPTION FROM PHONEKIND
WHERE PHONEKIND.ID = PHONE.PHONEKINDID);
UPDATE PHONE SET AVAILABILITY = (SELECT WHENAVAILABLE
FROM AVAILABILITY WHERE AVAILABILITY.ID = PHONE.AVAILABILITYID);
COMMIT;
ALTER TABLE PHONE SET UNUSED COLUMN PHONEKINDID;
ALTER TABLE PHONE SET UNUSED COLUMN AVAILABILITYID;
ALTER TABLE PHONE DROP UNUSED COLUMNS;
EXEC DBMS_STATS.GATHER_TABLE_STATS('SH', 'FRIEND',
estimate_percent => 100,
method_opt => 'for all columns size 1');
