| Contains Clause is not working on certain scenario (merged 3) [message #494517] |
Tue, 15 February 2011 07:15  |
oraranjangmailcom
Messages: 67
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 (merged 3) [message #494619 is a reply to message #494517] |
Wed, 16 February 2011 01:31  |
 |
Barbara Boehmer
Messages: 9101
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 |
oraranjangmailcom
Messages: 67
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
