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: [EMAIL PROTECTED]; [EMAIL PROTECTED]
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
--------
Think you know someone who can answer the above question? Forward it to
them!
to unsubscribe, send a blank email to [EMAIL PROTECTED]
to subscribe send a blank email to [EMAIL PROTECTED]
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: [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).
