Databases Reference
In-Depth Information
7.
Empty the cache once more:
CONNECT /@TESTDB AS SYSDBA
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;
8.
Execute the same query as mentioned in step 5:
CONNECT sh@TESTDB/sh
SET AUTOTRACE TRACEONLY
SELECT F.FIRSTNAME, F.LASTNAME, P.PHONEKIND, P.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
WHERE F.ID = 29912;
SET AUTOTRACE OFF
9.
Drop the schema:
DROP TABLE FRIEND_PHONE;
DROP TABLE PHONE;
DROP TABLE AVAILABILITY;
DROP TABLE PHONEKIND;
DROP TABLE FRIEND;
How it works...
In the example, we first created a small database schema, based on the logical schema
presented in the
Getting ready
section of this recipe, which is in 3NF, and then we populated
it with some data.
Moving on, in step 3, we gathered statistics on the newly created objects and in step 4,
we cleared the buffers and cache.
