BASIC_STORAGE Attributes

The BASIC_STORAGE indexing type supports these attributes for database tables and indexes.

Table 39 BASIC_STORAGE Attributes

Attribute	Attribute Value
`big_io`	Parameter clause to improve the query performance for the `CONTEXT` index that is extensively used for IO operations. It uses `SECUREFILES`, and hence the tablespace must use automatic segment space management (ASSM). This clause mainly improves the query performance for rotational disks, where seeks are expensive compared to serial reads. Creating an index with the `BIG_IO` index option requires the `CREATE` `TRIGGER` privilege, as a temporary trigger is created during the indexing process. There is not much of a query performance improvement when the data storage is on solid state disks. Set it to `YES` to enable the `BIG_IO` index option for the `CONTEXT` index. The default is `NO`. Note: `BIG_IO` index option is not supported for local Oracle Text search index. The `BIG_IO` attribute of the `CONTEXT` indextype is deprecated with Oracle AI Database 26ai, and can be disabled or removed in a future release. Oracle recommends that you allow this value to be set to its default value of `N`. `BIG_IO` was introduced to reduce the cost of seeks when index postings exceeded 4KB in length. However, the internal code is relatively inefficient, and the attribute cannot be combined with newer index options. Seek cost is much less relevant for solid state disks or non-volatile memory devices (NVMe), and seek cost is irrelevant when postings are cached. This setting is therefore of little benefit for most indexes.
`c_table_clause`	Parameter clause to specify the storage clause for the `$C` table. Specify the storage and tablespace clauses to add to the end of the internal `CREATE INDEX` statements. To understand the purpose of `DR$INDEX_NAME$C`, see Oracle Text Application Developer's Guide.
`d_table_clause`	Parameter clause to specify the storage clause for the `$D` table (document/attribute storage table ). This clause may be specified if the forward index feature is being used. The forward index feature is used to increase the query performance while calculating snippets. If the `d_table_clause` is manually set, then it is recommended that you choose SecureFiles with high compression for the document blob column `doc` of the `$D` table. If the `d_table_clause` is not set, then the document blob uses SecureFiles by default, if the index owner's default tablespace is ASSM and the database compatible parameter is 11.0 or higher. The `$D` table is created to save a copy of a document into the index by either specifying a `save_copy` column or by specifying the `save_copy` storage attribute.
`d_index_clause`	Parameter clause for the `$DI` search index creation on the `$D` table. Specify storage and tablespace clauses to add to the end of the internal `CREATE` `Index` statement.
`e_table_clause`	Parameter clause for the `$E` table (section/namespace dictionary table) creation.
`forward_index`	Parameter clause to improve the performance of the following `CTX_DOC` package procedures: `ctx_doc.snippet` `ctx_doc.highlight` `ctx_doc.markup` Set it to `TRUE` to enable the forward index feature. This creates the $O table. The $O table stores the mapping information from the token offsets in the $I table to character offsets in the indexed documents. The default is `FALSE`.
`g_index_clause`	Parameter clause for the `$H` btree index on the `$G` table. Specify the storage and tablespace clauses to add to the end of the internal `CREATE INDEX` statement. When a `CONTEXT` index is created with the `STAGE_ITAB` index option, an empty `$G` table is created with the `$H` btree index on it. Use the `g_index_clause` clause in conjunction with the `STAGE_ITAB` index option for improving the query performance for the `CONTEXT` index that is extensively used for DML operations.
`g_table_clause`	Parameter clause for the `$G` table (staging inverted table) creation. Specify the storage and tablespace clauses to add to the end of the internal `CREATE TABLE` statement. When a `CONTEXT` index is created with the `STAGE_ITAB` index option, an empty `$G` table is created with the `$H` btree index on it. Use the `g_table_clause` clause in conjunction with the `STAGE_ITAB` index option for improving the query performance for the `CONTEXT` index that is extensively used for DML operations.
`h_index_clause`	Parameter clause for the `$H` btree index on the `$G` table. Specify the storage and tablespace clauses to add to the end of the internal `CREATE INDEX` statement.
`i_index_clause`	Parameter clause for the `$X` index creation on the `$I` table. Specify storage and tablespace clauses to add to the end of the internal `CREATE` `INDEX` statement. The default clause is: `'COMPRESS 2'`, which instructs Oracle Text to compress this index table. If you choose to override the default, Oracle recommends including `COMPRESS 2` in your parameter clause to compress this table, because such compression saves disk space and helps query performance.
`i_rowid_index_clause`	Parameter clause to specify the storage clause for the `$R` index on `dr$rowid` column of the `$I` table. Specify storage and tablespace clauses to add to the end of the internal `CREATE` `INDEX` statement. This clause is only used by the `CTXCAT` index type. Note: The Oracle Text indextype `CTXCAT` is deprecated with Oracle AI Database 26ai. The indextype itself, and it's operator `CTXCAT`, can be removed in a future release. `CTXCAT` was introduced when indexes were typically a few megabytes in size. Modern, large indexes, can be difficult to manage with `CTXCAT`. The addition of index sets to `CTXCAT` can be achieved more effectively by the use of `FILTER BY` and `ORDER BY` columns, or `SDATA`, or both, in the `CONTEXT` indextype. `CTXCAT` is therefore rarely an appropriate choice. Oracle recommends that you choose the more efficient `CONTEXT` indextype.
`i_table_clause`	Parameter clause for the `$I` table (main inverted index table) creation. Specify storage and tablespace clauses to add to the end of the internal `CREATE` `TABLE` statement. The `$I` table is the index data table. Note: Oracle strongly recommends that you do not specify "disable storage in row" for `$I` LOBs, as this greatly degrades the query performance.
`k_table_clause`	Parameter clause for the `$K` table (docid to rowid mapping table) creation. Specify storage and tablespace clauses to add to the end of the internal `CREATE` `TABLE` statement. The `$K` table is the keymap table.
`k_index_clause`	Parameter clause for the `$KI` index creation on the `$K` table. Specify storage and tablespace clauses to add to the end of the internal `CREATE` `INDEX` statement.
`kd_index_clause`	Parameter clause for the `$KD` index creation on the `$K` table . Specify storage and tablespace clauses to add to the end of the internal `CREATE TABLE` statement. The `$KD` index is a btree index on top of the `$K` table. It facilitates a quick docid-to-rowid (KD) mapping. Docids are used internally by Oracle Text, and ROWIDs are used by the database.
`kr_index_clause`	Parameter clause for the `$KR` index (rowid to docid mapping index) creation on the `$K` table. Specify storage and tablespace clauses to add to the end of the internal `CREATE INDEX` statement. Similar to the `$KD` index, the `$KR` index is a btree index on top of the `$K` table. It facilitates a quick rowid-to-docid mapping (KR) mapping. Docids are used internally by Oracle Text and ROWIDs are used by the database.
`kg_table_clause`	Parameter clause for the `$KG` table (K-gram table (token grams for wildcard/substring acceleration)) creation. Specify storage and tablespace clauses to add to the end of the internal `CREATE TABLE` statement. The `$KG` table stores the k-gram index to facilitate efficient wildcard search.
`kg_index_clause`	Parameter clause for the `$KGI` index creation on `gram_text`. Specify storage and tablespace clauses to add to the end of the internal `CREATE INDEX` statement.
`n_table_clause`	Parameter clause for the `$N` table (deleted documents tracking table ) creation. Specify storage and tablespace clauses to add to the end of the internal `CREATE` `TABLE` statement. The `$N` table is the negative list table which keeps track of deleted document IDs. These document IDs must be cleaned up by index optimization.
`n_index_clause`	Parameter clause for the `$NI` index creation on the `$N` table. Specify storage and tablespace clauses to add to the end of the internal `CREATE` `INDEX` statement.
`o_table_clause`	Parameter clause to specify the storage clause for the `$O` table (Offset table). This clause may be specified if the forward index feature is being used. The forward index feature is used to increase the query performance while calculating snippets. If the `o_table_clause` is manually set, then it is recommended that you choose SecureFiles with high compression for the document blob column `mapping` of the $O table. If the `o_table_clause` is not set, then the document blob uses SecureFiles by default, if the index owner's default tablespace is ASSM and the database compatible parameter is 11.0 or higher. The `$O` table is created when the forward index feature is enabled by specifying the `forward_index` storage attribute. The `$O` table stores the mapping information from the token offsets in the $I table to character offsets in the indexed documents.
`o_index_clause`	Parameter clause for the `$Z` index creation on the `$O` table.
`p_table_clause`	Parameter clause for the substring or pattern `$P` table creation if you have enabled `SUBSTRING_INDEX` in the `BASIC_WORDLIST`. Specify storage and tablespace clauses to add to the end of the internal `CREATE` `TABLE` statement. The `$P` table is an index-organized table so the storage clause you specify must be appropriate to this type of table.
`p_index_clause`	Parameter clause for the `$PI` index creation on the `$P` table. Specify storage and tablespace clauses to add to the end of the internal `CREATE` `INDEX` statement.
`r_table_clause`	Parameter clause for the `$R` table creation. Specify storage and tablespace clauses to add to the end of the internal `CREATE TABLE` statement. The `$R` table is the `ROWID` table. The default clause is: `'LOB(DATA) STORE AS (CACHE)'` If you modify this attribute, always include this clause for good performance. Note: When you set the `COMPATIBLE` database parameter to 18.1 or higher, all Oracle Text indexes are created using the default `FAST_DML` option, that is, the indexes will not have the `$R` mapping table.
`s_table_clause`	Parameter clause for the `$S` table (Sortable SDATA table) creation. Specify storage and tablespace clauses to add to the end of the internal `CREATE` `TABLE` statement. The default clause is `nocompress`. For performance reasons, `$S` table must be created on a tablespace with db block size >= 4K without overflow segment and without a `PCTTHRESHOLD` clause. If $S is created on a tablespace with db block size < 4K, or is created with an overflow segment or with `PCTTHRESHOLD` clause, then appropriate errors will be raised during `CREATE` `INDEX`. The S table is the table that stores `SDATA` section values. If this clause is specified for a storage preference in an index without `SDATA`, then it will have no effect on the index, and index creation will still succeed.
`save_copy`	Parameter clause to specify saving the document to the `$D` index table. Specify this clause to use the forward index feature for increasing the query performance while calculating snippets. Set it to `PLAINTEXT` to save the copy of a document in the `$D` table in the plaintext format. This improves the performance of snippet generation, since it does not invoke the datastore or filter to fetch the text. This also improves the performance of highlight. Set it to `FILTERED` to save the copy of a document in the `$D` table in the filtered (HTML) format. This improves the performance of highlight and markup, but requires more disk space than plaintext format. It is less efficient for snippets generation, since the HTML markup must be removed during the creation of snippets. The default is `NONE`, and the copy of a document is not saved in the `$D` table.
`save_copy_max_size`	Parameter clause to specify the maximum size of a document to save in the `$D` table using a basic_storage attribute. If the document size is greater than the size specified in this attribute, the truncated version of the document having the size specified in this attribute is saved in the `$D` table. If the `$D` table is using SecureFiles with compression for the document blob, then the `save_copy_max_size` restriction is applied on the document size before compression. The default is 0, and the whole document is saved in the `$D` table irrespective of its size. Note: The `save_copy_max_size` parameter clause is effective only when the `save_copy` parameter clause is specified.
`separate_offsets`	Parameter clause to improve the query performance for the `CONTEXT` index that is extensively used for IO operations, and whose queries are mainly single-word or boolean queries. Creating an index with the `SEPARATE_OFFSETS` index option requires the `CREATE` `TRIGGER` privilege, as a temporary trigger is created during the indexing process. Set it to `T` to enable the `SEPARATE_OFFSETS` index option for the `CONTEXT` index. The default is `F`. Note: The `SEPARATE_OFFSETS` index option is not supported for local Oracle Text search index.
`single_byte`	Storage option for better performance if all the indexed data that is known in advance is single-byte. When set to `TRUE`, all the data is treated as a single-byte (8-bit) data and the character set is irrelevant during indexing and querying. Ensure that no character in the data set crosses the single-byte (8-bit) limit. The default is `FALSE`.
`sn_table_clause`	Parameter clause for the `$SN` table (Searchable SDATA for NUMBER table) creation. Specify the storage and tablespace clauses to add at the end of the internal `CREATE TABLE` statement. The default clause is: `'LOB(VAL_INFO) STORE AS (CACHE)'`.
`sn_index_clause`	Parameter clause for the `$SNI` index (Searchable SDATA NUMBER index) creation on the `$SN` table. Specify the storage and tablespace clauses to add at the end of the internal `CREATE INDEX` statement.
`sni_index_clause`	Parameter clause for the `$SNI` index creation. Specify the storage and tablespace clauses to add at the end of the internal `CREATE INDEX` statement.
`sd_table_clause`	Parameter clause for the `$SD` table (Searchable SDATA for DATE table) creation. Specify the storage and tablespace clauses to add at the end of the internal `CREATE TABLE` statement. The default clause is: `'LOB(VAL_INFO) STORE AS (CACHE)'`.
`sd_index_clause`	Parameter clause for the `$SDI` index (Searchable SDATA DATE index) creation on the `$SD` table. Specify the storage and tablespace clauses to add at the end of the internal `CREATE INDEX` statement.
`sdi_index_clause`	Parameter clause for the `$SDI` index creation. Specify the storage and tablespace clauses to add at the end of the internal `CREATE INDEX` statement.
`sv_table_clause`	Parameter clause for the `$SV` table (Searchable SDATA for VARCHAR2(255) table) creation. Specify the storage and tablespace clauses to add at the end of the internal `CREATE TABLE` statement. The default clause is: `'LOB(VAL_INFO) STORE AS (CACHE)'`.
`sv_index_clause`	Parameter clause for the `$SVI` index (Searchable SDATA VARCHAR2 index) creation on the `$SV` table. Specify the storage and tablespace clauses to add at the end of the internal `CREATE INDEX` statement.
`sr_table_clause`	Parameter clause for the `$SR` table (Searchable SDATA for RAW(255) table) creation. Specify the storage and tablespace clauses to add at the end of the internal `CREATE TABLE` statement. The default clause is: `'LOB(VAL_INFO) STORE AS (CACHE)'`.
`sr_index_clause`	Parameter clause for the `$SRI` index (Searchable SDATA RAW index) creation on the `$SR` table . Specify the storage and tablespace clauses to add at the end of the internal `CREATE INDEX` statement.
`sbd_table_clause`	Parameter clause for the `$SBD` table (Searchable SDATA for BINARY_DOUBLE table) creation. Specify the storage and tablespace clauses to add at the end of the internal `CREATE TABLE` statement. The default clause is: `'LOB(VAL_INFO) STORE AS (CACHE)'`.
`sbd_index_clause`	Parameter clause for the `$SBDI` index (Searchable SDATA BINARY_DOUBLE index) creation on the `$SBD` table. Specify the storage and tablespace clauses to add at the end of the internal `CREATE INDEX` statement.
`sbf_table_clause`	Parameter clause for the `$SBF` table (Searchable SDATA for BINARY_FLOAT table) creation. Specify the storage and tablespace clauses to add at the end of the internal `CREATE TABLE` statement. The default clause is: `'LOB(VAL_INFO) STORE AS (CACHE)'`.
`sbf_index_clause`	Parameter clause for the `$SBFI` index (Searchable SDATA BINARY_FLOAT index) creation on the `$SBF` table. Specify the storage and tablespace clauses to add at the end of the internal `CREATE INDEX` statement.
`st_table_clause`	Parameter clause for the `$ST` table (Searchable SDATA for TIMESTAMP(9) table) creation. Specify the storage and tablespace clauses to add at the end of the internal `CREATE TABLE` statement. The default clause is: `'LOB(VAL_INFO) STORE AS (CACHE)'`.
`st_index_clause`	Parameter clause for the `$STI` index (Searchable SDATA TIMESTAMP index) creation on `$ST` table. Specify the storage and tablespace clauses to add at the end of the internal `CREATE INDEX` statement.
`stz_table_clause`	Parameter clause for the `$STZ` table (Searchable SDATA for TIMESTAMP(9) WITH TIME ZONE table) creation. Specify the storage and tablespace clauses to add at the end of the internal `CREATE TABLE` statement. The default clause is: `'LOB(VAL_INFO) STORE AS (CACHE)'`.
`stz_index_clause`	Parameter clause for the `$STZI` index (Searchable SDATA TIMESTAMP WITH TIME ZONE index) creation on the `$STZ` table. Specify the storage and tablespace clauses to add at the end of the internal `CREATE INDEX` statement.
`stage_itab`	Switch to improve the query performance for the `CONTEXT` index that is extensively used for DML operations. When the `STAGE_ITAB` index option is disabled, then when a new document is added to the index, `SYNC_INDEX` is called to make the documents searchable. This creates new rows in the $I table, thus increasing the fragmentation in the $I table. This leads to the deterioration of the query performance. When the `STAGE_ITAB` index option is enabled, the information about the new documents is stored in the $G staging table, and not in the $I table. This ensures that the $I table does not get fragmented, and thus does not deteriorate the query performance. When the `STAGE_ITAB` index option is enabled, the $H btree index is also created on the $G table. The $G table and $H btree index are equivalent to the $I table and $X btree index. Set `stage_itab` to `YES` to enable the `STAGE_ITAB` index option for the `CONTEXT` index. The default is `NO`.
`stage_itab_auto_opt`	New storage option to enable automatic background optimize merge. `stage_itab` and `stage_itab_auto_opt` must be set to `TRUE` to enable automatic background optimize merge. Setting `stage_itab_auto_opt` to `TRUE` is not supported when `stage_itab_max_rows` is set to `0` as the zero value disables row movement from the $G table to the $I table.
`stage_itab_max_rows`	Storage option to ensure that the $G (`stage_itab`) table fits into the KEEP pool and also that the $G table does not get filled up too frequently. This option is also required to ensure that $G does not grow too big and start slowing down the query and the index synchronization performance. When the number of rows in the $G table exceeds this setting, a process is started to move all data from the $G table to the $I table, optimizing the data as it is moved. Note that this may cause certain SYNC operations or commits if SYNC(ON COMMIT) is used to take an unexpectedly long time because they may be moving many $G rows which have been inserted by other processes. If this is unacceptable, set `stage_itab_max_rows` to `0` and use an auto optimization job instead. When scheduling an auto optimization job, set `stage_itab_max_rows` to 0 to disable the automatic merging that now happens through sync index. If `stage_itab_max_rows` is not set to 0 and an attempt is made to schedule an auto optimization job, then an error occurs. You can set `stage_itab_max_rows` to either 0 or any value greater than or equal to 1000. The default value is 10K. A system with a very heavy DML load (inserts, deletes, and updates) but a low query load might benefit from a larger value as this reduces the number of merge operations which are necessary. For such indexes, Oracle recommends a value of 100K to 1 million. If you set the value to 0 the automatic background merge is turned off. In this case, you must manually run `CTX_DDL.OPTIMIZE_INDEX` in `MERGE` mode to move rows from the $G staging table to the $I permanent index table. With `stage_itab`, when queries are run during heavy DML operations, Oracle AI Database can issue the following error: `ORA-08176 consistent read failure; rollback data not available`. In such cases, increase the size of the `UNDO` tablespace and the `UNDO_RETENTION` initialization parameter.
`stage_itab_parallel`	New storage option controls the degree of parallelism used to merge rows from the `stage_itab` (`$G` table) back to the `$I` table when the `stage_itab_max_rows` limit is hit. The default value is 16 for the degree of parallelism.
`u_table_clause`	Parameter clause for the `$U` table (rowid staging/queue table). Specify the storage and tablespace clauses to add at the end of the internal `CREATE TABLE` statement. The `$U` table keeps track of concurrent updates.
`u_index_clause`	Parameter clause for the `$UI` search index creation on the `$U` table . Specify the storage and tablespace clauses to add at the end of the internal `CREATE INDEX` statement.
`x_index_clause`	Parameter clause for the `$X` index creation. Specify storage and tablespace clauses to add to the end of the internal `CREATE INDEX` statement.

Related Topics

SYNC_INDEX