Home » Server Options » Text & interMedia » Contains Clause is not working on certain scenario (merged 3) (Oracle 10g, Windows, Oracle Client 10g)
Contains Clause is not working on certain scenario (merged 3) [message #494517] Tue, 15 February 2011 07:15 Go to next message
oraranjangmailcom
Messages: 65
Registered: April 2010
Member
Hi

I have created a oracle text index(code is as below) but on one scenario it is not selecting a record. I don't understand why it's happening. Requesting for some comments on this ...

SQL> create table t (a varchar2(100));
    Table created.

SQL> insert into t values('A');
    1 row created.

SQL> insert into t values('B');
    1 row created.

SQL> insert into t values('AB ABC ABCD');
    1 row created.

SQL> COMMIT;
    Commit complete.

SQL> CREATE INDEX
  2     idx_ct ON t (a)
  3     indextype is ctxsys.context;
Index created.

[b]SQL> [/b]select a from t where contains(a,'A')>0;

no rows selected

[b]SQL> [/b]SELECT A FROM T WHERE CONTAINS(A,'B')>0;

A
----------------------------------------------------
B

[b]SQL> SELECT A FROM T WHERE CONTAINS(A,'A')>0;

no rows selected[/b]

[b]SQL> [/b]SELECT A FROM T WHERE CONTAINS(A,'AB')>0;

A
----------------------------------------------------
AB ABC ABCD


Table "t" has a record of value "A" & "B" but "A" is not getting selected(highlighted in code).

Thanks in Advance,
Ranjan
Re: Contains Clause is not working on certain scenario [message #494542 is a reply to message #494517] Tue, 15 February 2011 11:47 Go to previous messageGo to next message
Michel Cadot
Messages: 60058
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can repeat it every couple of hours if you want but the most likely is that you will have LESS answers.

Regards
Michel
Re: Contains Clause is not working on certain scenario [message #494603 is a reply to message #494542] Tue, 15 February 2011 23:47 Go to previous messageGo to next message
oraranjangmailcom
Messages: 65
Registered: April 2010
Member
Hi,

Anyidea why it's happening like that. Please let me know, if I can do any further steps in incorporating Index, so that I can select above criteria.

Thanks,
Ranjan
Re: Contains Clause is not working on certain scenario (merged 3) [message #494619 is a reply to message #494517] Wed, 16 February 2011 01:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
If you do not specify a stoplist when you create your index, Oracle Text uses the default_stoplist. It is a list of common words that are not indexed, such as the words "a" and "the". If you want to index and search for such words, then you need to use an empty_stoplist, as shown below.


SCOTT@orcl_11gR2> create table t (a varchar2(100))
2 /

Table created.

SCOTT@orcl_11gR2> insert all
2 into t values('A')
3 into t values('B')
4 into t values('AB ABC ABCD')
5 select * from dual
6 /

3 rows created.

SCOTT@orcl_11gR2> CREATE INDEX idx_ct ON t (a)
2 indextype is ctxsys.context
3 parameters ('stoplist ctxsys.empty_stoplist')
4 /

Index created.

SCOTT@orcl_11gR2> select a from t where contains(a,'A')>0
2 /

A
--------------------------------------------------------------------------------
A

1 row selected.

SCOTT@orcl_11gR2> SELECT A FROM T WHERE CONTAINS(A,'B')>0
2 /

A
--------------------------------------------------------------------------------
B

1 row selected.

SCOTT@orcl_11gR2> SELECT A FROM T WHERE CONTAINS(A,'A')>0
2 /

A
--------------------------------------------------------------------------------
A

1 row selected.

SCOTT@orcl_11gR2> SELECT A FROM T WHERE CONTAINS(A,'AB')>0
2 /

A
--------------------------------------------------------------------------------
AB ABC ABCD

1 row selected.

SCOTT@orcl_11gR2>
Re: Contains Clause is not working on certain scenario (merged 3) [message #494651 is a reply to message #494619] Wed, 16 February 2011 05:48 Go to previous messageGo to next message
oraranjangmailcom
Messages: 65
Registered: April 2010
Member
This was helpful... thanks

One quick question..

When we truncate the table, the context index data will also be deleted - please confirm, if so then please confirm, that no need to synchronized or refresh the Oracle Text Index.
I read below comments in google(http://download.oracle.com/docs/),

"Unlike a regular database index, the Oracle Text index is not dynamically updated with each insert or update of information. Rather, the index must be refreshed (or synchronized) periodically, using the Oracle Text stored procedure ctx_ddl.sync_index. "

Note: Above comments are on insert & update of rows, I need confirmation on Delete.

Thanks in Advance,
Ranjan
Re: Contains Clause is not working on certain scenario (merged 3) [message #494693 is a reply to message #494651] Wed, 16 February 2011 10:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
When Oracle text indexes are created, it automatically creates some domain index tables of the form dr$index_name$i and a few others with other ending letters instead of i. Delete and truncate have different effects on those tables. Delete will not remove committed, synchronized data from those tables. Synchronization will not remove data from those tables. Optimize or truncate removes the data. Please see the demonstration below.

SCOTT@orcl_11gR2> create table test_tab (test_col  varchar2 (30))
  2  /

Table created.

SCOTT@orcl_11gR2> insert into test_tab values ('test data')
  2  /

1 row created.

SCOTT@orcl_11gR2> create index test_idx on test_tab (test_col)
  2  indextype is ctxsys.context
  3  /

Index created.

SCOTT@orcl_11gR2> -- deletion does not remove data from domain index tables:
SCOTT@orcl_11gR2> delete from test_tab
  2  /

1 row deleted.

SCOTT@orcl_11gR2> commit
  2  /

Commit complete.

SCOTT@orcl_11gR2> select token_text from dr$test_idx$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
DATA
TEST

2 rows selected.

SCOTT@orcl_11gR2> -- synchronization does not remove data from domain index tables:
SCOTT@orcl_11gR2> exec ctx_ddl.sync_index ('test_idx')

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select token_text from dr$test_idx$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
DATA
TEST

2 rows selected.

SCOTT@orcl_11gR2> -- optimization removes data from domain index tables:
SCOTT@orcl_11gR2> exec ctx_ddl.optimize_index ('test_idx', 'full')

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select token_text from dr$test_idx$i
  2  /

no rows selected

SCOTT@orcl_11gR2> insert into test_tab values ('test data')
  2  /

1 row created.

SCOTT@orcl_11gR2> commit
  2  /

Commit complete.

SCOTT@orcl_11gR2> exec ctx_ddl.sync_index ('test_idx')

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> delete from test_tab
  2  /

1 row deleted.

SCOTT@orcl_11gR2> commit
  2  /

Commit complete.

SCOTT@orcl_11gR2> exec ctx_ddl.sync_index ('test_idx')

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select token_text from dr$test_idx$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
DATA
TEST

2 rows selected.

SCOTT@orcl_11gR2> -- truncate removes data from domain index tables:
SCOTT@orcl_11gR2> truncate table test_tab
  2  /

Table truncated.

SCOTT@orcl_11gR2> select token_text from dr$test_idx$i
  2  /

no rows selected

SCOTT@orcl_11gR2>

Re: Contains Clause is not working on certain scenario (merged 3) [message #494765 is a reply to message #494693] Thu, 17 February 2011 00:57 Go to previous message
oraranjangmailcom
Messages: 65
Registered: April 2010
Member
Thanks ... Smile
Previous Topic: CTXSYS ERROR
Next Topic: Re: Contains Clause is not working on certain scenario (split from unrelated hijacked thread by bb)
Goto Forum:
  


Current Time: Thu Dec 25 19:39:27 CST 2014

Total time taken to generate the page: 0.07948 seconds