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: CREATE INDEX with BIG_IO storage preference is not supported in Oracle Autonomous Data Warehouse Cloud with Oracle Database release 18c, version 18.3 artifacts unless you grant the privilege to create a trigger to the user. You must also disable parallel DML either at the session or system level. Run the following SQL statements to grant the privilege to create a trigger and disable parallel DML:
grant create trigger to user;
alter session disable parallel dml; |
d_table_clause |
Parameter clause to specify the storage clause for the $D 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 dr$indexname$E table creation. Used to specify the storage and tablespace clauses to add to the end of the internal CREATE TABLE statement. |
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.
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. |
i_index_clause |
Parameter clause for dr$indexname$X index creation. 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. |
i_table_clause |
Parameter clause for dr$indexname$I 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 dr$indexname$K 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. |
kg_table_clause |
Parameter clause for $KG table 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 $KGI index creation. Specify storage and tablespace clauses to add to the end of the internal CREATE INDEX statement. |
n_table_clause |
Parameter clause for dr$indexname$N 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. |
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.
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. |
p_table_clause |
Parameter clause for the substring index if you have enabled SUBSTRING_INDEX in the BASIC_WORDLIST.
Specify storage and tablespace clauses to add to the end of the internal CREATE INDEX statement. The P table is an index-organized table so the storage clause you specify must be appropriate to this type of table. |
query_filter_cache_size |
Parameter clause to specify the maximum size of the query filter cache in bytes. The query filter cache is allocated out of the shared pool, so its maximum size must be smaller than the shared pool size. When this storage preference is set at the partition level, it is implicitly set at the index level.
The default is 0. |
r_table_clause |
Parameter clause for dr$indexname$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 dr$indexname$S 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.
Set it to T to enable the SEPARATE_OFFSETS index option for the CONTEXT index. The default is F. |
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. |
small_r_row |
Storage attribute to reduce the size of $R row. It improves DML and query performance during parallel DML and query workload. It reduces lock contention during DMLs, thus improving the DML performance. |
sn_table_clause |
Parameter clause for dr$indexname$SN 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 dr$indexname$SNI table creation. Specify the storage and tablespace clauses to add at the end of the internal CREATE INDEX statement. |
sd_table_clause |
Parameter clause for dr$indexname$SD 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 dr$indexname$SDI table creation. Specify the storage and tablespace clauses to add at the end of the internal CREATE INDEX statement. |
sv_table_clause |
Parameter clause for dr$indexname$SV 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 dr$indexname$SVI table creation. Specify the storage and tablespace clauses to add at the end of the internal CREATE INDEX statement. |
sr_table_clause |
Parameter clause for dr$indexname$SR 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 dr$indexname$SRI table creation. Specify the storage and tablespace clauses to add at the end of the internal CREATE INDEX statement. |
sbd_table_clause |
Parameter clause for dr$indexname$SBD 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 dr$indexname$SBDI table creation. Specify the storage and tablespace clauses to add at the end of the internal CREATE INDEX statement. |
sbf_table_clause |
Parameter clause for dr$indexname$SBF 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 dr$indexname$SBFI table creation. Specify the storage and tablespace clauses to add at the end of the internal CREATE INDEX statement. |
st_table_clause |
Parameter clause for dr$indexname$ST 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 dr$indexname$STI table creation. Specify the storage and tablespace clauses to add at the end of the internal CREATE INDEX statement. |
stz_table_clause |
Parameter clause for dr$indexname$STZ 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 dr$indexname$STZI table creation. Specify the storage and tablespace clauses to add at the end of the internal CREATE INDEX statement. |
sids_table_clause |
Parameter clause for dr$indexname$SIDS 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)'. |
sids_index_clause |
Parameter clause for dr$indexname$SIDSI table creation. Specify the storage and tablespace clauses to add at the end of the internal CREATE INDEX statement. |
siym_table_clause |
Parameter clause for dr$indexname$SIYM 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)'. |
siym_index_clause |
Parameter clause for dr$indexname$SIYMI table creation. 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 1 million. Oracle recommends a value of 100K to 1 million for optimal merge performance during sync index.
With stage_itab, when queries are run during heavy DML operations, Oracle 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 |
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. |