Database Reference
In-Depth Information
EODA@ORA12CR1> alter table colocated
2 add constraint colocated_pk
3 primary key(x);
Table altered.
EODA@ORA12CR1> begin
2 dbms_stats.gather_table_stats( user, 'COLOCATED');
3 end;
4 /
PL/SQL procedure successfully completed.
This table fits the description we laid out earlier with about 100 rows/block in an 8KB database. In this table,
there is a very good chance that the rows with
X=1
,
2
,
3
are on the same block. Now, we'll take this table and purposely
“disorganize” it. In the
COLOCATED
table, we created the
Y
column with a leading random number, and we'll use that
fact to disorganize the data so that it will definitely not be ordered by primary key anymore:
EODA@ORA12CR1> create table disorganized
2 as
3 select x,y
4 from colocated
5 order by y;
Table created.
EODA@ORA12CR1> alter table disorganized
2 add constraint disorganized_pk
3 primary key (x);
Table altered.
EODA@ORA12CR1> begin
2 dbms_stats.gather_table_stats( user, 'DISORGANIZED');
3 end;
4 /
PL/SQL procedure successfully completed.
Arguably, these are the same tables—it is a relational database, so physical organization has no bearing on the
answers returned (at least that's what they teach in theoretical database courses). In fact, the performance characteristics
of these two tables are as different as night and day, while the answers returned are identical. Given the same exact
question, using the same exact query plans, and reviewing the
TKPROF
(SQL trace) output, we see the following:
select * from colocated where x between 20000 and 40000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 6675 0.06 0.21 0 14495 0 100005
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6685 0.06 0.21 0 14495 0 100005
...
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
20001 20001 20001 TABLE ACCESS BY INDEX ROWID BATCHED COLOCATED...
20001 20001 20001 INDEX RANGE SCAN COLOCATED_PK (cr=1374 pr=0 pw=0...
********************************************************************************
