Consistency

Here’s a funny little glitch – typical of the sort of oddity that creeps into the data dictionary from time to time – cut-n-pasted from 11.1.0.7:

SQL> select count(*) from user_indexes;

  COUNT(*)
----------
      1074

1 row selected.

SQL> select count(*) from user_objects where object_type = 'INDEX';

  COUNT(*)
----------
       917

1 row selected.

We seem to have 157 indexes that aren’t indexes – what could they be. Perhaps a small enhancement to our query on user_indexes (the one that has the larger result) will help:

SQL> select index_type, count(*) from user_indexes group by index_type order by count(*) desc;

INDEX_TYPE                    COUNT(*)
--------------------------- ----------
NORMAL                             854
LOB                                157
IOT - TOP                           40
FUNCTION-BASED NORMAL               13
CLUSTER                             10

5 rows selected.

Spot the missing (or excess, depending on your viewpoint) 157 indexes. LOB indexes (the system generated index that maps a LOB segment) aren’t reported as being of type index, and the following predicate in the definition of the view user_objects tells us why not:

  and (o.type# not in (1  /* INDEX - handled below */,
                      10 /* NON-EXISTENT */)
       or
       (o.type# = 1 and 1 = (select 1
                              from sys.ind$ i
                             where i.obj# = o.obj#
                               and i.type# in (1, 2, 3, 4, 6, 7, 9))))

Compare this with the definition of view user_indexes, and we can see that user_objects is explicitly excluding LOB and “IOT – NESTED” indexes.

        decode(i.type#, 1, 'NORMAL'||
                          decode(bitand(i.property, 4), 0, '', 4, '/REV'),
                      2, 'BITMAP', 3, 'CLUSTER', 4, 'IOT - TOP',
                      5, 'IOT - NESTED', 6, 'SECONDARY', 7, 'ANSI', 8, 'LOB',
                      9, 'DOMAIN'),

This changes in 11.2.0.3, where type 8 (LOB) is explicitly included in the user_objects view.

Does anyone have an example of an ANSI index, or an “IOT – NESTED” index ?