[bbk2900] 第56集 - Chapter 14-Using Oracle Dasta Storage Structures Efficiently(02)

Hash Clustered Tables

Hash Clustered Table的设计目的就是减少I/O的次数，如果设计不当（一般因为size大小设置问题） 导致overflow blocks ,无疑会增大I/O的次数，造成事倍功半.

[oracle@arcerzhang usefull_scripts]$ ll
total 4
-rw-r--r-- 1 oracle oinstall 3642 May  9 12:45 show_space.sql
[oracle@arcerzhang usefull_scripts]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 9 12:46:35 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> !pwd
/RealData/oracle/scripts/usefull_scripts

SQL> @show_space.sql

Procedure created.

SQL> set serverout on
SQL> exec show_space('HC','U3','CLUSTER');
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks ............................           1,009
Total Blocks............................           1,152
Total Bytes.............................       9,437,184
Total MBytes............................               9
Unused Blocks...........................             117
Unused Bytes............................         958,464
Last Used Ext FileId....................               7
Last Used Ext BlockId...................           1,152
Last Used Block.........................              11

PL/SQL procedure successfully completed.

 

create or replace procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user that wants to use it
authid current_user
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
l_segment_space_mgmt varchar2(255);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number; l_fs1_bytes number;
l_fs2_blocks number; l_fs2_bytes number;
l_fs3_blocks number; l_fs3_bytes number;
l_fs4_blocks number; l_fs4_bytes number;
l_full_blocks number; l_full_bytes number;
-- inline procedure to print out numbers nicely formatted
-- with a simple label
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
to_char(p_num,'999,999,999,999') );
end;
begin
-- this query is executed dynamically in order to allow this procedure
-- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
-- via a role as is customary.
-- NOTE: at runtime, the invoker MUST have access to these two
-- views!
-- this query determines if the object is an ASSM object or not
begin
execute immediate
'select ts.segment_space_management
from dba_segments seg, dba_tablespaces ts
where seg.segment_name = :p_segname
and (:p_partition is null or
seg.partition_name = :p_partition)
and seg.owner = :p_owner
and seg.tablespace_name = ts.tablespace_name'
into l_segment_space_mgmt
using p_segname, p_partition, p_partition, p_owner;
exception
when too_many_rows then
dbms_output.put_line
( 'This must be a partitioned table, use p_partition => ');
return;
end;
-- if the object is in an ASSM tablespace, we must use this API
-- call to get space information, else we use the FREE_BLOCKS
-- API for the user managed segments
if l_segment_space_mgmt = 'AUTO'
then
dbms_space.space_usage
( p_owner, p_segname, p_type, l_unformatted_blocks,
l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
p( 'Unformatted Blocks ', l_unformatted_blocks );
p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
p( 'FS4 Blocks (75-100)', l_fs4_blocks );
p( 'Full Blocks ', l_full_blocks );
else
dbms_space.free_blocks(
segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks);
p( 'Free Blocks', l_free_blks );
end if;
-- and then the unused space API call to get the rest of the
-- information
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/

 

Where Hash is useful

Hashing is useful when you have the following conditions:

• Most Queries are equality queries on the cluster key:

SELECT ... WHERE cluster_key = ...;

In such cases,the cluster key in the equality condition is hashed,and the corresponding  hash key is usually found with a single read.In comparision,for an indexed table the key  value must first be found in the index(usually serveral reads),and then the row is read from the table(another read).

• The tables in the hash cluster  are primarily static in size so that you can determine the number of rows and amount of space required for the tables in the cluster.If tables in a hash cluster require more space than the initial allocation  for the cluster,performance degradation can be substantial because overflow blocks are required. 

When to Use Clusters

Criterion(规则)	Index	Hash
Uniform Key distribution	Y	Y
Evenly distribution key values		Y
Rarely（罕见的） updated key	Y	Y
Often joined master-deatil tables	Y
Predicatable number of key values		Y
Queries using equality predicate on key		Y

 

 

 

 

 

 

 

 

 

有关Cluster相关资料，可以查阅书籍

<<Expert Oracle Database Architecture Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions >>

<<Oracle® Database Administrator's Guide 11g Release 2 (11.2)>>