Oracle Text - Part 5

Generating XML Output

The INDEX_STATS procedure supports both formatted text and XML output.The following code
creates the INDEX_STATS report in text format:

-- Table to store our report
CREATE TABLE index_report (id NUMBER(10), report CLOB);

DECLARE
v_report CLOB := null;
BEGIN
CTX_REPORT.INDEX_STATS(index_name => 'SONG_INDEX',
report => v_report,part_name => NULL,frag_stats => NULL,
list_size => 20,report_format => NULL);
INSERT INTO index_report (id, report) VALUES (1, v_report);
COMMIT;
DBMS_LOB.FREETEMPORARY(v_report);
END;
/

Then output can be viewed:

SELECT report FROM index_report WHERE id = 1;

To find out the size of the index, we can use the function CTX_REPORT.INDEX_SIZE. This is a function returning a CLOB.

select ctx_report.index_size('SONG_INDEX') from dual;

for a XML report :
SQL> select ctx_report.index_size('SONG_INDEX', null, 'XML') from dual;

This will give us a heap of XML output, including sizes for each object.

Here's a section of the output:

<SIZE_OBJECT_NAME> text_user.SYS_IL0000051565C00006$$
</SIZE_OBJECT_NAME>
<SIZE_OBJECT_TYPE> INDEX (LOB)
</SIZE_OBJECT_TYPE>
<SIZE_TABLE_NAME> text_user.DR$TI$I
</SIZE_TABLE_NAME>
<SIZE_TABLESPACE_NAME> USERS
</SIZE_TABLESPACE_NAME>
<SIZE_BLOCKS_ALLOCATED> 8
</SIZE_BLOCKS_ALLOCATED>
<SIZE_BLOCKS_USED> 4
</SIZE_BLOCKS_USED>
<SIZE_BYTES_ALLOCATED> 65536
</SIZE_BYTES_ALLOCATED>
<SIZE_BYTES_USED> 32768
</SIZE_BYTES_USED>
</SIZE_OBJECT>
<SIZE_OBJECT>


selecting a CLOB, force it into an XMLTYPE value:
select xmltype(ctx_report.index_size('SONG_INDEX', null, 'XML')) from dual;

select extract(xmltype(ctx_report.index_size('SONG_INDEX', null, 'XML')), '//SIZE_OBJECT') from dual;

We're now using the XML DB "extract" function to fetch all of the XML within tags. The syntax '//SIZE_OBJECT' is an XPATH expression meaning "all the XML within a SIZE_OBJECT element anywhere below the root element".the reader is encouraged to look at the tutorial at w3Schools.

select extract(xmltype(replace(ctx_report.index_size('SONG_INDEX', null, 'XML'),chr(10),'')),'//SIZE_OBJECT') from dual;

Now specify that only information from the table $I is want . this is done with
within a predicate the xpath expression:

select extract(xmltype(replace(ctx_report.index_size('SONG_INDEX', null, 'XML'),chr(10),'')),'//SIZE_OBJECT[SIZE_OBJECT_NAME="text_user.DR$TI$I"]') from dual;

The size in bytes can be get by adding the XPATH expression:

select extract(xmltype(replace(ctx_report.index_size('SONG_INDEX', null, 'XML'),chr(10),'')),'//SIZE_OBJECT[SIZE_OBJECT_NAME="text_user.DR$TI$I"]/SIZE_BYTES_USED') from dual;

To avoid tags. There's two ways of doing this 1) the text() function within the XPATH, 2) use extractValue rather than extract.

select extract(xmltype(replace(ctx_report.index_size('SONG_INDEX', null, 'XML'),chr(10),'')),'//SIZE_OBJECT[SIZE_OBJECT_NAME="text_user.DR$TI$I"]/SIZE_BYTES_USED/text()') as "Table Size" from dual;

select extractValue(xmltype(replace(ctx_report.index_size('SONG_INDEX', null, 'XML'),chr(10),'')), '//SIZE_OBJECT[SIZE_OBJECT_NAME="text_user.DR$TI$I"]/SIZE_BYTES_USED')as "Table Size" from dual;

For fetching ALL the sizes, perhaps to add them together to get a summary XMLSequence can use,which returns a collection of XMLType values. This function can use in a TABLE clause to unnest the collection values into multiple rows. Now that we're generating a "table", we no longer need to reference DUAL.

To get just the sizes:

select * from table(xmlsequence( extract(xmltype(replace(ctx_report.index_size('SONG_INDEX', null, 'XML'),chr(10),'')),'//SIZE_OBJECT/SIZE_BYTES_USED')));

To get all the object info, one object per row:

select * from table(xmlsequence( extract(xmltype(replace(ctx_report.index_size('SONG_INDEX', null, 'XML'),chr(10),'')), '//SIZE_OBJECT')));

