Oracle Scratchpad

November 23, 2012


Filed under: Indexing,Oracle — Jonathan Lewis @ 5:18 pm BST Nov 23,2012

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

SQL> select count(*) from user_indexes;


1 row selected.

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


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 */)
       (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(, 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, 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 ?

1 Comment »

  1. […] Jonathan Lewis is sharing a funny little glitch – typical of the sort of oddity that creeps into the data dictionary from time to time. […]

    Pingback by Log Buffer #298, A Carnival of the Vanities for DBAs | The Pythian Blog — December 7, 2012 @ 7:16 am BST Dec 7,2012 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by