Home » Server Options » Text & interMedia » Partionning table and index with Oracle text
Partionning table and index with Oracle text [message #76095] Mon, 14 June 2004 01:56 Go to next message
sanjuro
Messages: 2
Registered: June 2004
Junior Member
I would like to create a partionned table and a partionned index on differents tablespaces.
----------------------------------------------
The only way i have found to create table and index it
is following :
>Partitionning table by range and

-- Test 2
select 'Creation table edoc_biblio2 partionne par range' "Operation" from dual;
drop table edoc_biblio2 ;
create table edoc_biblio2 (
doc_id number primary key,
confidentiality varchar2(50),
category_id NUMBER ,
typedoc Number,
title varchar2(2000),
code_mgt NUMBER(2) DEFAULT 0, -- -1 = deleted , 0= OK
auteur VARCHAR2(50),
version VARCHAR2(10),
version_auto_increment NUMBER(10),
text BLOB,
do_be_deleted NUMBER(1),
download_date DATE,
download_user VARCHAR2(50),
download_mgt VARCHAR2(10),
upload_date DATE,
upload_user VARCHAR2(50),
upload_mgt VARCHAR2(10),
upload_file_name VARCHAR2(2000),
upload_file_name_ON_SERVER VARCHAR2(2000)
)
TABLESPACE tbs_Orcl90_repo_ref
PCTFREE 10
PCTUSED 40
STORAGE (
INITIAL 10K
NEXT 5K
PCTINCREASE 0
)
PARALLEL 1
partition by range (typedoc)
(partition pList_Application values less than (10) tablespace tbs_xpart_00 ,
partition pList_Audio values less than (20) tablespace tbs_xpart_01,
partition pList_Default values less than (MAXVALUE) tablespace tbs_xpart_02)
/

Select 'Test 2A Creation index partionne' "Operation" from dual;
create index idx_2A_edoc_biblio2
on edoc_biblio2 (text) indextype is ctxsys.context
local (partition pList_Application, partition pList_Audio,partition pList_Default)
/

----------------------------------------------
You can see that :

The table is partionned by range and i am able to dispatche data according to tablespaces.
BUT i have not found how to names the tablespace during the index creation.
-----------------------------------------------
I would like if it is possible :
Create partionned table by LIST and indicate tablespace
according data.
Create partionned index and indicate tablespace according partition
------------------------------------------------
Thanks a lot for your hand
Regards Ken

Report message to a moderator

Re: Partionning table and index with Oracle text [message #76096 is a reply to message #76095] Tue, 15 June 2004 06:36 Go to previous messageGo to next message
sanjuro
Messages: 2
Registered: June 2004
Junior Member
This is the solution

create table edoc_biblio (
doc_id number primary key,
confidentiality varchar2(50),
category_id NUMBER ,
typedoc Number,
title varchar2(2000),
code_mgt NUMBER(2) DEFAULT 0, -- -1 = deleted , 0= OK
auteur VARCHAR2(50),
version VARCHAR2(10),
version_auto_increment NUMBER(10),
text BLOB,
do_be_deleted NUMBER(1),
download_date DATE,
download_user VARCHAR2(50),
download_mgt VARCHAR2(10),
upload_date DATE,
upload_user VARCHAR2(50),
upload_mgt VARCHAR2(10),
upload_file_name VARCHAR2(2000),
upload_file_name_ON_SERVER VARCHAR2(2000)
)
TABLESPACE tbs_Orcl90_repo_ref
PCTFREE 10
PCTUSED 40
STORAGE (
INITIAL 10K
NEXT 5K
PCTINCREASE 0
)
PARALLEL 1
partition by range (typedoc)
(partition pList_Application values less than (10) tablespace tbs_xpart_00 ,
partition pList_Audio values less than (20) tablespace tbs_xpart_01,
partition pList_Default values less than (MAXVALUE) tablespace tbs_xpart_02)
/

begin
ctx_ddl.create_preference('Application_storage', 'BASIC_STORAGE');
ctx_ddl.set_attribute('Application_storage', 'I_TABLE_CLAUSE', 'tablespace tbs_xpart_00 storage (initial 1k)');
ctx_ddl.set_attribute('Application_storage', 'K_TABLE_CLAUSE', 'tablespace tbs_xpart_00 storage (initial 1K)');
ctx_ddl.set_attribute('Application_storage', 'R_TABLE_CLAUSE', 'tablespace tbs_xpart_00 storage (initial 1K)');
ctx_ddl.set_attribute('Application_storage', 'N_TABLE_CLAUSE', 'tablespace tbs_xpart_00 storage (initial 1K)');
ctx_ddl.set_attribute('Application_storage', 'I_INDEX_CLAUSE', 'tablespace tbs_xpart_00 storage (initial 1K)');
ctx_ddl.set_attribute('Application_storage', 'P_TABLE_CLAUSE', 'tablespace tbs_xpart_00 storage (initial 1K)');
end;
/

begin
ctx_ddl.create_preference('Audio_storage', 'BASIC_STORAGE');
ctx_ddl.set_attribute('Audio_storage', 'I_TABLE_CLAUSE', 'tablespace tbs_xpart_01 storage (initial 1k)');
ctx_ddl.set_attribute('Audio_storage', 'K_TABLE_CLAUSE', 'tablespace tbs_xpart_01 storage (initial 1K)');
ctx_ddl.set_attribute('Audio_storage', 'R_TABLE_CLAUSE', 'tablespace tbs_xpart_01 storage (initial 1K)');
ctx_ddl.set_attribute('Audio_storage', 'N_TABLE_CLAUSE', 'tablespace tbs_xpart_01 storage (initial 1K)');
ctx_ddl.set_attribute('Audio_storage', 'I_INDEX_CLAUSE', 'tablespace tbs_xpart_01 storage (initial 1K)');
ctx_ddl.set_attribute('Audio_storage', 'P_TABLE_CLAUSE', 'tablespace tbs_xpart_01 storage (initial 1K)');
end;
/

begin
ctx_ddl.create_preference('Default_storage', 'BASIC_STORAGE');
ctx_ddl.set_attribute('Default_storage', 'I_TABLE_CLAUSE', 'tablespace tbs_xpart_02 storage (initial 1k)');
ctx_ddl.set_attribute('Default_storage', 'K_TABLE_CLAUSE', 'tablespace tbs_xpart_02 storage (initial 1K)');
ctx_ddl.set_attribute('Default_storage', 'R_TABLE_CLAUSE', 'tablespace tbs_xpart_02 storage (initial 1K)');
ctx_ddl.set_attribute('Default_storage', 'N_TABLE_CLAUSE', 'tablespace tbs_xpart_02 storage (initial 1K)');
ctx_ddl.set_attribute('Default_storage', 'I_INDEX_CLAUSE', 'tablespace tbs_xpart_02 storage (initial 1K)');
ctx_ddl.set_attribute('Default_storage', 'P_TABLE_CLAUSE', 'tablespace tbs_xpart_02 storage (initial 1K)');
end;
/
-- For doing finally ....

Select 'Test 2C Creation index partionne : TAR ORACLE' from dual;
create index idx_edoc_biblio
on edoc_biblio (text) indextype is ctxsys.context
local (partition pList_Application parameters ('storage Application_storage'),
partition pList_Audio parameters ('storage Audio_storage'),
partition pList_Default parameters ('storage Default_storage'))
--parameters ('lexer my_lexer')
/

Report message to a moderator

Re: Partionning table and index with Oracle text [message #205859 is a reply to message #76096] Mon, 27 November 2006 23:16 Go to previous message
rasikeg@gmail.com
Messages: 4
Registered: November 2006
Location: LK
Junior Member
Hi sanjuro,

I do have my Oracle Text index similar to what you have mentioned here , only issue is it creates the index but does not utilize the partition tablespace that I have created.

I have setup the BASIC_STORAGE as follows,

begin
ctx_ddl.create_preference('my_default_st', 'BASIC_STORAGE');
ctx_ddl.set_attribute('my_default_st', 'I_TABLE_CLAUSE','tablespace ot_index
storage (initial 256K)');
ctx_ddl.set_attribute('my_default_st', 'K_TABLE_CLAUSE','tablespace ot_index
storage (initial 256K)');
ctx_ddl.set_attribute('my_default_st', 'R_TABLE_CLAUSE','tablespace ot_index
storage (initial 256K) lob (data) store as (disable storage in row cache)');
ctx_ddl.set_attribute('my_default_st', 'N_TABLE_CLAUSE','tablespace ot_index
storage (initial 256K)');
ctx_ddl.set_attribute('my_default_st', 'I_INDEX_CLAUSE','tablespace ot_index
storage storage (initial 256K) compress 2');
ctx_ddl.set_attribute('my_default_st', 'P_TABLE_CLAUSE','tablespace ot_index
storage (initial 256K)');
end;
/

begin
ctx_ddl.drop_preference('part_tbs1');
end;
/

begin
ctx_ddl.create_preference('part_tbs1', 'BASIC_STORAGE');
ctx_ddl.set_attribute('part_tbs1', 'I_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K)');
ctx_ddl.set_attribute('part_tbs1', 'K_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K)');
ctx_ddl.set_attribute('part_tbs1', 'R_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K) lob (data) store as (disable storage in row cache)');
ctx_ddl.set_attribute('part_tbs1', 'N_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K)');
ctx_ddl.set_attribute('part_tbs1', 'I_INDEX_CLAUSE','tablespace ot_index storage
(initial 256K) compress 2');
ctx_ddl.set_attribute('part_tbs1', 'P_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K)');
end;
/

begin
ctx_ddl.drop_preference('part_tbs2');
end;
/

begin
ctx_ddl.create_preference('part_tbs2', 'BASIC_STORAGE');
ctx_ddl.set_attribute('part_tbs2', 'I_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K)');
ctx_ddl.set_attribute('part_tbs2', 'K_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K)');
ctx_ddl.set_attribute('part_tbs2', 'R_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K) lob (data) store as (disable storage in row cache)');
ctx_ddl.set_attribute('part_tbs2', 'N_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K)');
ctx_ddl.set_attribute('part_tbs2', 'I_INDEX_CLAUSE','tablespace ot_index storage
(initial 256K) compress 2');
ctx_ddl.set_attribute('part_tbs2', 'P_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K)');
end;
/


begin
ctx_ddl.drop_preference('part_tbs3');
end;
/

begin
ctx_ddl.create_preference('part_tbs3', 'BASIC_STORAGE');
ctx_ddl.set_attribute('part_tbs3', 'I_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K)');
ctx_ddl.set_attribute('part_tbs3', 'K_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K)');
ctx_ddl.set_attribute('part_tbs3', 'R_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K) lob (data) store as (disable storage in row cache)');
ctx_ddl.set_attribute('part_tbs3', 'N_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K)');
ctx_ddl.set_attribute('part_tbs3', 'I_INDEX_CLAUSE','tablespace ot_index storage
(initial 256K) compress 2');
ctx_ddl.set_attribute('part_tbs3', 'P_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K)');
end;
/


begin
ctx_ddl.drop_preference('part_tbs4');
end;
/

begin
ctx_ddl.create_preference('part_tbs4', 'BASIC_STORAGE');
ctx_ddl.set_attribute('part_tbs4', 'I_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K)');
ctx_ddl.set_attribute('part_tbs4', 'K_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K)');
ctx_ddl.set_attribute('part_tbs4', 'R_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K) lob (data) store as (disable storage in row cache)');
ctx_ddl.set_attribute('part_tbs4', 'N_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K)');
ctx_ddl.set_attribute('part_tbs4', 'I_INDEX_CLAUSE','tablespace ot_index storage
(initial 256K) compress 2');
ctx_ddl.set_attribute('part_tbs4', 'P_TABLE_CLAUSE','tablespace ot_index storage
(initial 256K)');
end;
/

-- create the user data store prior to proceeding to next section


-- Create the OT index preferences
BEGIN
CTX_DDL.CREATE_PREFERENCE('my_asg_uds', 'user_datastore');
CTX_DDL.SET_ATTRIBUTE ('my_asg_uds', 'procedure','ot_user_datastore_pr');
CTX_DDL.SET_ATTRIBUTE ('my_asg_uds', 'output_type', 'CLOB');
END;
/

PROMPT Create the section group
-- Create the section group
BEGIN
CTX_DDL.CREATE_SECTION_GROUP(group_name=>'my_asg',
group_type=>'AUTO_SECTION_GROUP');
END;
/


set timing on
-- Create the Oracle Text Index

CREATE INDEX my_asg_text_nuidx ON my_search_tab (ot_col)
INDEXTYPE IS CTXSYS.CONTEXT
LOCAL
(PARTITION P1 parameters ('storage part_tbs1'),
PARTITION P2 parameters ('storage part_tbs2'),
PARTITION P3 parameters ('storage part_tbs3'),
PARTITION P4 parameters ('storage part_tbs4')
)
PARAMETERS('STORAGE my_default_st DATASTORE my_asg_uds SECTION GROUP my_asg')
PARALLEL;
/

can you please help me out ?

Report message to a moderator

Previous Topic: ordimage problem
Next Topic: Cannot create text index in partition table on Oracle 10g
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 17 19:59:56 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.