Database Reference
In-Depth Information
EODA@ORA12CR1> alter table t_hashed add constraint
2 t_hashed_pk primary key(object_id)
3 /
Table altered.
EODA@ORA12CR1> begin
2 dbms_stats.gather_table_stats( user, 'T_HASHED' );
3 end;
4 /
PL/SQL procedure successfully completed.
I created the hash cluster with a
SIZE
of 150 bytes. This is because I determined the average row size for a row
in my table would be about 100 bytes, but would vary up and down based on the data with many rows coming in at
around 150 bytes. I then created and populated a table in that cluster as a copy of
ALL_OBJECTS
.
Next, I created the conventional clone of the table:
EODA@ORA12CR1> create table t_heap
2 as
3 select *
4 from t_hashed
5 /
Table created.
EODA@ORA12CR1> alter table t_heap add constraint
2 t_heap_pk primary key(object_id)
3 /
Table altered.
EODA@ORA12CR1> begin
2 dbms_stats.gather_table_stats( user, 'T_HEAP' );
3 end;
4 /
PL/SQL procedure successfully completed.
Now, all I needed was some random data to pick rows from each of the tables with. To achieve that, I simply
selected all of the
OBJECT_ID
s into an array and had them sorted randomly, to hit the table all over in a scattered fashion.
I used a PL/SQL package to define and declare the array and a bit of PL/SQL code to prime the array, to fill it up:
EODA@ORA12CR1> create or replace package state_pkg
2 as
3 type array is table of t_hashed.object_id%type;
4 g_data array;
5 end;
6 /
Package created.
EODA@ORA12CR1> begin
2 select object_id bulk collect into state_pkg.g_data
3 from t_hashed
4 order by dbms_random.random;
5 end;
6 /
PL/SQL procedure successfully completed.
