Incorrect Stats from ANALYZE ?

Environment: Oracle 8.0.4.1.0 and 8.0.4.3.0

             IRIX 6.2 and Irix 6.4

I've run into a problem with the CBO, and I can't decide if it's a bug or if I'm being completely daft.

I have the following table, the relevant columns of which are thus:

SQL> desc path_structure

 Name                            Null?    Type
 ------------------------------- -------- ----
 ASSET_ID                                 VARCHAR2(200)
 INSTANCE_ID                              VARCHAR2(50)
 PARENT_INSTANCE_ID                       VARCHAR2(50)

I've analyzed the table (analyze table path_structure compute statistics) to produce the following stats for the number of distinct values:

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------------------------ ------------
PATH_STRUCTURE                 ASSET_ID                                  2
PATH_STRUCTURE                 INSTANCE_ID                            4961
PATH_STRUCTURE                 PARENT_INSTANCE_ID                     4859

What seems wrong, is that there are many more distinct values for the asset_id column:

SQL> select count(distinct asset_id) from path_structure;

COUNT(DISTINCTASSET_ID)

                   1113

The table itself has about 40,000 rows, and there is an index on (asset_id,instance_id). The CBO is choosing to do a full table scan on path_structure for the following query:

select *
  from path_structure
 where asset_id = :myassetid;

I presume it is doing so because the table statistics (according to dba_tab_col_statistics) indicate that there are only two distinct values for the asset_id column. Presumably this means the CBO believes the query will return approximately half the rows in the table, making the FTS a better option.

What I can't understand is why num_distinct = 2 for the asset_id column, if a 'select count(distinct asset_id)' query indicates that there are 1113 distinct values. I'm particularly confused because the other columns have the right value for num_distinct in dba_tab_col_statistics.

Has anybody seen anything like this before?

• Tom

Thomas A. La Porte
DreamWorks Feature Animation
tlaporte_at_anim.dreamworks.com Received on Fri Jun 11 1999 - 13:27:15 CDT