Monday, March 19, 2007

423,551 Invalid Indexes

A friend received an alarming email from a commendably proactive DBA who was concerned that he had discovered a potentially serious performance issue on the production system:

The following result shows there are many invalid indexes. For example, in the first row we see that there are nearly 423551 invalid indexes for just one row of a table. So if we rebuilt the index it would improve the performance.

Wow, that sure is a lot of invalid indexes for just one row of a table. For the sake of anonymity, I've run his query against the SCOTT/TIGER schema, where fortunately there are only 14 invalid indexes (or perhaps 10, if that's how you count them). Perhaps you can see what the problem might be:

SELECT DISTINCT
       t.table_name
     , i.index_name
     , t.num_rows table_rows
     , i.num_rows index_rows
FROM   dba_tables t
     , dba_indexes i
WHERE  t.table_name = 'DEPT'
AND    t.owner = 'SCOTT'
AND    i.owner = t.owner
AND    i.index_type = 'NORMAL';

TABLE_NAME                     INDEX_NAME                     TABLE_ROWS INDEX_ROWS
------------------------------ ------------------------------ ---------- ----------
DEPT                           PK_EMP                                  4         14
DEPT                           EMP_DEPT_IX                             4         14
DEPT                           PK_DEPT                                 4          4
DEPT                           PK_SALGRADE                             4          5

4 rows selected.

There is worse to come. It's not just DEPT that has invalid indexes. He goes on:

The following result shows there are many invalid indexes, so if we rebuild the index, it would improve the performance. At the same time from the first and last rows, indexes are not being used, so we need to look into it.
TABLE_NAME                     INDEX_NAME                     TABLE_ROWS INDEX_ROWS
------------------------------ ------------------------------ ---------- ----------
SALGRADE                       PK_DEPT                                 5          4
SALGRADE                       PK_EMP                                  5         14
SALGRADE                       EMP_DEPT_IX                             5         14
SALGRADE                       PK_SALGRADE                             5          5

TABLE_NAME                     INDEX_NAME                     TABLE_ROWS INDEX_ROWS
------------------------------ ------------------------------ ---------- ----------
EMP                            PK_DEPT                                14          4
EMP                            PK_EMP                                 14         14
EMP                            EMP_DEPT_IX                            14         14
EMP                            PK_SALGRADE                            14          5

I just can't help wondering what he thinks an index is.

6 comments:

Noons said...

wanna bet he reckons as well the index must be rebuilt after every insert?

Unknown said...

I sure hope that guy is a consultant somewhere - I know some project managers who'd be falling over themselves to throw a pile of cash at him.

Anonymous said...

To the other posters: give the guy a break, he is trying to help and be proactive just got his facts mixed up. Not everyone knows everything and all he needs is educating

William Robertson said...

The guy is employed as a DBA, but he doesn't know what indexes are or what it means for database objects to be invalid?

OK so he made a simple mistake in his join: we've all done it. He drew the wrong conclusion from the results - unfortunate. Perhaps there is a language issue, and by "invalid" he really meant that the stats were out of date, and by "n invalid indexes for just one row of a table" he meant a discrepancy of n between the stats for the index and those for the corresponding table. By "rebuild" he probably meant "re-analyze".

Anonymous said...

Out here in Singapore, I interviewed at least 20 people who claimed they were production DBAs for at least a couple of years (and I actually do believe them).

About a half of them didn't know how a standard index worked (I didn't provide any giveaways by saying "the b-tree index").

Most of them were Oracle certified though... (which is one of the reasons I never did it)

Jeffrey Kemp said...

Someone should tell him he needs to query on "i.index_type = 'ABNORMAL'" as well, just in case.