RE: Hash Cluster Question

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Sun, 25 Mar 2001 21:30:13 -0800
Message-ID: <F001.002D7802.20010325205020@fatcity.com>

Hi Jay,

Hash clusters are always created at full size based on the HASHKEYS and SIZE parameters. Thus the high-water mark immediately reflects the expected size of the segment, even before it contains any data. The high-water mark is only raised if block chaining becomes necessary, which should hopefully be rare. This means that, apart from block chaining, the full table scan cost of a hash clustered table is invariant on the number of rows in the table.

What you are seeing is the normal, expected behaviour. If the performance of this query is important, you should provide an index that the optimizer can use to satisfy the query instead of using a full table scan.

Hash clusters cannot be truncated. You would get ORA-03293 if you were to try.

@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/

-----Original Message-----
Sent: Monday, 26 March 2001 14:20
To: oracle-l_at_fatcity.com; oracledba_at_lazydba.com

I am brand-spankin' new to Hash Clusters and we are bringing up an application that uses them. One of the consultants that is helping to implement this application is having a performance issue querying against a table in the hash cluster. The table is empty. The statement is:

SQL> set timing on
SQL> select count(1)
2 from sysadm.rtx_lt_001;

COUNT(1)

Elapsed: 00:00:53.85

I analyzed the schema using estimate statistics, which has seemed to help slightly. Now, I see that the HC_RTX_LT_001 segment has 16 extents, but a min_extents of only 1. How could it have been created that way? My guess is by using import, without compress=y. I am really tempted to truncate this table, since it is empty, but I do not know if that is a wise thing to do with hash clusters.
Any thoughts or guidelines on hash clusters is welcomed! We are running 8.1.6.2.0 on Compaq Tru64 5.1.

Jay Hostetter

SQL> run
  1 select
table_name,cluster_name,initial_extent,next_extent,min_extents   2 from dba_tables
  3* where table_name = 'RTX_LT_001'

TABLE_NAME                     CLUSTER_NAME

INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS

------------------------------ ------------------------------ ----------
---- ----------- -----------
RTX_LT_001                     HC_RTX_LT_001
104857600   104857600           1

SQL> run
  1 select cluster_name,cluster_type,key_size, initial_extent,next_extent,min_extents
  2 from dba_clusters
  3* where cluster_name = 'HC_RTX_LT_001'

CLUSTER_NAME CLUST KEY_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS --------------- ----- ---------- -------------- ----------- ----------- HC_RTX_LT_001 HASH 16384 104857600 104857600 1 SQL> run
1 select segment_name,initial_extent,next_Extent,min_Extents,extents 2 from dba_segments
3* where segment_name = 'HC_RTX_LT_001'

SEGMENT_NAME                   INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS

EXTENTS

------------------------------ -------------- ----------- ----------- --
--------
HC_RTX_LT_001                       104857600   104857600           1

Think you know someone who can answer the above question? Forward it to them!
to unsubscribe, send a blank email to oracledba-unsubscribe_at_LAZYDBA.com to subscribe send a blank email to oracledba-subscribe_at_LAZYDBA.com Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve Adams
  INET: steve.adams_at_ixora.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Sun Mar 25 2001 - 23:30:13 CST