Well, I've searched and searched Technet and couldn't find that link. Very helpful. Thanks.

how can i use the index attributes in a domain index clause?

Sorry,

i tried it and got this message

ORA-29850 invalid option for creation of domain indexes

Cause: The user specified an invalid option like ASC, DESC, SORT or a parallel clause, partitioning clause or physical attributes
clause.
Action: Choose one of the valid clauses for creation of domain indexes.

But it was my fault... I used also the NOLOGGING clause.

Parallel CREATE INDEX for interMedia in 8.1.7 requires the underlying table to be partitioned.

Dear Tom,

Thanks for all your help.

My Oracle Vetsion 8.1.7.3

I like to create a PARTITIONED meterialized view(Snapshot) based on a NON-PARTITION table which is located on remote DB(using db link SOURCE_DB_LNK).

I need partitioned MV because I have to use Parallel Domain Indexing (otherwise it is taking 22 Hrs)

CREATE SNAPSHOT mv2
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
TABLESPACE noncrm_data
LOB(Y) STORE AS (TABLESPACE NONCRM_INDEX)
build IMMEDIATE USING INDEX TABLESPACE noncrm_index
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
REFRESH fast
AS SELECT * FROM tab1@SOURCE_DB_LNK;


Questions
1) is it possible. If so kindly provide me an example.

Dear Tom,

I like to use the NOLOGGING workaround for DOMAIN index provided by metalink.
</code> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=135046.1 <code>
We are using one tablespace for all indexes (B-Tree,Domain,etc) and one tablespace for data.

After creating DOMAIN index, is it possible to set the tablespace back to logging?

Dear Tom,
<AskTom>
NOLOGGING does not affect redo generation at all.
</AskTom>


but... the below document says
<Doc>
Creating an index with NOLOGGING has the following benefits:
Space is saved in the redo log files.
</Doc>

</code> http://download-west.oracle.com/docs/cd/B12037_01/server.101/b10739/indexes.htm#sthref2127 <code>

Kindly enlighten me

Hi Tom,
I tried to create a domain index but it throws errors.How to
fix this problem?

SQL> desc mytab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------
 X                                                  CLOB
 
I have data in it in the form of 5 rows. 


SQL> create index myindx on mytab(x) indextype is ctxsys.ctxcat

create index myindx on mytab(x) indextype is ctxsys.ctxcat
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10509: invalid text column: X
ORA-06512: at "CTXSYS.DRUE", line 157
ORA-06512: at "CTXSYS.CATINDEXMETHODS", line 100

Do I have to start any server process?
Please do reply.
Bye!
 

ops$tkyte@ORA9IR2> create table t ( x clob, y varchar2(4000) );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx_x on t(x) indextype is ctxsys.ctxcat;
create index t_idx_x on t(x) indextype is ctxsys.ctxcat
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:<b>
DRG-10509: invalid text column: X</b>
ORA-06512: at "CTXSYS.DRUE", line 157
ORA-06512: at "CTXSYS.CATINDEXMETHODS", line 100
 
 
ops$tkyte@ORA9IR2> create index t_idx_y on t(y) indextype is ctxsys.ctxcat;
 
Index created.
 
ops$tkyte@ORA9IR2>

<quote>
The column you specify when you create a CTXCAT index must be of type CHAR or VARCHAR2. No other types are supported for CTXCAT.
</quote>
 

Tom,
I have heard many times and read some info about intermedia indexes on your site, but really do not know what are they and what they are used for? Can you give me some insights on what exactly is this and why we use it (examples could be helpful) or if you have answered it many times before, can you provide me that link (so you do not have to repeat again?).

Thanks ,