Hash Cluster Question

From: Jay Hostetter <jhostetter_at_decommunications.com>
Date: Sun, 25 Mar 2001 21:02:32 -0800
Message-ID: <F001.002D77D6.20010325202527@fatcity.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         16

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jay Hostetter
INET: jhostetter_at_decommunications.com

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:02:32 CST