Full Frontal PR: Getting People Talking about You, Your Business, or Your Product
ISBN: 1590595254
EAN: 2147483647
EAN: 2147483647
Year: 2005
Pages: 41
Pages: 41
Authors: Richard Laermer, Michael Prichinello
interMedia is a collection of features, tightly integrated into the Oracle database, that enable you to load rich content into the database, securely manage it, and ultimately, deliver it for use in an application. Rich content is widely used in most web applications today, and includes text, data, images, audio files, and video.
This chapter focuses on one of my favorite components of interMedia: interMedia Text. I find that interMedia Text is typically an under-utilized technology. This stems from a lack of understanding of what it is, and what it does. Most people understand what interMedia Text can do from a high level, and how to 'text-enable' a table. Once you delve beneath the surface though, you'll discover that interMedia Text is a brilliantly crafted feature of the Oracle database.
After a quick overview of interMedia's history we will:
Discuss the potential uses of interMedia Text such as searching for text, indexing data from many different sources of data and searching XML applications, amongst others.
Take a look at how the database actually implements this feature.
Cover some of the mechanisms of interMedia Text such as indexing, the ABOUT operator, and section searching.
During my work on a large development project in 1992, I had my first introduction to interMedia Text or, as it was called then, SQL*TextRetrieval. At that time, one of my tasks was to integrate a variety of different database systems in a larger, distributed network of databases. One of these database products was proprietary in every way possible. It lacked a SQL interface for database definition and amounts of textual data. Our job was to write a SQL interface to it.
Sometime in the middle of this development project, our Oracle sales consultant delivered information about the next generation of Oracle's SQL*TextRetrieval product, to be called TextServer3. One of the advantages of TextServer3 was that it was highly optimized for the client-server environment. Along with TextServer3 came a somewhat obtuse C-based interface, but at least now I had the ability to store all of my textual data within an Oracle database, and also to access other data within the same database via SQL. I was hooked.
In 1996, Oracle released the next generation of TextServer, called ConText Option, which was dramatically different from previous versions. No longer did I have to store and manage my textual content via a C or Forms-based API. I could do everything from SQL. ConText Option provided many PL/SQL procedures and packages that enabled me to store text, create indexes, perform queries, perform index maintenance, and so on, and I never had to write a single line of C. Of the many advances ConText Option delivered, two in my opinion are most noteworthy. First and foremost, ConText Option was no longer on the periphery of database integration. It shipped with Oracle7, and was a separately licensed, installable option to the database, and for all practical purposes, was tightly integrated with the Oracle7 database. Secondly, ConText Option went beyond standard text-retrieval, and offered linguistic analysis of text and documents, enabling an application developer to build a system that could 'see' beyond just the words and actually exploit the overall meaning of textual data. And don't forget that all of this was accessible via SQL, which made use of these advanced features dramatically easier.
One of the advanced features of the Oracle8i database is the extensibility framework. Using the services provided, developers were now given the tools to create custom, complex data types, and also craft their own core database services in support of these data types. With the extensibility framework, new index types could be defined, custom statistics collection methods could be employed, and custom cost and selectivity functions could be integrated into an Oracle database. Using this information, the query optimizer could now intelligently and efficiently access these new data types. The ConText Option team recognized the value of these services, and went about creating the current product, interMedia Text, which was first released with Oracle8i in 1999.
There are countless ways in which interMedia Text can be exploited in applications, including:
Searching for text - You need to quickly build an application that can efficiently search textual data.
Managing a variety of documents - You have to build an application that permits searching across a mix of document formats, including text, Microsoft Word, Lotus 1-2-3, and Microsoft Excel.
Indexing text from many data sources - You need to build an application that manages textual data not only from an Oracle database, but also from a file system as well as the Internet.
Building applications beyond just text - Beyond searching for words and phrases, you are tasked to build a knowledge base with brief snapshots or 'gists' about each document, or you need to classify documents based upon the concepts they discuss, rather than just the words they contain.
Searching XML applications - interMedia Text gives the application developer all the tools needed to build systems which can query not only the content of XML documents, but also perform these queries confined to a specific structure of the XML document.
And, of course, the fact that this functionality is in an Oracle database means that you can exploit the inherent scalability and security of Oracle, and apply this to your textual data.
There are, of course, a number of ways that you can use to search for text within the Oracle database, without using the interMedia functionality. In the following example, we create a simple table, insert a couple of rows then use the standard INSTR function and LIKE operator to search the text column in the table:
SQL> create table mytext 2 ( id number primary key, 3 thetext varchar2(4000) 4 ) 5 / Table created. SQL> insert into mytext 2 values( 1, 'The headquarters of Oracle Corporation is ' || 3 'in Redwood Shores, California.'); 1 row created. SQL> insert into mytext 2 values( 2, 'Oracle has many training centers around the world.'); 1 row created. SQL> commit; Commit complete. SQL> select id 2 from mytext 3 where instr( thetext, 'Oracle') > 0; ID ---------- 1 2 SQL> select id 2 from mytext 3 where thetext like '%Oracle%'; ID ---------- 1 2
Using the SQL INSTR function, we can search for the occurrence of a substring within another string. Using the LIKE operator we can also search for patterns within a string. There are many times when the use of the INSTR function or LIKE operator is ideal, and anything more would be overkill, especially when searching across fairly small tables.
However, these methods of locating text will typically result in a full tablescan, and they tend to be very expensive in terms of resources. Furthermore, they are actually fairly limited in functionality. They would not, for example, be of use if you needed to build an application that answered questions such as:
Find all rows that contain 'Oracle' near the word 'Corporation', and no more than 10 words apart.
Find all rows that contain 'Oracle' or 'California', and rank in relevance order.
Find all rows that have the same linguistic root as 'train' (for example, trained, training, trains).
Perform a case-insensitive search across a document library.
Such queries just scratch the surface of what cannot be done via traditional means, but which can easily be accomplished through the use of interMedia Text. In order to demonstrate how easily interMedia can answer questions such as those posed above, we first need to create a simple interMedia Text index on our text column:
| Note | In order to use the interMedia Text PL/SQL packages, the user must be granted the role CTXAPP. |
SQL> create index mytext_idx 2 on mytext( thetext ) 3 indextype is CTXSYS.CONTEXT 4 / Index created.
With the creation of the new index type, CTXSYS.CONTEXT, we have 'Text-enabled' our existing table. We can now make use of the variety of operators that interMedia Text supports, for sophisticated handling of textual content. The following examples demonstrate the use of the CONTAINS query operator to answer three of the above four questions (don't worry about the intricacies of the SQL syntax for now, as this will be explained a little later):
SQL> select id 2 from mytext 3 where contains( thetext, 'near((Oracle,Corporation),10)') > 0; ID ---------- 1 SQL> select score(1), id 2 from mytext 3 where contains( thetext, 'oracle or california', 1 ) > 0 4 order by score(1) desc 5 / SCORE(1) ID ---------- ---------- 4 1 3 2 SQL> select id 2 from mytext 3 where contains( thetext, '$train') > 0; ID ---------- 2
Every bit as important as functionality, is the performance aspects of an interMedia Text index, which surpasses traditional relational methods of locating text within a column. Being truly a new index type within the Oracle database, information that is maintained about the data being indexed can be exploited during query plan creation. Thus, the Oracle kernel is given the optimal path to locating textual data within a column indexed by interMedia Text.
Beyond the ability to index plain text columns in a database, interMedia Text is also bundled with document filters for a huge variety of document formats. interMedia Text will automatically filter Microsoft Word 2000 for Windows, Microsoft Word 98 for the Macintosh, Lotus 1-2-3 spreadsheets, Adobe Acrobat PDF, and even Microsoft PowerPoint presentation files. In total, over 150 document and file filters are bundled with interMedia Text.
| Note | The availability of filters is dependent upon the specific release of Oracle 8i. For example, Oracle 8.1.5 and Oracle 8.1.6 were released before the introduction of Microsoft Word 2000 for Windows. Hence, there is no filter for this document format in interMedia Text 8.1.5 or 8.1.6, but it is present in interMedia Text with Oracle 8.1.7. |
The filtering technology that is bundled with interMedia Text is licensed from Inso Corporation and, as far as accuracy and efficiency of filters go, I feel that these are the best on the market. The list of current file formats supported is documented in an appendix of the Oracle8i interMedia Text Reference.
| Note | At the time of writing, the Inso filters were not available on the Linux platform, and there were no plans to eventually make them available, which is unfortunate. This doesn't mean that interMedia Text cannot be exploited on a Linux platform, but it does mean that if you are deploying Oracle 8i on a Linux system, you'll either need to restrict your document types to plain text or HTML, or you'll have to create what is called a USER_FILTER object. |
interMedia Text is not just about storing files inside a database. An interMedia Text datastore object allows you to specify exactly where your text/data is stored. A datastore object basically supplies information to the interMedia Text index, letting it 'know' where your data is located. This information can only be specified at index creation time.
As we saw in an earlier example, the data for an interMedia Text index can come directly from the database, being stored internally in a column. This is the DIRECT_DATASTORE datastore object, and is the default datastore if none is specified. The database column type can be CHAR, VARCHAR, VARCHAR2, BLOB, CLOB, or BFILE. Also, please note that you can create an interMedia Text index on a column of type LONG or LONG RAW, but these are deprecated datatypes since the release of Oracle 8 and you absolutely should not use them for any new applications.
Another interesting datastore type that is based upon text stored internally in a column is the DETAIL_DATASTORE. The master/detail relationship is very common in any database application. Stated in simple terms, it describes a relationship from a single row in a master or parent table, to zero or more rows in a detail table, with typically a foreign-key constraint from the detail table to the master table. A purchase order is a good example of a master/detail relationship, with typically one row for a purchase order itself, and zero or more rows in the detail table making up the line items of the purchase order. A DETAIL_DATASTORE lets the application developer maintain this logical relationship.
Let's look at an example. Basically, we need to create a masterdetail structure that permits us to perform an interMedia Text query on a master table, but with the actual source of the data for the interMedia Text being derived from a detail table. To demonstrate, we first need to create our masterdetail tables and load them with data:
SQL> create table purchase_order 2 ( id number primary key, 3 description varchar2(100), 4 line_item_body char(1) 5 ) 6 / Table created. SQL> create table line_item 2 ( po_id number, 3 po_sequence number, 4 line_item_detail varchar2(1000) 5 ) 6 / Table created. SQL> insert into purchase_order ( id, description ) 2 values( 1, 'Many Office Items' ) 3 / 1 row created. SQL> insert into line_item( po_id, po_sequence, line_item_detail ) 2 values( 1, 1, 'Paperclips to be used for many reports') 3 / 1 row created. SQL> insert into line_item( po_id, po_sequence, line_item_detail ) 2 values( 1, 2, 'Some more Oracle letterhead') 3 / 1 row created. SQL> insert into line_item( po_id, po_sequence, line_item_detail ) 2 values( 1, 3, 'Optical mouse') 3 / 1 row created. SQL> commit; Commit complete.
Note that the column LINE_ITEM_BODY is essentially a 'dummy' column, as it simply exists so that we can create the interMedia Text index on a column in the master table. I never inserted any data into it. Now, prior to index creation, we need to set the interMedia Text preferences so that the index can locate the data to be indexed:
SQL> begin 2 ctx_ddl.create_preference( 'po_pref', 'DETAIL_DATASTORE' ); 3 ctx_ddl.set_attribute( 'po_pref', 'detail_table', 'line_item' ); 4 ctx_ddl.set_attribute( 'po_pref', 'detail_key', 'po_id' ); 5 ctx_ddl.set_attribute( 'po_pref', 'detail_lineno', 'po_sequence' ); 6 ctx_ddl.set_attribute( 'po_pref', 'detail_text', 'line_item_detail' ); 7 end; 8 / PL/SQL procedure successfully completed.
First we create a user-defined preference PO_PREF. It is a DETAIL_DATASTORE that will store all the information necessary to locate the data from the detail table. In subsequent lines, we define the name of the detail table, the key that joins to the master table, how the rows are ordered, and finally, the column that is actually getting indexed.
Now, we just create our index and see it in action:
SQL> create index po_index on purchase_order( line_item_body ) 2 indextype is ctxsys.context 3 parameters( 'datastore po_pref' ) 4 / Index created. SQL> select id 2 from purchase_order 3 where contains( line_item_body, 'Oracle' ) > 0 4 / ID ---------- 1
Although we created the index on LINE_ITEM_BODY, we could have specified the master table column DESCRIPTION when creating the index. However, bear in mind that any changes to this column (this is not a dummy column) will initiate a re-index operation on the master row and all associated detail rows.
interMedia Text also supports data sources external to the database - specifically, files external to the database as well as Uniform Resource Locators (URLs). In many work environments, files are commonly stored on a network-accessible shared file system. Rather than imposing the requirement to build an application that stores all text and document data within the Oracle database, the FILE_DATASTORE datastore object allows Oracle to manage the text index itself, and not the storage and security of the files. Using a FILE_DATASTORE, you would not store the text of the document in a column. Rather, you store a file system reference to the file, which must be accessible from the database server file system. So, even though you might be using a Windows client, for example, if the Oracle database was running on your favorite flavor of UNIX, your reference to the file would follow UNIX file system syntax, as in /export/home/tkyte/MyWordDoc.doc. Note that this type of external file access is unrelated to an alternative form of external file access from an Oracle database using BFILEs.
Another type of datastore external to the database is a URL_DATASTORE. This is very much like a FILE_DATASTORE datastore object, but instead of storing a file system reference in a column in the Oracle database, you can store a URL. At the time of indexing a row, interMedia Text will actually fetch the data over HTTP from the database server itself. Again, Oracle is not storing or managing the data itself. The index is constructed from the filtered contents from the HTTP stream, and the actual data fetched from the URL is discarded. FTP (file transfer protocol) is also a valid protocol when using a URL_DATASTORE, so interMedia Text can also index files accessible via FTP from the database server. With Oracle 8.1.7 and later, you can also embed the username and password directly in an FTP URL (for example, ftp://uid:pwd@ftp.bogus.com/tmp/test.doc).
Some people actually think that the URL_DATASTORE is good for building your own Web-crawling robot, but not much else (by the way, it does not have this Web-crawling capability out-of-the-box). This is not true. Colleagues of mine have architected a very large, distributed, Internet-accessible system with many different database nodes. A requirement of this system was to provide a single, unified search interface to all of the textual data across the nodes. They could have constructed a system with interMedia Text indexes on the database tables on each node, and then the query could be composed of many smaller, distributed queries against each node. However, rather than follow a route that may have led to non-optimal performance, they architected a solution that dedicated a single server to interMedia Text, and the indexes were created exclusively using the URL_DATASTORE datastore object. In this system, the distributed nodes were responsible for inserting URLs of new or modified content into the search data node. In this fashion, every time a new document was created or an existing one was modified, the machine responsible for indexing the content was given the URL to retrieve that content. So, instead of the indexing machine crawling all of the possible documents looking for new/modified content, the content providers notified the indexing machine of new content.. Not only did the distributed query go away, but also there was now a single point of truth, which resulted in less administration.
One of the most compelling reasons to use interMedia Text rather than a file system-based solution is the fact that it's an Oracle database. Firstly, an Oracle database is transactional in nature, whereas a file system is not. The integrity of your data will not be compromised, and the ACID properties of a relational database are also observed by interMedia Text.
| Note | For a definition of the ACID properties of a relational database, please reference Chapter 4 on Transactions. |
Secondly, in Oracle, the database manipulation language is SQL, and interMedia Text is wholly accessible from SQL. This enables an application developer to choose from a huge variety of tools that can 'talk' SQL. Then, if you were so inclined (not my recommendation), you could build an interMedia Text application that was accessible from Microsoft Excel, communicating over an ODBC driver to the Oracle database.
Having fulfilled many DBA roles in my career, I appreciate the fact that once all of my data is contained inside the Oracle database, when I backup my database I am backing up my application and all of its data. In the event of a recovery situation, I can restore the application and its data to any point in time. In a file system-based approach, I would always need to ensure that my database and file system were backed up, and hopefully, they were relatively consistent when the backup was performed.
One important caveat to this point, though, is that if you are using interMedia Text to index information contained outside of an Oracle database, your backup strategy is slightly altered. If you are using the URL_DATASTORE or FILE_DATASTORE datastores for your content, interMedia Text is only maintaining references to the content, and not managing the content as well. Thus, content can typically become stale, be deleted, or become unavailable as time passes and this can have an impact on your application. Additionally, no longer do you have a complete backup of your entire application when you backup the Oracle database. You will also need to develop a separate backup strategy for any content managed externally from the Oracle database.
Go to your favorite web search engine, type in a frequently occurring word on the Internet like 'database', and wait for the plethora of search results to return. Text indexing is very powerful and can be used in many applications. This is particularly true when the domain of information is very large, since it becomes difficult for an end user to sift through all of the data. interMedia Text has integrated features that let you transform all of this data into useful information.
Integrated into interMedia Text is an extensible knowledge base, used during indexing and analysis of the text, which confers considerable linguistic capabilities on this feature. Not only can we search the text, we can analyze the meaning of text. So, at index creation time, we can optionally generate themes for the interMedia Text index itself. This allows us to create applications that can, for example, analyze documents and classify them by theme, rather than by specific words or phrases.
When theme generation was first available in an Oracle database, I ran a quick test to see what it could do. I fed into a table in an Oracle database over one thousand news-clippings from various computer journals. I created an interMedia Text index on the column that was used to store the actual text of the articles, and then generated the themes for each article. I searched for all documents that had a theme of 'database', and the results included a news article that did not contain a single occurrence of the actual word 'database' and yet interMedia Text generated database as a theme. At first I thought that this was an error with interMedia Text itself but after a little careful thought, I realized that this was actually a very powerful feature - the ability to locate text in a database, based upon the meaning of the text. It is not statistical analysis, or some form of word-counting, it truly is linguistic analysis of the text.
A small example illustrates these capabilities:
SQL> create table mydocs 2 ( id number primary key, 3 thetext varchar2(4000) 4 ) 5 / Table created. SQL> create table mythemes 2 ( query_id number, 3 theme varchar2(2000), 4 weight number 5 ) 6 / Table created. SQL> insert into mydocs( id, thetext ) 2 values( 1, 3 'Go to your favorite Web search engine, type in a frequently 4 occurring word on the Internet like ''database'', and wait 5 for the plethora of search results to return.' 6 ) 7 / 1 row created. SQL> commit; Commit complete. SQL> create index my_idx on mydocs(thetext) indextype is ctxsys.context; Index created. SQL> begin 2 ctx_doc.themes( index_name => 'my_idx', 3 textkey => '1', 4 restab => 'mythemes' 5 ); 6 end; 7 / PL/SQL procedure successfully completed. SQL> select theme, weight from mythemes order by weight desc; THEME WEIGHT ---------------------------------------- ---------- occurrences 12 search engines 12 Internet 11 result 11 returns 11 databases 11 searches 10 favoritism 6 type 5 plethora 4 frequency 3 words 3 12 rows selected.
Our PL/SQL procedure takes the table pointed to by the index MY_IDX, finds the row with key = 1 and retrieves the data that is indexed. It then runs that data through a thematic analyzer. This analyzer generates, and assigns a weight to, all of the 'themes' of the document (for example, a paper on banking might extract themes like 'money', 'credit', and so on). It puts these themes into the results table MYTHEMES.
Now if I did this for all of the data within my application, my end users would now have the ability to search, not only for those rows which contained a certain word, but also be able to search for those rows which were most similar in meaning to the text in a particular row.
Note that with more information for interMedia Text to analyze, the themes can be calculated with much greater accuracy than the simple sentence shown in the example above.
Also note that I created an ID column with a primary key constraint. In interMedia Text in Oracle 8i 8.1.6 and earlier, the existence of a primary key was required before you could create an interMedia Text index on the same table. In Oracle 8i 8.1.7 and later, interMedia Text no longer requires the existence of a primary key.
People frequently ask me how they can efficiently search within a document that has embedded markup, like HTML or XML. Fortunately, interMedia Text makes this trivial through the use of an object called sections.
This solution really comes together when you combine the features of XML parsing and section definition with a URL_DATASTORE. If XML lives up to its billing as the communication medium through which heterogeneous systems communicate, then an application developer armed with interMedia Text can quickly create a searchable, online knowledgebase of data from a variety of systems. A full example of indexing XML is presented later in this chapter.
This section will describe how interMedia Text is implemented and what you can expect from your use of it.
As mentioned earlier, interMedia Text was built using the Oracle-provided extensibility framework. Using this facility, the interMedia Text team was able to introduce into Oracle the equivalent of a text-specific type of index. If we take a look at some of the underlying database objects, we can pull back the covers, and begin to learn how this is truly implemented. The database objects that make up interMedia Text are always owned by user CTXSYS:
SQL> connect ctxsys/ctxsys Connected. SQL> select indextype_name, implementation_name 2 from user_indextypes; INDEXTYPE_NAME IMPLEMENTATION_NAME ------------------------------ ------------------------------ CONTEXT TEXTINDEXMETHODS CTXCAT CATINDEXMETHODS
Here, we see that there are actually two index types owned within the interMedia Text schema. The first index type is CONTEXT, one which most interMedia Text users are familiar with. The second index type, CTXCAT, is a catalog index type, and provides a subset of the functionality available with a CONTEXT index type. The catalog index type, which was introduced with Oracle 8.1.7, is ideal for textual data that can be classified as short text fragments.
SQL> select library_name, file_spec, dynamic from user_libraries; LIBRARY_NAME FILE_SPEC D ------------------------------ ---------------------------------------- - DR$LIB N DR$LIBX O:\Oracle\Ora81\Bin\oractxx8.dll Y
Here, we can see that there are two libraries associated with interMedia Text. The library DR$LIB is not dynamic, and hence, is a library of trusted code within the Oracle database itself. The other library, DR$LIBX, is a shared library, and is on the dependant operating system. As the above query was performed against a database running in a Windows environment, the file specification to this shared library is Windows-specific. If you perform the same query in a UNIX environment, you will see something different. These libraries are specific to interMedia Text. They contain a collection of methods, so that the Oracle kernel can handle these interMedia Text index types.
SQL> select operator_name, number_of_binds from user_operators; OPERATOR_NAME NUMBER_OF_BINDS ------------------------------ --------------- CATSEARCH 2 CONTAINS 8 SCORE 5
Within the extensibility framework, you can also define a rather unique database object called an operator. An operator is referenced by an index type, and has a number of bindings associated with each operator. Very much like in PL/SQL, where you can define a function with the same name but different parameter types, the extensibility framework lets you define an operator that maps to different user-defined methods, based upon the signature of its use.
SQL> select distinct method_name, type_name from user_method_params order by 2 type_name; METHOD_NAME TYPE_NAME ------------------------------ ------------------------------ ODCIGETINTERFACES CATINDEXMETHODS ODCIINDEXALTER CATINDEXMETHODS ODCIINDEXCREATE CATINDEXMETHODS ODCIINDEXDELETE CATINDEXMETHODS ODCIINDEXDROP CATINDEXMETHODS ODCIINDEXGETMETADATA CATINDEXMETHODS ODCIINDEXINSERT CATINDEXMETHODS ODCIINDEXTRUNCATE CATINDEXMETHODS ODCIINDEXUPDATE CATINDEXMETHODS ODCIINDEXUTILCLEANUP CATINDEXMETHODS ODCIINDEXUTILGETTABLENAMES CATINDEXMETHODS RANK CTX_FEEDBACK_ITEM_TYPE ODCIGETINTERFACES TEXTINDEXMETHODS ODCIINDEXALTER TEXTINDEXMETHODS ODCIINDEXCREATE TEXTINDEXMETHODS ODCIINDEXDROP TEXTINDEXMETHODS ODCIINDEXGETMETADATA TEXTINDEXMETHODS ODCIINDEXTRUNCATE TEXTINDEXMETHODS ODCIINDEXUTILCLEANUP TEXTINDEXMETHODS ODCIINDEXUTILGETTABLENAMES TEXTINDEXMETHODS ODCIGETINTERFACES TEXTOPTSTATS 21 rows selected.
After viewing these results, it should start to become clear how a developer could exploit the extensibility framework within the Oracle database. Associated with each type are sets of named methods, which the Oracle extensibility framework defines by a unique name. For example, the methods associated with the maintenance of an index, ODCIIndexInsert, ODCIIndexUpdate, and ODCIIndexDelete are invoked by Oracle when data that is associated with an index is created, modified, or deleted. Thus, when maintenance needs to be performed on an interMedia Text index for a new row, the Oracle engine invokes the method associated with ODCIIndexInsert. This custom routine then performs whatever operations are necessary against the interMedia Text index, and then signals to the Oracle database that it is complete.
Now that we've seen the underlying architecture of the implementation of interMedia Text, let's take a look at some of the database objects associated with this custom index type when you actually create an interMedia Text index.
SQL> select table_name 2 from user_tables 3 where table_name like '%MYTEXT%'; TABLE_NAME ------------------------------ MYTEXT SQL> create index mytext_idx 2 on mytext( thetext ) 3 indextype is ctxsys.context 4 / Index created. SQL> select table_name 2 from user_tables 3 where table_name like '%MYTEXT%'; TABLE_NAME ------------------------------ DR$MYTEXT_IDX$I DR$MYTEXT_IDX$K DR$MYTEXT_IDX$N DR$MYTEXT_IDX$R MYTEXT
We started in our SQL*PLUS session, by querying from the USER_TABLES views for all tables that contained the name MYTEXT. After creating our table MYTEXT, and an interMedia Text index on this same table, we see that we now have a total of five tables that contain this name, including our original table.
There are a total of four tables that are automatically created whenever you create an interMedia Text index. These table names will always have a prefix of DR$, then the index name, and then a suffix of either $I, $K, $N, or $R. These tables will always be created in the same Oracle schema that owns the interMedia Text index. Let's examine them in greater detail.
SQL> desc dr$mytext_idx$i; Name Null? Type ----------------------------------------- -------- ----------------------- TOKEN_TEXT NOT NULL VARCHAR2(64) TOKEN_TYPE NOT NULL NUMBER(3) TOKEN_FIRST NOT NULL NUMBER(10) TOKEN_LAST NOT NULL NUMBER(10) TOKEN_COUNT NOT NULL NUMBER(10) TOKEN_INFO BLOB SQL> desc dr$mytext_idx$k; Name Null? Type ----------------------------------------- -------- ----------------------- DOCID NUMBER(38) TEXTKEY NOT NULL ROWID SQL> desc dr$mytext_idx$n; Name Null? Type ----------------------------------------- -------- ----------------------- NLT_DOCID NOT NULL NUMBER(38) NLT_MARK NOT NULL CHAR(1) SQL> desc dr$mytext_idx$r; Name Null? Type ----------------------------------------- -------- ----------------------- ROW_NO NUMBER(3) DATA BLOB
Every interMedia Text index will have a set of tables created with a similar structure as the ones above. The token table DR$MYTEXT_IDX$I is the primary table of any interMedia Text index. This table is used to maintain an entry for every token indexed, as well as a bitmap of all documents that contain this token. Other binary information is stored in this table to maintain the proximity information of the tokens within the columns themselves. Note that I'm careful to use the word 'token' in this context, as interMedia Text has the ability to index several multi-byte languages, including Chinese, Japanese, and Korean. It would be inaccurate to say the DR$I table is used to index 'words'.
The DR$K and DR$R tables are merely mapping tables between a ROWID and a document identifier.
The last table, the DR$N table, or 'negative row table', is used to maintain a list of documents/rows which have been deleted. In effect, when you delete a row from a table that has an interMedia Text index defined on it, the physical removal of this row's information from the interMedia Text index is deferred. Using this internal table, the row is referenced by a document identifier, and marked for cleanup during the next rebuild or optimization.
Also, note that the DR$K and DR$N tables are created as index-organized tables. If at any time either of these tables is referenced from interMedia Text code, it will typically reference both columns contained in the table. For efficiency, and to avoid any extra I/O, these were implemented as index-organized tables.
Let me summarize this section by saying that although it's interesting to look at how interMedia Text is implemented within the Oracle extensibility framework, it is by no means essential information to effectively using interMedia Text. On the contrary, many developers have built quite sophisticated applications using interMedia Text, but were not completely aware of the purpose of these underlying tables.
Using the sample table we created in the previous section, let's step through the actual process of inserting text, and controlling when interMedia Text actually processes our changes:
SQL> delete from mytext; 2 rows deleted. SQL> insert into mytext( id, thetext ) 2 values( 1, 'interMedia Text is quite simple to use'); 1 row created. SQL> insert into mytext( id, thetext ) 2 values( 2, 'interMedia Text is powerful, yet easy to learn'); 1 row created. SQL> commit; Commit complete.
So at this point, do you think we should be able to query for the token text, which would return both rows from our table? The answer is really, 'maybe'. If the index is not synchronized, then the underlying interMedia Text index will not have been updated with our changes yet. To synchronize an index means to bring it up to date with the pending changes. So, how do we tell if there are pending updates to the interMedia Text index?
SQL> select pnd_index_name, pnd_rowid from ctx_user_pending; PND_INDEX_NAME PND_ROWID ------------------------------ ------------------ MYTEXT_IDX AAAGFlAABAAAIV0AAA MYTEXT_IDX AAAGFlAABAAAIV0AAB
By querying the CTX_USER_PENDING view, we're able to determine that there are two rows associated with the MYTEXT_IDX interMedia Text index that are pending update. This view, CTX_USER_PENDING, is a view on the CTXSYS-owned table DR$PENDING. Any time a new row is inserted into our table MYTEXT, a row will also be inserted into the table DR$PENDING for the MYTEXT_IDX interMedia Text index. Both of these inserts occur within the same physical transaction, so that if the transaction, which inserted into the MYTEXT table was rolled back, the insert into DR$PENDING would also be rolled back.
There are three different ways in which an interMedia Text index can be synchronized. These can be performed in a number of different scenarios, and for a number of different reasons. Later, I will talk about why you would choose one method over the other.
The simplest method to synchronize an index is to run the program ctxsrv. This program runs in a fashion similar to a UNIX daemon. You start this program, let it run in the background, and it will automatically take care of periodically synchronizing the index. This method is advisable when you are dealing with a small set (under 10,000 rows), and each row does not comprise a great deal of text.
Another method to synchronize the index is to issue the ALTER INDEX statement. It is usually not an issue to allow the queue of pending requests to build up and process the synchronization actions to the index in batch. In many cases, this is the preferred method of synchronizing an index, as it will minimize overall index fragmentation. The syntax used to 'sync' an index is:
alter index [schema.]index rebuild [online] parameters( 'sync [memory memsize]' )
You would want to rebuild an index online to make it available during the 'sync' process. Additionally, you can specify the amount of memory to be used during the synchronization process. The more memory specified for the 'sync' process, then the larger the indexing batch size can be, which will ultimately result in a more compact interMedia Text index.
Although some may argue that this truly isn't a repeatable way to synchronize an index, I assert that a third way to synchronize an index is to simply create the index itself. When you issue the CREATE INDEX statement for an index of type CONTEXT, it will result in the creation of the index, as well as the indexing of any column data upon which the index was created. This usually throws people for a loop when they have existing data in their table. They create their index, and then they add subsequent rows to the table. As the changes incurred by the new rows aren't realized until the index is synchronized, many people have come to me complaining that the only way to bring their index up to date is to drop and then recreate! It truly is a way to keep your index in synchronization, albeit a wholly inefficient one which I would strongly discourage.
The semantics of SQL inherently prevent two distinct users from issuing an ALTER INDEX REBUILD on the same index concurrently, but there are no restrictions from rebuilding or synchronizing multiple interMedia Text indexes at the same time.
Continuing with our example, we will synchronize our index:
SQL> alter index mytext_idx rebuild online parameters('sync memory 20M'); Index altered. SQL> select pnd_index_name, pnd_rowid from ctx_user_pending; no rows selected
At this point, our index is synchronized, and we should be ready to perform a query using it:
SQL> select id 2 from mytext 3 where contains( thetext, 'easy') > 0 4 / ID ---------- 2
Let's explore a little further, and look at the data in one of the internal tables that was created when we created the interMedia Text index:
SQL> select token_text, token_type from dr$mytext_idx$i; TOKEN_TEXT TOKEN_TYPE ---------------------------------------------------------------- ---------- EASY 0 INTERMEDIA 0 LEARN 0 POWERFUL 0 QUITE 0 SIMPLE 0 TEXT 0 USE 0 YET 0 interMedia Text 1 learning 1 TOKEN_TEXT TOKEN_TYPE ---------------------------------------------------------------- ---------- powerfulness 1 simplicity 1
By querying the DR$I table associated with the index MYTEXT_IDX, we are able to examine some of the information that interMedia Text processed during index synchronization.
Firstly, notice that many of the values for TOKEN_TEXT are in all uppercase. These are the actual words from our text itself, and are stored in a case-normalized fashion. If we so desired, we could have directed interMedia Text to create an index in mixed case when we issued the CREATE INDEX statement.
The next thing you should notice is that there are a number of tokens where TOKEN_TYPE=1, which are stored in mixed case. More importantly though, you should note that the data 'simplicity' and 'learning' are not contained in either of the rows in the MYTEXT table. So where did this data come from? The default setting for the lexer (a lexer is used to determine how to break apart a block of text into individual tokens) in the English language is to generate theme index information as well. Thus, every row where TOKEN_TEXT=1, is a generated theme from interMedia Text's linguistic analysis.
Lastly, something you may not notice right away is the absence of certain words in this table. The words is and to are not part of the table of tokens, although they were part of the data in our table. These are considered stopwords, and are considered poor information to index, as they are frequently occurring in much English text, and can be classified essentially as noise. The words is, to, and about 120 others are part of the default stoplist for the English language, which was used, by default, when we created the index. Oracle delivers interMedia Text with default stoplists for over 40 different languages. Keep in mind that there is no requirement for you to use a stoplist, and you can also create your own customized stoplist.
To conclude this section, I want to issue a word of caution. While it is quite interesting to look at how interMedia Text is implemented, especially by looking at the tokens generated when we create an index, you should never create other database objects on these internally implemented structures. As an example, you should not create a view on top of the DR$MYTEXT_IDX$I table. Nor should you create a trigger on the DR$MYTEXT_IDX$K table. The structure of this implementation is subject to change and most likely will change in subsequent releases of the product.
With the introduction of the ABOUT operator, Oracle has greatly simplified the use of themes in queries, and has also dramatically improved the precision of queries overall. In the English language implementation, the ABOUT operator will search for all the rows that match the normalized representation of the concept provided. I mentioned earlier that the default setting was True for generation of theme index information in the English language. This theme index information will be used to locate other rows that match in concept. If for some reason you decided not to generate theme index information, the ABOUT operator would revert to a simple token-based search.
SQL> select id from mytext where contains(thetext,'about(databases)') > 0; no rows selected
As expected, there are no rows in our example table that are about the concept 'databases'.
SQL> select id from mytext where contains(thetext,'about(simply)') > 0; ID ---------- 1
There is one row that is about the concept simply. To be accurate, there is one row that is similar in concept to the normalized version of the word simply. To prove this:
SQL> select id from mytext where contains(thetext,'simply') > 0; no rows selected
When we remove the ABOUT operator from our query, no rows are returned, our thetext column has no occurrences of the word simply. There is one row that matches in concept to the normalized root of simply though.
The concept associated with a word is not the same as the linguistic root of a word. Use of the stem operator ($) in interMedia Text, enables the user to search for inflectional or derivational stems of a word. Thus, a stem search on the word 'health' may include in the result, documents that contain the word 'healthy'. An ABOUT search of health may also return documents that contain the word wellness though.
The ABOUT operator is very easy to include in your applications, and permits application developers to exploit the power of theme generation, and linguistic analysis with little effort. The ABOUT operator moves an application from enabling the end user to search using words, to now searching across concepts. Quite powerful, indeed.
The last topic I'll cover in-depth is section searching. Sections enable granular query access to a document, and can greatly improve the precision of these queries. A section can really be nothing more than an application developer-defined sequence of characters to delimit the start and end of a logical unit of a document. The popularity of several standard markup languages, such as HTML and XML, will show the power of section searching within interMedia Text.
A typical document has commonly occurring logical elements that comprise its structure. Most documents have some form of name, may have a heading, may have boilerplate information, most likely have a body, and can contain a table of contents, glossary, appendix, and so on. All of these things are logical units that make up the structure of a document.
As an example of where this could be useful, consider a fictitious Department of Defense. It may be adequate just to find all documents in our library that contains the phrase 'Hellfire missile'. It may be even more meaningful to find all documents in our library that contain the phrase 'Hellfire missile' in the title of the document, or perhaps in the glossary of the document. interMedia Text provides a way to let an application developer define the sequence of characters that portray these logical sections of the structure of a document. Additionally, interMedia Text supports searching within these same logical sections.
interMedia Text uses the term 'sections' to specify these logical units, or grouping of text within a document. A collection of section identifiers is aggregated into a section group, and it is this section group that can be referenced when creating an interMedia Text index.
Hypertext Markup Language, or HTML, originally started as a way to denote structure within a document, but it quickly transformed into a language that was part structural representation, and part visual rendering. Regardless, interMedia Text ships with the default components to let an application developer create a section of logical structural unit out of every markup tag contained within a document.
In the same fashion, XML support is native in interMedia Text starting with Oracle 8.1.6. Given an arbitrary XML document, you can easily (and automatically, if you wish) define sections for every XML element within that document.
Let's first take a look at the following HTML example:
SQL> create table my_html_docs 2 ( id number primary key, 3 html_text varchar2(4000)) 4 / Table created. SQL> insert into my_html_docs( id, html_text ) 2 values( 1, 3 '<html> 4 <title>Oracle Technology</title> 5 <body>This is about the wonderful marvels of 8i and 9i</body> 6 </html>' ) 7 / 1 row created. SQL> commit; Commit complete. SQL> create index my_html_idx on my_html_docs( html_text ) 2 indextype is ctxsys.context 3 / Index created.
At this point, we should be able to locate a row, based upon any indexed word in the HTML document.
SQL> select id from my_html_docs 2 where contains( html_text, 'Oracle' ) > 0 3 / ID ---------- 1 SQL> select id from my_html_docs 2 where contains( html_text, 'html' ) > 0 3 / ID ---------- 1
We can easily construct a query to locate all rows that contain the word Oracle, but we immediately have a couple of things we'd like to change about our current solution. Firstly, the actual markup elements themselves should not be indexed, as these will be commonly occurring, and are not part of the actual content of the document. Secondly, we're able to search for words within our HTML document, but not with respect to the structural element that contains them. We know that we have a row that contains Oracle somewhere in the content, but it could be in the heading, the body, the footer, the title, and so on.
Let's assume that our application requirement is to be able to search within the titles of our HTML documents. To easily accomplish this, we will create a section group containing a field section for TITLE, and then drop and recreate the index:
SQL> begin 2 ctx_ddl.create_section_group('my_section_group','BASIC_SECTION_GROUP'); 3 ctx_ddl.add_field_section( 4 group_name => 'my_section_group', 5 section_name => 'Title', 6 tag => 'title', 7 visible => FALSE ); 8 end; 9 / PL/SQL procedure successfully completed. SQL> drop index my_html_idx; Index dropped. SQL> create index my_html_idx on my_html_docs( html_text ) 2 indextype is ctxsys.context 3 parameters( 'section group my_section_group' ) 4 / Index created.
We have created a new section group called MY_SECTION_GROUP, and added a field section with a name of Title. Note that our section name maps to the tag title, and that this section will not be visible. If a field section is marked as visible, the text contained within the tags will be treated as part of the containing document. If a field section is marked as not visible, then the contents between the field section start and end tags are treated as separate from the containing document, and will only be searchable when querying within the field section.
Like most modern markup languages (for example, XML, HTML, WML), in interMedia Text a start tag begins with <, and ends with >. Additionally, an end tag begins with the sequence </, and ends with >.
SQL> select id 2 from my_html_docs 3 where contains( html_text, 'Oracle' ) > 0 4 / no rows selected
A query that previously returned our single row now returns zero rows, and all we have done is define a section group for the interMedia Text index. Remember that I defined the field section as not visible, so the text contained within the title tags is treated as a sub-document.
SQL> select id 2 from my_html_docs 3 where contains( html_text, 'Oracle within title' ) > 0 4 / ID ---------- 1
We are now able to execute a query that restricts our text search to only the title field sections across all of our documents. And if we try and search for the text of the tag itself we see that interMedia Text does not index the text of the tags themselves:
SQL> select id 2 from my_html_docs 3 where contains( html_text, 'title' ) > 0 4 / no rows selected
Although earlier I defined my own section group type based upon the section group BASIC_SECTION_GROUP, interMedia ships with pre-defined section group system preferences for HTML and XML, called HTML_SECTION_GROUP and XML_SECTION_GROUP respectively. The use of this section group does not automatically define sections for every possible HTML and XML element. You would still need to define these yourself, but by the use of these section groups, interMedia Text will now know how to correctly transform your marked up document to text. If we now apply this to our example:
SQL> drop index my_html_idx; Index dropped. SQL> create index my_html_idx on my_html_docs( html_text ) 2 indextype is ctxsys.context 3 parameters( 'section group ctxsys.html_section_group' ) 4 / Index created. SQL> select id 2 from my_html_docs 3 where contains( html_text, 'html') > 0 4 / no rows selected
By simply specifying the preference of the HTML_SECTION_GROUP section group, we now avoid indexing all of the elements that are HTML markup tags within our document. This will not only improve the accuracy of all queries against our documents, but will also reduce the overall size of the interMedia Text index. For example, lets say for whatever reason, I wanted to search for the word title across all of my HTML documents. If I did not use the HTML_SECTION_GROUP for my interMedia Text index, I might end up with every HTML document that had a title section (that is, a section in the HTML document delimited by the <title> and </title> tags) in my result. By ignoring the tags and focusing solely on the content of my HTML documents, my search accuracy is greatly improved.
Now, switching our focus to processing of XML documents, let's say you had a requirement to manage a collection of XML documents, and also provide an interface with which to query within the structural elements of an XML document. To further compound the issue, your collection of XML documents may not all conform to the same structural definition contained in a DTD (Document Type Definition).
Following the previous examples, you may think that your only option is to determine all the searchable elements over all of your XML documents, and then go about defining interMedia Text sections for every one of these elements. Fortunately, interMedia Text includes a facility to automatically create and index sections out of every tag contained within a document.
Introduced with interMedia Text in Oracle 8.1.6, the AUTO_SECTION_GROUP operates just like the XML_SECTION_GROUP section group, but removes the burden from the application developer to define, in advance, all of the sections. The AUTO_SECTION_GROUP section group will instruct interMedia Text to automatically create sections for any non-empty tags within a document. Whereas, an application developer can map an arbitrary section name to a tag, these generated sections will be defined with the same name as the tag itself.
SQL> create table my_xml_docs 2 ( id number primary key, 3 xmldoc varchar2(4000) 4 ) 5 / Table created. SQL> insert into my_xml_docs( id, xmldoc ) 2 values( 1, 3 '<appointment type="personal"> 4 <title>Team Meeting</title> 5 <start_date>31-MAR-2001</start_date> 6 <start_time>1100</start_time> 7 <notes>Review projects for Q1</notes> 8 <attendees> 9 <attendee>Joel</attendee> 10 <attendee>Tom</attendee> 11 </attendees> 12 </appointment>' ) 13 / 1 row created. SQL> commit; Commit complete. SQL> create index my_xml_idx on my_xml_docs( xmldoc ) 2 indextype is ctxsys.context 3 parameters('section group ctxsys.auto_section_group') 4 / Index created.
At this point, without any further intervention, interMedia Text has automatically created sections for every tag contained within our XML documents. So if we wanted to locate all documents that contained the word projects in a note element, we would issue:
SQL> select id 2 from my_xml_docs 3 where contains( xmldoc, 'projects within notes' ) > 0 4 / ID ---------- 1
The auto-sectioning process creates a special kind of section called a zone section. Whereas our previous examples showing section definition demonstrated the use of field sections, zone sections are a different type of section. Unlike field sections, zone sections can overlap one another, and can also be nested. As the AUTO_SECTION_GROUP section group instructs interMedia Text to create zone sections out of all non-empty tags, we can now issue queries like:
SQL> select id 2 from my_xml_docs 3 where contains( xmldoc, 'projects within appointment' ) > 0 4 / ID ---------- 1 SQL> select id 2 from my_xml_docs 3 where contains( xmldoc, 'Joel within attendees' ) > 0 4 / ID ---------- 1
The section specified in these two previous queries do not directly contain the search terms, but the search terms are nested within the structure of these sections. Zone sections, and the use of auto-sectioning, enable users to control the scope of a search across an XML document. It can be as broad or narrow as you see fit.
With the use of section groups, you can instruct interMedia Text to index attributes of tags themselves. Again with the AUTO_SECTION_GROUP section group, attribute values of sections are also automatically created and indexed.
So, if we wished to locate all appointments that are personal, that is, all XML documents that contain the string personal as an attribute called type within the tag appointment, we would issue:
SQL> select id 2 from my_xml_docs 3 where contains( xmldoc, 'personal within appointment@type' ) > 0 4 / ID ---------- 1
As you can see, the definition and indexing of sections is a very powerful feature of interMedia Text. One point you want to remember, though, is that you do not always want to use the AUTO_SECTION_GROUP. Although there is a facility to instruct interMedia Text and the auto-sectioner not to index certain tags, you may end up indexing and creating sections for many document elements, which may pollute the index itself. This will increase the overall size of the index and possibly degrade performance. It is a powerful feature, but it should be used with prudence.
There are a number of issues you should be aware of when using interMedia Text. Not all of these are readily apparent, but I will address the most common ones that I have come across in my experience.
Rather than a caveat, this is more of a common 'mischaracterization' of interMedia Text. Upon the mention of interMedia Text, I have heard customers, and Oracle employees alike, refer to this as Oracle's document management solution. Without question, interMedia Text is not document management.
Document management is a science in its own right. A document management solution is a system with a feature set that assists in the life cycle of documents. This can include basic check-in and check-out functionality, multiple versions and subversions of documents, access control lists, a logical collection structure, text query interface, and also a mechanism to publish documents.
interMedia Text itself is not document management. interMedia Text can be exploited in a document management system, and provides many of the building blocks that you would find in complete systems. As a matter of fact, Oracle has tightly integrated the features of interMedia Text with the Oracle Internet File System, which can be used for content management, and provides much of the basic document management functionality.
There are many times when you would want to build a system and run the process ctxsrv in the background to periodically synchronize your interMedia Text index, and keep it in synchronization in almost real-time. One of the issues that can arise from performing many frequent index synchronizations for a large document set, is that the interMedia Text index can end up in a non-compact, fragmented state.
There is no hard and fast rule that states that you should periodically synchronize your indexes in batches, versus synchronizing them with ctxsrv as soon as they are committed. Much of this is dependent upon the nature of your application, how frequently text is updated, if at all, how many documents are in the total set, and how large your documents are.
A good example is my AskTom web site. This is a site used by Oracle customers to ask technical questions about Oracle products. The questions are reviewed, followed up with a (hopefully) descriptive response and, optionally, published. Once these questions and answers are published, they are inserted into a table that is indexed with an interMedia Text index. This table of published questions and answers is searchable via a page on the AskTom web site.
This system has a relatively small number of total rows (less than 10,000 at the time of this writing). There are hardly ever any updates to the system; once the questions and answers are published, they are almost never updated, or deleted. The number of total rows inserted on any given day is usually 25 or less, and these occur throughout the day. We perform index synchronization via the ctxsrv process running in the background, which is ideal for this system as it is primarily search-only, with very few inserts and updates.
On the other hand, if you were preparing to periodically load one million documents into a table over the next week, it would be ill-advised to index these using ctxsrv. You typically want the batch size to be as large as possible without causing memory paging when interMedia Text synchronizes an index. Letting these requests build up in the queue, and then processing in a large batch will result in a more compact index.
Regardless of whichever method is chosen, you should periodically optimize your interMedia Text indexes using the ALTER INDEX REBUILD syntax. The optimization process will not only result in a more compact state of your index, but the information maintained from previous logical deletions will be cleaned up as well.
interMedia Text does not constrain the location of your textual data to the database. You could choose to store your textual data directly in a database, but you can also use interMedia Text to index information contained in documents on the server file system, or even documents accessible via a URL.
When your data is maintained in an Oracle database, any updates to this data are automatically processed by interMedia Text. When the source of your data is outside of the database though, the onus is now on the application developer to ensure that data updated outside of the database is synchronized with the index.
The easiest way to trigger an update on an individual row is to update one of the columns indexed by an interMedia Text index. As an example, if I used the following table and index to maintain my list of indexed URLs:
SQL> create table my_urls 2 ( id number primary key, 3 theurl varchar2(4000) 4 ) / Table created. SQL> create index my_url_idx on my_urls( theurl ) 2 indextype is ctxsys.context 3 parameters( 'datastore ctxsys.url_datastore' ) 4 / Index created.
I could easily trigger a 'refresh' of a particular row by issuing:
SQL> update my_urls 2 set theurl = theurl 3 where id = 1 4 / 0 rows updated.
By document services, I mean the set of services provided by interMedia Text to filter 'on-demand' a document into text or HTML, and also, optionally, to highlight the hits in a document as the result of a search.
Another common misconception that some people have is that interMedia Text maintains all information needed to reconstruct an entire document. This misconception leads people to believe that once a document is indexed, the actual source of the document can be discarded. This is true if all you will ever do are queries against the indexed information, but it is not true if you want to employ some of these document services.
For example, if you create an interMedia Text index using a URL_DATASTORE, and you wish to generate an HTML rendition of a row via the CTX_DOC.FILTER procedure; if the URL is not accessible, the call to CTX_DOC.FILTER will fail. interMedia Text needs to access the original document to perform this operation. This also applies to files that reside outside of the database on the file system, indexed using the FILE_DATASTORE preference.
There are many situations where an interMedia Text index provides much more functionality than is required by the application. The use of an interMedia Text index also entails certain maintenance functions to keep the index synchronized, optimized, and so on. In order to support applications where the full functionality of an interMedia Text index is not required, a new index type called the catalog index, or ctxcat for short was introduced with the release of interMedia Text in Oracle 8.1.7.
Typically, the majority of textual data is not stored in a database system as a large collection of documents. In many database applications, text is typically not formatted, occurs in small fragments, is not broken up into logical sections, and the sections are so small as to inhibit high quality linguistic analysis. Also, these types of database-centric applications often perform queries against the textual data combined with other relational constraints. For example, consider a database used to log reported problems, along with a description of how to resolve them. It may have, say, an 80 character free-form subject field (what the problem is about) and a large text field with the problem resolution and description. It might also store other pieces of structured information, such as the date the problem was reported, the analyst working on it, the product on which the problem occurred, and so on. Here we have a combination of text (not a document persay) and structured data. It will be frequently queried to find the answers to questions such as, 'find all problems regarding the database (a product) version 8.1.6 (another attribute) that contains a reference to "ORA-01555" in the subject (a text search)'. It is for these types of applications that the catalog index was created.
As you might expect from a 'cut-down' version of the full index, there are a number of limitations to ctxcat. The types of query operators supported by a catalog index are a subset of the operators supported in a 'full' interMedia Text index. The text that you wish to index with ctxcat must be contained in the Oracle database and must be plain text. Furthermore, ctxcat does not support multiple languages within the same index. However, given these limitations, the catalog index performs exceedingly well in many applications.
One nice feature of the catalog index is the lack of index maintenance. DML performed against a catalog index is entirely transactional. Hence, there is no requirement to periodically synchronize a catalog index, or run the ctxsrv process for periodic synchronization.
Another compelling reason to use a catalog index is its inherent support for structured queries. The application developer can define index sets, which are used by the catalog index to process efficiently both text search, and a query across other structural data. An index set allows interMedia to store some of the structured relational information in its index, along with the text it is indexing. This will allow interMedia using both a text search and structured search at the same time, in order to find documents that meet very specific criteria Let's take a look at a short example:
SQL> create table mynews 2 ( id number primary key, 3 date_created date, 4 news_text varchar2(4000) ) 5 / Table created. SQL> insert into mynews 2 values( 1, '01-JAN-1990', 'Oracle is doing well' ) 3 / 1 row created. SQL> insert into mynews 2 values( 2, '01-JAN-2001', 'I am looking forward to 9i' ) 3 / 1 row created. SQL> commit; Commit complete. SQL> begin 2 ctx_ddl.create_index_set( 'news_index_set' ); 3 ctx_ddl.add_index( 'news_index_set', 'date_created' ); 4 end; 5 / SQL> create index news_idx on mynews( news_text ) 2 indextype is ctxsys.ctxcat 3 parameters( 'index set news_index_set' ) 4 / Index created.
Note that the index type I specified to create a catalog index is CTXSYS.CTXCAT. Additionally, I created an index set NEWS_INDEX_SET, and added the column DATE_CREATED to this index set. This will enable interMedia Text to efficiently process any queries that include constraints on both the columns NEWS_TEXT and DATE_CREATED.
SQL> select id 2 from mynews 3 where catsearch( news_text, 'Oracle', null ) > 0 4 and date_created < sysdate 5 / ID ---------- 1 SQL> select id 2 from mynews 3 where catsearch( news_text, 'Oracle', 'date_created < sysdate' ) > 0 4 / ID ---------- 1
Here, we can see both methods of locating all rows that contain Oracle in the text of the news, and where the DATE_CREATED is earlier than today. The first query would first use interMedia to find all of the rows that might satisfy our query and would then access those rows to see if the DATE_CREATED was less than SYSDATE. The second, more efficient query, would use the interMedia index, which has incorporated the DATE_CREATED column, to find only those rows that satisfy the text query and the DATE_CREATED < SYSDATE constraint simultaneously. Rather than using the CONTAINS operator, a search using the catalog index uses the CATSEARCH operator. As we had previously defined an index set which contains the DATE_CREATED column, I was also able to specify the structured condition of our query directly in the CATSEARCH operator. Using this information, Oracle can very efficiently process both constraints in the query.
There are a number of restrictions on the type of conditions that can be specified in the structure constraints of the CATSEARCH operator, specifically that it only supports AND, OR, and NOT logical operators, but for a large number of applications, the catalog index is suitable, and often preferred.
In my dealings with application developers and customers who use interMedia Text, I tend to see the same few issues cropping up time and again. They are the ones I cover in this section.
A number of people have come to me inquiring why some of their information is indexed, but not any new rows they recently added. The most common cause of this is that the interMedia Text index is simply out of sync. If you query the view CTX_USER_PENDING, you can quickly assess if there are pending indexing requests. If so, simply synchronize the index using one of the methods described earlier.
Another common cause for this apparent lack of indexed information is errors, especially during the filtering process. If you are attempting to index documents with the Inso filters, and the document format is not supported, this will result in errors, and the specific document will not be indexed. If you query the view CTX_USER_INDEX_ERRORS, this should provide suitable information in tracking down the indexing issue.
In the version of interMedia Text supplied with Oracle 8.1.5 and 8.1.6, all filtering of text was handled via the Inso filters through external procedures. External procedures are functions that are typically written in C, stored in a shared library on the database server, and invoked from PL/SQL. These external procedures run in an address space that is separate from the Oracle server itself.
If you have documents to be filtered by interMedia Text, but external procedures are not properly configured on the database server, this will typically result in one or more of the following errors:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine ORA-20000: interMedia text error: DRG-50704: Net8 listener is not running or cannot start external procedures ORA-28575: unable to open RPC connection to external procedure agent
You should refer to the Net8 Administrator's Guide for complete configuration information about external procedures, but here is a very quick and dirty check to see if external procedures are working. From the database server (not on your local client), you want to execute from a telnet session (or command window if your database server is installed on Microsoft Windows) the command tnsping extproc_connection_data. If you don't see a result of OK, as in:
oracle8i@cmh:/> tnsping extproc_connection_data TNS Ping Utility for Solaris: Version 8.1.7.0.0 - Production on 30-MAR-2001 13:46:59 (c) Copyright 1997 Oracle Corporation. All rights reserved. Attempting to contact (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)) OK (100 msec)
then your configuration of external procedures is incorrect.
External procedures are not required for interMedia Text filtering as of Oracle 8.1.7. The filtering process has been folded into the database server itself.
With the Oracle 9i release, interMedia Text is undergoing another name change, and will be referred to as Oracle Text. All of the functionality that is present in the Oracle 8i release of interMedia Text will also be present in Oracle Text, but the Oracle 9i release also introduces many powerful new features.
One of the most desired features in interMedia Text was automatic classification of documents, based upon their content. interMedia Text gave the application developer all of the tools necessary to construct a system to generate the themes and summaries of documents, which could then be used to develop a pseudo-document classification system. With Oracle Text, document classification is now a native feature. This feature facilitates the construction of a system that permits the end user to ask, 'which queries match this document?'
Oracle Text also improves upon the native support of XML documents. An XML document is composed of content, as well as structural metadata. The elements of an XML document have implicit relationships, and the structural metadata can be used to convey these relationships. XPath, a W3C recommendation (http://www.w3.org/TR/xpath) provides a way to obtain certain elements of an XML document, based upon the content and relative structure of the document elements. Oracle Text introduces a new XPATH operator, which enables SQL queries based upon the structural elements of a document.
These are just a few of the new features coming to interMedia Text/Oracle Text with the Oracle 9i release.
In this chapter, we examined the rich feature set of interMedia Text, and how it can be easily exploited in a variety of applications. Although this chapter covered many facets of interMedia Text, there are still many more things I haven't covered. You can use a thesaurus, define a custom lexer, generate HTML renditions of all of your documents regardless of format, store your query expressions for later use, and even define your own custom stopword lists.
interMedia Text itself is a very broad topic, and one which can't be covered in a single chapter. Besides being very feature-rich, interMedia Text is quite easy to use and understand. After reading this chapter, you should now have a clear understanding of how interMedia Text is implemented, and also how you can exploit the power of interMedia Text in many of your applications.