Home » Server Options » Text & interMedia » wild card search on CLOB not giving require result in 12C (Oracle 12 C Standard edition)
wild card search on CLOB not giving require result in 12C [message #638325] Tue, 09 June 2015 08:58 Go to next message
panot4u
Messages: 5
Registered: September 2006
Junior Member
Issue :- There is a CLOB Column (which is divided into section groups) on which we are doing blank wild card searching , which is not working and give the record which are not match to search criteria and this searching is gave proper result in Oracle 11 G.

==============================================================================================
Query For 12C :-
==============================================================================================
select indexedmetatext from n1_1433376000_1433462400 where contains(indexedmetatext,'((( ( % within BZ ) ) or ( ( % within BY ) )) and (sdata(isprotocolname = ''http'')) )')>0

OutPut :- All rows are coming , present in table;

==============================================================================================
Query for 11g
==============================================================================================
select /*+ index ( v2_1430784000_1438560000 ( indexedmetatext)) */ indexedmetatext from v2_1430784000_1438560000 where contains(indexedmetatext,'((( ( % within BZ ) ) or ( ( % within BY ) )) and (sdata(isprotocolname = ''http'')) )')>0

OutPut :- only one row come .

Help
Re: wild card search on CLOB not giving require result in 12C [message #638338 is a reply to message #638325] Tue, 09 June 2015 13:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Your exact version may make a difference. Oracle may also consider that this is expected behavior and that it was a fluke that it worked the way you want in an earlier version. Searching for % is searching for all rows and therefore unnecessary. However, I am guessing that you may be building your query dynamically and that is why you have included it. You also have some unnecessary parentheses. It would help if you would provide a simplified test case and show that it produces the error for you, otherwise it is difficult to produce a comparable test case. I have provided an example below, showing that it works for me. Please try to provide something similar.

SCOTT@orcl12c> select banner from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE	12.1.0.1.0	Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

5 rows selected.

SCOTT@orcl12c> create table n1_1433376000_1433462400
  2    (isprotocolname	 varchar2(15),
  3  	indexedmetatext  clob)
  4  /

Table created.

SCOTT@orcl12c> insert into n1_1433376000_1433462400 (isprotocolname, indexedmetatext)
  2  values ('http', '<BZ>something</BZ><BY>whatever</BY>')
  3  /

1 row created.

SCOTT@orcl12c> insert into n1_1433376000_1433462400 (isprotocolname, indexedmetatext)
  2  values ('text', '<BZ>whatever</BZ><BY>something</BY>')
  3  /

1 row created.

SCOTT@orcl12c> create index test_index on n1_1433376000_1433462400 (indexedmetatext)
  2  indextype is ctxsys.context
  3  filter by isprotocolname
  4  parameters ('section group  ctxsys.auto_section_group')
  5  /

Index created.

SCOTT@orcl12c> -- your query:
SCOTT@orcl12c> select indexedmetatext
  2  from   n1_1433376000_1433462400
  3  where  contains
  4  	      (indexedmetatext,
  5  	       '((( ( % within BZ ) ) or ( ( % within BY ) )) and (sdata(isprotocolname = ''http'')) )') > 0
  6  /

INDEXEDMETATEXT
--------------------------------------------------------------------------------
<BZ>something</BZ><BY>whatever</BY>

1 row selected.

SCOTT@orcl12c> -- your query with unnecessary parentheses removed:
SCOTT@orcl12c> select indexedmetatext
  2  from   n1_1433376000_1433462400
  3  where  contains
  4  	      (indexedmetatext,
  5  	       '(% within BZ or % within BY) and
  6  		 sdata (isprotocolname = ''http'')') > 0
  7  /

INDEXEDMETATEXT
--------------------------------------------------------------------------------
<BZ>something</BZ><BY>whatever</BY>

1 row selected.

SCOTT@orcl12c> -- the query is the same as below:
SCOTT@orcl12c> select indexedmetatext
  2  from   n1_1433376000_1433462400
  3  where  contains
  4  	      (indexedmetatext,
  5  	       'sdata (isprotocolname = ''http'')') > 0
  6  /

INDEXEDMETATEXT
--------------------------------------------------------------------------------
<BZ>something</BZ><BY>whatever</BY>

1 row selected.

Re: wild card search on CLOB not giving require result in 12C [message #638360 is a reply to message #638325] Wed, 10 June 2015 01:21 Go to previous messageGo to next message
panot4u
Messages: 5
Registered: September 2006
Junior Member
Hi Barbara,

1. We are using standard edition.

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

I tried creating the same test case mentioned by you. results are different, table still returning all rows as earlier.

Steps:

SQL> create table n1_1433376000_1433462400
2 (isprotocolname varchar2(15),
3 indexedmetatext clob)
4 /

Table created.

SQL>
SQL> insert into n1_1433376000_1433462400 (isprotocolname, indexedmetatext) valu
es ('http', '<BZ>something</BZ><BY>whatever</BY>');

1 row created.

SQL> insert into n1_1433376000_1433462400 (isprotocolname, indexedmetatext) valu
es ('http', '<BT>whatever</BT>');

1 row created.

SQL> insert into n1_1433376000_1433462400 (isprotocolname, indexedmetatext) valu
es ('http', '<BX>something</BX><BA>whatever</BA>');

1 row created.

SQL> insert into n1_1433376000_1433462400 (isprotocolname, indexedmetatext) valu
es ('http', '<BM>something</BM><BN>whatever</BN>');

1 row created.

SQL> commit;

Commit complete.

SQL> create index test_index on n1_1433376000_1433462400 (indexedmetatext)
2 indextype is ctxsys.context
3 filter by isprotocolname
4 parameters ('section group ctxsys.auto_section_group')
5 /

Index created.


Output of First query.

SQL> select indexedmetatext
2 from n1_1433376000_1433462400
3 where contains (
4 indexedmetatext,
5 '((( ( % within BZ ) ) or ( ( % within BY ) )) and (sdata(isprotocolname =
''http'')) )') > 0;

INDEXEDMETATEXT
--------------------------------------------------------------------------------

<BZ>something</BZ><BY>whatever</BY>
<BT>whatever</BT>
<BX>something</BX><BA>whatever</BA>
<BM>something</BM><BN>whatever</BN>


Output of query with unnecessary parentheses removed:

SQL> select indexedmetatext
2 from n1_1433376000_1433462400
3 where contains
4 (indexedmetatext,
5 '(% within BZ or % within BY) and
6 sdata (isprotocolname = ''http'')') > 0
7 /

INDEXEDMETATEXT
-------------------------------------------------------------------------------

<BZ>something</BZ><BY>whatever</BY>
<BT>whatever</BT>
<BX>something</BX><BA>whatever</BA>
<BM>something</BM><BN>whatever</BN>

Re: wild card search on CLOB not giving require result in 12C [message #638363 is a reply to message #638360] Wed, 10 June 2015 01:39 Go to previous messageGo to next message
panot4u
Messages: 5
Registered: September 2006
Junior Member
One more update, If I am passing, what I am searching within BZ (i.e %something% within BZ) then it is returning the expected record.


SQL> select indexedmetatext
2 from n1_1433376000_1433462400
3 where contains
4 (indexedmetatext,
5 '(%something% within BZ)') >0
6 /

INDEXEDMETATEXT
--------------------------------------------------------------------------------

<BZ>something</BZ><BY>whatever</BY>

Re: wild card search on CLOB not giving require result in 12C [message #638367 is a reply to message #638363] Wed, 10 June 2015 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.

Re: wild card search on CLOB not giving require result in 12C [message #638404 is a reply to message #638360] Wed, 10 June 2015 19:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
As I said before, your query is the same as:

select indexedmetatext 
from   n1_1433376000_1433462400 
where  contains
         (indexedmetatext,
          'sdata (isprotocolname = ''http'')') > 0
/


So, it is returning the correct results. This is expected behavior. Apparently, when you search for % within BZ it does not verify that BZ actually exists. Your results were different from mine because your test data was different.

[Updated on: Wed, 10 June 2015 19:23]

Report message to a moderator

Re: wild card search on CLOB not giving require result in 12C [message #638411 is a reply to message #638404] Thu, 11 June 2015 01:23 Go to previous messageGo to next message
panot4u
Messages: 5
Registered: September 2006
Junior Member
Thanks Barbara,

Do you have any suggestion,

I just need to search if we have BZ tag exist in the indexedmetatext column, as % within BZ is not giving expected result.
Re: wild card search on CLOB not giving require result in 12C [message #638438 is a reply to message #638411] Thu, 11 June 2015 11:52 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I don't know what the source of your data is or if you have control of the format or not. If you do, then you can add something to the start or end of each field like the tag name or the word start or some special character, then you can search for that. Another option would be to create an additional index without sections and search for the BZ tag using that index in a separate clause.



Previous Topic: Find subset of string
Next Topic: which index is good ctxsys.context/ctxsys.CTXCAT
Goto Forum:
  


Current Time: Tue Mar 19 00:18:24 CDT 2024