Oracle Ultra Search Online Documentation
Release 9.2


Home	Book List	Contents	Master Index	Feedback

Table Data Source Synchronization

The Ultra Search crawler crawls database tables in the main Oracle9i database instance where Ultra Search is installed. Additionally, the Ultra Search crawler 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 Oracle9i 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.

Oracle Databases

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:

Create Log Table

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:

For every primary key column of the base table, a column must be created in the log table.

There can be up to only eight primary key columns in the base table.

Each column in the log table that corresponds to a primary key column must be named Kx, where x is a number from one to eight.

Each column in the log table that corresponds to a primary key column must be of type VARCHAR2(1000).

There must be exactly one column named "mark" that has type CHAR(1).

The column named "mark" must have a default value 'F'.

Example:

The base table employees has the following structure:
Column name Column type
ID
NUMBER
NAME
VARCHAR2(200)
ADDRESS
VARCHAR2(400)
TELEPHONE
VARCHAR2(10)
USERNAME
VARCHAR2(24)
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
K1
NUMBER
K2
VARCHAR2(200)
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')
Create Log Triggers

An INSERT trigger, UPDATE trigger and DELETE trigger are created. The Oracle trigger definitions are as follows:
INSERT Trigger Statement
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;
UPDATE Trigger Statement
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;
DELETE Trigger Statement
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;
INSERT Trigger

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.

UPDATE Trigger

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.

DELETE Trigger

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.

Column name	Column type
ID	NUMBER
NAME	VARCHAR2(200)
ADDRESS	VARCHAR2(400)
TELEPHONE	VARCHAR2(10)
USERNAME	VARCHAR2(24)

Column name	Column type
K1	NUMBER
K2	VARCHAR2(200)

INSERT Trigger Statement	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;
UPDATE Trigger Statement	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;
DELETE Trigger Statement	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;

Non-Oracle Databases

For tables in non-Oracle remote databases, you must perform the following steps:

Step 1: Manually create the log table yourself. The log table must conform to the rules for log tables described earlier. Also, they must reside in the same schema and database instance as the base table.

Step 2: Create three triggers that record inserts, updates, and deletes on the base table. These triggers must exhibit the same behavior as the triggers described earlier for Oracle tables.

Step 3: Associate the log table.
When you have completed these tasks, you can then choose the "Enable logging mechanism (non-Oracle tables)" option during the creation of an Ultra Search table data source. By choosing that option, the Ultra Search administration tool prompts you for the name of the log table in the remote database. Ultra Search associates this log table with the base table. Ultra Search assumes that you have correctly performed steps 1 and 2.


Home	Book List	Contents	Master Index	Feedback