Processing information into individual values:

select extractValue(Column_Value, '/SIZE_OBJECT/SIZE_OBJECT_NAME') as "Name",
extractValue(Column_Value, '/SIZE_OBJECT/SIZE_TABLESPACE_NAME') as "Tablespace",
extractValue(Column_Value, '/SIZE_OBJECT/SIZE_BYTES_USED') as "Bytes"
from table(xmlsequence(extract(xmltype(replace(ctx_report.index_size('SONG_INDEX', null, 'XML'),chr(10),'')),'//SIZE_OBJECT')));

To get sum of all the sizes to get an aggragate total size of all objects
used in the index.

select sum(extractValue(Column_Value, '/SIZE_OBJECT/SIZE_BYTES_USED')) "Total"
from table(xmlsequence(extract(xmltype(replace(ctx_report.index_size('ti', null, 'XML'),chr(10),'')),'//SIZE_OBJECT')));

INDEX_STATS

Most of the CTX_REPORT procedures have two forms - a function which returns a CLOB (as done above)and an alternate form which requires to pass in a CLOB to be populated. One procedure - INDEX_STATS - does not have a function version. This is because it needs to do a full table scan of the main index table, which is likely to take some time.Therefore when calling INDEX_STATS it is needed to go about things a little differently. First create a table with an XMLType column. Then call a PL/SQL block which passes a temporary CLOB to CTX_REPORT, and then inserts that CLOB into the XMLType column. Then various XML operations can perform on that table.

create table output(report XMLType);

declare
tlob clob;
begin
ctx_report.index_stats(index_name=>'ti', report=>tlob,
list_size=>20, report_format=>'XML');
insert into output values (xmlType (replace(tlob,chr(10),'')) );
commit;
dbms_lob.freetemporary(tlob);
end;
/

There is a single row in table OUTPUT, which contains XML report. First well get the
estimated row fragmentation:

select extractValue(report,'//STAT_STATISTIC[@NAME="estimated row fragmentation"]')
as "Fragmentation" from output;

Now get the top three most frequent tokens.

using the position function:

