1

Topic: Full-text query search by means of Oracle Text why does not search?

Kind time of days!
Tried to make full-text query search by means of Oracle Text. All made as in http://oraclestart.blogspot.in/2013/08/oracle-text.html
Index creation transited without errors.
Select query * from docs where contains (text, '  ')> 0; returns the necessary line, and here
select * from docs where contains (text, ' $ ')> 0; returns nothing.
If to view the auxiliary table DR$DOCS_IDX$I in TOKEN_TEXT there is a value to "GO"
In what there can be a reason?

2

Re: Full-text query search by means of Oracle Text why does not search?

In DR$DOCS_IDX$I all in an uppercase, search does not work for lower and does not work for the upper.

3

Re: Full-text query search by means of Oracle Text why does not search?

orcl_1989;
I can not so worked with contains but why/what for you interposed $ much

select * from docs where contains (text, '>>> $ <<<to go ')> 0

I.e. by default the Oracle works only with English and the French text (as I remember), and Russian  it is necessary to add  the dictionary

4

Re: Full-text query search by means of Oracle Text why does not search?

$ - The operator .
Example under the link http://oraclestart.blogspot.in/2013/08/oracle-text.html

5

Re: Full-text query search by means of Oracle Text why does not search?

All did by an example. As spoke earlier at one stage of errors was not, but search does not work.

6

Re: Full-text query search by means of Oracle Text why does not search?

And the version of Orakly what?

7

Re: Full-text query search by means of Oracle Text why does not search?

oracle 12.2.01

8

Re: Full-text query search by means of Oracle Text why does not search?

orcl_1989;
and if to walk after dock?

9

Re: Full-text query search by means of Oracle Text why does not search?

Search is necessary on a word form, and here about correspondence of own names.

10

Re: Full-text query search by means of Oracle Text why does not search?

orcl_1989;
That can you that , I can
https://docs.oracle.com/database/121/CC … #CCREF1350
D.2.3.5 Index stems
This feature enables you to specify a stemmer for stem indexing. Tokens are stemmed to a single base form at index time in addition to the normal forms. Specifying index stems enables better query performance for stem queries, for example $computed.
This feature is supported for English, Dutch, French, German, Italian, and Spanish.
But even in this case the word can "go" to be written in the text as "went" to "go" "walked"... In the beginning of a sentence from the first capital letter. If a word the INCREASING letters, I think, it perceive as a proper name and it will not participate in search. But it is my guesses. It is possible to esteem still about About

11

Re: Full-text query search by means of Oracle Text why does not search?

MaximaXXL wrote:

.... And Russian  it is necessary to add  the dictionary

Yes. Without the Russian dictionary it does not turn out. Too tried. Dictionaries for Rissii are ready, but paid.

2 wrote:

the size BIG - small characters can be?

The register and gaps of value have no. It is Still important that only it eliminates digits from search.
:
[spoiler]

