Databases Reference
In-Depth Information
8.
Clear the previous example:
DROP CLUSTER LOC_ENTRIES INCLUDING TABLES;
9.
Recreate the cluster and index as before:
CREATE CLUSTER LOC_ENTRIES (COUNTRY_ID CHAR(2)) SIZE 100;
CREATE INDEX IDX_LOC_ENTRIES ON CLUSTER LOC_ENTRIES;
10. Create the first table without populating the data:
CREATE TABLE CL_COUNTRIES CLUSTER LOC_ENTRIES (COUNTRY_ID) AS
SELECT * FROM COUNTRIES WHERE 1=0;
11. Create the second table without populating the data:
CREATE TABLE CL_LOCATIONS CLUSTER LOC_ENTRIES (COUNTRY_ID) AS
SELECT L.*, CAST(‹*› AS CHAR(1000)) AS FOO_DATA
FROM LOCATIONS L WHERE 1=0;
12. Populate the data in a "clustered" way using PL/SQL:
declare
l_country_id char(2);
begin
for c in (select * from countries)
loop
insert into CL_COUNTRIES
(country_id, country_name, region_id) values
(c.country_id, c.country_name, c.region_id);
insert into CL_LOCATIONS select L.*, ‹*› from locations L
where L.country_id = c.country_id;
end loop;
end;
13. Verify the database blocks where the data of the two tables are stored:
select
cl_countries.country_id,
dbms_rowid.rowid_block_number(cl_countries.rowid)
as cou_blk,
dbms_rowid.rowid_block_number(cl_locations.rowid) as loc_blk
from cl_countries, cl_locations
where cl_countries.country_id = cl_locations.country_id
order by 1;