select extract(value(d), '//STAT_TOKEN_TEXT')
from output, table(xmlsequence(extract(report,'/CTXREPORT/INDEX_STATS/STAT_TOKEN_STATS/STAT_TOKEN_LIST[@NAME="most frequent tokens"]/
STAT_TOKEN[position()<4]'))) d; Conclusion
The XML output mode of CTX_REPORT allows powerful manipulation of index information. In order to make full use of these,a good understanding of the XML features of the Oracle database, such as extract, extractValue, XMLSequence, and of XPATH syntax in general is needed.With the use of XML exploding within the data processing world, such an understanding is likely to be very useful in the future.

Oracle Text - Part 1

Oracle Text ie: Context Oracle text (formerly: interMedia text) in 10g
----------------------------------------------------------------

User = CTXSYS

Mainly used for Not basic SQL searching; Its about full retrieval against virtually any datatype (including all LOB types). The indexing & searching abilities of Oracle Text are not restricted to data stored in the database. It can index & search documents stored on the filesystem also and index more than 150 document types, including Microsoft Word, PDF, and XML.

Oracle Text search functionality includes fuzzy, stemming (search for mice and find mouse),wildcard

Demo

CREATE USER text_user IDENTIFIED BY oracle;
GRANT connect, resource, ctxapp TO text_user;

CONN text_user/oracle

CREATE TABLE songs (ID NUMBER(10),
Title VARCHAR2(50), Genre VARCHAR2(50));

INSERT INTO songs (ID, Title, Genre)
VALUES (1, 'The Preble Mice Go Squeak', 'CHILD');
INSERT INTO songs (ID, Title, Genre)
VALUES (2, 'Benri The Cat', 'CHILD');
INSERT INTO songs (ID, Title, Genre)
VALUES (3, 'My Mouse Won't Work Blues', 'COMPUTER ENGINEER');
INSERT INTO songs (ID, Title, Genre)
VALUES (4, 'My Pen Leaked - Ballad Of The Pocket Protector', 'COMPUTER ENGINEER');
INSERT INTO songs (ID, Title, Genre)
VALUES (5, 'The Mechanical Pencil - Get The Lead Out', 'HEAVY METAL');
COMMIT;

Creating an Oracle Text index is completely different from creating any other database index.Before creating the index it is required to determine which features want to use and create the necessary structures to support the index.

Most frequently used type of Oracle Text index is CONTEXT index.
if not need multilingual features so this type of simple index creation is sufficient.The basic index creation is broken into two steps:

Step 1: Create the preferences. Preferences tell Oracle Text how index should be stored,how data should be filtered, the language(s) that will be indexed, and how fuzzy and stemming queries should be treated. For this use the CTX_DDL built-in package to create the preferences and set the attributes. The CTX_DDL package is owned by the user CTXSYS . The TEXT_USER schema (user) employed for the examples has EXECUTE permissions on CTX_DDL, because it was granted the CTXAPP role.

BEGIN
CTX_DDL.CREATE_PREFERENCE('english_lexer','basic_lexer');
CTX_DDL.SET_ATTRIBUTE('english_lexer','index_themes','no');
END;
/

The CREATE_PREFERENCE procedure establishes the name and type of LEXER to be used. A LEXER decides how text is broken apart for indexing, and it can be set for different languages. Here BASIC_LEXER object type is used , which supports most Western European, white-space-delimited languages.

Lexer

The lexers job is to separate the sectioners output into words or tokens.In the
simplest case for a Western European language, the lexer just splits text into
uninterrupted strings of alphanumeric characters. So the string:
Aha! Its the 5:15 train, coming here now!
would be split into the words, minus any punctuation or special symbols:
aha it s the 5 15 train coming here now
The lexer typically removes stopwords,which are common words defined by the
application developer; or taken from a default list. That would likely reduce the
list above to:
aha * * * 5 15 train coming * now
Note the asterisks representing removed stopwords. Although they are not actually
indexed, the presence of a stopword at the position is noted in the index. In a
search, any stopword will match that word when used as part of a phrase. For
example, “kicking the ball” will match “kicking a ball” but will not match “kicking
ball”.The set of stopwords may be specified by the application developer, who can also choose to explicitly define all numbers as stopwords.

The following command shows how to set the Japanese lexer:
ctx_ddl.create_preference('JAPANESE_LEXER','japanese_vgram_lexer')

  Set Chinese Lexer
  exec ctx_ddl.create_preference('CHINESE_LEXER','chinese_vgram_lexer');
 
  Set Korean Lexer
  exec ctx_ddl.create_preference('KOREAN_LEXER','korean_morph_lexer');

If the language of the documents is
not known, the new AUTO_LEXER may be used, which provides automatic language recognition, and extensive segmentation and stemming capabilities for multiple languages.

This are the languages supported by the AUTO_LEXER. Those in
bold support context-sensitive stemming.
Arabic, Catalan,Czech, Danish, Dutch, English, Finnish, French, German, Greek,
Hebrew, Hungarian, Italian,Nynorsk,Polish, Portuguese, Romanian, Russian, Serbian, Slovak,Slovenian, Spanish, Swedish, Simplified Chinese, Traditional Chinese, Croatian,Japanese, Korean, Bokmal, Persian, Thai, Turkish

Set Multi-lexer named GLOBAL_LEXER


 exec ctx_ddl.create_preference('GLOBAL_LEXER', 'multi_lexer');
 exec ctx_ddl.add_sub_lexer('GLOBAL_LEXER','default','ENGLISH_LEXER');
 exec ctx_ddl.add_sub_lexer('GLOBAL_LEXER','traditional chinese','CHINESE_LEXER','chn');
 exec ctx_ddl.add_sub_lexer('GLOBAL_LEXER','japanese','JAPANESE_LEXER','jpn');
 exec ctx_ddl.add_sub_lexer('GLOBAL_LEXER','korean','KOREAN_LEXER','kor');

DataStore


The datastore defines from where the text to be indexed should be fetched.Provided datastores allow for text which is stored within a database, on a file system, or accessed remotely via the HTTP protocol (the URL datastore).


Default Datastore

The default datastore is in the database itself. Text may be stored in a VARCHAR2 column (up to 4000 characters), or in a CLOB (Character Large Object) column.Formatted text (such as Word or PDF documents) can be stored in BLOB (Binary Large Object) columns.

File Datastore

Text to be indexed is stored on any file system which is accessible to the database
server. The name or path to the file is stored in the database, typically in a VARCHAR2 column.

URL Datastore

The database contains an HTTP protocol URL, and the text to be indexed is
fetched directly from the URL at indexing time.

SET_ATTRIBUTE procedure is used to instruct Oracle Text not to generate themes

Step 2:
CREATE INDEX song_index ON songs(title) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('LEXER english_lexer STOPLIST ctxsys.default_stoplist')

The first parameter is the LEXER.The second parameter in this example is the STOPLIST.Stoplists provide Oracle with a list of words not to index. Typical words to exclude are of,the, a, is, and so on. In the example, the default stoplist is provided as the parameter value. This stoplist is shipped with Oracle Database and is owned by the CTXSYS user.
The CTXAPP role granted to my user provides permissions to use this stoplist.



Oracle Text provides three types of indexes that cover all text search needs:
standard, catalog, and classification

• Standard index type for traditional full-text retrieval over documents and
web pages. The context index type provides a rich set of text search capabilities for finding
spurious results.

• Catalog index type - the first text index designed specifically for eBusiness
catalogs. The ctxcat catalog index type provides flexible searching and
sorting at web-speed.

• Classification index type for building classification or routing applications.
The ctxrule index type is created on a table of queries, where the queries
define the classification or routing criteria.

Testing the Index

By running a query using the CONTAINS() operator

SELECT score(1), title, genre FROM songs
WHERE CONTAINS(title, 'mice', 1) > 0;

This query should return a single row:

SCORE(1)TITLE GENRE
------- ----------------------- -----
5 The Preble Mice Go Squeak CHILD


SCORE() operator ranks search results by relevance and provides a numeric value in the result set that allows me to determine which values are the best matches to the search criteria. In this query, SCORE() is given a label of 1 (inside the parentheses) that corresponds to the matching CONTAINS() search. The last argument in the CONTAINS() clause has the same label value, 1. If there are multiple CONTAINS() operators, separate labels can be used with SCORE() and CONTAINS() to determine the relevance for each.

Here a wildcard (%) character is not used for searching.This is because Oracle Text
creates tokens in its indexing process. These tokens(words or phrases), generated based on the LEXER defined in the index creation, are words and phrases found in the text. When a query is issued with the CONTAINS() operator, the Oracle Text index tables—not the document where the text originated—are searched for a match.Here tokens (words or phrases)are searched and not blocks of text so a wildcard is not necessary if full term is supplied.If a token matches the keyword, the source document is retrieved as a match.

However, there are occasions when a wildcard character would be used. If there are two documents,one with the word pen and the other with the word pencil, it is needed to search for pen% in order to get both records.

Another type of query that uses the CONTAINS() operator is called stemming
(search for mice and find mouse). Some words, although originating from the same root or stem, are not spelled similarly enough for a wildcard to be of any use. For example, a search for mice% will not return mouse. If root form of a word is known , all variants of that word can be matched

Because mice and mouse share the same stem,

SELECT score(1), title, genre FROM songs WHERE CONTAINS(title, '$mice', 1) > 0;

This query now returns two rows:

SCORE(1)TITLE GENRE
------- ----------------------- ---------------
5 My Mouse Wont Work Blues COMPUTER ENGINEER
5 The Preble Mice Go Squeak CHILD

This query returns both rows with the same score.It can find either form by adding a $ in front of the keyword mice.


Tuning and administering Oracle Text

One of the most useful reports for tuning Oracle Text applications is QUERY_LOG_SUMMARY. This report lets you analyze your indexes and queries to maximize effectiveness by checking how queries match data.

In SONGS table there is a song called "My Mouse Wont Work Blues." If That is a Top 40 hit, so it should be flying off the shelves, but nobody is buying it. One place to look for reasons is the QUERY_LOG_SUMMARY report. Using the QUERY_LOG_SUMMARY report,it can see if people are performing searches for similar titles but not the exact title, meaning that the song is never returned as a match.

The first thing need to do is begin logging the queries which is done with the CTX_OUTPUT package, as follows:

exec ctx_output.start_query_log('text_index.txt');

With the query log turned on, run the same incorrect query 100 times as follows:

DECLARE
v_title VARCHAR2(50);
BEGIN
FOR y IN 1..100 LOOP
BEGIN
SELECT title INTO v_title FROM songs
WHERE ID = 3 AND CONTAINS (title,'mice') > 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
END;
/

After the querying has finished,turn logging off, as follows:

exec ctx_output.end_query_log;

Using the QUERY_LOG_SUMMARY procedure,it can evaluate the log file contents using the following:

DECLARE
v_report CTX_REPORT.QUERY_TABLE;
BEGIN
CTX_REPORT.QUERY_LOG_SUMMARY('text_index.txt',
'song_index',v_report,5,TRUE,FALSE);

FOR y in 1..v_report.count LOOP
DBMS_OUTPUT.PUT_LINE('The query for'
||v_report(y).query||' ran and failed'
||v_report(y).times||' times.');
END LOOP;
EXCEPTION WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
/
This returns the following result:

The query for mice ran and failed 100 times.

The reason for the search failure is obvious. ID 3 contains the word mouse,
not mice. Having identified the problem with my keyword search, I can correct the
query by adding stemming ($), as described

SELECT title FROM songs WHERE ID = 3 AND CONTAINS (title, '$mice') > 0;

TITLE
-------------------------
My Mouse Wont Work Blues

This search returns the record what want, so adding stemming to the online stores search— specify that all user searches include the '$' by default. This results in successful searches on mouse and mice, and soon the CDs are selling.
-------------------------------------------