Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, VV.

Asked: April 10, 2018 - 4:48 pm UTC

Last updated: April 11, 2018 - 3:37 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi - I just ran a full schema stats on one of our schemas. After the full schema stats was complete, I checked the num_rows in dba_tables and num_rows in dba_indexes. The num_rows from dba_tables looks good but num_rows from dba_indexes shows as 0 for a specific table. Why does that happen? how do we fix this ?

and Connor said...

Those stats might be perfectly accurate. Don't forget that entirely null entries are not indexed, so the number of rows in an index can be less than the number of rows in a table, eg

SQL> create table t as
  2  select rownum x from dual
  3  connect by level <= 10;

Table created.

SQL>
SQL> alter table t add y int;

Table altered.

SQL>
SQL> select * from t;

         X          Y
---------- ----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL>
SQL> create index ix on t ( y ) ;

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows from user_tables where table_name = 'T';

  NUM_ROWS
----------
        10

1 row selected.

SQL>
SQL> select num_rows from user_indexes where table_name = 'T';

  NUM_ROWS
----------
         0

1 row selected.



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.