What tables are involved in queries?

Answer: All queries look at the index token table. The table’s name has the form of DR$indexname$I and contains the list of tokens (TOKEN_TEXT column) and the information about the row and word positions where the token occurs (TOKEN_INFO column).

The row information is stored as internal docid values that must be translated into external rowid values. The table that you use depends on the type of lookup:

• For functional lookups, use the $K table, DR$indexname$K. This simple Index Organized Table (IOT) contains a row for each docid/rowid pair.

• For indexed lookups, use the $R table, DR$indexname$R. This table holds the complete list of rowids in a BLOB column.

Starting with Oracle Database 12c Release 2 ( 12.2), a new storage attribute, SMALL_R_ROW, was introduced to reduce the size of the $R row. It populates $R rows on demand instead of creating 22 static rows, thereby reducing the Data Manipulation Language contention. The contention happens when parallel insert, update, and delete operations try to lock the same $R row.

You can easily find out whether a functional or indexed lookup is being used by examining a SQL trace and looking for the $K or $R tables. Note: These internal index tables are subject to change from release to release. Oracle recommends that you do not directly access these tables in your application.