Oracle Ultra Search User's Guide Release 9.0.3 Part Number B10043-01 |
|
| View PDF |
Ultra Search crawls database tables in the main Oracle9i database instance where Ultra Search is installed. Additionally, it can crawl remote databases if they have been linked to the main Oracle9i database. Remote databases are linked to the main Oracle9i instance with database links.
See Also:
Oracle9i Database Administrator's Guide for instructions on how to create database links. |
Ultra Search provides a logging mechanism to optimize crawling of table sources. Using this logging mechanism, only newly updated documents are revisited during the crawling process. If the source database is not an Oracle database, then you must perform a sequence of steps to use this feature.
Before creating log tables and log triggers, make sure that the Ultra Search instance schema has the 'CREATE ANY TABLE' and 'CREATE ANY TRIGGER' system privileges. For tables in Oracle databases, data definition language (DDL) statements are provided to create the following:
The log table stores changes that have occurred in the base table. The Ultra Search crawler uses the change information to figure out which rows need to be recrawled. For example, a log table generated by Ultra Search could be named WK$LOG
.
The structure of the log table conforms to the following rules:
VARCHAR2
(1000).CHAR
(1).Example:
The base table employees has the following structure:
Column Name | Column Type |
---|---|
|
|
|
|
|
|
|
|
|
|
If the primary key of the employees table comprises of the ID
and NAME
columns, then a log table WK$LOG
(name is generated on the fly) is created with the following structure:
Column Name | Column Type |
---|---|
|
|
|
|
The SQL statement for creating the log table will be as follows:
CREATE TABLE WK$LOG( K1 VARCHAR2(1000), K2 VARCHAR2(1000), MARK CHAR(1) default 'F')
An INSERT
trigger, UPDATE
trigger and DELETE
trigger are created. The Oracle trigger definitions are as follows:
Every time a row is inserted into the employees base table, the INSERT
trigger inserts a row into the log table. The row in the log table records the new values of the id and the name into the k1 and k2 columns. An 'F' is inserted into the mark column to signal the crawler that work needs to be done for this row.
For example:
CREATE OR REPLACE TRIGGER wk$ins AFTER INSERT ON employees FOR EACH ROW BEGIN INSERT INTO WK$LOG(k1,k2,mark) VALUES(:new.id,:new.name,'F'); END;
Every time a row is updated in the employees base table, the UPDATE
trigger inserts two rows into the log table. The first row in the log table records the old values of the id and the name into the k1 and k2 columns. An 'F' is inserted into the mark column to signal the crawler that work needs to be done for this row. The second row in the log table records the new values of the id and the name into the k1 and k2 columns.
For example:
CREATE OR REPLACE TRIGGER wk$upd AFTER UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO WK$LOG(k1,k2,mark) VALUES(:old.id,:old.name,'F'); INSERT INTO WK$LOG(k1,mark) VALUES(:new.id,:new.name,'F'); END;
Every time a row is deleted from the employees base table, the DELETE
trigger inserts a row into the log table. The row in the log table records the old values of the id and the name into the k1 and k2 columns. An 'F' is inserted into the mark column to signal the crawler that work needs to be done for this row.
For example:
CREATE OR REPLACE TRIGGER wk$del AFTER DELETE ON employees FOR EACH ROW BEGIN INSERT INTO WK$LOG(k1,k2,mark) VALUES(:old.id,:old.name,'F'); END;
For tables in non-Oracle remote databases, you must perform the following steps: