BASIC_STORAGE Examples

The following examples specify that the index tables are to be created in the foo tablespace with an initial extent of 1K:

begin
ctx_ddl.create_preference('mystore', 'BASIC_STORAGE');
ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE',
                        'tablespace foo storage (initial 1K)');
ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE',
                        'tablespace foo storage (initial 1K)');
ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE',
                        'tablespace users storage (initial 1K) lob
                         (data) store as (disable storage in row cache)');
ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE',
                        'tablespace foo storage (initial 1K)');
ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE',
                        'tablespace foo storage (initial 1K) compress 2');
ctx_ddl.set_attribute('mystore', 'P_TABLE_CLAUSE',
                        'tablespace foo storage (initial 1K)');
ctx_ddl.set_attribute('mystore', 'S_TABLE_CLAUSE',
                        'tablespace foo storage (initial 1K)');
ctx_ddl.set_attribute('mystore', 'U_TABLE_CLAUSE',
                        'tablespace foo storage (initial 1K)');end;

The following example adds to the end of the internal table that is created.

exec ctx_ddl.create_preference('sto', 'basic_storage');
exec ctx_ddl.set_attribute('sto', 'e_table_clause', 'tablespace foo');

The following example uses query_filter_cache_size storage parameter for a partitioned index:

exec ctx_ddl.create_preference('fcs', 'basic_storage');
exec ctx_ddl.set_attribute('fcs', 'query_filter_cache_size', '100000000');

create table fc(id number primary key, txt varchar2(255))
partition by range (id)
(
        partition p1 values less than (25),
        partition p2 values less than (50),
        partition p3 values less than (75)
);

create index fci on fc(txt) indextype is ctxsys.context
   local (
    partition p1,
    partition p2,
    partition p3) parameters('storage fcs memory 49M sync (on commit)');

The query filter cache is an index level storage preference. The storage preference for the query filter cache can be set at partition level only if this is also set at the index level.

select count(*) from fc partition (p1) where contains(txt,'ctxfiltercache((hello))')>0;

Note: Starting in Oracle Database Release 21c, CTXFILTERCACHE is deprecated, and also CTX_FILTER_CACHE_STATISTICS and QUERY_FILTER_CACHE_SIZE.

SINGLE_BYTE Data Indexing Storage Attribute

Syntax

ctx_ddl.set_attribute(storage_pref_name, 'SINGLE_BYTE', BOOLEAN);

storage_pref_name

Specify the first argument as the storage preference name.

SINGLE_BYTE

Specify the storage attribute name as SINGLE_BYTE or single_byte.

BOOLEAN

Indicate whether the attribute is set. By default, the value is FALSE. It implies that the database character set identifies whether the documents are stored as single-byte or multi-byte.

The following example sets the storage preference and enables the single_byte storage attribute:

exec ctx_ddl.create_preference('mysto', 'basic_storage');
ctx_ddl.set_attribute('mysto', 'single_byte', 'TRUE');

SMALL_R_ROW Storage Attribute

Syntax

ctx_ddl.set_attribute(storage_pref_name, 'SMALL_R_ROW', BOOLEAN);

storage_pref_name

Specify the first argument as the storage preference name.

SMALL_R_ROW

Specify the storage attribute name as SMALL_R_ROW or small_r_row..

BOOLEAN

Indicate whether the attribute is set. By default, the value is TRUE.

The following example sets the storage preference and enables the small_r_row storage attribute:

begin
ctx_ddl.create_preference('sto', 'basic_storage');
ctx_ddl.set_attribute('sto', 'small_r_row', 'T',
end;

To enable or disablesmall_r_row feature on an existing index:

ALTER INDEX index_name rebuild PARAMETERS('replace storage sto');

By default, small_r_row=TRUE , however, for earlier releases, small_r_row=FALSE.