Forum Stats

  • 3,814,069 Users
  • 2,258,812 Discussions
  • 7,892,521 Comments

Discussions

Setting the tablespace for generated indexes

mNem
mNem Member Posts: 1,380 Gold Trophy
edited Feb 26, 2020 7:48PM in Text

I want to set the tablespace for all oracle text generated tables and the indexes. Managed to get that for most but not for all.

-- drop table whs_base_temp cascade constraints purge;

create table whs_base_temp

(

  id number constraint whs_base_temp_id_pk primary key using index tablespace ts_whs_index

, title varchar2(400) constraint whs_base_temp_title_nn not null

, description varchar2(2000)

, submitters varchar2(200)

/* ... other columns ..*/

)

tablespace ts_whs_data

;

begin
ctx_ddl.drop_preference   ('whs_base_storage');
ctx_ddl.drop_preference   ('whs_base_datastore');
ctx_ddl.drop_section_group('whs_base_section_group');
end;

-- Not sure if I am setting up the following correctly...

begin
ctx_ddl.create_preference   ('whs_base_storage', 'BASIC_STORAGE' );
ctx_ddl.set_attribute       ('whs_base_storage', 'STAGE_ITAB', 'true' );

ctx_ddl.set_attribute       ('whs_base_storage', 'g_table_clause', 'tablespace TS_WHS_INDEX storage (initial 1K)');
ctx_ddl.set_attribute       ('whs_base_storage', 'g_index_clause', 'tablespace TS_WHS_INDEX storage (initial 1K)');
ctx_ddl.set_attribute       ('whs_base_storage', 'i_table_clause', 'tablespace TS_WHS_INDEX storage (initial 1K)');
ctx_ddl.set_attribute       ('whs_base_storage', 'i_index_clause', 'tablespace TS_WHS_INDEX storage (initial 1K)');
ctx_ddl.set_attribute       ('whs_base_storage', 'K_TABLE_CLAUSE', 'tablespace TS_WHS_INDEX storage (initial 1K)');
ctx_ddl.set_attribute       ('whs_base_storage', 'K_INDEX_CLAUSE', 'tablespace TS_WHS_INDEX storage (initial 1K)');
ctx_ddl.set_attribute       ('whs_base_storage', 'N_TABLE_CLAUSE', 'tablespace TS_WHS_INDEX storage (initial 1K)');
ctx_ddl.set_attribute       ('whs_base_storage', 'U_table_clause', 'tablespace TS_WHS_INDEX storage (initial 1K)');

ctx_ddl.create_preference   ('whs_base_datastore', 'MULTI_COLUMN_DATASTORE');
ctx_ddl.set_attribute       ('whs_base_datastore', 'COLUMNS','title,description,submitters');
ctx_ddl.create_section_group('whs_base_section_group', 'BASIC_SECTION_GROUP');
ctx_ddl.add_field_section   ('whs_base_section_group', 'title', 'title', true);
ctx_ddl.add_field_section   ('whs_base_section_group', 'description', 'description', true);
ctx_ddl.add_field_section   ('whs_base_section_group', 'submitters', 'submitters', true);
end;

--drop index whs_base_temp_ft_idx;

CREATE INDEX whs_base_temp_ft_idx ON whs_base_temp(title)

INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS

('storage whs_base_storage datastore whs_base_datastore section group whs_base_section_group sync (on commit)')

;

SQL> select table_name, tablespace_name from user_all_tables order by 1;

TABLE_NAME                                                                                                                       TABLESPACE_NAME              
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------
DR$WHS_BASE_TEMP_FT_IDX$G                                                                                                        TS_WHS_INDEX                 
DR$WHS_BASE_TEMP_FT_IDX$I                                                                                                        TS_WHS_INDEX                 
DR$WHS_BASE_TEMP_FT_IDX$K                                                                                                        TS_WHS_INDEX                 
DR$WHS_BASE_TEMP_FT_IDX$N                                                                                                                                     
DR$WHS_BASE_TEMP_FT_IDX$U                                                                                                        TS_WHS_INDEX                 
WHS_BASE_TEMP                                                                                                                    TS_WHS_DATA                  

6 rows selected.

SQL> select index_name, tablespace_name from user_indexes order by 1;

INDEX_NAME                                                                                                                       TABLESPACE_NAME              
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------
DR$WHS_BASE_TEMP_FT_IDX$H                                                                                                        TS_WHS_INDEX                 
DR$WHS_BASE_TEMP_FT_IDX$KD                                                                                                       TS_WHS_DATA *(1)                
DR$WHS_BASE_TEMP_FT_IDX$KR                                                                                                       TS_WHS_DATA                  
DR$WHS_BASE_TEMP_FT_IDX$X                                                                                                        TS_WHS_INDEX *(2)                
SYS_C00208440                                                                                                                    TS_WHS_INDEX                 
SYS_IL0000468684C00006$$                                                                                                         TS_WHS_INDEX                 
SYS_IL0000468692C00006$$                                                                                                         TS_WHS_INDEX                 
SYS_IOT_TOP_468688                                                                                                               TS_WHS_INDEX                 
WHS_BASE_TEMP_FT_IDX                                                                                                                                          
WHS_BASE_TEMP_ID_PK                                                                                                              TS_WHS_INDEX                 

10 rows selected.

This is my first attempt to work with oracle text. I may have got few things wrong here (please feel free the correct), but how to get

*(1) to point to ts_whs_index?

*(2) if it is possible to set the name for indexes starting with SYS_*?

Thanks

Best Answer

  • Roger Ford-Oracle
    Roger Ford-Oracle Member Posts: 1,132 Employee
    edited Nov 20, 2019 1:23PM Answer ✓

    There are additional attributes for the KR and KD indexes:

    KD_INDEX_CLAUSE

    KR_INDEX_CLAUSE

    for the SYS indexes, there is no direct way in Oracle Text to specify their location.  The only way to do that would be to change your default tablespace.
    AFTER creation it should be possible to do 'ALTER INDEX ... REBUILD TABLESPACE <tablespace_name>' though I confess I haven't tested it myself.

    mNemkutrovsky

Answers