CREATE TABLE ott
(
id NUMBER (2) NOT NULL;
texts VARCHAR2 (2000 CHAR);
CONSTRAINT ott_pk PRIMARY KEY (id)
);
INSERT INTO ott (id, texts)
VALUES (
1;
' Tried to make full-text query search by means of Oracle Text. All made as in http://oraclestart.blogspot.in/2013/08/oracle-text.html
Index creation transited without errors.
Select query * from docs where contains (text, "")> 0; returns the necessary line, and here
select * from docs where contains (text, "$ to go")> 0; returns nothing.
If to view the auxiliary table DR$DOCS_IDX$I in TOKEN_TEXT there is a value to "GO"
In what there can be a reason?); ');
INSERT INTO ott (id, texts)
VALUES (
2;
' that Can you that , I can
https://docs.oracle.com/database/121/CCREF/amultlng.htm#CCREF1350
D.2.3.5 Index stems
This feature enables you to specify a stemmer for stem indexing. Tokens are stemmed to a single base form at index time in addition to the normal forms. Specifying index stems enables better query performance for stem queries, for example $computed.
This feature is supported for English, Dutch, French, German, Italian, and Spanish.
But even in this case the word can "go" to be written in the text as "went" to "go" "walked"... In the beginning of a sentence from the first capital letter. If a word the INCREASING letters, I think, it perceive as a proper name and it will not participate in search. But it is my guesses. It is possible to esteem still about About ');
/*==============================================================*/
/* Index: Oracle Text */
/*==============================================================*/
CREATE INDEX idx_oratxt_texts
ON ott (texts)
INDEXTYPE IS ctxsys.context
PARAMETERS (' STOPLIST CTXSYS.EMPTY_STOPLIST SYNC (ON COMMIT) ');
BEGIN
DBMS_STATS.gather_table_stats (ownname => USER, tabname => ' OTT ', estimate_percent => 100, cascade => TRUE);
DBMS_STATS.gather_index_stats (ownname => USER, indname => ' IDX_ORATXT_TEXTS', estimate_percent => 100);
END;
/
SELECT id FROM ott WHERE contains (texts, '  ')> 0;
ID
----------
1
1 row selected.
SELECT id FROM ott WHERE contains (texts, '  ')> 0;
ID
----------
1
1 row selected.
SELECT id FROM ott WHERE contains (texts, ' Sasha ')> 0;
ID
----------
1
1 row selected.
SELECT id FROM ott WHERE contains (texts, ' $ ')> 0;
no rows selected.
SELECT id FROM ott WHERE contains (texts, ' And AND  AND Can AND go AND and % AND without AND the INDEX ')> 0;
ID
----------
1
1 row selected.
SELECT id FROM ott WHERE contains (texts, ' French AND German AND that that  ')> 0;
ID
----------
2
1 row selected.

[/spoiler]

12

Re: Full-text query search by means of Oracle Text why does not search?

Vladimir Filin;
And still not documented #: Oracle Text index it is necessary  for the first and it is explicit! Cascade for them it is not supported - the name  hangs up in the dictionary. If , it is necessary to create again the parent table,  an index,  the table.

13

Re: Full-text query search by means of Oracle Text why does not search?

If there is a possibility to refuse from oracle text do it faster. More  at  only json.

14

Re: Full-text query search by means of Oracle Text why does not search?

Vladimir Filin
Yes. Without the Russian dictionary it does not turn out. Too tried. Dictionaries for Rissii are ready, but paid.
As already told all forms without problems and
If to view the auxiliary table DR$DOCS_IDX$I in TOKEN_TEXT there is a value to "GO"
I.e. the problem not in dictionaries, and in usage of the operator  $ as I understand it does not fulfill, but if
select * from docs where contains (text, ' $ ')> 0; - that returns the necessary line.

15

Re: Full-text query search by means of Oracle Text why does not search?

orcl_1989;
Look
Oracle Text russian morphology
.....
stax

16

Re: Full-text query search by means of Oracle Text why does not search?

orcl_1989;
Checked up the Example . Interesting. Considered that word forms it is the separate dictionary,  for Russian.
In 11R2 received DRG-11135:
[spoiler]
[code]
select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
5 rows selected.
[/code]
[code]
create table docs (id number not null, text varchar2 (1000 char) not null);
insert into docs values (1, ' There was Sasha on highway and sucked drying. ');
insert into docs values (2, ' London is the capital of The United Kingdom of Great Britain and Northern Ireland. ');
begin
ctx_ddl.create_preference (' my_wordlist ', ' BASIC_WORDLIST ');
ctx_ddl.create_preference (' my_lexer ', ' AUTO_LEXER ');
ctx_ddl.set_attribute (' my_lexer ', ' INDEX_STEMS ','YES');
end;
create index docs_idx on docs (text) indextype is ctxsys.context parameters (' LEXER my_lexer WORDLIST my_wordlist ');
Table created.
1 row created.
1 row created.
PL/SQL procedure successfully completed.
create index docs_idx on docs (text) indextype is ctxsys.context parameters (' LEXER my_lexer WORDLIST my_wordlist ')
Error at line 9
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10502: index DOCS_IDX does not exist
DRG-11135: feature not generally available
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366
[/code]
[/spoiler]
In 12.1 Non-CDB all works, as in example :
[spoiler]
[code]
select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
5 rows selected.
[/code]
[code]
create table docs (id number not null, text varchar2 (1000 char) not null);
insert into docs values (1, ' There was Sasha on highway and sucked drying. ');
insert into docs values (2, ' London is the capital of The United Kingdom of Great Britain and Northern Ireland. ');
begin
ctx_ddl.create_preference (' my_wordlist ', ' BASIC_WORDLIST ');
ctx_ddl.create_preference (' my_lexer ', ' AUTO_LEXER ');
ctx_ddl.set_attribute (' my_lexer ', ' INDEX_STEMS ','YES');
end;
create index docs_idx on docs (text) indextype is ctxsys.context parameters (' LEXER my_lexer WORDLIST my_wordlist ');
Table created.
1 row created.
1 row created.
PL/SQL procedure successfully completed.
Index created.
[/code]
[code]
select id from docs where contains (text, '  ')> 0;
ID
----------
1
1 row selected.
select id from docs where contains (text, ' $ ')> 0;
ID
----------
1
1 row selected.
select id from docs where contains (text, ' united ')> 0;
ID
----------
2
1 row selected.
select id from docs where contains (text, ' uni % ')> 0;
ID
----------
2
1 row selected.
[/code]
[/spoiler]
In 12.1 PDB too all works:
[spoiler]
[code]
select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
5 rows selected.
[/code]
[code]
create table docs (id number not null, text varchar2 (1000 char) not null);
insert into docs values (1, ' There was Sasha on highway and sucked drying. ');
insert into docs values (2, ' London is the capital of The United Kingdom of Great Britain and Northern Ireland. ');
begin
ctx_ddl.create_preference (' my_wordlist ', ' BASIC_WORDLIST ');
ctx_ddl.create_preference (' my_lexer ', ' AUTO_LEXER ');
ctx_ddl.set_attribute (' my_lexer ', ' INDEX_STEMS ','YES');
end;
create index docs_idx on docs (text) indextype is ctxsys.context parameters (' LEXER my_lexer WORDLIST my_wordlist ');
Table created.
1 row created.
1 row created.
PL/SQL procedure successfully completed.
Index created.
[/code]
[code]
[code]
select id from docs where contains (text, '  ')> 0;
ID
----------
1
1 row selected.
select id from docs where contains (text, ' $ ')> 0;
ID
----------
1
1 row selected.
select id from docs where contains (text, ' united ')> 0;
ID
----------
2
1 row selected.
select id from docs where contains (text, ' uni % ')> 0;
ID
----------
2
1 row selected.
[/code]
[/spoiler]

17

Re: Full-text query search by means of Oracle Text why does not search?

Vladimir Filin
What coding of basis?
At me at 12.2.0.1
select * from docs where contains (text, ' $ ')> 0; returns nothing.

18

Re: Full-text query search by means of Oracle Text why does not search?

orcl_1989 wrote:

what coding of basis?

SELECT * FROM nls_database_parameters;
NLS_RDBMS_VERSION 12.1.0.2.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .;
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN

Specification about 11R2: it at me the index on 11.2.0.4.0 was not constructed.
Checked up on 11.2.0.2.0 - all is under construction and works
Codings same, as in 12.1

19

Re: Full-text query search by means of Oracle Text why does not search?

All the same business in the coding.
Delivered 12.2.0.1 with parameters:

wrote:

SELECT * FROM nls_database_parameters;
NLS_RDBMS_VERSION 12.2.0.1.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .;
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN

And all earned.
All thanks for the help.