Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: tuning question...

Re: tuning question...

From: <gdas_at_my-deja.com>
Date: Fri, 25 Aug 2000 22:25:29 GMT
Message-ID: <8o6ro4$oor$1@nnrp1.deja.com>

David, thank you for the help. I checked the explain plans and index statistics and I think I found another problem...

This query involves two tables: datastage and feature. the primary keys of both tables are indexed. The indexes are :

DATASTAGE_UQ_ID_IDX for table datastage and PK_Feature for table feature

The following plan shows a cardinality of 4 for PK_Feature...By definition PK_FEATURE should be a high cardinality index since the number of index values will equal the number of rows in the table.

(In terms of table statistics...datastage have approx. 500,000 rows and
feature has approximately 1.5 million)

I apologize if the formatting of this plan doesn't come out right.

SELECT STATEMENT			 (cardinality 1)
	SORT	AGGREGATE		 (cardinality 1)
		NESTED LOOPS			147
			TABLE ACCESS	BY INDEX ROWID	FEATURE

(cardinality 4)
INDEX RANGE SCAN PK_FEATURE (cardinality 4) TABLE ACCESS BY INDEX ROWID DATASTAGE (cardinality 3682) INDEX RANGE SCAN DATASTAGE_UQ_ID_IDX (cardinality 3682)

I ran the query you gave me against the system table:

select table_name, index_name, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key
from user_indexes where table_name in ('FEATURE') and index_name ='PK_FEATURE'; The results are very suspicious. Only the table_name and index name were returned. All the values for blevel, leaf_blocks, distinct_keys etc... were all null/empty.

Is something corrupted? I will try to rebuild the index to see if that might help. In the meantime, do you have any other ideas?

Thanks
Gavin

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Aug 25 2000 - 17:25:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US