OraFAQ Forum: Text & interMedia » which index is good ctxsys.context/ctxsys.CTXCAT

Home » Server Options » Text & interMedia » which index is good ctxsys.context/ctxsys.CTXCAT

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

which index is good ctxsys.context/ctxsys.CTXCAT [message #640099]

Wed, 22 July 2015 05:34

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

hi All,

I have table which contains billion records,there was no index on one column.

1)create table test1(id number,name varchar2(250);

2)i inserted 5 million records in the above table.

3)Now i ran the query as below

select * from test1 where name like '%raj%';

it is going for full scan and executing more than 5 hours.

4)Now i tried to create index on this column using the below index

create index i_indexname on test1(name) indextype is ctxsys.context

Now i got output with in 15 minutes.

But is there any problem by using above index in Database side.
otherwise can anyone suggest any other method to implement the same.

Regards,
rajesh

Report message to a moderator

Re: which index is good ctxsys.context/ctxsys.CTXCAT [message #640103 is a reply to message #640099]

Wed, 22 July 2015 06:30

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

mvrkr44 wrote on Wed, 22 July 2015 05:34

But is there any problem by using above index in Database side.

What kind of problems would you anticipate?
And what do you mean by "using index in Database side"? where/how else would you use it?

Quote:

otherwise can anyone suggest any other method to implement the same.

What other method would you envision? Looks to me like your use case is a textbook example of what context indexes were designed for.

Report message to a moderator

Re: which index is good ctxsys.context/ctxsys.CTXCAT [message #640104 is a reply to message #640103]

Wed, 22 July 2015 06:47

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

hi EdStevens,

I created the ctxsys.context index on name column.
If i insert/update the test_1 table with new records.
Every time i need to run the

begin
ctx_ddl.sync_index('indexname') ;
end;
/

If i didn't run above command i am not getting the records in the below query for new records

select * from test_1 where contains(name,'%raj%')>0

In my current production,they are not allowing for ctx_ddl.sync_index package for every 5 minutes.
So i am trying any other method for retriving data very fast.

Regards,
Rajesh

Report message to a moderator

Re: which index is good ctxsys.context/ctxsys.CTXCAT [message #640138 is a reply to message #640103]

Wed, 22 July 2015 23:56

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

Hi,

if I use wildcard query with "%" in front of the string, the column index is not being used and a full table scan is happening.

It looks like there isn't a definitive suggestion on how to improve this kind of query, but perhaps you could share some valuable information from your experience on how to optimize the following query:

SELECT *
FROM myTable
WHERE UPPER(CustomerName) like '%ABC%'
OR UPPER(IndemnifierOneName) like '%ABC%'
OR UPPER(IndemnifierTwoName) like '%ABC%';
...where all 3 columns are of type varchar2(100) and ABC is a value of variable input parameter.

@All suggesting CONTEX index, please note my data gets updated every second of the day every day and this index requires re-syncing, hence it's not a good option for a table of 1.5 million rows.Please suggest any other solution.

Regards,
Rajesh

Report message to a moderator

Re: which index is good ctxsys.context/ctxsys.CTXCAT [message #640151 is a reply to message #640138]

Thu, 23 July 2015 02:06

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

Options:

1. Use 3 function-based indexes using upper and your column names.

2. Use one context index with a multi_column_datastore and transactional in your index parameters.

3. Use 3 ctxcat indexes.

I would tend to go for option 2, as it would use only one index and transactional eliminates the need for synchronization and optimization. It also allows you to add substring indexing for faster searches.

Report message to a moderator

Re: which index is good ctxsys.context/ctxsys.CTXCAT [message #640159 is a reply to message #640151]

Thu, 23 July 2015 03:02

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

Thanks Barbara Boehmer

Report message to a moderator

Re: which index is good ctxsys.context/ctxsys.CTXCAT [message #640160 is a reply to message #640151]

Thu, 23 July 2015 03:12

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

Hi Barbara Boehmer,

If i use option-2. for any new inserts/updates i need to run CTX_DDL.SYNC_INDEX package???

Regards,
Rajesh

Report message to a moderator

Re: which index is good ctxsys.context/ctxsys.CTXCAT [message #640165 is a reply to message #640151]

Thu, 23 July 2015 04:20

mvrkr44
Messages: 132
Registered: December 2012

Senior Member

hi

I used the below query for auto sync index
Create index i_name on myTable (CustomerName) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('SYNC ( ON COMMIT)').

But in production..every minute there will be thousands of records will bre insert/update/delete happening.
Is there any performance impact will be there on sync process.

Regards,
Rajesh

Report message to a moderator

Re: which index is good ctxsys.context/ctxsys.CTXCAT [message #640217 is a reply to message #640165]

Thu, 23 July 2015 16:51

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

If you use TRANSACTIONAL you don't need to synchronize or optimize, just like with the ctxcat index, but with more options.

Create index i_name on myTable (CustomerName) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('TRANSACTIONAL')

Report message to a moderator

Re: which index is good ctxsys.context/ctxsys.CTXCAT [message #640218 is a reply to message #640217]

Thu, 23 July 2015 17:05

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

Apparently, Oracle still recommends sync(on commit) when using transactional. The following is an excerpt from online documentation.

"TRANSACTIONAL

Specifies that documents can be searched immediately after they are inserted or updated. If a text index is created with TRANSACTIONAL enabled, then, in addition to processing the synchronized rowids already in the index, the CONTAINS operator will process unsynchronized rowids as well. Oracle Text does in-memory indexing of unsynchronized rowids and processes the query against the in-memory index.

TRANSACTIONAL is an index-level parameter and does not apply at the partition level.

You must still synchronize your text indexes from time to time (with CTX_DDL.SYNC_INDEX) to bring pending rowids into the index. Query performance degrades as the number of unsynchronized rowids increases. For that reason, Oracle recommends setting up your index to use automatic synchronization with the EVERY or ON COMMIT parameter. (See "SYNC (MANUAL | EVERY "interval-string" | ON COMMIT)".)

Transactional querying for indexes that have been created with the TRANSACTIONAL parameter can be turned on and off (for the duration of a user session) with the PL/SQL variable CTX_QUERY.disable_transactional_query. This is useful, for example, if you find that querying is slow due to the presence of too many pending rowids. Here is an example of setting this session variable:

exec ctx_query.disable_transactional_query := TRUE;

If the index uses AUTO_FILTER, queries involving unsynchronized rowids will require filtering of unsynchronized documents."

Report message to a moderator

Previous Topic:	wild card search on CLOB not giving require result in 12C
Next Topic:	Searching Synonyms

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 07 20:05:24 CDT 2024