Friday, October 28, 2011
ORACLE SQL TUTORIAL: INDEX OVERVIEW PART 2
PART 1: http://www.shareoracleapps.com/2011/10/oracle-sql-tutorial-index-part-1.html
continuation.....
HOW A TEXT QEURY WORKS?
When a function such as CONTAINS or CATSEARCH is used in query,
the text portion of the query is processed by oracle text. The remainder of the
query is processed just like a regular query within the database. The result of
the text query processing and the regular query processing are merged to return
a single set of records to the user.
SEARCHING FOR AN EXACT MATCH OF A WORD
The following queries will search for a word called
‘prperty’ whose score is greater than zero.
SQL> select * from books where contains(info,
‘property’) > 0;
SQL> select * from books where catsearch(info,
‘property’, null) > 0;
Suppose if you want to know the score of the
‘property’ in each book, if score values for individual searches range from 0
to 10 for each occurrence of the string within the text then use the score
function.
SQL> select title, score(10) from books where
contains(info, ‘property’, 10) > 0;
SEARCHING FOR AN EXACT MATCH OF MULTIPLE WORDS
The following queries will search for two words.
SQL> select * from books where contains(info,
‘property AND harvests’) > 0;
SQL> select * from books where catsearch(info,
‘property AND harvests’, null) > 0;
Instead of using AND you could hae used an
ampersand(&). Before using this method, set define off so the &
character will not be seen as part of a variable name.
SQL> set define off
SQL> select * from books where contains(info,
‘property & harvests’) > 0;
SQL> select * from books where catsearch(info,
‘property harvests’, null) > 0;
The following queries will search for more than two
words.
SQL> select * from books where contains(info,
‘property AND harvests AND workers’) > 0;
SQL> select * from books where catsearch(info,
‘property harvests workers’, null) > 0;
The following queries will search for either of the two
words.
SQL> select * from books where contains(info,
‘property OR harvests’) > 0;
Instead of OR you can use a vertical line (|).
SQL> select * from books where contains(info,
‘property | harvests’) > 0;
SQL> select * from books where catsearch(info,
‘property | harvests’, null) > 0;
In the following queries the ACCUM(accumulate)
operator adds together the scores of the individual searches and compares the
accumulated score to the threshold value.
SQL> select * from books where contains(info,
‘property ACCUM harvests’) > 0;
SQL> select * from books where catsearch(info,
‘property ACCUM harvests’, null) > 0;
Instead of OR you can use a comma(,).
SQL> select * from books where contains(info,
‘property , harvests’) > 0;
SQL> select * from books where catsearch(info,
‘property , harvests’, null) > 0;
In the following queries the MINUS operator subtracts
the score of the second term’s search from the score of the first term’s
search.
SQL> select * from books where contains(info,
‘property MINUS harvests’) > 0;
SQL> select * from books where catsearch(info,
‘property NOT harvests’, null) > 0;
Instead of MINUS you can use – and instead of NOT
you can use ~.
SQL> select * from books where contains(info,
‘property - harvests’) > 0;
SQL> select * from books where catsearch(info,
‘property ~ harvests’, null) > 0;
SEARCHING FOR AN EXACT MATCH OF A PHRASE
The following queries will search for the phrase. If the search
phrase includes a reserved word within oracle text, the you must use curly
braces ({}) to enclose text.
SQL> select * from books where contains(info, ‘transactions
{and} finances’) > 0;
SQL> select * from books where catsearch(info, ‘transactions
{and} finances’, null) > 0;
You can enclose the entire phrase within curly braces, in which
case any reserved words within the phrase will be treated as part of the search
criteria.
SQL> select * from books where contains(info, ‘{transactions
and finances}’) > 0;
SQL> select * from books where catsearch(info, ‘{transactions
and finances}’, null) > 0;
SEARCHING FOR WORDS THAT ARE NEAR EACH OTHER
The following queries will search for the words that
are in between the search terms.
SQL> select * from books where contains(info, ‘workers
NEAR harvests’) > 0;
Instead of NEAR you can use ;.
SQL> select * from books where contains(info, ‘workers
; harvests’) > 0;
In CONTEXT index queries, you can specify the
maximum number of words between the search terms.
SQL> select * from books where contains(info, ‘NEAR((workers,
harvests),10)’ > 0;
USING WILDCARDS DURING SEARCHES
You can use wildcards to expand the list of valid search terms
used during your query. Just as in regular text-string wildcard processing, two
wildcards are available.
% - percent sign; multiple-character
wildcard
_ - underscore; single-character
wildcard
SQL> select * from books where contains(info, ‘worker%’)
> 0;
SQL> select * from books where contains(info, ‘work___’)
> 0;
SEARCHING FOR WORDS THAT SHARE THE SAME STEM
Rather than using wildcards, you can use stem-expansion
capabilities to expand the list of text strings. Given the ‘stem’ of a word,
oracle will expand the list of words to search for to include all words having
the same stem. Sample expansions are show here.
Play - plays playing played playful
SQL> select * from books where contains(info, ‘$manage’)
> 0;
SEARCHING FOR FUZZY MATCHES
A fuzzy match expands the specified search term to include words
that are spelled similarly but that do not necessarily have the same word stem.
Fuzzy matches are most helpful when the text contains misspellings. The
misspellings can be either in the searched text or in the search string
specified by the user during the query.
The following queries will not return anything because its search
does not contain the word ‘hardest’.
SQL> select * from books where contains(info, ‘hardest’)
> 0;
It does, however, contains the word ‘harvest’. A fuzzy match will
return the books containing the word ‘harvest’ even though ‘harvest’ has a
different word stem thant the word used as the search term.
To use a fuzzy match, precede the search term with a question
mark, with no space between the question mark and the beginning of the search
term.
SQL> select * from books where contains(info, ‘?hardest’)
> 0;
SEARCHING FOR WORDS THAT SOUND LIKE OTHER WORDS
SOUNDEX, expands search terms based on how the word sounds. The
SOUNDEX expansion method uses the same text-matching logic available via the
SOUNDEX function in SQL.
To use the SOUNDEX option, you must precede the search term with
an exclamation mark(!).
SQL> select * from books where contains(info, ‘!grate’)
> 0;
INDEX SYNCHRONIZATION
When using CONTEXT indexes, you need to manage the text index
contents; the text indexes are not updated when the base table is updated. When
the table was updated, its text index is out of sync with the base table. To
sync of the index, execute the SYNC_INDEX procedure of the CTX_DDL package.
SQL> exec CTX_DDL.SYNC_INDEX(‘book_index’);
INDEX SETS
Historically, problems with queries of text indexes have occurred
when other criteria are used alongside text searches as part of the where
clause. To improve the mixed query capability, oracle features index sets. The
indexes within the index set may be structured relational columns or on text
columns.
To create an index set, use the CTX_DDL package to create the
index set and add indexes to it. When you create a text index, you can then
specify the index set it belongs to.
SQL> exec CTX_DDL.CREATE_INDEX_SET(‘books_index_set’);
The add non-text indexes.
SQL> exec CTX_DDL.ADD_INDEX(‘books_index_set’,
‘title_index’);
Now create a CTXCAT text index. Specify
ctxsys.ctxcat as the index type, and list the index set in the parameters
clause.
SQL> create index book_index on books(info) indextype
is ctxsys.ctxcat parameters(‘index set books_index_set’);
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
0 Responses to “ORACLE SQL TUTORIAL: INDEX OVERVIEW PART 2”
Post a Comment