Your repsonse is as fast as speed of light
Thanks much.
Minh

Hello Tom,
a context index inverts columns names.
See:

drop table t;

create table t (
id number(7) not null primary key,
author varchar2(20),
title varchar2(20)
)
tablespace data
/

insert into t (id, author, title) values (1, 'Iglesias', 'Espana');
insert into t (id, author, title) values (2, 'Rebok', 'Spanien');
insert into t (id, author, title) values (3, 'Doe', 'Spain');

exec ctx_ddl.drop_preference('t_datastore');
exec ctx_ddl.create_preference('t_datastore', 'multi_column_datastore');
exec ctx_ddl.set_attribute('t_datastore', 'columns', 'author,title');


drop index t_ctx_idx
/

create index t_ctx_idx on t(author)
indextype is ctxsys.context
parameters('datastore t_datastore')
/

col author format a10
col title format a10
select * from t
where contains(author, 'title', 1) > 0
/
/*
ID AUTHOR TITLE
---------- ---------- ----------
1 Iglesias Espana
2 Rebok Spanien
3 Doe Spain
*/

select * from (select token_text, token_count from "DR$T_CTX_IDX$I" order by token_count desc)
where rownum < 4;
/*
TOKEN_TEXT TOKEN_COUNT
------------ -----------
AUTHOR 3
TITLE 3
DOE 1
*/

Using 10.2.0.1.0 under RedHat.
Yours,
Richard

Helo Tom,

I mean: the column names themselves appear in the token list
with the count equal to the cardinality of the table being inverted.
The "contains" query returns ALL records if the term searched is one of the column names.
Using user_datastore with an appropriate glue function yields
correct results.
Yours,
Richard

Hello Tom, .....

listen, im using a table that has a clob column... over a year i do inserts without empty_clob's intructions, but... even now im having very big performance troubles.

im doing the next:

into a package i have dinamic information and i have a variable called cuerpo and it type is cLOB.

then ... the clob variable (cuerpo) that has inside HTML code... and then, i make the next insert:

INSERT INTO cto_confirmacion
(fol_confirmacion,
cuerpo,
id_email,
folio_factura)
VALUES
(1,
cuerpo,
1,
20);

over a year... this doesnt have any problem... even now... is very very very slow... almost 7 seconds 4 operation before maybe less than a second.

i tried to change:

INSERT INTO Xtable
(fol,
cuerpo,
idX,
folio_X)
VALUES
(1,
empty_clob(),
1,
20);

inmmediatly

UPDATE Xtable
SET cuerpo = cuerpo
WHERE fol = 1

and the primary key is fol....

even then... is very very slow....

i dont know what to do... im very worried.

Thanks 4 anything.

Regards.

Hello again Tom.

Listen, i have an store procedure that have some logic to insert confirmations.

This procedure make an HTML text that is put it into a CLOB variable. the way to write the varible is create first a clob variable in memory like this:


Definition:
vlBody tableX.cuerpo%type := empty_clob();
vlTemporal varchar2(3500);

I create it in memory:
DBMS_LOB.CREATETEMPORARY (vlBody, TRUE, DBMS_LOB.CALL);
DBMS_LOB.OPEN(vlBody, DBMS_LOB.LOB_READWRITE);

and Then i begin to write:
vlTemporal := 'XXXXXXXXXXXXXXXXXXXXXX';
amount := LENGTH(vlTemporal); --number of characters to write
offset := 1; --begin writing to the first character of the CLOB

-- First write
DBMS_LOB.WRITE(vlBody, amount, offset, vlTemporal);

-- Write again (really its html code)
vlTemporal := 'WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW';
DBMS_LOB.WRITEAPPEND(vlBody, LENGTH(vlTemporal), vlTemporal);

Then i write some times again... until close the variable and make my insert:

vlTemporal := 'WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWYYYYYYY';
DBMS_LOB.WRITEAPPEND(vlBody, LENGTH(vlTemporal), vlTemporalBanOrden);

(after, i write like this many times)

-- close variable
DBMS_LOB.CLOSE(vlBody);

insert into tableX(entity, cuerpo, sit_cuerpo) values ('ENTITY1', vlBody, 'AC');

-- Destroy variable.
DBMS_LOB.FREETEMPORARY(vlBody);

Even if something bad happens (exception) i destroy variable in memory.

And i make commit (im not sure that this is necesary but the procedure is executed by a job).

This in a begin was ok... but now its very very very slow... almost 5 or 8 seconds for transaccion in a productive database.

I have the same code in a Developer Database and its very fast even 0.203 seconds.

The size of the file is aprox 8 kb and the size to store clob into table is 6mb.

I make a profiler to my code and the trouble is in the INSERT.... there is the problem...

what more can i do?

Many thanks.

Mariah.