Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: tuning question...
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
![]() |
![]() |