Skip navigation

Simulated in 11.2.0.3 and 12.1.0.2

A type of index does not appear in DBA_INDEXES or simular views.  These are nosegment indexes.  First, let's see what are nosegment indexes.


Let's pretend I must tune the following SELECT because it does a costly full table scan (let's assume the CUSTOMERS table is huge):

set autotrace on explain only

select count(1) from customers where CUST_STATE_PROVINCE='Utrecht' ;

COUNT(1)

----------

       853

 

Plan hash value: 296924608

--------------------------------------------------------------------------------

| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |     1 |    11 |   423   (1)| 00:00:01 |

|   1 |  SORT AGGREGATE    |           |     1 |    11 |            |          |

|*  2 |   TABLE ACCESS FULL| CUSTOMERS |   383 |  4213 |   423   (1)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

    2 - filter("CUST_STATE_PROVINCE"='Utrecht')

 

Obviously, an index on the "CUST_STA_PROVINCE" column would suffice to avoid the full table scan.  I cannot, however, modify that environnment, or cannot afford the time it would take to create an index on that huge table.

So, I can create a nosegment index, also known as a virtual index.

create index FD_CSP on customers (CUST_STATE_PROVINCE) nosegment ;

alter session set "_use_nosegment_indexes"=true ; -- to tell Oracle to use nosegment indexes

set autotrace on explain only

select count(1) from customers where CUST_STATE_PROVINCE='Utrecht' ;

 

Index FD_CSP created.

Session altered.

Autotrace Enabled

Displays the execution plan only.

  COUNT(1)

----------

       853

 

Plan hash value: 208049494

----------------------------------------------------------------------------

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |        |     1 |    11 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |        |     1 |    11 |            |          |

|*  2 |   INDEX RANGE SCAN| FD_CSP |   383 |  4213 |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

    2 - access("CUST_STATE_PROVINCE"='Utrecht')

 

This way, my index is created instantly.  A nosegment index does not have a segment associated to it: there's no physical storage for it.  It's just a few lines in the Oracle dictionary tables.

I must, however, tell the database to avail itself of that type of index by issueing "alter session set "_use_nosegment_indexes"=true ;".  And as you can see in the EXPLAIN PLAN above, the optimizer chose that index.  Of course, If I actually run my SELECT, the database will not use my nosegment index, since it does not exist physically.

So to conclude that first part, nosegment indexes are handy to test the usefulnes of indexing columns when you cannot afford to do that for real.


Now, I have been using nosegment indexes for quite a while, but only recently have I discovered you will not find them in any of the _INDEXES views.  I thought it was weird enough to be the subject of this blog post (hence the title)!

select index_name from user_indexes where index_name='FD_CSP' ;

select index_name from all_indexes where index_name='FD_CSP' ;

select index_name from dba_indexes where index_name='FD_CSP' ;

select object_name,status, OBJECT_TYPE from user_objects where object_name='FD_CSP' ;

 

no rows selected

no rows selected

no rows selected

OBJECT_NAME  STATUS  OBJECT_TYPE

------------ ------- ------------

FD_CSP       VALID   INDEX

 

But they do appear in USER_OBJECTS (and DBA_OBJECTS and ALL_OBJECTS), and are listed as indexes!  At least, there is one view I can use to keep track of them.  But typically, nosegment indexes are used as try-outs before the eventual real index, so are short-lived (DROP INDEX is enough to get rid of them).

Just to be thorough, here is a screenshot of the "INFO" command of SQLCLI (SQLDeveloper's command line version), which gives all information about a table, including its indexes:

160131INFOonCUSTOMERS.png

And our nosegment index is not listed in there either.  So remember, nosegment indexes cannot be listed except with the _OBJECTS views.