| wild card search on CLOB not giving require result in 12C [message #638325] |
Tue, 09 June 2015 08:58  |
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  |
 |
Barbara Boehmer
Messages: 9100
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 |
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 |
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 #638404 is a reply to message #638360] |
Wed, 10 June 2015 19:21 |
|
Barbara Boehmer
Messages: 9100
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 #638438 is a reply to message #638411] |
Thu, 11 June 2015 11:52 |
|
Barbara Boehmer
Messages: 9100
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
