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)
----------
0
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
