different result :-(
Mirko, April 28, 2003 - 5:51 pm UTC
This was very helpfull, thanks. I have only one problem. The created index doesn't work. With the commands from your example, the last select returns no rows.
This is all what i see:
VARCHAR2_COL CLOB_COL1 CLOB_COL2
-------------------- -------------------- --------------------
What i'm doing wrong?
April 28, 2003 - 9:08 pm UTC
if it returned column headings -- it would return data? sqlplus doesn't print them out with no data found.
Please elaborate (cut and paste maybe the whole thing)....
I was using sqlplus 8174 against 8174 -- what about you.
Forgot the version
Mirko, April 28, 2003 - 5:57 pm UTC
My oracle version is 9.2.0.1.0.
Is this part of the problem?
April 28, 2003 - 9:11 pm UTC
ops$tkyte@ORA920> select varchar2_col, clob_col1, clob_col2
2 from t1, t2, t3
3 where t1.id = t2.id
4 and t1.id = t3.id
5 and contains( varchar2_col, 'this & that & thing' ) > 0;
VARCHAR2_COL CLOB_COL1 CLOB_COL2
-------------------- -------------------- --------------------
This That and the other thing
ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production
really no rows
Mirko, April 29, 2003 - 3:27 am UTC
This is my output from PL/SQL Developer:
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as obr
SQL>
SQL> drop table t1;
Table dropped
SQL> drop table t2;
Table dropped
SQL> drop table t3;
Table dropped
SQL> create table t1 ( id int primary key, varchar2_col varchar2(80) );
Table created
SQL> create table t2 ( id int primary key, clob_col1 clob );
Table created
SQL> create table t3 ( id int primary key, clob_col2 clob );
Table created
SQL> insert into t1 values ( 1, 'This' );
1 row inserted
SQL> insert into t2 values ( 1, 'That' );
1 row inserted
SQL> insert into t3 values ( 1, 'and the other thing' );
1 row inserted
SQL> commit;
Commit complete
SQL> grant select on t1 to ctxsys;
Grant succeeded
SQL> grant select on t2 to ctxsys;
Grant succeeded
SQL> grant select on t3 to ctxsys;
Grant succeeded
SQL> pause
SQL> connect ctxsys/ctxsys
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as ctxsys
SQL> create or replace procedure glue_them_together( p_id in rowid, p_lob in out clob )
2 as
3 begin
4 for x in ( select varchar2_col, clob_col1, clob_col2
5 from obr.t1, obr.t2, obr.t3
6
7 where t1.rowid = p_id
8 and t2.id(+) = t1.id
9 and t3.id(+) = t1.id )
10 loop
11 dbms_lob.writeAppend( p_lob, length(x.varchar2_col)+1, x.varchar2_col||' ');
12 dbms_lob.append( p_lob, x.clob_col1 );
13 dbms_lob.writeAppend( p_lob, 1, ' ' );
14 dbms_lob.append( p_lob, x.clob_col2 );
15 end loop;
16 end;
17 /
Procedure created
SQL> grant execute on glue_them_together to obr;
Grant succeeded
SQL> pause
SQL> connect obr/obr
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as obr
SQL> set termout off
SQL> REM GET afiedt.buf NOLIST
SQL> set termout on
SQL> exec ctx_ddl.drop_preference('foo_user_datastore');
PL/SQL procedure successfully completed
SQL> exec ctx_ddl.drop_preference('my_lexer');
PL/SQL procedure successfully completed
SQL> begin
2 ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
3 ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'glue_them_together' );
4 end;
5 /
PL/SQL procedure successfully completed
SQL> begin
2 ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
3 ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
4 ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
5 end;
6 /
PL/SQL procedure successfully completed
SQL> create index t1_idx on t1(varchar2_col)
2 indextype is ctxsys.context
3 parameters( 'datastore foo_user_datastore lexer my_lexer');
Index created
SQL> set define off
SQL> column varchar2_col format a20
SQL> column clob_col1 format a20
SQL> column clob_col2 format a20
SQL> select varchar2_col, clob_col1, clob_col2
2 from t1, t2, t3
3 where t1.id = t2.id
4 and t1.id = t3.id
5 and contains( varchar2_col, 'this & that & thing' ) > 0;
VARCHAR2_COL CLOB_COL1 CLOB_COL2
-------------------- -------------------- --------------------
SQL>
And this is the script (for copy & paste):
drop table t1;
drop table t2;
drop table t3;
create table t1 ( id int primary key, varchar2_col varchar2(80) );
create table t2 ( id int primary key, clob_col1 clob );
create table t3 ( id int primary key, clob_col2 clob );
insert into t1 values ( 1, 'This' );
insert into t2 values ( 1, 'That' );
insert into t3 values ( 1, 'and the other thing' );
commit;
grant select on t1 to ctxsys;
grant select on t2 to ctxsys;
grant select on t3 to ctxsys;
pause
connect ctxsys/ctxsys
create or replace procedure glue_them_together( p_id in rowid, p_lob in out clob )
as
begin
for x in ( select varchar2_col, clob_col1, clob_col2
from obr.t1, obr.t2, obr.t3
where t1.rowid = p_id
and t2.id(+) = t1.id
and t3.id(+) = t1.id )
loop
dbms_lob.writeAppend( p_lob, length(x.varchar2_col)+1, x.varchar2_col||' ');
dbms_lob.append( p_lob, x.clob_col1 );
dbms_lob.writeAppend( p_lob, 1, ' ' );
dbms_lob.append( p_lob, x.clob_col2 );
end loop;
end;
/
grant execute on glue_them_together to obr;
pause
connect obr/obr
set termout off
REM GET afiedt.buf NOLIST
set termout on
exec ctx_ddl.drop_preference('foo_user_datastore');
exec ctx_ddl.drop_preference('my_lexer');
begin
ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'glue_them_together' );
end;
/
begin
ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
end;
/
create index t1_idx on t1(varchar2_col)
indextype is ctxsys.context
parameters( 'datastore foo_user_datastore lexer my_lexer');
set define off
column varchar2_col format a20
column clob_col1 format a20
column clob_col2 format a20
select varchar2_col, clob_col1, clob_col2
from t1, t2, t3
where t1.id = t2.id
and t1.id = t3.id
and contains( varchar2_col, 'this & that & thing' ) > 0;
April 29, 2003 - 8:22 am UTC
what version of sqlplus -- if you remove the "contains" (just select all data) what then.
It looks like it is "working" -- the headings wouldn't print otherwise. Don't know why it isn't showing the data. But if you get "nothing" without contains -- we know it is "working"
perhaps you can put it in a plsql block and dbms_output it - just to see the data.
TKPROF it as well.
or use autotrace -- see the "rows processed". then we can fix sqlplus....
Solved!
Mirko, April 29, 2003 - 6:08 am UTC
Case sensitive search condition!
Following query works:
select varchar2_col, clob_col1, clob_col2
from t1, t2, t3
where t1.id = t2.id
and t1.id = t3.id
and contains( varchar2_col, 'This & That & thing' ) > 0;
Good solution.... but I run out of cursors
Tom Best, July 08, 2003 - 3:04 pm UTC
Thanks for this answer, Tom. I did this, but intermedia is leaving a cursor open for each call to the "glue" proc. I don't see any bug reports about this in metalink, either.
I get this:
SQL> create index resitem_text on siebel.s_resitem(name)
2 indextype is ctxsys.context
3 parameters( 'datastore foo_user_datastore lexer my_lexer')
4 /
create index resitem_text on siebel.s_resitem(name)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: interMedia Text error:
ORA-01000: maximum open cursors exceeded
DRG-50857: oracle error in drueixe
oracle error in drueixe
ORA-06512: at "CTXSYS.DRUE", line 126
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 78
ORA-06512: at line 1
And, sure enough, v$open_cursor contains hundreds of rows with this SQL_TEXT in it:
begin ctxsys."GLUE_THEM_TOGETHER"(:rid, :lob); end;
BTW - I creating the index with a user that does not own the table being indexed.
At first I coded it exactly as you had it, and got this result, and then I coded it with an explicit cursor, but still get the error. Here is my code as of now:
PROCEDURE GLUE_THEM_TOGETHER( p_id in rowid, p_lob in out clob )
as
cursor x is
select name, desc_text, faq_ques_text, resolution_text, x_problem_text
from siebel.s_resitem sr
where sr.rowid = p_id;
l_name siebel.s_resitem.name%type;
l_desc_text siebel.s_resitem.desc_text%type;
l_faq_ques_text siebel.s_resitem.faq_ques_text%type;
l_resolution_text siebel.s_resitem.resolution_text%type;
l_x_problem_text siebel.s_resitem.x_problem_text%type;
begin
/*
for x in ( select name, desc_text, faq_ques_text, resolution_text, x_problem_text
from siebel.s_resitem sr
where sr.rowid = p_id)
*/
open x;
loop
fetch x into l_name, l_desc_text, l_faq_ques_text, l_resolution_text, l_x_problem_text;
dbms_lob.writeAppend( p_lob, length(l_name)+1, l_name||' ');
dbms_lob.writeAppend( p_lob, length(l_desc_text)+1, l_desc_text||' ');
dbms_lob.writeAppend( p_lob, length(l_faq_ques_text)+1, l_faq_ques_text||' ');
dbms_lob.writeAppend( p_lob, length(l_resolution_text)+1, l_resolution_text||' ');
dbms_lob.writeAppend( p_lob, length(l_x_problem_text)+1, l_x_problem_text);
end loop;
close x;
end;
July 08, 2003 - 3:52 pm UTC
what version?
i've used this technique on hundreds of thousands/millions of rows.
Version is 8.1.7.2.0
Tom Best, July 08, 2003 - 4:05 pm UTC
Version is 8.1.7.2.0
July 08, 2003 - 4:20 pm UTC
how many rows in the table being indexed.
# of rows
Tom Best, July 08, 2003 - 5:07 pm UTC
select count(*) from siebel.s_resitem
Query finished, retrieving results...
COUNT(*)
---------------------------------------
26201
desc siebel.s_resitem
Describing siebel.s_resitem....
NAME Null? Type
------------------------------- --------- -----
ROW_ID NOT NULL VARCHAR2(15)
CREATED NOT NULL DATE
CREATED_BY NOT NULL VARCHAR2(15)
LAST_UPD NOT NULL DATE
LAST_UPD_BY NOT NULL VARCHAR2(15)
MODIFICATION_NUM NOT NULL NUMBER(10,0)
CONFLICT_ID NOT NULL VARCHAR2(15)
NAME NOT NULL VARCHAR2(100)
SOLUTION_ITEM_FLG NOT NULL CHAR(1)
FILE_AUTO_UPD_FLG CHAR(1)
FILE_DEFER_FLG CHAR(1)
FILE_DOCK_REQ_FLG CHAR(1)
FILE_DOCK_STAT_FLG CHAR(1)
FILE_NAME VARCHAR2(200)
DESC_TEXT VARCHAR2(255)
FAQ_QUES_TEXT VARCHAR2(250)
FILE_DATE DATE
FILE_EXT VARCHAR2(10)
FILE_REV_NUM VARCHAR2(15)
FILE_SIZE NUMBER(22,7)
FILE_SRC_PATH VARCHAR2(255)
FILE_SRC_TYPE VARCHAR2(30)
INTR_PUBLISH_FLG CHAR(1)
ORIG_FILE_NAME VARCHAR2(220)
PR_AUTHOR_ID VARCHAR2(15)
PUBLISH_FLG CHAR(1)
RESITEM_TYPE VARCHAR2(30)
RESOLUTION_TEXT VARCHAR2(2000)
SAVED_AS_FILE_NAME VARCHAR2(220)
STATUS_CD VARCHAR2(30)
X_AREA VARCHAR2(50)
X_CHANGE_FLG CHAR(1)
X_LANG_ID VARCHAR2(15)
X_PAR_SOLUTION_ID VARCHAR2(15)
X_PLATFORM_CD VARCHAR2(30)
X_PROBLEM_TEXT VARCHAR2(2000)
X_PROD_DEFECT_ID VARCHAR2(15)
X_PROD_LINE_ID VARCHAR2(15)
X_VERSION VARCHAR2(50)
X_CONF_FLAG CHAR(1)
*** SCRIPT END : Session:CTXSYS
July 08, 2003 - 6:21 pm UTC
well, i've done tables much much larger then that, I've not hit this personally. I neither can find any bugs logged against this at all.
what is your open cursors set to currently?
is your data in need of a lob, we could try a varchar2 if it is under 32k and see if that makes any difference (just use a varchar2 in out instead of a lob)
A reader, July 09, 2003 - 8:36 am UTC
Hi Tom,
This Question is to TOM BEST (Siebel guy).
Why you are using intermedia in siebel application?
There is thing called Fulcrum search available in Siebel.
The reason for this question is we are using siebel call center and I am also looking any possibility to use Intermedia in siebel
Thanks
My Stupid
Tom Best, July 09, 2003 - 2:41 pm UTC
I had a bug. The last call to dbms_lob.writeAppend had the length + 1, but does not need the appended space (' '). And, indeed the doc for this function says:
"There is an error if the input amount is more than the data in the buffer."
I would not have expected this particular error, but that's good to know now.
Sorry to waste your time, Tom.
To the guy with the Siebel question... I believe predecesors of mine investigated Fulcrum and declined to use it. When using Oracle text, we just have to make sure we rebuild these indexes after an SRF release, since Siebel doesn't know about it.
A reader, July 14, 2003 - 1:29 pm UTC
Is it true that a table, using context indexes with USER_DATASTORE, that whenever new entries are added to a table the index needs to be re-created.
Can this be a performance hit?
July 15, 2003 - 12:58 am UTC
no, the index does not need to be recreated, it needs to be maintained (added to) just like any index would
David Pujol, October 13, 2003 - 1:29 pm UTC
Complete Answers
MSU, September 02, 2004 - 1:53 am UTC
Tom,
When I run a query I get the following error
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-10837: section AccountingSegment@id does not exist
One of the the table which I query contains an XML as a column and "AccountingSegment" is one of the tag in the XML. I am unable to interpret the error message. Can you help me what error is it actually.
September 02, 2004 - 8:08 am UTC
give me a full example to work with please.
[tkyte@xtkyte-pc tkyte]$ oerr drg 10837
10837,0, "section %(1)s does not exist"
// *Cause: The given section name does not exist in USER_SECTIONS
// *Action: Rewrite query with valid section name
// *Mnemonic: QE_INV_SECTION
//
it is basically saying the section you tried to use is not an indexed section. so, give us an example that creates the table, inserts a row, creates the index with this section and queries it.
Complete Answers
MSU, September 03, 2004 - 9:51 am UTC
Hi Tom,
This has reference to what you had asked for in your prior followup.
The application is a Typical Java ORACLE webbased application. When an order is placed through the web all the details regarding the order are made into an XML script and it is stored in the ORDER_XML column of the ORDER_FORMATS table. The script for the same is given below.
CREATE TABLE ORDER_FORMATS
(
ORDER_FORMAT_ID NUMBER(18) NOT NULL,
ORDER_ID NUMBER(18) NOT NULL,
ORDER_TYPE_ID NUMBER(18) NOT NULL,
ORDER_XML SYS.XMLTYPE NOT NULL,
STATUS_ID NUMBER(18) NOT NULL,
CREATED_BY_ID NUMBER(18) NOT NULL,
CREATED_DATE DATE NOT NULL,
UPDATED_BY_ID NUMBER(18),
UPDATED_DATE DATE
)
LOGGING
NOCACHE
NOPARALLEL
/
CREATE INDEX IDX_ORDERFORMATS_ORDERXML ON ORDER_FORMATS
(SYS_MAKEXML("SYS_NC00005$"))
LOGGING
NOPARALLEL
/
CREATE INDEX IDX_ORDERFORMATS_ORDTYPEORDID ON ORDER_FORMATS
(ORDER_TYPE_ID, ORDER_ID)
LOGGING
NOPARALLEL
/
CREATE INDEX IDX_ORDFORMATS_STATTYPEORDID ON ORDER_FORMATS
(STATUS_ID, ORDER_TYPE_ID, ORDER_ID)
LOGGING
NOPARALLEL
/
A typical entry in the ORDER_FORMATS table will be as given below.
SQL> select * from order_formats where order_id = 2055138;
ORDER_FORMAT_ID --> 61510
ORDER_ID --> 2055138
ORDER_TYPE_ID --> 2
STATUS_ID --> 1
CREATED_BY_ID --> 40164019
CREATED_DATE --> 03-SEP-04
UPDATED_BY_ID --> 40164019
UPDATED_D --> 03-SEP-04
ORDER_XML
<cXML timestamp="2004-09-03T10:24::03">
<Request>
<OrderRequest>
<OrderRequestHeader orderDate="20040903T10:23:03">
<Total>
<Money currency="USD">0.55</Money>
</Total>
<ShipTo>
<Address addressID="40249628">
<Name xml:lang="en">Demoville Elementary School</Name>
<PostalAddress name="Demoville Elementary School">
<Street>500 Demo Lane</Street>
<City>Appleton</City>
<State>WI</State>
<PostalCode>99999</PostalCode>
<Country isoCountryCode="USA">null</Country>
<Components>
<AddressID>40249628</AddressID>
<LocalExempt>false</LocalExempt>
<FederalExempt>false</FederalExempt>
</Components>
</PostalAddress>
<Email name="Demoville Elementary School">marketplace-dev@junebox.com</Email>
</Address>
</ShipTo>
<BillTo>
<Address addressID="40249624">
<Name xml:lang="en">Demoville School District</Name>
<PostalAddress name="Demoville School District">
<Street>500 Demo Lane</Street>
<City>Appleton</City>
<State>WI</State>
<PostalCode>99999</PostalCode>
<Country isoCountryCode="USA">null</Country>
<Components>
<AddressID>40249624</AddressID>
<LocalExempt>false</LocalExempt>
<FederalExempt>false</FederalExempt>
</Components>
</PostalAddress>
<Email name="Demoville School District">marketplace-dev@junebox.com</Email>
</Address>
</BillTo>
</OrderRequestHeader>
<ItemOut quantity="1" lineNumber="1">
<ItemID>
<SupplierPartID>30000072</SupplierPartID>
<SupplierPartAuxiliaryID>Catalog|SS04|054054|40181493|4|000072|40554160|2055138</SupplierPartAuxiliaryID>
</ItemID>
<ItemDetail>
<UnitPrice>
<Money currency="USD">0.55</Money>
</UnitPrice>
<Description xml:lang="en">59972 JUMBO SMOOTH PAPER CLIP BX/100 CLASSROOM SELECT (301E)</Description>
<UnitOfMeasure>BX</UnitOfMeasure>
<Classification domain="UNSPSC">4412210400</Classification>
<ManufacturerPartID>59972</ManufacturerPartID>
<ManufacturerName>CHARLES LEONARD INC</ManufacturerName>
<Components>
<Hazardous>false</Hazardous>
<ManufacturerID>40019843</ManufacturerID>
<Direct>true</Direct>
<SSIVendorID>036858</SSIVendorID>
<Ups>true</Ups>
</Components>
</ItemDetail>
<Shipping>
<Description xml:lang="en">Freight Charges</Description>
<Money currency="USD">5.95</Money>
<Components>
<CMFShipping>
<CMFFreight>
<FreightRateID>1062</FreightRateID>
<FreightTypeID>2</FreightTypeID>
<FreightChargeType/>
<Description xml:lang="en">Freight Charges</Description>
<Money currency="USD">5.95</Money>
<Date/>
<Status/>
</CMFFreight>
</CMFShipping>
</Components>
</Shipping>
<Distribution>
<Accounting>
<AccountingSegment id="10 e 111 5900 4250">
<Name>10 e 111 5900 4250</Name>
<Description>null</Description>
</AccountingSegment>
</Accounting>
</Distribution>
<Components>
<ListPrice>0.55</ListPrice>
<ExtendedPriceFloat>0.55</ExtendedPriceFloat>
<PricingMethod>List</PricingMethod>
<CatalogPricingOnly>N</CatalogPricingOnly>
<CanadaTaxCode>1051</CanadaTaxCode>
</Components>
</ItemOut>
</OrderRequest>
<Components>
<PunchOutUser>false</PunchOutUser>
<PersonalCreditCard>false</PersonalCreditCard>
<UidLong>40164019</UidLong>
<YantraOrderNumber>S2055138</YantraOrderNumber>
<OrderStatusID>800</OrderStatusID>
<OrderID>2055138</OrderID>
<storeID>4</storeID>
<ExtendedGrandTotalFloat>6.5</ExtendedGrandTotalFloat>
<IdentID>40124582</IdentID>
<organizationID>40022967</organizationID>
<FederalTaxExempt>false</FederalTaxExempt>
<ExtendedPriceFloat>0.55</ExtendedPriceFloat>
</Components>
</Request>
</cXML>
SELECT
o.order_id,
o.order_number,
o.identity_id,
o.organization_id,
o.order_status_id,
o.created_date,
o.store_id,
o.yantra_order_header_key,
u.user_name,
u.first_name,
u.last_name
FROM
order_formats formats,
orders o,
identities i,
users u
WHERE
formats.order_id = o.order_id
AND
formats.order_xml.extract('/cXML/Request/OrderRequest/OrderRequestHead
er/ShipTo/Address/PostalAddress/DeliverTo[1]/text()').getStringVal()
LIKE '%attention%'
AND
UPPER(formats.order_xml.extract('/cXML/Request/OrderRequest/OrderReque
stHeader@orderID').getStringVal()) = 'PO'
AND (contains(formats.order_xml, '%'||'budget'||'% within
AccountingSegment@id') > 0)
AND
formats.order_xml.extract('/cXML/Request/OrderRequest/OrderRequestHead
er/ShipTo/Address@addressID').getStringVal() = '40249624'
AND o.identity_id = i.identity_id
AND u.user_id = i.user_id
ORDER BY o.order_number DESC
I get the following error message
*
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-10837: section AccountingSegment@id does not exist.
Do let me know what best can be done
September 03, 2004 - 1:05 pm UTC
"The application is a Typical Java ORACLE webbased application"
sent chills down my spine already.
So, you have some of the most STRUCTURED DATA ON THE PLANET -- an Order Entry system -- and you globbed it into XML. man. man o man o man o man.
why would you take perfectly structured, awesomely relational, perfect data and XML it?
how about create's that can be run -- and a simple, small, just as big as it needs to be example -- with an insert into statement and all.
You can post it in the fashion I post my examples -- starting with the create table, the single --- small--- insert, and the SINGLE create index (that works) that is needed, followed by the shortest query possible.
Multiple Columns
John French, September 23, 2004 - 2:34 pm UTC
I have a table with multiple columns that I want to create a context search on. What is the best context indexing to use.
I would like search across version, tmNotes, tmActionsBefore and tmActionsAfter columns in the table below.
CREATE TABLE ReleaseNote (
id int PRIMARY KEY,
version varchar(15),
tmNotes varchar2(4000),
tmActionsBefore varchar2(4000),
tmActionsAfter varchar2(4000),
appSize varchar(15),
downloadSize varchar(15),
upgradeSize varchar(15),
reboot number(1),
rebootReason varchar(200),
dependencies varchar(200),
applicationId int,
releaseId int
);
September 24, 2004 - 9:25 am UTC
you would probably use a multicolumn data store for something so simple.
Sort on Clob Columns
Lamya, November 03, 2004 - 3:58 pm UTC
Tom ,
I have a table
create table t
( id number,
summary clob ) ;
insert into t values ( 1, 'blan blab Gene...');
insert into t values ( 2, 'Gene...blah');
insert into t values ( 3, 'blah blah blah');
What I require is to do a search / sort on the clob column
without using dbms_lob functionality or Oracle Text . I told my manager its not possible but I want to make sure that is the case
Thanks a lot
Lamya
Not getting the expected results..
Mohini, September 01, 2005 - 12:33 pm UTC
Tom,
I ran the test case almost like yours..except instead of clobs, I used varchar2.
Questions: (Please refer to the results in the script run)
1. If the columns are all varchar2 then can index be created on any column?
2. Does the column size matter?
3. Can we just create a dummy varchar2 column in one of the tables and create
index on it?
4. How does the index get updated?
Here is what I am trying to accomplish:
A seven digit number is passed in as a Key word.
The application needs to return two recordsets:
Look for an exact match in one of the columns and return those recordsets.
Then do a full like (%%) "or" search on few columns (of different tables)
and return that recordset excluding any records from the first recordset.
1. Does a full like search work with this procedure (please refer to the test run)
2. The mixed case attribute didn't work for me (please refer to the test run)
/*********Script*************/
--log in as any user
--create tables
drop table t1;
drop table t2;
drop table t3;
drop table t4;
create table t1 ( id int primary key, varchar2_col_t1
varchar2(80) );
create table t2 ( id int primary key, varchar2_col_t2
varchar2(80));
create table t3 ( id int primary key, varchar2_col_t3
varchar2(80));
--create a dummy table for glued index
--create table t4 ( dummycol varchar2(1) );
--insert data
insert into t1 values ( 1, 'This' );
insert into t2 values ( 1, 'That' );
insert into t3 values ( 1, 'and the other thing' );
insert into t1 values ( 2, 'ThisTHATotherwhAT' );
commit;
--grant rights to ctxsys
grant select on t1 to ctxsys;
grant select on t2 to ctxsys;
grant select on t3 to ctxsys;
--log in as ctxsys user
--procedure has to be owned by ctxsys
create or replace procedure glue_them_together( p_id in rowid,
p_lob in out clob )
as
begin
for x in ( select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
from textuser.t1, textuser.t2,
textuser.t3
where t1.rowid = p_id
and t2.id(+) = t1.id
and t3.id(+) = t1.id )
loop
dbms_lob.writeAppend( p_lob, length(x.varchar2_col_t1)+1, x.varchar2_col_t1||' ');
dbms_lob.writeAppend( p_lob, length(x.varchar2_col_t2)+1, x.varchar2_col_t2||' ');
dbms_lob.writeAppend( p_lob, length(x.varchar2_col_t3)+1, x.varchar2_col_t3||' ');
end loop;
end;
/
grant execute on glue_them_together to textuser;
--create preferences
exec ctx_ddl.drop_preference('foo_user_datastore');
exec ctx_ddl.drop_preference('my_lexer');
begin
ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'glue_them_together' );
end;
/
begin
ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
end;
/
--any user
create index t1_idx on t1(varchar2_col_t1)
indextype is ctxsys.context
parameters( 'datastore ctxsys.foo_user_datastore lexer ctxsys.my_lexer');
set define off;
column varchar2_col_t1 format a20
column varchar2_col_t2 format a20
column varchar2_col_t3 format a20
--all records
select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
from t1, t2, t3
where t1.id = t2.id (+)
and t1.id = t3.id (+);
--generic case
select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
from t1, t2, t3
where t1.id = t2.id (+)
and t1.id = t3.id (+)
and contains( varchar2_col_t1, 'This' ) > 0;
--mixed case
select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
from t1, t2, t3
where t1.id = t2.id (+)
and t1.id = t3.id (+)
and contains( varchar2_col_t1, 'this' ) > 0;
--%% (full search)
select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
from t1, t2, t3
where t1.id = t2.id (+)
and t1.id = t3.id (+)
and contains( varchar2_col_t1, 'what' ) > 0;
/*********Script Run*************/
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
P:\>sqlplus textuser@ora9i
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Sep 1 09:34:18 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
9.2.0.6.0(dev) textuser@ora9i> --log in as any user
9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --create tables
9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> drop table t1;
Table dropped.
9.2.0.6.0(dev) textuser@ora9i> drop table t2;
Table dropped.
9.2.0.6.0(dev) textuser@ora9i> drop table t3;
Table dropped.
9.2.0.6.0(dev) textuser@ora9i> drop table t4;
drop table t4
*
ERROR at line 1:
ORA-00942: table or view does not exist
9.2.0.6.0(dev) textuser@ora9i> create table t1 ( id int primary key, varchar2_col_t1
2 varchar2(80) );
Table created.
9.2.0.6.0(dev) textuser@ora9i> create table t2 ( id int primary key, varchar2_col_t2
2 varchar2(80));
Table created.
9.2.0.6.0(dev) textuser@ora9i> create table t3 ( id int primary key, varchar2_col_t3
2 varchar2(80));
Table created.
9.2.0.6.0(dev) textuser@ora9i> --create a dummy table for glued index
9.2.0.6.0(dev) textuser@ora9i> --create table t4 ( dummycol varchar2(1) );
9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --insert data
9.2.0.6.0(dev) textuser@ora9i> insert into t1 values ( 1, 'This' );
1 row created.
9.2.0.6.0(dev) textuser@ora9i> insert into t2 values ( 1, 'That' );
1 row created.
9.2.0.6.0(dev) textuser@ora9i> insert into t3 values ( 1, 'and the other thing' );
1 row created.
9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> insert into t1 values ( 2, 'ThisTHATotherwhAT' );
1 row created.
9.2.0.6.0(dev) textuser@ora9i> commit;
Commit complete.
9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --grant rights to ctxsys
9.2.0.6.0(dev) textuser@ora9i> grant select on t1 to ctxsys;
Grant succeeded.
9.2.0.6.0(dev) textuser@ora9i> grant select on t2 to ctxsys;
Grant succeeded.
9.2.0.6.0(dev) textuser@ora9i> grant select on t3 to ctxsys;
Grant succeeded.
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
P:\>sqlplus ctxsys@ora9i
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Sep 1 09:34:54 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
9.2.0.6.0(dev) ctxsys@ora9i> --log in as ctxsys user
9.2.0.6.0(dev) ctxsys@ora9i> --procedure has to be owned by ctxsys
9.2.0.6.0(dev) ctxsys@ora9i> create or replace procedure glue_them_together( p_id in rowid,
2 p_lob in out clob )
3 as
4 begin
5 for x in ( select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
6 from textuser.t1, textuser.t2,
7 textuser.t3
8 where t1.rowid = p_id
9 and t2.id(+) = t1.id
10 and t3.id(+) = t1.id )
11 loop
12 dbms_lob.writeAppend( p_lob, length(x.varchar2_col_t1)+1, x.varchar2_col_t1||' ');
13 dbms_lob.writeAppend( p_lob, length(x.varchar2_col_t2)+1, x.varchar2_col_t2||' ');
14 dbms_lob.writeAppend( p_lob, length(x.varchar2_col_t3)+1, x.varchar2_col_t3||' ');
15 end loop;
16
17 end;
18 /
Procedure created.
9.2.0.6.0(dev) ctxsys@ora9i> grant execute on glue_them_together to textuser;
Grant succeeded.
9.2.0.6.0(dev) ctxsys@ora9i>
9.2.0.6.0(dev) ctxsys@ora9i>
9.2.0.6.0(dev) ctxsys@ora9i>
9.2.0.6.0(dev) ctxsys@ora9i> --create preferences
9.2.0.6.0(dev) ctxsys@ora9i>
9.2.0.6.0(dev) ctxsys@ora9i> exec ctx_ddl.drop_preference('foo_user_datastore');
PL/SQL procedure successfully completed.
9.2.0.6.0(dev) ctxsys@ora9i> exec ctx_ddl.drop_preference('my_lexer');
PL/SQL procedure successfully completed.
9.2.0.6.0(dev) ctxsys@ora9i>
9.2.0.6.0(dev) ctxsys@ora9i> begin
2 ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
3 ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'glue_them_together' );
4 end;
5 /
PL/SQL procedure successfully completed.
9.2.0.6.0(dev) ctxsys@ora9i>
9.2.0.6.0(dev) ctxsys@ora9i> begin
2 ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
3 ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
4 ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
5 end;
6 /
PL/SQL procedure successfully completed.
9.2.0.6.0(dev) ctxsys@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --any user
9.2.0.6.0(dev) textuser@ora9i> create index t1_idx on t1(varchar2_col_t1)
2 indextype is ctxsys.context
3 parameters( 'datastore ctxsys.foo_user_datastore lexer ctxsys.my_lexer');
Index created.
9.2.0.6.0(dev) textuser@ora9i> set define off;
9.2.0.6.0(dev) textuser@ora9i> column varchar2_col_t1 format a20
9.2.0.6.0(dev) textuser@ora9i> column varchar2_col_t2 format a20
9.2.0.6.0(dev) textuser@ora9i> column varchar2_col_t3 format a20
9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --all records
9.2.0.6.0(dev) textuser@ora9i> select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
2 from t1, t2, t3
3 where t1.id = t2.id (+)
4 and t1.id = t3.id (+);
VARCHAR2_COL_T1 VARCHAR2_COL_T2 VARCHAR2_COL_T3
-------------------- -------------------- --------------------
This That and the other thing
ThisTHATotherwhAT
9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --generic case
9.2.0.6.0(dev) textuser@ora9i> select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
2 from t1, t2, t3
3 where t1.id = t2.id (+)
4 and t1.id = t3.id (+)
5 and contains( varchar2_col_t1, 'This' ) > 0;
VARCHAR2_COL_T1 VARCHAR2_COL_T2 VARCHAR2_COL_T3
-------------------- -------------------- --------------------
This That and the other thing
9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --mixed case
9.2.0.6.0(dev) textuser@ora9i> select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
2 from t1, t2, t3
3 where t1.id = t2.id (+)
4 and t1.id = t3.id (+)
5 and contains( varchar2_col_t1, 'this' ) > 0;
no rows selected
9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --%% (full search)
9.2.0.6.0(dev) textuser@ora9i> select varchar2_col_t1, varchar2_col_t2, varchar2_col_t3
2 from t1, t2, t3
3 where t1.id = t2.id (+)
4 and t1.id = t3.id (+)
5 and contains( varchar2_col_t1, 'what' ) > 0;
no rows selected
September 01, 2005 - 3:59 pm UTC
this is big, can you just point out a simple example where you thing "something is wrong".
1. If the columns are all varchar2 then can index be created on any column?
index can be created on any column that text supports.
2. Does the column size matter?
nope
3. Can we just create a dummy varchar2 column in one of the tables and create
index on it?
yep.
4. How does the index get updated?
you sync the index in 9i. I have a job to do so.
Mixed case and full like
mohini, September 01, 2005 - 10:07 pm UTC
towards the end of my "tiny" script...
I ran two sqls..where I didn't get any results for mixed case or a full like..
thanks..
9.2.0.6.0(dev) textuser@ora9i> --mixed case
9.2.0.6.0(dev) textuser@ora9i> select varchar2_col_t1, varchar2_col_t2,
varchar2_col_t3
2 from t1, t2, t3
3 where t1.id = t2.id (+)
4 and t1.id = t3.id (+)
5 and contains( varchar2_col_t1, 'this' ) > 0;
no rows selected
9.2.0.6.0(dev) textuser@ora9i>
9.2.0.6.0(dev) textuser@ora9i> --%% (full search)
9.2.0.6.0(dev) textuser@ora9i> select varchar2_col_t1, varchar2_col_t2,
varchar2_col_t3
2 from t1, t2, t3
3 where t1.id = t2.id (+)
4 and t1.id = t3.id (+)
5 and contains( varchar2_col_t1, 'what' ) > 0;
no rows selected
September 02, 2005 - 1:23 am UTC
Mixed case....
Mohini, September 06, 2005 - 1:36 pm UTC
9i (Release 2)
Couple of issues:
1. I am not getting the mixed case to fly for some reason...
2. Index rebuild (exec ctx_ddl.sync_index('T1_IDX');) is taking 5 to 7 minutes?
--Thanks.
September 06, 2005 - 8:52 pm UTC
examples please.
(sure a rebuild of something big could take 5 to 7 minutes, why not? no details to work with here at all)
index rebuild
mohini, September 06, 2005 - 3:49 pm UTC
O.k. the mixed case issue has been resolved..I had set mixed_case attribute to YES...I changed it to 'NO'..and it is all working.
But the re-synching of the index is still taking a while..and there are only 2-3 rows in the tables (from your example above)
Thanks
September 06, 2005 - 9:07 pm UTC
my example doesn't take minutes on my system, are you doing it exactly the same?
index sync and memory
Mohini, September 06, 2005 - 4:53 pm UTC
Docs..Docs..Docs..got my answer..
Synchronizing the Index
Synchronizing the index involves processing all pending updates, inserts, and deletes to the base table. You can do this in PL/SQL with the CTX_DDL.SYNC_INDEX procedure.
The following example synchronizes the index with 2 megabytes of memory:
begin
ctx_ddl.sync_index('myindex', '2M');
end;
Excluding the exact match..
mohini, September 08, 2005 - 7:10 pm UTC
9i (Release 2)
Tom,
Here is a scenerio:
user enters a keword "john"..
database will search along various employee related fields like employees lastname, firstname, title, location etc. and
two recordsets will be returned:
First one needs to be an exact match..like where firstname or lastname...is john
Second recordset needs to have all the partial matches (excluding the exact matches records from the first resultset)..
So, I thought I can do the whole set up in your example above for the first recordset...
and for the second recordset, I will add a wordlist preference..and pass the keyword as %john% to the contains clause (wordlist preference would always be there..it will get utilized in the second resultset)...
Now, I am trying to see what is the best way to exclude the records...will it be something like this:
select empno, lastname, firstname, dname
from emp e, dept d
where e.deptno = d.deptno
and (contains e.emplid, '%john%') > 0
and empno not in
(
select empno
from emp e, dept d
where e.deptno = d.deptno
and (contains e.emplid, 'john') > 0
);
Is there a better way to accomplish this?
Thanks
September 08, 2005 - 7:36 pm UTC
why not just get them all at once?
ops$tkyte@ORA10G> create table emp as select * from scott.emp;
Table created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> update emp set ename = 'JFORDX' where ename = 'MILLER';
1 row updated.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create index emp_idx on emp(ename) indextype is ctxsys.context;
Index created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select empno, ename, contains(ename,'ford')
2 from emp
3 where contains(ename,'%ford%') > 0;
EMPNO ENAME CONTAINS(ENAME,'FORD')
---------- ---------- ----------------------
7902 FORD 6
7934 JFORDX 0
contains() <> 0 -> exact match.
= 0 -> partial match.
skipjoins
mohini, September 15, 2005 - 7:08 pm UTC
9i release 2
Tom,
We have the following requirements:
1. keyword search oconner should return both o'conner and oconner
I am setting the skipjoins attribute of the basic_lexer to accomplish this...works fine..
Is it the correct way to accomplish this?
can skipjoins take comma delimited character list if we want to add more characters that we need to skip?
2. Some users have asked if we can add a capability..so that
even if something is misspelled..results are found...by correcting it? I have no idea how to begin addressing this?
should we add a spell check first and then pass the keyword..to oracle text?
3. Can Soundex capabilities be added to the search?
Thanks.
September 15, 2005 - 8:36 pm UTC
1) yes on the o'conner
probably not on the "list" - it just removes the character from the string, not what you wanted.
You would take the comma delimited list and "or" them in the text query.
2) thesaurus if you have a "list of miss-spellings", otherwise, I'd recommend what google does (you get a spell checker piece of software and ask it for spellings and let them figure it out)
for example, google:
theasauras
3) sure, text supports soundex. and fuzzy..
multiple skip characters..
mohini, September 16, 2005 - 4:37 pm UTC
"You would take the comma delimited list and "or" them in the text query."
So, you are saying if we want to ignore more than one characters then we can't add multiple characters to the skip join attribute...like
exec ctx_ddl.set_attribute('textuser_lexer', 'skipjoins', '''-');
we have to use "or" in the query
select varchar2_col_t1, varchar2_col_t2
from t1, t2
where t1.id = t2.id (+)
and
(contains( varchar2_col_t1, '%floydmyer%' ) > 0
or contains( varchar2_col_t1, '%floyd-myer%' ) > 0)
September 16, 2005 - 6:09 pm UTC
oh, i misinterpreted you -- sorry.
floydmyer and floyd-myer are the same in that case.
skip joins..
mohini, September 19, 2005 - 4:19 pm UTC
So, if you add a character to skip join attribute..then it should be ignored right..it worked for me for '..but not for
the -
I did re-sync the index:
exec ctx_ddl.set_attribute('textuser_lexer', 'skipjoins', '''-');
exec ctx_ddl.sync_index('t1_idx','5M');
textuser@dev> select * from t1;
ID VARCHAR2_COL_T1
---------- --------------------------------------------------------------------------------
1 wal mart
4 o'conner
5 oconner
6 floyd-myer
7 floydmyer
textuser@dev> select varchar2_col_t1, varchar2_col_t2, contains( varchar2_col_t1, 'o''conner' ) hits
2 from t1, t2
3 where t1.id = t2.id (+)
4 and contains( varchar2_col_t1, '%floydmyer%' ) > 0;
VARCHAR2_COL_T1 VARCHAR2_COL_T2
HITS
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------
- ----------
floydmyer china
0
textuser@dev>
textuser@dev> /
VARCHAR2_COL_T1 VARCHAR2_COL_T2
-------------------------------------------------------------------------------- -----------------------------------
-
oconner toronto
o'conner canada
textuser@dev> select varchar2_col_t1, varchar2_col_t2, contains( varchar2_col_t1, 'o''conner' ) hits
2 from t1, t2
3 where t1.id = t2.id (+)
4 and contains( varchar2_col_t1, '%oconner%' ) > 0;
VARCHAR2_COL_T1 VARCHAR2_COL_T2
HITS
-------------------------------------------------------------------------------- -----------------------------------
- ----------
oconner toronto
4
o'conner canada
4
September 19, 2005 - 5:01 pm UTC
but sync only syncs, it does not rebuild.
if you'd like me to look, generate the entire script that
a) creates table
b) inserts data
c) created the preference
d) create index
(eg: a test case to work with)
skipjoin
mohini, September 19, 2005 - 5:50 pm UTC
It worked..I guess I only synched the index after adding that new attribute..instead of rebuilding the index..again..
Thanks..
textuser@dev> select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4 and contains( varchar2_col_t1, '%oconner%' ) > 0;
VARCHAR2_COL_T1 VARCHAR2_COL_T2
----------------------------------- -----------------------------------
oconner toronto
o'conner canada
textuser@dev> select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4 and contains( varchar2_col_t1, '%floydmyer%' ) > 0;
VARCHAR2_COL_T1 VARCHAR2_COL_T2
----------------------------------- -----------------------------------
floydmyer china
floyd-myer france
textuser@dev>
floyd-myer no records
mohini, September 19, 2005 - 5:56 pm UTC
Back again..
Floydmyer does brin back both "Floydmyer" and "Floyd-myer"
but Floyd-myer brings back no records..
Thanks..
textuser@dev> select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4 and contains( varchar2_col_t1, '%floydmyer%' ) > 0;
VARCHAR2_COL_T1 VARCHAR2_COL_T2
----------------------------------- -----------------------------------
floydmyer china
floyd-myer france
textuser@dev> select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4 and contains( varchar2_col_t1, '%floyd-myer%' ) > 0;
no rows selected
Here is my script to create the test case:
drop table t1;
drop table t2;
create table t1 ( id int primary key, varchar2_col_t1
varchar2(80) );
create table t2 ( id int primary key, varchar2_col_t2
varchar2(80));
insert into t1 values ( 1, 'wal mart' );
insert into t2 values ( 1, 'microsoft' );
insert into t1 values (4, 'o''conner');
insert into t2 values ( 4, 'canada' );
insert into t1 values (5, 'oconner');
insert into t2 values ( 5, 'toronto' );
insert into t1 values (6, 'floyd-myer');
insert into t2 values ( 6, 'france' );
insert into t1 values (7, 'floydmyer');
insert into t2 values ( 7, 'china' );
grant select on t1 to ctxsys;
grant select on t2 to ctxsys;
create or replace procedure ctxsys.glued_textuser ( p_id in rowid,
p_lob in out clob )
as
begin
for x in ( select varchar2_col_t1, varchar2_col_t2
from textuser.t1, textuser.t2
where t1.id = t2.id(+)
and t1.rowid = p_id
)
loop
dbms_lob.writeAppend( p_lob, length(x.varchar2_col_t1)+1, x.varchar2_col_t1||' ');
dbms_lob.writeAppend( p_lob, length(x.varchar2_col_t2)+1, x.varchar2_col_t2||' ');
end loop;
end;
/
grant execute on glued_textuser to textuser;
--create preferences
exec ctx_ddl.drop_preference('textuser_datastore');
exec ctx_ddl.drop_preference('textuser_lexer');
exec ctx_ddl.drop_preference('textuser_substring_pref');
begin
ctx_ddl.create_preference( 'textuser_datastore', 'user_datastore' );
ctx_ddl.set_attribute( 'textuser_datastore', 'procedure', 'glued_textuser' );
end;
/
begin
ctx_ddl.create_preference( 'textuser_lexer', 'BASIC_LEXER' );
ctx_ddl.set_attribute( 'textuser_lexer', 'base_letter', 'YES');
ctx_ddl.set_attribute( 'textuser_lexer', 'mixed_case', 'NO' );
ctx_ddl.set_attribute('textuser_lexer', 'skipjoins', '''-');
end;
/
--to enhance the %% searches
begin
ctx_ddl.create_preference('textuser_substring_pref',
'basic_wordlist');
ctx_ddl.set_attribute('textuser_substring_pref',
'substring_index','TRUE');
end;
/
create index t1_idx on t1(varchar2_col_t1)
indextype is ctxsys.context
parameters( 'datastore ctxsys.textuser_datastore lexer ctxsys.textuser_lexer wordlist ctxsys.textuser_substring_pref memory 50m');
September 19, 2005 - 11:43 pm UTC
ops$tkyte@ORA9IR2> select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4 and contains( varchar2_col_t1, '{%floyd-myer%}' ) > 0;
VARCHAR2_COL_T1
-------------------------------------------------------------------------------
VARCHAR2_COL_T2
-------------------------------------------------------------------------------
floydmyer
china
floyd-myer
france
{} when do we need to add these to the keyword?
A reader, September 20, 2005 - 3:36 pm UTC
So, when do I need to add "{" to the keyword..
cause my partial searches don't bring any thing back..
like '{%con%}' does not bring back any records...but '%con%'
does..and so does '{%oconner%}'
1 select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4* and contains( varchar2_col_t1, '{%oconner%}' ) > 0
textuser@dev> /
VARCHAR2_COL_T1 VARCHAR2_COL_T2
----------------------------------- -----------------------------------
oconner toronto
o'conner canada
textuser@dev> ed
Wrote file afiedt.buf
1 select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4* and contains( varchar2_col_t1, '{%con%}' ) > 0
textuser@dev> /
no rows selected
textuser@dev> ed
Wrote file afiedt.buf
1 select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4* and contains( varchar2_col_t1, '%con%' ) > 0
textuser@dev> /
VARCHAR2_COL_T1 VARCHAR2_COL_T2
----------------------------------- -----------------------------------
oconner toronto
o'conner canada
textuser@dev>
Use Of Curly
Mohini, September 22, 2005 - 6:05 pm UTC
Tom,
Not finding much info on curly..
when does it need to be added to the keyword..
From my followup above..
It is working with some searches but not the others...
Thanks...
September 22, 2005 - 9:51 pm UTC
curly? can you clarify
Curly Braces {}
Mohini, September 22, 2005 - 10:06 pm UTC
I was not getting any hits for "floyd-myer"..and you suggested the following...what are the curly braces for..
Thanks:
ops$tkyte@ORA9IR2> select varchar2_col_t1, varchar2_col_t2
2 from t1, t2
3 where t1.id = t2.id (+)
4 and contains( varchar2_col_t1, '{%floyd-myer%}' ) > 0;
VARCHAR2_COL_T1
-------------------------------------------------------------------------------
VARCHAR2_COL_T2
-------------------------------------------------------------------------------
floydmyer
china
floyd-myer
france
September 23, 2005 - 9:04 am UTC
Search Text
DEBASISH GHOSH, February 25, 2006 - 3:30 am UTC
I have a table with name field and values are like
NAME
-------------
DEBASISHG
DEBASISH
DEBASISHGHOSH
DEBAS ISHG
D EBA SISH G.
DEBASISH GHOSH.
GHOSH DEBASISH
DEBASHIS
DEBASHISH
DEBABRATA
DEBARATI
.......
......
LIKE THAT
I WANT TO QUERY WITH THE STRING 'DEBASISH' AND THIS WILL RETURN ALL 9 ROWS THAT MATCHES WITH THE SEARCH STRING DO NOT USING SOUNDX FUNCTION.
PLEASE LET ME KNOW IN ADVANCE
DEBASISH
February 25, 2006 - 11:17 am UTC
I only see 5 rows that match that.
You'll have to explain your matching algorith, what you had in mind. Sounds like you might have meant to say
After removing spaces (replace(str,' ','')) I would like to retrieve all rows where that new string is like '%DEBASISH%'
perhaps that is what you were looking for?
soundex would be entirely "not appropriate" since you have multiple words.
Text Search
Debasish Ghosh, February 27, 2006 - 4:40 am UTC
actully I want if 70% character is matched with the search string then it will return the entire field value.
I don't want to use soundx or loop like
for r in 1..no of records loop
for j 1..fieldlength loop
....
....
end loop;
end loop;
if there is mellion of records then this type of loop take plenty of time.
February 27, 2006 - 7:12 am UTC
you'll need to be very much more "precise"
for you see, you have a bunch of WORDS in a sentence:
D EBA SISH G.
I see no way that matches your search string, unless and until you give an algorithm that says step 1: remove all whitespace from consideration.
and then you need to define what precisely is 70% of your search string. Leading 70%, trailing, middle, what.
The lack of detail here makes it impossible for anyone to say anything sensible.
Debasish Ghosh, February 27, 2006 - 8:03 am UTC
remove all whitespace from consideration.
and matches Leading 70% of the search string
i.e.
search string is '%DEBASISH%' ATLEAST 70% OF THIS STRING
FROM LEADING POSITION.
FIELD VALUE ARE AFTER REMOVE ALL WHITESPACE
DEBASISH GHOSH DEBASISHGHOSH
DEBA SISH_ GHOSH DEBASISH_GHOSH
D EBA SISH G. DEBASUSHG.
GHOSH. DEBASISH GHOSHDEBASISH
DEBASHISH G DEBASHISHG
G DEB A SHI SH GDEBASHISH
DEBARUN DEBARUN
DEBASISKUMAR DEBASISKUMAR
DEBAS SINGH DEBASSINGH
DEBA BOSE DEBABOSE
DEBARATI ROY DEBARATIROY
AND THE RESULT SHOULD BE
DEBASISH GHOSH
DEBA SISH_ GHOSH
D EBA SISH G.
GHOSH. DEBASISH
DEBASHISH G
G DEB A SHI SH
DEBASISKUMAR
THAKS FOR ADVANCE
February 27, 2006 - 8:37 am UTC
then replace whitespace with nothing (as suggested)
and take 70% of the string (using substr)
and LIKE for it.
where replace( column, ' ', '' )
like '%' || substr( :bv, 1, ceil(length(:bv)*.7) ) || '%'
take the leading 70%, wrap it with '%' - match it to the column in question.
THANKS
DEBASISH GHOSH, February 28, 2006 - 12:33 am UTC
THANKS A LOT.
search text
G, February 28, 2006 - 3:15 am UTC
it's run ok but some porblem
i have
DEBASISHG
DEBA_ SISH G
DEBA* SISH
DEBASHISH
DEBASISHG
GDEBASHISH
GHOSH DEBASISH
when i query through
select str from cc where
str like '%' || substr( 'DEBASISH', 1, ceil(length
('DEBASISH')*.7) ) || '%'
SQL> /
STR
--------------------
DEBASISHG
DEBASISHG
GHOSH DEBASISH
only 3 rows selectd but it should be
DEBASISHG
DEBASISHG
DEBASISH
DEBASISHG
GHOSHDEBASISH
truncate all characters other than a-z or A-Z or 0-9
February 28, 2006 - 7:19 am UTC
and you cannot figure out based in the techniques provided what you might need to do.
read about
replace
translate
and in 10g, the regex functions which might be even more flexible for you
(requirements specification, I'm wondering what ever happened to that "fine art". Watching the evolution of this one is sort of funny)
I am getting extra rows in the following scenario
Srini, March 09, 2006 - 5:47 pm UTC
create table srini_names (nid number,name varchar(20));
create table srini_addrs (aid number,nid number, addr varchar2(50));
insert into srini_names values (1,'srini','x');
insert into srini_names values (2,'reko','x');
insert into srini_names values (3,'ron','x');
insert into srini_names values (4,'barry','x');
insert into srini_addrs values (1,1,'1 governors way');
insert into srini_addrs values (2,1,'10 speen street');
insert into srini_addrs values (3,2,'50 main street');
insert into srini_addrs values (4,2,'678 maple street');
insert into srini_addrs values (5,3,'891 brrok pkwy');
insert into srini_addrs values (6,3,'1 any governors');
CREATE OR REPLACE procedure srini_name_addr (p_id in rowid,p_lob IN OUT clob)
is
templob clob;
temp varchar2(4000);
begin
for c1 in (select nid,name from pbds.srini_names where rowid = p_id)
loop
dbms_lob.writeAppend( p_lob, length(c1.name)+1, c1.name||' ');
for c2 in (select addr from pbds.srini_addrs a where a.nid = c1.nid)
loop
--dbms_lob.append( templob, c2.addr );
--dbms_lob.append( p_lob, c2.addr );
dbms_lob.append( p_lob, c2.addr );
--temp := temp || c2.addr;
end loop;
end loop;
end;
/
create index srini_name_addr_idx on pbds.srini_names(dummy) indextype is
ctxsys.context parameters('datastore srini_name_addr_ds');
select name,addr from srini_names n, srini_addrs a where n.nid=a.nid and contains (dummy,'governors')>0;
returns
NAME ADDR
-------------------- -----------------------------------
srini 1 governors way
srini 10 speen street
as supposed to
NAME ADDR
-------------------- -----------------------------------
srini 1 governors way
March 10, 2006 - 11:55 am UTC
and you don't see why?
one would need a complete example (i cannot run your example - even after the table correction, missing a preference) - but look at your function and "think about it"
you seem to be glueing ever address for a given name into a big string, so.... all rows with the same name - would have every address - by your design.
Best sometimes not to post non-functional code and say "why doesn't this work" when you don't also post "this is what I'm trying to accomplish" - because the code does exactly what you programmed it to do.
correction in table create script
Srini, March 09, 2006 - 5:50 pm UTC
create table srini_names (nid number,name varchar(20),dummy varchar(10));
Srini, March 10, 2006 - 1:40 pm UTC
Apologies for not being clear ..here is the missing pref
begin
ctx_ddl.create_preference( 'srini_name_addr_ds', 'user_datastore' );
ctx_ddl.set_attribute( 'srini_name_addr_ds', 'procedure', 'srini_name_addr' );
end;
/
requirement is to type a text in single text box which should search names and addresses to return the name and address.
If i type a text , and the text is part of address , i should get the name and address with the text typed in .
normal query would be . ..
select name,addr from srini_names n,
srini_addrs a
where n.nid=a.nid(+)
and a.addr like '%governors%'
which returns
NAME ADDR
-------------------- ----------------
srini 1 governors way
I am trying to achive the same with oracle text with single index. Since i am driving from names table each name is getting appended with all the addresses for that given name .
May be i am not supposed to glue the columns this way .
March 10, 2006 - 8:32 pm UTC
but I answered your question - look at your function.
You are concatenating them all together - look at your code!!!
Your code glues every address together. Rethink your logic, you don't want a double loop - you just want to query out two columns from a single row.
Also, you might look at a ctxcat index instead.
Srini Akkala, March 24, 2006 - 10:38 am UTC
Thank you very much . You said
"you just want to query out two columns from a single row."
I am not able to figure out how to do this
Any code samples would be really helpfull.
Thank you very much
March 24, 2006 - 10:47 am UTC
stop storing them as a big string, look at your code.
multi columns Mixed structured query with TEXT search for best performance
Charlie Zhu, August 17, 2006 - 3:23 pm UTC
Here is my story:
Oracle 10.1 or 10.2 are both OK.
I'd like to get Oracle TEXT work for Mixed structured query
for search conditions on many columns.
TEXT developer's guide says one CONTAINS (maybe on one TEXT index)
for best performance.
Book table(
Author varchar2(200),
Title varchar2(200),
Publisher varchar2(200),
Price number(8,2),
Add_date date)
My contains systax will be like:
contains('london WITHIN authorname AND life WITHIN titlename AND price < 10.00 AND add_date > TO_DATE(''17-01-2006'',''DD-MM-YYYY'')');
How to make it work?
Now I can query against 3 text/varchar2 columns, for each or together.
with multi_column_datastore AND section_group
1: long in AUTHORNAME and life in TitleName
variable l_string varchar2(4000)
exec :l_string := 'london WITHIN authorname AND life WITHIN titlename';
SELECT /*+ first_rows(100) */ rowdf,AUTHORNAME, titlename
FROM abedba.BOOK
WHERE CONTAINS (rowdf, :l_string) > 0;
2: london AND life across many columns
exec :l_string := 'london AND life';
SELECT /*+ first_rows(100) */ rowdf,AUTHORNAME, titlename
FROM abedba.BOOK
WHERE CONTAINS (rowdf, :l_string) > 0;
FYI,
exec ctx_ddl.drop_preference('book_multi');
begin
ctx_ddl.create_preference('book_multi','multi_column_datastore');
ctx_ddl.set_attribute('book_multi', 'columns','authorname,titlename');
end;
/
-- create the section preference
begin
ctx_ddl.drop_section_group ( group_name => 'book_section_group' );
end;
/
begin
ctx_ddl.create_section_group ( group_name => 'book_section_group' ,
group_type => 'basic_section_group' );
ctx_ddl.add_field_section ( group_name => 'book_section_group' ,
section_name => 'authorname', tag => 'authorname', visible => true );
ctx_ddl.add_field_section ( group_name => 'book_section_group' ,
section_name => 'titlename', tag => 'titlename', visible => true );
end;
/
create index book_text_index on book(rowdf)
indextype is ctxsys.context
parameters ('DATASTORE book_multi section group book_section_group');
Thanks a lot.
August 17, 2006 - 3:26 pm UTC
i'm confused - i did not run your example, but isn't your example the answer to "how to make it work"?
Re: Multi columns Mixed structured query with TEXT search for best performance
Charlie Zhu, August 17, 2006 - 3:52 pm UTC
No. I only make it work for TEXT/Varchar2 columns.
I do not know how to make it work include NUMBER, DATE columns, include structured query together in one CONTAINS.
Thanks for the quick response.
Re: Multi columns Mixed structured query with TEXT search for best performance
Charlie Zhu, August 17, 2006 - 4:57 pm UTC
That's good to know.
How to write a CONTAINS expression for:
'life WITHIN titlename AND price < 10.00' ?
I only got '10.00 WITHIN price' works.
Thanks again,
Charlie
Re: CONTAINS syntax on multiple columns
Charlie Zhu, August 18, 2006 - 12:18 pm UTC
You can only index one TEXT/VARCHAR2 column with catalog CTXCAT type TEXT index.
How can I index many VARCHAR2 and many NUMBER/DATE columns?
for Mixed structured query
to get the result set like:
contains('london WITHIN authorname AND life WITHIN titlename AND price < 10.00
AND add_date > TO_DATE(''17-01-2006'',''DD-MM-YYYY'')
ORDER BY price');
You may come to our site to have a look: </code>
http://www.abebooks.com/ <code>
and click the "Advanced Search",
It's very common to let customer search book by Title, Author, Decription and Price at same time.
(We're using Endeca as TEXT search engine, but get data sync problem from Oracle database)
Thanks again.
August 18, 2006 - 12:40 pm UTC
all i can do is point you to the documentation there, you have ctxcat searches (and a description of what it can and cannot do) and you have the contains searches.
sounds like you might be doing what I do. A mix of contains and relational.
select ..
from ...
where contains( ... ) > 0
and subject like :bind;
for example - when I use advanced search here on asktom - the query looks a bit like that.
Re: Multi columns Mixed structured query with TEXT search for best performance
Charlie Zhu, August 18, 2006 - 1:23 pm UTC
I guess this discussion will continue for a while. :)
Here is one solution: Advanced MDATA - tips and tricks
</code>
http://www.oracle.com/technology/products/text/htdocs/mdata_tricks.html
see "Mixed Queries with Range Predicates" and "Sorting"
I just don't like to re-invent the wheel, and try to get a easy fast solution.
the 2nd solution is progressive relaxation:
http://www.oracle.com/technology/products/text/htdocs/prog_relax.html <code>
Both these solution need granula To_Char(Number/Date) scheme design
and some PL/SQL coding to do the logical partition detection work.
For example,
select score(1), book_info from test_table
where contains (month_info, '
<query>
<textquery>
<progression>
<seq> mdata(month, 122003) </seq>
<seq> mdata(month, 112003) </seq>
<seq> mdata(month, 102003) </seq>
<seq> mdata(month, 092003) </seq>
<seq> mdata(month, 082003) </seq>
<seq> mdata(month, 072003) </seq>
<seq> mdata(month, 062003) </seq>
<seq> mdata(month, 052003) </seq>
<seq> mdata(month, 042003) </seq>
<seq> mdata(month, 032003) </seq>
<seq> mdata(month, 022003) </seq>
<seq> mdata(month, 012003) </seq>
</progression>
</textquery>
</query>
',1) > 0 and rownum <= 5;
August 18, 2006 - 4:23 pm UTC
thanks for the input, appreciate that!
new TEXT search product with TripleHop (MatchPoint)
Charlie Zhu, August 18, 2006 - 2:35 pm UTC
I know Oracle acquired TripleHop (MatchPoint), Im interesting the roadmap.
Should I wait until the MatchPoint is built into Oracle database, to migrate our Endeca search engine to Oracle?
--Endeca is a light weight distribution TEXT search engine, we got 70 search servers now.
(Maybe that time Oracle can do better mixed structure query on many text and many number/date columns.)
BTW,
I did not get any rows returned from your Advanced Search page.
Just a message:
{gold} Approximately 356 records found based on text query.
Can I see your SQL text behind the search (if its possible)?
Mixed Queries with Range Predicates
Charlie Zhu, August 18, 2006 - 6:16 pm UTC
Good to know I can do some contribution to AskTom site!
MDATA and Progressive Relaxation work good on 50,000 rows, I'm going to benchmark it on 80 million rows data (real scale) and paste my sample code soon.
Different levels of mdata is good for date,
Progressive Relaxation is good for number(price ...)
Here are my search conditions on your Advanced Search,
--
Last Updated Between 01-AUG-2004 and 01-AUG-2006
Subject Like: search
Page Contains: gold
Order By: Last Updated
I just wonder how you translate it to SQL Contains...
Thanks and have a good weekend.
context search
sreevani, August 24, 2006 - 3:57 am UTC
I am using oracle context search for a name to search, but Iam not getting results for some names,although data is available in database.
August 27, 2006 - 7:41 pm UTC
sorry?
I really don't know what else to say, given that the information provided by you is similar to me saying to you:
my car won't start, although it used to start.
oracle text
sreevani, September 09, 2006 - 2:12 am UTC
Reviewer: sreevani from India
I am using oracle context search for a name to search, but Iam not getting results for some names,although data is available in database.
U didn't get my point. My query is like this
select name fro tran_ec where contains(name,'(axx or a) and rama and mohan').
The data in the database is like this:
'A.Rama MOHAN'.
This is an example. But for some lots of data it is the behavior is same.i.e No rows selected. Mainly for initials starting with A and S.Please help me in this regard.
REgards
vani.
September 09, 2006 - 12:19 pm UTC
When and if I locate "U", I'll be sure to let them know?
you give a completely and utterly incomplete example to work with.
your initial question above had no point - it was of the "my car won't start" type, no information, not a thing anyone could be expected to comment on. think about it.
(a would be "a stopword" however, not really indexed.... you would have to have it stop being a stopword
http://docs.oracle.com/docs/cd/B19306_01/text.102/b14217/ind.htm#sthref263
ops$tkyte%ORA10GR2> create table t ( name varchar2(30) );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 'A.Rama MOHAN' );
1 row created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(name) indextype is ctxsys.context;
Index created.
ops$tkyte%ORA10GR2> select token_text from DR$T_IDX$I;
TOKEN_TEXT
----------------------------------------------------------------
MOHAN
RAMA
ops$tkyte%ORA10GR2> drop index t_idx;
Index dropped.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
2 ctx_ddl.create_stoplist( 'empty_stoplist', 'BASIC_STOPLIST' );
3 end;
4 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> create index t_idx on t(name) indextype is ctxsys.context parameters( 'stoplist empty_stoplist' );
Index created.
ops$tkyte%ORA10GR2> select token_text from DR$T_IDX$I;
TOKEN_TEXT
----------------------------------------------------------------
A
MOHAN
RAMA
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t where contains( name, '(axx or a) and rama and mohan' ) > 0;
NAME
------------------------------
A.Rama MOHAN
oracle text
sreevani, September 09, 2006 - 2:13 am UTC
Reviewer: sreevani from India
I am using oracle context search for a name to search, but Iam not getting results for some names,although data is available in database.
U didn't get my point. My query is like this
select name from tran_ec where contains(name,'(axx or a) and rama and mohan')>0.
The data in the database is like this:
'A.Rama MOHAN'.
This is an example. But for some lots of data it is the behavior is same.i.e No rows selected. Mainly for initials starting with A and S.Please help me in this regard.
REgards
vani.
SYNC to sync all the indexes
Vinayak Awasthi, September 11, 2006 - 10:20 am UTC
Tom,
we need to call ctx_ddl.sync everytime the contents of the base table is changed for the column on which search is performed.
My question is should we call this procedure every time when a dml is performed.Scenario is we have an internal website where news items are continously updated by the news team.Do you recommend calling this function after every dml continously (through a trigger) or it can be done through some job. But the search will not return any data till the job has run and updated teh index.
Your advice on this please.
September 11, 2006 - 10:41 am UTC
no, you, don't.
10g can sync on commit.
You can sync every N minutes (that is my approach on asktom).
How often I recreate the index
sreevani, September 12, 2006 - 2:56 am UTC
Tom,
How often I recreate the index for new inserted and updated records.
vani
September 12, 2006 - 8:27 am UTC
??
10g XE not returning data
Vinayak, September 12, 2006 - 9:13 am UTC
Hi Tom,
You said that 10g auto sync our index on commit.I am using Oracle 10g XE and set autocommit flag unchecked. I insert into the table , commits it explicitly and then run the query but it didn't returned any rows. Am I missing something there.
Here is my script:
create table oracle_text(text varchar2(100));
create index idx_oracle_text on oracle_text(text) INDEXTYPE IS CTXSYS.CONTEXT;
insert into oracle_text values('Keep good work going');
insert into oracle_text values('Knowledge sharing is gem');
commit;
select * from oracle_text where contains(text,'Keep')>0;
<<no data found>>
select * from oracle_text where contains(text,'sharing')>0;
<<no data found>>
Then I sync the indexes:
begin
ctx_ddl.sync_index('idx_oracle_text');
end;
and again tried the queries:
select * from oracle_text where contains(text,'Keep')>0;
Keep good work going
select * from oracle_text where contains(text,'sharing')>0;
Knowledge sharing is gem
Here is my version info:
select * from v$version;
-----------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Yours inputs Tom...
September 12, 2006 - 9:19 am UTC
you didn't enable the automatic syncronization
I said "it can", not that "it does by default"
Thanks!!!
vinayak, September 12, 2006 - 9:24 am UTC
Tom, Thanks for that.
From where do we enable this automatic syncronization, something in init.ora file.
September 12, 2006 - 10:51 am UTC
Question on multi_column_datastore
Manjunath, October 27, 2006 - 3:42 pm UTC
Hi Tom,
We have a requirement to search for strings that can be contained in multiple columns(lets say in 12 columns) in the same table. For this, an index on all these columns(using multi_column_datastore) works fine. However, if there is a need to search only in 5 columns(instead of 12), should another index be created or is there some other way?
Thanks and regards
Manjunath
Contains Instead of LIKE '%'
Emad Kehail, January 26, 2007 - 2:53 am UTC
Hello Tom,
I hope you help me in this:
I have a table looks like this
create table subscribers (
id numer(10),
first_name varchar2(30),
father_name varchar2(30),
grandfather_name varchar2(30),
last_name varchar2(30))
/
The application is built using Oracle Forms. Many times, the end users are not so sure of the spelling of the name, therefore they use the "%" wildcard with name fields. This will be reflected to the queries the application will send them to the Oracle Server.
We have the following queries
1) select *
from subscribers
where last_name like '%family_name%';
2) select *
from subscribers
where last_name like 'family_name%';
3) select *
from subscribers
where last_name like '%family_name%' and first_name like '%first_name%';
4) select *
from subscribers
where last_name like 'family_name%' and first_name like 'first_name%';
As well as searching on the father_name and grandfather_name fields. But most of the search are on the first_name and the last_name.
These queries are killing the server since we have millions of records. BTree indexes will not help here because of the LIKE and the "%"
I am thinking to use Oracle Text here, but I am not sure whether I have to go for a CONTEXT index on each individual column, or I can use the MULTI_COLUMN_DATASTORE indexing.
Thanks
blast from the past ...
LJ, January 17, 2008 - 8:50 am UTC
On July 8, 2003 Tom Best posted about having a lot of open cursors left behind when using the CTX_DDL and the glue_them_together procedure you shared with us.
After a few follow ups on that, the thread changed to some other focus.
I have a 10.2.0.3 database and set up the Text feature exactly as the examples above (multi_column_datastore), and it works great, except it leaves a lot of cursors open. Like 2,000 of them.
Did anyone ever find a reason why, or any way to close the cursor? And possibly what cursor or cursors are in fact being left open?
CONTAINS Clause with OR and other joined tables gives wrong set of results
VKOUL, June 05, 2008 - 4:50 am UTC
Hi Tom,
I am not able to make out why is it happenning.
From SYS schema:
-- Run as SYS START
create user test identified by test default tablespace users quota unlimited on users;
grant connect, resource to test;
grant ctxapp to test;
grant execute on ctx_ddl to test;
-- Run as SYS STOP
From the newly created user test do :
DECLARE
ts VARCHAR2(30) := 'USERS';
BEGIN
BEGIN
ctx_ddl.drop_preference('my_storage');
EXCEPTION WHEN OTHERS THEN NULL; -- Ignore errors if the preference is not there
END;
BEGIN
ctx_ddl.drop_preference('my_lexer');
EXCEPTION WHEN OTHERS THEN NULL; -- Ignore errors if the preference is not there
END;
BEGIN
ctx_ddl.drop_stoplist('my_stoplist');
EXCEPTION WHEN OTHERS THEN NULL; -- Ignore errors if the stop list is not there
END;
ctx_ddl.create_preference('my_storage','BASIC_STORAGE');
ctx_ddl.set_attribute('my_storage','I_TABLE_CLAUSE','TABLESPACE '||ts);
ctx_ddl.set_attribute('my_storage','K_TABLE_CLAUSE','TABLESPACE '||ts);
ctx_ddl.set_attribute('my_storage','R_TABLE_CLAUSE','TABLESPACE '||ts);
ctx_ddl.set_attribute('my_storage','N_TABLE_CLAUSE','TABLESPACE '||ts);
ctx_ddl.set_attribute('my_storage','I_INDEX_CLAUSE','TABLESPACE '||ts);
ctx_ddl.create_preference('my_lexer','BASIC_LEXER');
ctx_ddl.set_attribute('my_lexer','INDEX_THEMES','FALSE');
ctx_ddl.set_attribute('my_lexer','INDEX_TEXT','TRUE');
ctx_ddl.set_attribute('my_lexer','ENDJOINS','+');
-- Create empty stop list (noise words)
CTX_DDL.CREATE_STOPLIST('my_stoplist', 'BASIC_STOPLIST');
-- use this syntax to add words
-- CTX_DDL.ADD_STOPWORD('my_stoplist', 'the');
END;
/
CREATE TABLE T1
(
T1_PK NUMBER(10) ,
T1_NAME VARCHAR2(900 BYTE) NOT NULL,
T1_CODE VARCHAR2(765 BYTE) ,
T1_ROOT NUMBER(10)
)
/
CREATE INDEX FT_TABLE_NAME ON T1 (T1_NAME)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('lexer my_lexer storage my_storage stoplist my_stoplist')
/
CREATE INDEX FT_TABLE_CODE ON T1 (T1_CODE)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('lexer my_lexer storage my_storage stoplist my_stoplist')
/
CREATE INDEX IDX_TMX_ACTIVITY_6YHX9H0P ON T1 (T1_CODE, T1_PK)
/
CREATE INDEX IDX_TMX_ACTIVITY_6YHX9H0U ON T1 (T1_PK, T1_ROOT)
/
CREATE INDEX IDX_TMX_ACTIVITY_PATCH2 ON T1 (UPPER("T1_NAME"), T1_PK)
/
CREATE UNIQUE INDEX PK_T1 ON T1 (T1_PK)
/
ALTER TABLE T1 ADD (CONSTRAINT PK_T1 PRIMARY KEY (T1_PK) USING INDEX)
/
CREATE TABLE T3
(
T3_PK NUMBER(10) NOT NULL,
T3_DOMAININD NUMBER(1) NOT NULL
)
/
CREATE UNIQUE INDEX PK_T3 ON T3 (T3_PK)
/
ALTER TABLE T3 ADD (CONSTRAINT PK_T3 PRIMARY KEY (T3_PK) USING INDEX)
/
CREATE TABLE T2
(
T2_ACTFK NUMBER(10) NOT NULL,
T2_DOMAINFK NUMBER(10) NOT NULL,
T2_PRMYIND NUMBER(3) NOT NULL
)
/
CREATE INDEX IDX_T2_6YHX9H0J ON T2 (T2_ACTFK, T2_DOMAINFK, T2_PRMYIND)
/
CREATE UNIQUE INDEX PK_T2 ON T2 (T2_DOMAINFK, T2_ACTFK)
/
ALTER TABLE T2 ADD (CONSTRAINT PK_T2 PRIMARY KEY (T2_DOMAINFK, T2_ACTFK) USING INDEX)
/
ALTER TABLE T2 ADD (CONSTRAINT FK_T2_ACTFK_PLKKJS0G FOREIGN KEY (T2_ACTFK ) REFERENCES T1 (T1_PK) ON DELETE CASCADE,
CONSTRAINT FK_T2_DOMAINFK_PLKKJS0H FOREIGN KEY (T2_DOMAINFK) REFERENCES T3 (T3_PK) ON DELETE CASCADE
)
/
insert into t1
select rownum, 'name35 '||TO_CHAR(ROWNUM), 'name35', MOD(ROWNUM, 5)
from all_objects
where rownum < 36;
insert into t3
select rownum, 1
from all_objects where rownum < 10;
insert into t3
select rownum+10, 0
from all_objects where rownum < 10;
insert into t2
select rownum, 2, 1
from all_objects
where rownum < 36;
insert into t2
select rownum, 1, 1
from all_objects
where rownum < 36;
BEGIN
FOR ind IN (SELECT DISTINCT pnd_index_name
FROM ctx_user_pending
)
LOOP
ctxsys.ctx_ddl.sync_index(idx_name => ind.pnd_index_name, memory => '16M');
END LOOP;
END;
/
commit;
COLUMN T1_Name Format a15
COLUMN T1_Code Format a15
PROMPT Pulling the whole set of data
PROMPT (There are 28 records with T3_PK = 1 and 28 records with T3_PK = 2)
SELECT T2_PrmyInd, T3_PK, T3_DomainInd, T1_Name, T1_Code
FROM T1
INNER
JOIN T2 ON T1_Root = T2_ActFK
INNER
JOIN T3 ON T2_DomainFK = T3_PK
WHERE
T2_PrmyInd = 1
AND
T3_DomainInd = 1
AND
(
(contains(T1_Name, 'name35') > 0)
OR
(contains(T1_Code, 'name35') > 0)
)
ORDER BY T3_PK
/
T2_PRMYIND T3_PK T3_DOMAININD T1_NAME T1_CODE
---------- ---------- ------------ --------------- --------
1 1 1 name35 1 name35
1 1 1 name35 2 name35
1 1 1 name35 4 name35
1 1 1 name35 7 name35
1 1 1 name35 9 name35
1 1 1 name35 12 name35
1 1 1 name35 14 name35
1 1 1 name35 17 name35
1 1 1 name35 19 name35
1 1 1 name35 31 name35
1 1 1 name35 29 name35
1 1 1 name35 28 name35
1 1 1 name35 27 name35
1 1 1 name35 26 name35
1 1 1 name35 24 name35
1 1 1 name35 23 name35
1 1 1 name35 22 name35
1 1 1 name35 21 name35
1 1 1 name35 34 name35
1 1 1 name35 33 name35
1 1 1 name35 32 name35
1 1 1 name35 18 name35
1 1 1 name35 16 name35
1 1 1 name35 13 name35
1 1 1 name35 11 name35
1 1 1 name35 8 name35
1 1 1 name35 6 name35
1 1 1 name35 3 name35
1 2 1 name35 1 name35
1 2 1 name35 34 name35
1 2 1 name35 33 name35
1 2 1 name35 32 name35
1 2 1 name35 31 name35
1 2 1 name35 29 name35
1 2 1 name35 28 name35
1 2 1 name35 27 name35
1 2 1 name35 26 name35
1 2 1 name35 24 name35
1 2 1 name35 23 name35
1 2 1 name35 22 name35
1 2 1 name35 21 name35
1 2 1 name35 19 name35
1 2 1 name35 18 name35
1 2 1 name35 17 name35
1 2 1 name35 16 name35
1 2 1 name35 14 name35
1 2 1 name35 13 name35
1 2 1 name35 12 name35
1 2 1 name35 11 name35
1 2 1 name35 2 name35
1 2 1 name35 4 name35
1 2 1 name35 7 name35
1 2 1 name35 9 name35
1 2 1 name35 8 name35
1 2 1 name35 6 name35
1 2 1 name35 3 name35
56 rows selected.
SQL>
PROMPT Filtering on T3_PK = 1, should give 28 records but it gives 2 records.
SELECT T2_PrmyInd, T3_PK, T3_DomainInd, T1_Name, T1_Code
FROM T1
INNER
JOIN T2 ON T1_Root = T2_ActFK
INNER
JOIN T3 ON T2_DomainFK = T3_PK
WHERE
T2_PrmyInd = 1
AND
T3_DomainInd = 1
AND
(
(contains(T1_Name, 'name35') > 0)
OR
(contains(T1_Code, 'name35') > 0)
)
AND T3_PK = 1
/
T2_PRMYIND T3_PK T3_DOMAININD T1_NAME T1_CODE
---------- ---------- ------------ --------------- --------
1 1 1 name35 1 name35
1 1 1 name35 2 name35
2 rows selected.
PROMPT Filtering on T3_PK = 1 after doing an inline view with ROWNUM > 0,
PROMPT so that it won't merge the queries, should give 28 records and it gives 28 records.
SELECT * FROM (
SELECT T2_PrmyInd, T3_PK, T3_DomainInd, T1_Name, T1_Code
FROM T1
INNER
JOIN T2 ON T1_Root = T2_ActFK
INNER
JOIN T3 ON T2_DomainFK = T3_PK
WHERE
T2_PrmyInd = 1
AND
T3_DomainInd = 1
AND
(
(contains(T1_Name, 'name35') > 0)
OR
(contains(T1_Code, 'name35') > 0)
)
AND ROWNUM > 0
)
WHERE T3_PK = 1
/
T2_PRMYIND T3_PK T3_DOMAININD T1_NAME T1_CODE
---------- ---------- ------------ --------------- -------
1 1 1 name35 1 name35
1 1 1 name35 2 name35
1 1 1 name35 3 name35
1 1 1 name35 4 name35
1 1 1 name35 6 name35
1 1 1 name35 7 name35
1 1 1 name35 8 name35
1 1 1 name35 9 name35
1 1 1 name35 11 name35
1 1 1 name35 12 name35
1 1 1 name35 13 name35
1 1 1 name35 14 name35
1 1 1 name35 16 name35
1 1 1 name35 17 name35
1 1 1 name35 18 name35
1 1 1 name35 19 name35
1 1 1 name35 21 name35
1 1 1 name35 22 name35
1 1 1 name35 23 name35
1 1 1 name35 24 name35
1 1 1 name35 26 name35
1 1 1 name35 27 name35
1 1 1 name35 28 name35
1 1 1 name35 29 name35
1 1 1 name35 31 name35
1 1 1 name35 32 name35
1 1 1 name35 33 name35
1 1 1 name35 34 name35
28 rows selected.
SQL>
Is there something I am missing while taking creating these fulltext indexes.
Observations :
1. If we put the CONTAINS before all other conditions then results are coming as expected.
OR
2. If we take out one CONTAINS on the T1_CODE out, the results are coming as expected.
SELECT T2_PrmyInd, T3_PK, T3_DomainInd, T1_Name, T1_Code
FROM T1
INNER
JOIN T2 ON T1_Root = T2_ActFK
INNER
JOIN T3 ON T2_DomainFK = T3_PK
WHERE
T2_PrmyInd = 1
AND
T3_DomainInd = 1
AND
(
(contains(T1_Name, 'name35') > 0)
-- OR
-- (contains(T1_Code, 'name35') > 0)
)
AND T3_PK = 1
/
T2_PRMYIND T3_PK T3_DOMAININD T1_NAME T1_CODE
---------- ---------- ------------ --------------- --------
1 1 1 name35 34 name35
1 1 1 name35 33 name35
1 1 1 name35 32 name35
1 1 1 name35 31 name35
1 1 1 name35 29 name35
1 1 1 name35 28 name35
1 1 1 name35 27 name35
1 1 1 name35 26 name35
1 1 1 name35 24 name35
1 1 1 name35 23 name35
1 1 1 name35 22 name35
1 1 1 name35 21 name35
1 1 1 name35 19 name35
1 1 1 name35 18 name35
1 1 1 name35 17 name35
1 1 1 name35 16 name35
1 1 1 name35 14 name35
1 1 1 name35 13 name35
1 1 1 name35 12 name35
1 1 1 name35 11 name35
1 1 1 name35 9 name35
1 1 1 name35 8 name35
1 1 1 name35 7 name35
1 1 1 name35 6 name35
1 1 1 name35 4 name35
1 1 1 name35 3 name35
1 1 1 name35 2 name35
1 1 1 name35 1 name35
28 rows selected.
SQL>
SELECT T2_PrmyInd, T3_PK, T3_DomainInd, T1_Name, T1_Code
FROM T1
INNER
JOIN T2 ON T1_Root = T2_ActFK
INNER
JOIN T3 ON T2_DomainFK = T3_PK
WHERE
(
(contains(T1_Name, 'name35') > 0)
OR
(contains(T1_Code, 'name35') > 0)
)
AND T2_PrmyInd = 1
AND
T3_DomainInd = 1
AND
T3_PK = 1
/
T2_PRMYIND T3_PK T3_DOMAININD T1_NAME T1_CODE
---------- ---------- ------------ --------------- --------
1 1 1 name35 1 name35
1 1 1 name35 2 name35
1 1 1 name35 3 name35
1 1 1 name35 4 name35
1 1 1 name35 6 name35
1 1 1 name35 7 name35
1 1 1 name35 8 name35
1 1 1 name35 9 name35
1 1 1 name35 11 name35
1 1 1 name35 12 name35
1 1 1 name35 13 name35
1 1 1 name35 14 name35
1 1 1 name35 16 name35
1 1 1 name35 17 name35
1 1 1 name35 18 name35
1 1 1 name35 19 name35
1 1 1 name35 21 name35
1 1 1 name35 22 name35
1 1 1 name35 23 name35
1 1 1 name35 24 name35
1 1 1 name35 26 name35
1 1 1 name35 27 name35
1 1 1 name35 28 name35
1 1 1 name35 29 name35
1 1 1 name35 31 name35
1 1 1 name35 32 name35
1 1 1 name35 33 name35
1 1 1 name35 34 name35
28 rows selected.
SQL>
SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
SQL>
Could it be an Oracle bug ?
Thanks
June 05, 2008 - 10:00 am UTC
before I even look at this - a challenge for you
HOW SMALL CAN YOU MAKE THIS
I know it could be smaller.
when I have to hit page down over and over - it is just too big - I'm sure you don't need 80% of the stuff there to demonstrate the issue.
for Vkoul: works in 11g, better ways
Barbara Boehmer, June 05, 2008 - 2:39 pm UTC
Vkoul,
I ran your script on 11g and it returns 28 rows as it should, not just the 2 that you got in 9i. I have noticed in 9i, 10g, and 11g, that using multiple contains clauses with multiple context indexes causes problems, including sometimes just getting an error instead of results. There are some things that seem to help, such as using the non-ANSI join syntax and putting the contains clauses first and sometimes using inline views or hints. However, it is best to minimize the contains clauses by either using a user_datastore with a procedure to concatenate the values or if the text columns are all in one table, as in your case, you can just use a multi_column_datastore. Below, I have provided a run of your script on 11g, followed by suggested query syntax with your existing indexes, followed by a query using a multi_column_datastore. Also, there is an Oracle Text forum on the OTN forums, where you can post such questions questions about Oracle Text:
http://forums.oracle.com/forums/forum.jspa?forumID=71
It is usually best to try posting your Oracle Text problem there first. Tom Kyte usually has a backlog of questions, so we consider him a scarce resource, and try to ask him only when the rest of us can't figure it out or agree amongst ourselves.
Regards,
Barbara
SYS@orcl_11g> -- your test:
SYS@orcl_11g> select banner from v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
SYS@orcl_11g> create user test identified by test default tablespace users quota unlimited on users;
User created.
SYS@orcl_11g>
SYS@orcl_11g> grant connect, resource to test;
Grant succeeded.
SYS@orcl_11g>
SYS@orcl_11g> grant ctxapp to test;
Grant succeeded.
SYS@orcl_11g>
SYS@orcl_11g> grant execute on ctx_ddl to test;
Grant succeeded.
SYS@orcl_11g>
SYS@orcl_11g> connect test/test
Connected.
TEST@orcl_11g>
TEST@orcl_11g>
TEST@orcl_11g> DECLARE
2 ts VARCHAR2(30) := 'USERS';
3 BEGIN
4
5 BEGIN
6 ctx_ddl.drop_preference('my_storage');
7 EXCEPTION WHEN OTHERS THEN NULL; -- Ignore errors if the preference is not there
8 END;
9
10 BEGIN
11 ctx_ddl.drop_preference('my_lexer');
12 EXCEPTION WHEN OTHERS THEN NULL; -- Ignore errors if the preference is not there
13 END;
14
15 BEGIN
16 ctx_ddl.drop_stoplist('my_stoplist');
17 EXCEPTION WHEN OTHERS THEN NULL; -- Ignore errors if the stop list is not there
18 END;
19
20 ctx_ddl.create_preference('my_storage','BASIC_STORAGE');
21 ctx_ddl.set_attribute('my_storage','I_TABLE_CLAUSE','TABLESPACE '||ts);
22 ctx_ddl.set_attribute('my_storage','K_TABLE_CLAUSE','TABLESPACE '||ts);
23 ctx_ddl.set_attribute('my_storage','R_TABLE_CLAUSE','TABLESPACE '||ts);
24 ctx_ddl.set_attribute('my_storage','N_TABLE_CLAUSE','TABLESPACE '||ts);
25 ctx_ddl.set_attribute('my_storage','I_INDEX_CLAUSE','TABLESPACE '||ts);
26
27 ctx_ddl.create_preference('my_lexer','BASIC_LEXER');
28 ctx_ddl.set_attribute('my_lexer','INDEX_THEMES','FALSE');
29 ctx_ddl.set_attribute('my_lexer','INDEX_TEXT','TRUE');
30 ctx_ddl.set_attribute('my_lexer','ENDJOINS','+');
31
32 -- Create empty stop list (noise words)
33 CTX_DDL.CREATE_STOPLIST('my_stoplist', 'BASIC_STOPLIST');
34
35 -- use this syntax to add words
36 -- CTX_DDL.ADD_STOPWORD('my_stoplist', 'the');
37
38 END;
39 /
PL/SQL procedure successfully completed.
TEST@orcl_11g>
TEST@orcl_11g> CREATE TABLE T1
2 (
3 T1_PK NUMBER(10) ,
4 T1_NAME VARCHAR2(900 BYTE) NOT NULL,
5 T1_CODE VARCHAR2(765 BYTE) ,
6 T1_ROOT NUMBER(10)
7 )
8 /
Table created.
TEST@orcl_11g>
TEST@orcl_11g> CREATE INDEX FT_TABLE_NAME ON T1 (T1_NAME)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS('lexer my_lexer storage my_storage stoplist my_stoplist')
4 /
Index created.
TEST@orcl_11g>
TEST@orcl_11g> CREATE INDEX FT_TABLE_CODE ON T1 (T1_CODE)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS('lexer my_lexer storage my_storage stoplist my_stoplist')
4 /
Index created.
TEST@orcl_11g>
TEST@orcl_11g> CREATE INDEX IDX_TMX_ACTIVITY_6YHX9H0P ON T1 (T1_CODE, T1_PK)
2 /
Index created.
TEST@orcl_11g>
TEST@orcl_11g> CREATE INDEX IDX_TMX_ACTIVITY_6YHX9H0U ON T1 (T1_PK, T1_ROOT)
2 /
Index created.
TEST@orcl_11g>
TEST@orcl_11g> CREATE INDEX IDX_TMX_ACTIVITY_PATCH2 ON T1 (UPPER("T1_NAME"), T1_PK)
2 /
Index created.
TEST@orcl_11g>
TEST@orcl_11g> CREATE UNIQUE INDEX PK_T1 ON T1 (T1_PK)
2 /
Index created.
TEST@orcl_11g>
TEST@orcl_11g> ALTER TABLE T1 ADD (CONSTRAINT PK_T1 PRIMARY KEY (T1_PK) USING INDEX)
2 /
Table altered.
TEST@orcl_11g>
TEST@orcl_11g> CREATE TABLE T3
2 (
3 T3_PK NUMBER(10) NOT NULL,
4 T3_DOMAININD NUMBER(1) NOT NULL
5 )
6 /
Table created.
TEST@orcl_11g>
TEST@orcl_11g> CREATE UNIQUE INDEX PK_T3 ON T3 (T3_PK)
2 /
Index created.
TEST@orcl_11g>
TEST@orcl_11g> ALTER TABLE T3 ADD (CONSTRAINT PK_T3 PRIMARY KEY (T3_PK) USING INDEX)
2 /
Table altered.
TEST@orcl_11g>
TEST@orcl_11g> CREATE TABLE T2
2 (
3 T2_ACTFK NUMBER(10) NOT NULL,
4 T2_DOMAINFK NUMBER(10) NOT NULL,
5 T2_PRMYIND NUMBER(3) NOT NULL
6 )
7 /
Table created.
TEST@orcl_11g>
TEST@orcl_11g> CREATE INDEX IDX_T2_6YHX9H0J ON T2 (T2_ACTFK, T2_DOMAINFK, T2_PRMYIND)
2 /
Index created.
TEST@orcl_11g>
TEST@orcl_11g> CREATE UNIQUE INDEX PK_T2 ON T2 (T2_DOMAINFK, T2_ACTFK)
2 /
Index created.
TEST@orcl_11g>
TEST@orcl_11g> ALTER TABLE T2 ADD (CONSTRAINT PK_T2 PRIMARY KEY (T2_DOMAINFK, T2_ACTFK) USING INDEX)
2 /
Table altered.
TEST@orcl_11g>
TEST@orcl_11g> ALTER TABLE T2 ADD (CONSTRAINT FK_T2_ACTFK_PLKKJS0G FOREIGN KEY (T2_ACTFK ) REFERENCES T1
2 (T1_PK) ON DELETE CASCADE,
3 CONSTRAINT FK_T2_DOMAINFK_PLKKJS0H FOREIGN KEY (T2_DOMAINFK) REFERENCES T3
4 (T3_PK) ON DELETE CASCADE
5 )
6 /
Table altered.
TEST@orcl_11g>
TEST@orcl_11g> insert into t1
2 select rownum, 'name35 '||TO_CHAR(ROWNUM), 'name35', MOD(ROWNUM, 5)
3 from all_objects
4 where rownum < 36;
35 rows created.
TEST@orcl_11g>
TEST@orcl_11g> insert into t3
2 select rownum, 1
3 from all_objects where rownum < 10;
9 rows created.
TEST@orcl_11g>
TEST@orcl_11g> insert into t3
2 select rownum+10, 0
3 from all_objects where rownum < 10;
9 rows created.
TEST@orcl_11g>
TEST@orcl_11g> insert into t2
2 select rownum, 2, 1
3 from all_objects
4 where rownum < 36;
35 rows created.
TEST@orcl_11g>
TEST@orcl_11g> insert into t2
2 select rownum, 1, 1
3 from all_objects
4 where rownum < 36;
35 rows created.
TEST@orcl_11g>
TEST@orcl_11g> BEGIN
2 FOR ind IN (SELECT DISTINCT pnd_index_name
3 FROM ctx_user_pending
4 )
5 LOOP
6 ctxsys.ctx_ddl.sync_index(idx_name => ind.pnd_index_name, memory => '16M');
7 END LOOP;
8 END;
9 /
PL/SQL procedure successfully completed.
TEST@orcl_11g>
TEST@orcl_11g> commit;
Commit complete.
TEST@orcl_11g>
TEST@orcl_11g> COLUMN T1_Name Format a15
TEST@orcl_11g> COLUMN T1_Code Format a15
TEST@orcl_11g>
TEST@orcl_11g>
TEST@orcl_11g>
TEST@orcl_11g> PROMPT Pulling the whole set of data
Pulling the whole set of data
TEST@orcl_11g> PROMPT (There are 28 records with T3_PK = 1 and 28 records with T3_PK = 2)
(There are 28 records with T3_PK = 1 and 28 records with T3_PK = 2)
TEST@orcl_11g>
TEST@orcl_11g> SELECT T2_PrmyInd, T3_PK, T3_DomainInd, T1_Name, T1_Code
2 FROM T1
3 INNER
4 JOIN T2 ON T1_Root = T2_ActFK
5 INNER
6 JOIN T3 ON T2_DomainFK = T3_PK
7 WHERE
8 T2_PrmyInd = 1
9 AND
10 T3_DomainInd = 1
11 AND
12 (
13 (contains(T1_Name, 'name35') > 0)
14 OR
15 (contains(T1_Code, 'name35') > 0)
16 )
17 ORDER BY T3_PK
18 /
T2_PRMYIND T3_PK T3_DOMAININD T1_NAME T1_CODE
---------- ---------- ------------ --------------- ---------------
1 1 1 name35 1 name35
1 1 1 name35 33 name35
1 1 1 name35 32 name35
1 1 1 name35 31 name35
1 1 1 name35 29 name35
1 1 1 name35 28 name35
1 1 1 name35 27 name35
1 1 1 name35 26 name35
1 1 1 name35 24 name35
1 1 1 name35 23 name35
1 1 1 name35 22 name35
1 1 1 name35 21 name35
1 1 1 name35 19 name35
1 1 1 name35 18 name35
1 1 1 name35 17 name35
1 1 1 name35 16 name35
1 1 1 name35 14 name35
1 1 1 name35 13 name35
1 1 1 name35 12 name35
1 1 1 name35 11 name35
1 1 1 name35 9 name35
1 1 1 name35 8 name35
1 1 1 name35 7 name35
1 1 1 name35 6 name35
1 1 1 name35 4 name35
1 1 1 name35 3 name35
1 1 1 name35 2 name35
1 1 1 name35 34 name35
1 2 1 name35 31 name35
1 2 1 name35 1 name35
1 2 1 name35 26 name35
1 2 1 name35 16 name35
1 2 1 name35 34 name35
1 2 1 name35 14 name35
1 2 1 name35 24 name35
1 2 1 name35 13 name35
1 2 1 name35 29 name35
1 2 1 name35 12 name35
1 2 1 name35 23 name35
1 2 1 name35 11 name35
1 2 1 name35 32 name35
1 2 1 name35 9 name35
1 2 1 name35 22 name35
1 2 1 name35 8 name35
1 2 1 name35 28 name35
1 2 1 name35 7 name35
1 2 1 name35 21 name35
1 2 1 name35 6 name35
1 2 1 name35 33 name35
1 2 1 name35 4 name35
1 2 1 name35 19 name35
1 2 1 name35 3 name35
1 2 1 name35 27 name35
1 2 1 name35 2 name35
1 2 1 name35 18 name35
1 2 1 name35 17 name35
56 rows selected.
TEST@orcl_11g>
TEST@orcl_11g> PROMPT Filtering on T3_PK = 1, should give 28 records but it gives 2 records. (ON MY SYSTEM IT DOES GIVE 28)
Filtering on T3_PK = 1, should give 28 records but it gives 2 records. (ON MY SYSTEM IT DOES GIVE 28)
TEST@orcl_11g>
TEST@orcl_11g> SELECT T2_PrmyInd, T3_PK, T3_DomainInd, T1_Name, T1_Code
2 FROM T1
3 INNER
4 JOIN T2 ON T1_Root = T2_ActFK
5 INNER
6 JOIN T3 ON T2_DomainFK = T3_PK
7 WHERE
8 T2_PrmyInd = 1
9 AND
10 T3_DomainInd = 1
11 AND
12 (
13 (contains(T1_Name, 'name35') > 0)
14 OR
15 (contains(T1_Code, 'name35') > 0)
16 )
17 AND T3_PK = 1
18 /
T2_PRMYIND T3_PK T3_DOMAININD T1_NAME T1_CODE
---------- ---------- ------------ --------------- ---------------
1 1 1 name35 1 name35
1 1 1 name35 2 name35
1 1 1 name35 3 name35
1 1 1 name35 4 name35
1 1 1 name35 6 name35
1 1 1 name35 7 name35
1 1 1 name35 8 name35
1 1 1 name35 9 name35
1 1 1 name35 11 name35
1 1 1 name35 12 name35
1 1 1 name35 13 name35
1 1 1 name35 14 name35
1 1 1 name35 16 name35
1 1 1 name35 17 name35
1 1 1 name35 18 name35
1 1 1 name35 19 name35
1 1 1 name35 21 name35
1 1 1 name35 22 name35
1 1 1 name35 23 name35
1 1 1 name35 24 name35
1 1 1 name35 26 name35
1 1 1 name35 27 name35
1 1 1 name35 28 name35
1 1 1 name35 29 name35
1 1 1 name35 31 name35
1 1 1 name35 32 name35
1 1 1 name35 33 name35
1 1 1 name35 34 name35
28 rows selected.
TEST@orcl_11g>
TEST@orcl_11g> -- suggested syntax with existing indexes:
TEST@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'T1')
PL/SQL procedure successfully completed.
TEST@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'T2')
PL/SQL procedure successfully completed.
TEST@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'T3')
PL/SQL procedure successfully completed.
TEST@orcl_11g> SELECT T2.T2_PrmyInd, T3.T3_PK, T3.T3_DomainInd, T1.T1_Name, T1.T1_Code
2 FROM T1, T2, T3
3 WHERE (contains (T1_Name, 'name35') > 0
4 OR
5 contains (T1_Code, 'name35') > 0)
6 AND T1.T1_Root = T2.T2_ActFK
7 AND T2.T2_DomainFK = T3.T3_Pk
8 AND T2.T2_PrmyInd = 1
9 AND T3.T3_DomainInd = 1
10 AND T3_pK = 1
11 /
T2_PRMYIND T3_PK T3_DOMAININD T1_NAME T1_CODE
---------- ---------- ------------ --------------- ---------------
1 1 1 name35 1 name35
1 1 1 name35 2 name35
1 1 1 name35 3 name35
1 1 1 name35 4 name35
1 1 1 name35 6 name35
1 1 1 name35 7 name35
1 1 1 name35 8 name35
1 1 1 name35 9 name35
1 1 1 name35 11 name35
1 1 1 name35 12 name35
1 1 1 name35 13 name35
1 1 1 name35 14 name35
1 1 1 name35 16 name35
1 1 1 name35 17 name35
1 1 1 name35 18 name35
1 1 1 name35 19 name35
1 1 1 name35 21 name35
1 1 1 name35 22 name35
1 1 1 name35 23 name35
1 1 1 name35 24 name35
1 1 1 name35 26 name35
1 1 1 name35 27 name35
1 1 1 name35 28 name35
1 1 1 name35 29 name35
1 1 1 name35 31 name35
1 1 1 name35 32 name35
1 1 1 name35 33 name35
1 1 1 name35 34 name35
28 rows selected.
TEST@orcl_11g>
TEST@orcl_11g> -- a better way:
TEST@orcl_11g> DROP INDEX ft_table_name
2 /
Index dropped.
TEST@orcl_11g> DROP INDEX ft_table_code
2 /
Index dropped.
TEST@orcl_11g> BEGIN
2
3 BEGIN
4 ctx_ddl.drop_preference ('my_multi');
5 EXCEPTION WHEN OTHERS THEN NULL; -- Ignore errors if the preference is not there
6 END;
7
8 ctx_ddl.create_preference ('my_multi', 'MULTI_COLUMN_DATASTORE');
9 ctx_ddl.set_attribute ('my_multi', 'COLUMNS', 't1_name, t1_code');
10
11 END;
12 /
PL/SQL procedure successfully completed.
TEST@orcl_11g> CREATE INDEX ft_table_name_code ON t1 (t1_name)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS
4 ('lexer my_lexer
5 storage my_storage
6 stoplist my_stoplist
7 datastore my_multi')
8 /
Index created.
TEST@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'T1')
PL/SQL procedure successfully completed.
TEST@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'T2')
PL/SQL procedure successfully completed.
TEST@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'T3')
PL/SQL procedure successfully completed.
TEST@orcl_11g> SELECT T2.T2_PrmyInd, T3.T3_PK, T3.T3_DomainInd, T1.T1_Name, T1.T1_Code
2 FROM T1, T2, T3
3 WHERE contains (t1_name, 'name35') > 0
4 AND T1.T1_Root = T2.T2_ActFK
5 AND T2.T2_DomainFK = T3.T3_Pk
6 AND T2.T2_PrmyInd = 1
7 AND T3.T3_DomainInd = 1
8 AND T3_pK = 1
9 /
T2_PRMYIND T3_PK T3_DOMAININD T1_NAME T1_CODE
---------- ---------- ------------ --------------- ---------------
1 1 1 name35 1 name35
1 1 1 name35 2 name35
1 1 1 name35 3 name35
1 1 1 name35 4 name35
1 1 1 name35 6 name35
1 1 1 name35 7 name35
1 1 1 name35 8 name35
1 1 1 name35 9 name35
1 1 1 name35 11 name35
1 1 1 name35 12 name35
1 1 1 name35 13 name35
1 1 1 name35 14 name35
1 1 1 name35 16 name35
1 1 1 name35 17 name35
1 1 1 name35 18 name35
1 1 1 name35 19 name35
1 1 1 name35 21 name35
1 1 1 name35 22 name35
1 1 1 name35 23 name35
1 1 1 name35 24 name35
1 1 1 name35 26 name35
1 1 1 name35 27 name35
1 1 1 name35 28 name35
1 1 1 name35 29 name35
1 1 1 name35 31 name35
1 1 1 name35 32 name35
1 1 1 name35 33 name35
1 1 1 name35 34 name35
28 rows selected.
TEST@orcl_11g>
continuation
Barbara Boehmer, June 05, 2008 - 2:43 pm UTC
Apparently, my post was too long, so the results of the last query were cut off, so here it is again:
TEST@orcl_11g> SELECT T2.T2_PrmyInd, T3.T3_PK, T3.T3_DomainInd, T1.T1_Name, T1.T1_Code
2 FROM T1, T2, T3
3 WHERE contains (t1_name, 'name35') > 0
4 AND T1.T1_Root = T2.T2_ActFK
5 AND T2.T2_DomainFK = T3.T3_Pk
6 AND T2.T2_PrmyInd = 1
7 AND T3.T3_DomainInd = 1
8 AND T3_pK = 1
9 /
T2_PRMYIND T3_PK T3_DOMAININD T1_NAME T1_CODE
---------- ---------- ------------ --------------- ---------------
1 1 1 name35 1 name35
1 1 1 name35 2 name35
1 1 1 name35 3 name35
1 1 1 name35 4 name35
1 1 1 name35 6 name35
1 1 1 name35 7 name35
1 1 1 name35 8 name35
1 1 1 name35 9 name35
1 1 1 name35 11 name35
1 1 1 name35 12 name35
1 1 1 name35 13 name35
1 1 1 name35 14 name35
1 1 1 name35 16 name35
1 1 1 name35 17 name35
1 1 1 name35 18 name35
1 1 1 name35 19 name35
1 1 1 name35 21 name35
1 1 1 name35 22 name35
1 1 1 name35 23 name35
1 1 1 name35 24 name35
1 1 1 name35 26 name35
1 1 1 name35 27 name35
1 1 1 name35 28 name35
1 1 1 name35 29 name35
1 1 1 name35 31 name35
1 1 1 name35 32 name35
1 1 1 name35 33 name35
1 1 1 name35 34 name35
28 rows selected.
TEST@orcl_11g>
CONTAINS OR WRONG result
VKOUL, June 05, 2008 - 3:53 pm UTC
Thanks everbody jumping on this.
1. We cannot change stuff at this time as there are millions of statements like this in packages.
2. We use ANSI JOINS throughout for a valid reason, we cannot go back on that.
3. Defining multi-column preferences is not an option as it would require a substancial change on code side followed by regression test.
4. We cannot simply ask our customers to go on 11g.
Is there a patch from Oracle on this ?
Thanks
June 05, 2008 - 5:47 pm UTC
please utilize support (and I'd suggest a SMALLER TEST CASE, you can (should, will) make it smaller)
this is not support here
to identify if this is a known problem with a patch, you'll need to work with support to identify it.
CONTAINS OR WRONG result
VKOUL, June 05, 2008 - 4:08 pm UTC
The problem does not go away when you use NON-ANSI join, it is still there. If you take one CONTAINS out, then stuff comes back correct at least for this query.
FORCE an ORDERED HINT fetches correct results too with multiple CONTAINS for this query for ANSI or NON-ANSI JOIN.
I do not think ANSI JOIN is a problem here. It is something to do with Query Plan. It looks like that problem is somewhere deep inside.
<code>
SQL> SELECT T2.T2_PrmyInd, T3.T3_PK, T3.T3_DomainInd, T1.T1_Name, T1.T1_Code
2 FROM T1, T2, T3
3 WHERE T1.T1_Root = T2.T2_ActFK
4 AND T2.T2_DomainFK = T3.T3_Pk
5 AND T2.T2_PrmyInd = 1
6 AND T3.T3_DomainInd = 1
7 AND (contains (t1_name, 'name35') > 0
8 OR
9 contains(T1_Code, 'name35') > 0
10 )
11 AND T3_pK = 1
12 /
T2_PRMYIND T3_PK T3_DOMAININD T1_NAME T1_CODE
---------- ---------- ------------ --------------- -------
1 1 1 name35 1 name35
1 1 1 name35 2 name35
SQL>
SQL> l
1 SELECT --+ ORDERED
2 T2.T2_PrmyInd, T3.T3_PK, T3.T3_DomainInd, T1.T1_Name, T1.T1_Cod
3 FROM T1, T2, T3
4 WHERE T1.T1_Root = T2.T2_ActFK
5 AND T2.T2_DomainFK = T3.T3_Pk
6 AND T2.T2_PrmyInd = 1
7 AND T3.T3_DomainInd = 1
8 AND (contains (t1_name, 'name35') > 0
9 OR
10 contains(T1_Code, 'name35') > 0
11 )
12* AND T3_pK = 1
SQL>
SQL> /
T2_PRMYIND T3_PK T3_DOMAININD T1_NAME T1_CODE
---------- ---------- ------------ --------------- -------
1 1 1 name35 1 name35
1 1 1 name35 2 name35
1 1 1 name35 3 name35
1 1 1 name35 4 name35
1 1 1 name35 6 name35
1 1 1 name35 7 name35
1 1 1 name35 8 name35
1 1 1 name35 9 name35
1 1 1 name35 11 name35
1 1 1 name35 12 name35
1 1 1 name35 13 name35
1 1 1 name35 14 name35
1 1 1 name35 16 name35
1 1 1 name35 17 name35
1 1 1 name35 18 name35
1 1 1 name35 19 name35
1 1 1 name35 21 name35
1 1 1 name35 22 name35
1 1 1 name35 23 name35
1 1 1 name35 24 name35
1 1 1 name35 26 name35
1 1 1 name35 27 name35
1 1 1 name35 28 name35
1 1 1 name35 29 name35
1 1 1 name35 31 name35
1 1 1 name35 32 name35
1 1 1 name35 33 name35
1 1 1 name35 34 name35
28 rows selected.
SQL>
</code>
for Vkoul
Barbara Boehmer, June 05, 2008 - 4:31 pm UTC
Vkoul,
If you really want to track down the source of the problem, you can try comparing explained plans and do some tracing. However, I believe that you are going to find that any solution that uses multiple contains clauses with or conditions may or may not produce the correct results, depending on the plan chosen. The only realistic solutions involve using just one contains clause. One other method of using just one contains clause is to create a materialized view that concatenates the two columns to be searched and create your context index on the materialized view.
Barbara
CONTAINS OR WRONG result
VKOUL, June 05, 2008 - 11:35 pm UTC
The problem only happens on 9.2.0.7 and in 9.2.0.8 the queries give right results. Oracle replied back that this problem is due to BUG 3393866 on 9.2.0.7.
Thanks all for your help.
create one text index on mutlple column and multiple tables, and meet outofmemory isue.
lsllcm, March 11, 2009 - 7:36 am UTC
I try to create one text index on mutlple column and multiple tables, and meet outofmemory isue. The db is oracle 10.2.0.4 linux version.
Below is my test case:
1.
connect jacky/jacky
2.
grant select on b1permit to ctxsys;
grant select on b3addres to ctxsys;
grant select on b3parcel to ctxsys;
3. connect ctxsys/ctxsys, create procedure
create or replace procedure full_text_index( p_id in rowid, p_lob IN
OUT clob )
as
begin
for x in ( select dept_code,id1,id2,id3,text1
from jacky.ta
where rowid = p_id )
loop
dbms_lob.writeappend( p_lob, length(x.text1), x.text1);
for y in ( select text2
from jacky.tb
where dept_code = x.dept_code
and id1 = x.id1
and id2 = x.id2
and id3 = x.id3
)
loop
dbms_lob.writeAppend( p_lob, length(y.text2), y.text2 );
end loop;
for y in ( select text3
from jacky.tc
where dept_code = x.dept_code
and id1 = x.id1
and id2 = x.id2
and id3 = x.id3
)
loop
dbms_lob.writeAppend( p_lob, length(y.text3), y.text3);
end loop;
end loop;
end;
/
4. connect jacky/jacky
exec ctx_ddl.drop_preference('foo_user_datastore');
exec ctx_ddl.drop_preference('my_lexer');
begin
ctx_ddl.create_preference( 'foo_user_datastore',
'user_datastore' );
ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure',
'ctxsys.full_text_index' );
end;
/
begin
ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
end;
/
5. create text index
create index t1_idx on b1permit(B1_ACCESS_BY_ACA)
indextype is ctxsys.context
parameters( 'datastore foo_user_datastore lexer my_lexer memory
1073741824');
6. get out of memory error
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drueixe
ORA-04030: out of process memory when trying to allocate 65548 bytes
(CTX PRM heap,draccbx:message buffer)
ORA-06512: at "CTXSYS.DRUE", line 191
ORA-06512: at "CTXSYS.DRUE", line 49
ORA-06512: at "CTXSYS.DRUE", line 147
ORA-06512: at "CTXSYS.D
ORA-04030: out of process memory when trying to allocate 16940 bytes
(pga heap,kgh stack)
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364
March 12, 2009 - 7:30 am UTC
so, what would I need to do to reproduce this? If you cannot give that to me, I'll have to refer you to support. You give no test case (no tables, no instructions as to how to populate tables) I can use.
create one text index on mutlple column and multiple tables, and meet outofmemory isue
Jacky, March 17, 2009 - 4:20 am UTC
I cannot replicate the issue with simple data as all_objects. I have gzip the data as 1m file. But I cannot upload here. Sorry my abruptness, I only can mail it to you (I get your mail from oracle site).
My database is linux 10.2.0.4 32bit.
I simply the test case as below:
-----------------------test case -------------------
create table t1 (object_name varchar2(100));
connect jacky/jacky
grant select on t1 to ctxsys;
connect ctxsys/ctxsys
create or replace procedure full_text_index( p_id in rowid, p_lob IN OUT clob )
as
begin
for x in ( select b1_special_text
from accela.t1
where rowid = p_id )
loop
dbms_lob.writeappend( p_lob, length(x.b1_special_text), x.b1_special_text);
end loop;
end;
/
grant execute on full_text_index to jacky;
connect jacky/jacky
exec ctx_ddl.drop_preference('foo_user_datastore');
exec ctx_ddl.drop_preference('my_lexer');
begin
ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'ctxsys.full_text_index' );
end;
/
begin
ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
end;
/
alter table t1 add c1 char(1);
create index t1_idx on t1(c1)
indextype is ctxsys.context
parameters( 'datastore foo_user_datastore memory 1073741824');
Thanks
create one text index on mutlple column and multiple tables, and meet outofmemory isue
Jacky, March 17, 2009 - 9:26 pm UTC
I cannot replicate the issue with simple data as all_objects. I have gzip the data as 1m file. But
I cannot upload here. Sorry my abruptness, I only can mail it to you (I get your mail from oracle
site).
My database is linux 10.2.0.4 32bit.
I simply the test case as below:
-----------------------test case -------------------
import table t1 from email attachemnt dump file
connect jacky/jacky
grant select on t1 to ctxsys;
connect ctxsys/ctxsys
create or replace procedure full_text_index( p_id in rowid, p_lob IN OUT clob )
as
begin
for x in ( select b1_special_text
from accela.t1
where rowid = p_id )
loop
dbms_lob.writeappend( p_lob, length(x.b1_special_text), x.b1_special_text);
end loop;
end;
/
grant execute on full_text_index to jacky;
connect jacky/jacky
exec ctx_ddl.drop_preference('foo_user_datastore');
exec ctx_ddl.drop_preference('my_lexer');
begin
ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'ctxsys.full_text_index' );
end;
/
begin
ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
end;
/
alter table t1 add c1 char(1);
create index t1_idx on t1(c1)
indextype is ctxsys.context
parameters( 'datastore foo_user_datastore memory 1073741824');
Thanks
problem with &
Branka Bowman, July 16, 2009 - 4:39 pm UTC
When I execute
exec ASP.quick_application_search('S & H',:p_rc)
I get "Enter value for h: " because it is after "&"
How can I solve this?
If I use AND, everuthing work fine.
July 16, 2009 - 6:02 pm UTC
SQL> set define off
problem with &
Branka Bowman, July 16, 2009 - 4:52 pm UTC
I have problem with & when using your example
1 select varchar2_col, clob_col1, clob_col2
2 from t1, t2, t3
3 where t1.id = t2.id
4 and t1.id = t3.id
5* and contains( varchar2_col, 'this & that & thing' ) > 0
/
Enter value for that:
Enter value for thing:
old 5: and contains( varchar2_col, 'this & that & thing' ) > 0
new 5: and contains( varchar2_col, 'this ' ) > 0
If I use AND instead, it work fine
July 16, 2009 - 6:03 pm UTC
SQL> set define off
& is a 'special' character to sqlplus by default, just disable it.
problem with special characters. "-"
Branka, July 23, 2009 - 1:36 pm UTC
Tom,
I used you example to test problem that I have. I have to search a lot of text that has "-" special character.
I used your example to see if it would work in your procedure, and it does not. In your case I get nothing back, in my I get too many data back.
Why oracle text has problem with "-" character, and how can I solve it. In my search, I wanted to search for that exactly pattern, (F-123-A), and I tried with "F-123-A", thinking that if would help, but it did not.
branka> select varchar2_col, clob_col1, clob_col2
2 from t1, t2, t3
3 where t1.id = t2.id
4 and t1.id = t3.id
5 and contains( varchar2_col, 'this & that & thing' ) > 0;
VARCHAR2_COL
--------------------------------------------------------------------------------
CLOB_COL1
----------------------------------------------------------------------------------------------------
CLOB_COL2
----------------------------------------------------------------------------------------------------
this
That
and the other thing
branka> update t1 set VARCHAR2_COL='BB' where id=1;
1 row updated.
branka> commit;
Commit complete.
branka> drop index t1_idx;
Index dropped.
branka>
branka>
branka> exec ctx_ddl.drop_preference('foo_user_datastore');
PL/SQL procedure successfully completed.
branka> exec ctx_ddl.drop_preference('my_lexer');
PL/SQL procedure successfully completed.
branka>
branka> begin
2 ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
3 ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'glue_them_together' );
4 end;
5 /
PL/SQL procedure successfully completed.
branka>
branka> begin
2 ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
3 ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
4 ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
5 end;
6 /
PL/SQL procedure successfully completed.
branka>
branka>
branka> create index t1_idx on t1(varchar2_col)
2 indextype is ctxsys.context
3 parameters( 'datastore foo_user_datastore lexer my_lexer');
Index created.
branka>
branka>
branka> select varchar2_col, clob_col1, clob_col2
2 from t1, t2, t3
3 where t1.id = t2.id
4 and t1.id = t3.id
5 and contains( varchar2_col, 'BB & that & thing' ) > 0;
VARCHAR2_COL
--------------------------------------------------------------------------------
CLOB_COL1
----------------------------------------------------------------------------------------------------
CLOB_COL2
----------------------------------------------------------------------------------------------------
BB
That
and the other thing
branka> update t1 set VARCHAR2_COL='F-123-A' where id=1;
1 row updated.
branka> commit;
Commit complete.
branka> drop index t1_idx;
Index dropped.
branka>
branka>
branka> exec ctx_ddl.drop_preference('foo_user_datastore');
PL/SQL procedure successfully completed.
branka> exec ctx_ddl.drop_preference('my_lexer');
PL/SQL procedure successfully completed.
branka> begin
2 ctx_ddl.create_preference( 'foo_user_datastore', 'user_datastore' );
3 ctx_ddl.set_attribute( 'foo_user_datastore', 'procedure', 'glue_them_together' );
4 end;
5 /
PL/SQL procedure successfully completed.
branka>
branka> begin
2 ctx_ddl.create_preference( 'my_lexer', 'BASIC_LEXER' );
3 ctx_ddl.set_attribute( 'my_lexer', 'base_letter', 'YES');
4 ctx_ddl.set_attribute( 'my_lexer', 'mixed_case', 'YES' );
5 end;
6 /
PL/SQL procedure successfully completed.
branka>
branka>
branka> create index t1_idx on t1(varchar2_col)
2 indextype is ctxsys.context
3 parameters( 'datastore foo_user_datastore lexer my_lexer');
Index created.
branka> select varchar2_col, clob_col1, clob_col2
2 from t1, t2, t3
3 where t1.id = t2.id
4 and t1.id = t3.id
5 and contains( varchar2_col, 'F-123-A & that & thing' ) > 0;
no rows selected
July 26, 2009 - 7:04 am UTC
Tuning of using "Contains" on multiple columns on multiple tables
Sri, September 10, 2010 - 4:11 am UTC
Hi Tom,
I have gone throught this post and could not find if it is possible to tune "contains" clause on two tables each having 2 columns which are indexed. I'll explain what I am looking for.
I have 2 very long tables (table1 and table2) each having 2 varchar2 columns (table1 = t1_col1, t1_col2 and table2 = t2_col1, t2_col2). There is no key field join existing between these tables (e.g. PK say id etc). My requirement is to produce report from these two tables, and select records where (table2.t2_col1 is a part of table1.t1_col1) AND (table2.t2_col2 is a part of table1.t1_col2). Now to do this, I've these options -
A) Using LIKE
select *
from table1, table2
where table2.t2_col1 like '%'||table1.t1_col1||'%' AND
table2.t2_col2 like '%'||table1.t1_col2||'%';
B) Using INSTR
select *
from table1, table2
where instr(table1.t1_col1,table2.t2_col1) AND
instr(table1.t1_col2,table2.t2_col2);
C) Using CONTAINS clause (after context indexing both columns of both tables)
select *
from table1, table2
where contains instr(table1.t1_col1,table2.t2_col1) > 0 AND
contains(table1.t1_col2,table2.t2_col2) > 0;
My query is is there any better way of doing this in terms of performance as all of these are incurring FTS.
Any help will be greatly appreciated.
Best regards.
September 13, 2010 - 7:33 am UTC
of course this will take a full scan of at least one of the tables - full scans are NOT evil.
But your query is bordering on evil - think about it, ugh.
the best you can hope for - short of a cartesian product - would be a full scan of the one table - with an index lookup for every single row that comes back - into the other table.
Sort of like this procedural psuedo code:
for x in (select * from t1)
loop
for y in (select rowid from t2 where <some lookup>)
loop
get the row from t2 by rowid and output t1 x t2
end loop
end loop
assuming you have a schema such as:
ops$tkyte%ORA11GR2> create table t1 ( c1 varchar2(100), c2 varchar2(100) );
Table created.
ops$tkyte%ORA11GR2> create table t2 ( c1 varchar2(100), c2 varchar2(100) );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t1 values ( 'hello', 'world' );
1 row created.
ops$tkyte%ORA11GR2> insert into t2 values ( 'this is hello', 'and this is world' );
1 row created.
ops$tkyte%ORA11GR2> insert into t2 values ( 'this is hello again', 'and this is world again' );
1 row created.
ops$tkyte%ORA11GR2> insert into t2 values ( 'this is something else', 'and this is another thing' );
1 row created.
ops$tkyte%ORA11GR2> insert into t2 values ( 'this is something else again', 'and this is another thing again' );
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create index t2_c1_idx on t2(c1) indextype is ctxsys.context;
Index created.
ops$tkyte%ORA11GR2> create index t2_c2_idx on t2(c2) indextype is ctxsys.context;
Index created.
You can query:
ops$tkyte%ORA11GR2> select t1.*, cast( multiset(
2 select rowid
3 from t2
4 where contains( c1, t1.c1 ) > 0
5 intersect
6 select rowid
7 from t2
8 where contains( c2, t1.c2 ) > 0
9 ) as sys.odcivarchar2List ) rids
10 from t1
11 /
C1
-------------------------------------------------------------------------------
C2
-------------------------------------------------------------------------------
RIDS
-------------------------------------------------------------------------------
hello
world
ODCIVARCHAR2LIST('AAAU12AAEAAAIDPAAA', 'AAAU12AAEAAAIDPAAB')
that mimics most of the psuedo code above, we have to flatten the result now:
ops$tkyte%ORA11GR2> select c1, c2, column_value
2 from (
3 select t1.*, cast( multiset(
4 select rowid
5 from t2
6 where contains( c1, t1.c1 ) > 0
7 intersect
8 select rowid
9 from t2
10 where contains( c2, t1.c2 ) > 0
11 ) as sys.odcivarchar2List ) rids
12 from t1
13 ), TABLE( rids )
14 /
C1
-------------------------------------------------------------------------------
C2
-------------------------------------------------------------------------------
COLUMN_VALUE
-------------------------------------------------------------------------------
hello
world
AAAU12AAEAAAIDPAAA
hello
world
AAAU12AAEAAAIDPAAB
and then we can join to it:
ops$tkyte%ORA11GR2> select t1.c1, t1.c2, t2.c1, t2.c2
2 from (
3 select c1, c2, column_value
4 from (
5 select t1.*, cast( multiset(
6 select rowid
7 from t2
8 where contains( c1, t1.c1 ) > 0
9 intersect
10 select rowid
11 from t2
12 where contains( c2, t1.c2 ) > 0
13 ) as sys.odcivarchar2List ) rids
14 from t1
15 ), TABLE( rids )
16 ) t1, t2
17 where t2.rowid = chartorowid( column_value )
18 /
C1
-------------------------------------------------------------------------------
C2
-------------------------------------------------------------------------------
C1
-------------------------------------------------------------------------------
C2
-------------------------------------------------------------------------------
hello
world
this is hello
and this is world
hello
world
this is hello again
and this is world again
But bear in mind - math, physics, speed of light - things like that will prevent this sort of search from being "fast" in any sense of the word.
Think about what if...
a) the index lookup is fast (1/1000th of a second - that would be really fast, probably faster than it can be)
b) t1 has 1,000,000 rows - pretty small table - what I use to test on my LAPTOP with...
then it would take at least 16.666 minutes (assuming the full scan of t1 takes 0.00000 seconds).
And if the index takes 1/100th of a second (probably more realistic....), you are talking 2.7777 hours....
You might consider parallelizing that if you have the resources...
re: comparing 2 tables and 2 columns
Stew Ashton, September 14, 2010 - 4:14 pm UTC
Here's a suggestion for doing one compare rather than two, plus reducing somewhat the number of LIKE comparisons. Sure gobbles up the CPU! (Sorry about no output, I'm at home and formatting it correctly is time-consuming...)
drop table t1;
create table t1(c1 varchar2(100), c2 varchar2(100), other varchar2(300));
insert into t1
select 'c1 ' || level, 'c2 ' || (level-1), rpad('-', 300, '-')
from dual connect by level <= 10000;
drop table t2;
create table t2(c1 varchar2(100), c2 varchar2(100), other varchar2(300));
insert into t2
select 'c1 ' || (level*10+1), 'c2 ' || (level*10), rpad('-', 300, '-')
from dual connect by level <= 10000;
create index idx_t1 on t1(length(c1), length(c2),c1||'%§%'||c2) compress 2;
create index idx_t2 on t2(length(c1), length(c2),'%'||c1||'%§%'||c2||'%') compress 2;
select /*+ gather_plan_statistics */ count(*) from t1, t2
where length(t2.c1) <= length(t1.c1)
and length(t2.c2) <= length(t1.c2)
and t1.c1||'%§%'||t1.c2 like '%'||t2.c1||'%§%'||t2.c2||'%';
select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS LAST'));
Correction
Stew Ashton, September 15, 2010 - 1:23 am UTC
Sorry, there were some unnecessary '%' on t1 (in the index and in the query).
create index idx_t1 on t1(length(c1), length(c2),c1||'§'||c2) compress 2;
create index idx_t2 on t2(length(c1), length(c2),'%'||c1||'%§%'||c2||'%') compress 2;
select /*+ gather_plan_statistics */ count(*) from t1, t2
where length(t2.c1) <= length(t1.c1)
and length(t2.c2) <= length(t1.c2)
and t1.c1||'§'||t1.c2 like '%'||t2.c1||'%§%'||t2.c2||'%';
Using "Oracle Text" CONTAINS syntax on multiple columns
Sri, September 15, 2010 - 1:26 am UTC
Hi Tom,
Your worked example was most useful and I understand it fully.
However I think somehow in your reply it is coming up with version of 8.1.7 so probably in my original comment I did some mistake as my actual version where I am trying this is 10.2.0.4. [I think the person who raised this thread in first place had this version and it is carrying forward from there].
On trying your worked example (t1/t2) in two different databases (10.2.0.2/10.2.0.4) I get this "ORA-03001: unimplemented feature". I'm assuming that this example was meant to be run on 8.1.7 which is causing this ?
Many Thanks again.
Regards.
September 15, 2010 - 8:12 am UTC
I did mine in 11gr2 - which accepted the set operation in the multi-set but 10g did not (interest, union, etc...)
You can try this instead:
ops$tkyte%ORA10GR2> select t1.c1, t1.c2, t2.c1, t2.c2
2 from (
3 select c1, c2, column_value
4 from (
5 select t1.*, cast( multiset(
6 select rowid
7 from t2
8 where contains( c1, t1.c1 ) > 0
9 and contains( c2, t1.c2 ) > 0
10 ) as sys.odcivarchar2List ) rids
11 from t1
12 ), TABLE( rids )
13 ) t1, t2
14 where t2.rowid = chartorowid( column_value )
15 /
It'll use the index on either C1 or C2 to "find" the row that might have your hit and then use an access on that same row to process the contains.
Using "Oracle Text" CONTAINS syntax on multiple columns
Sri, September 15, 2010 - 1:28 am UTC
Sorry forgot to mention that, the error ORA-03001 comes up at the first select statment ie.
SELECT t1.*, CAST( MULTISET(
SELECT ROWID
FROM t2
WHERE contains( c1, t1.c1 ) > 0
INTERSECT
SELECT ROWID
FROM t2
WHERE contains( c2, t1.c2 ) > 0
) AS sys.odcivarchar2List ) rids
FROM t1;
Thanks.
Using "Oracle Text" CONTAINS syntax on multiple columns
Sri, September 22, 2010 - 8:01 am UTC
Many thanks Tom, this is what I was after.
Cheers.
String tokenaizer
Ramki, January 20, 2012 - 3:03 am UTC
Hi Tom,
I am looking for String tokenizer and search a row based on token.
We have formula like : lstraf.cs_u_pln_udp_octet_rec_lsbts+lstraf.cs_u_pln_udp_octet_sent_lsbts
I want to search for "cs_u_pln_udp_octet_sent_lsbts"
select * from omc.test where contains( formula, 'cs_u_pln_udp_octet_rec_lsbts' ) > 0;
I tried following :
drop table test;
create table test ( formula varchar2 (400) );
insert into test values ('lstraf.cs_u_pln_udp_octet_rec_lsbts+lstraf.cs_u_pln_udp_octet_sent_lsbts');
create index t_idx on test(formula) indextype is ctxsys.context;
select token_text from dr$t_idx$i;
TOKEN_TEXT
--------------------
CS
LSBTS
LSTRAF
OCTET
PLN
REC
SENT
U
UDP
9 rows selected
Tokens are splited by "_"
I want tokes to be :
lstraf
cs_u_pln_udp_octet_rec_lsbts
cs_u_pln_udp_octet_sent_lsbts
i.e split by [+,-,*,%,^, ),(,. ]
How can I do this.
January 20, 2012 - 9:48 am UTC
search this site for the word
printjoins
contains in hierarchical SQL
A reader, January 25, 2012 - 3:35 am UTC
Hi Tom,
I am try use contains in hierarchical SQL.
But I am getting following error
SQL Error: ORA-20000: Oracle Text error:
DRG-10599: column is not indexed
I created sample table & data.
I my application this table will have 30,000 rows , using regexp_substr ( ) is slow,taking ~10 min. So I am trying to tokenize my formula column , and make it faster.
drop table t ;
CREATE TABLE T ( id VARCHAR2(10), formula VARCHAR2(50));
insert into t values ('f1', 'KPI1' );
insert into t values ('f2', 'KPI2' );
insert into t values ('f3', 'KPI3' );
insert into t values ('f4', 'KPI4' );
insert into t values ('f5', 'KPI5' );
INSERT INTO T VALUES ('f6', 'KPI6' );
insert into t values ('f7', '( f1 * f2 )' );
insert into t values ('f8', '( f4 + f3 ) ' );
insert into t values ('f9', '( f3 * f4 ) + f5' );
insert into t values ('f10','( f5 + f6 ) / f1' );
insert into t values ('f11','( f7 * f6 )' );
insert into t values ('f12','( f8 * f2 )' );
commit;
create index txt_formula_T on T(formula) indextype is ctxsys.context; --parameters('lexer my_lexer');
SELECT t.id , t.formula
from t
where contains (t.formula, 'f2' ) >0;
--- Contains is working
select ID, prior(ID),FORMULA, level
FROM T
start with id in (select id from t )
connect by nocycle regexp_substr(formula, '[^a-zA-Z0-9_]' ||prior(id) ||'[^a-zA-Z0-9_]') is not null;
-- regexp_substr ( ) works fine
select ID, prior(ID),FORMULA, level
FROM T
start with id in (select id from t )
connect by contains (formula, prior(id) ) >0;
SQL Error: ORA-20000: Oracle Text error:
DRG-10599: column is not indexed
Regards
ramki
January 26, 2012 - 10:05 am UTC
sorry, the contains operation is not going to work there. You are going to want to do that one procedurally - I would suggest "compiling" that table when you modify it - you won't be modifying it over and over and over again (but you'll probably be querying it over and over and over again - much more than modifying it I would guess)
I would have yet another table you "compile" this table into after you modify it - so that you just have to retrieve a single formula at runtime to evaluate.
how to arrange multiple column
rakta, March 28, 2023 - 9:00 am UTC
lik own excel sheet cell
i want to multiple column export own cell where its auto arrange
columnwise
example
1 RAM
Thapa
Magar
March 28, 2023 - 2:23 pm UTC
I've no idea what you're trying to do here or how this relates to the original post.