Contents

Title and Copyright Information

Send Us Your Comments

Preface

1 Introduction to Oracle Text

What is Oracle Text?

Types of Query Applications

Supported Document Formats

Theme Capabilities

Query Language and Operators

Document Services and Using a Thesaurus

Prerequisites For Building Your Query Application

Introduction to Loading Your Text Table

Storing Text in the Text Table

Storing File Path Names

Storing URLs

Storing Associated Document Information

Supported Column Types

Supported Document Formats

Loading Methods

Indexing Your Documents

Type of Index

When to Create a CONTEXT Index

When to Create a CTXCAT Index

When to Create a CTXRULE Index

Index Maintenance

Simple Text Query Application

Understanding How to Query Your Index

Understanding How to Query with CONTAINS

Understanding Structured Field Searching

Thesaural Queries

Document Section Searching

Other Query Features

Presenting the Hit List

Hitlist Example

Presenting Structured Fields

Ordering the Hit List

Presenting Document Hit Count

Document Presentation and Highlighting

Highlighting Example

Document List of Themes Example

Gist Example

2 Indexing

About Oracle Text Indexes

Structure of the Oracle Text CONTEXT Index

The Oracle Text Indexing Process

Partitioned Tables and Indexes

Creating an Index Online

Parallel Indexing

Limitations for Indexing

Considerations For Indexing

Type of Index

Location of Text

Document Formats and Filtering

Bypassing Rows for Indexing

Document Character Set

Document Language

Indexing Special Characters

Case-Sensitive Indexing and Querying

Language Specific Features

Fuzzy Matching and Stemming

Better Wildcard Query Performance

Document Section Searching

Stopwords and Stopthemes

Index Performance

Query Performance and Storage of LOB Columns

Index Creation

Procedure for Creating a CONTEXT Index

Creating Preferences

Creating Section Groups for Section Searching

Using Stopwords and Stoplists

Creating an Index

Creating a CONTEXT Index

Creating a CTXCAT Index

Creating a CTXRULE Index

Index Maintenance

Viewing Index Errors

Dropping an Index

Resuming Failed Index

Rebuilding an Index

Dropping a Preference

Managing DML Operations for a CONTEXT Index

Viewing Pending DML

Synchronizing the Index

Index Optimization

3 Querying

Overview of Queries

Querying with CONTAINS

Querying with CATSEARCH

Querying with MATCHES

Word and Phrase Queries

ABOUT Queries and Themes

Query Expressions

Case-Sensitive Searching

Query Feedback

Query Explain Plan

The CONTEXT Grammar

ABOUT Query

Logical Operators

Section Searching

Proximity Queries with NEAR Operator

Fuzzy, Stem, Soundex, Wildcard and Thesaurus Expansion Operators

Using CTXCAT Grammar

Stored Query Expressions

Calling PL/SQL Functions in CONTAINS

The CTXCAT Grammar

Using CONTEXT Grammar with CATSEARCH

Optimizing for Response Time

Other Factors that Influence Query Response Time

Counting Hits

SQL Count Hits Example

Counting Hits with a Structured Predicate

PL/SQL Count Hits Example

4 Document Presentation

Highlighting Query Terms

Text highlighting

Theme Highlighting

CTX_DOC Highlighting Procedures

Obtaining List of Themes, Gists, and Theme Summaries

List of Themes

Gist and Theme Summary

5 Performance Tuning

Optimizing Queries with Statistics

Collecting Statistics

Re-Collecting Statistics

Deleting Statistics

Optimizing Queries for Response Time

Other Factors that Influence Query Response Time

Improved Response Time with FIRST_ROWS(n) for ORDER BY Queries

Improved Response Time using Local Partitioned CONTEXT Index

Improved Response Time with Local Partitioned Index for Order by Score

Optimizing Queries for Throughput

CHOOSE and ALL ROWS Modes

FIRST_ROWS Mode

Parallel Queries

Tuning Queries with Blocking Operations

Frequently Asked Questions a About Query Performance

What is Query Performance?

What is the fastest type of text query?

Should I collect statistics on my tables?

How does the size of my data affect queries?

How does the format of my data affect queries?

What is a functional versus an indexed lookup?

What tables are involved in queries?

Does sorting the results slow a text-only query?

How do I make a ORDER BY score query faster?

Which Memory Settings Affect Querying?

Does out of line LOB storage of wide base table columns improve performance?

How can I make a CONTAINS query on more than one column faster?

Is it OK to have many expansions in a query?

How can local partition indexes help?

Should I query in parallel?

Should I index themes?

When should I use a CTXCAT index?

When is a CTXCAT index NOT suitable?

What optimizer hints are available, and what do they do?

Frequently Asked Questions About Indexing Performance

How long should indexing take?

Which index memory settings should I use?

How much disk overhead will indexing require?

How does the format of my data affect indexing?

Can I index in parallel?

How do I create a local partitioned index in parallel?

How can I tell how far my indexing has got?

Frequently Asked Questions About Updating the Index

How often should I index new or updated records?

How can I tell when my indexes are getting fragmented?

Does memory allocation affect index synchronization?

6 Document Section Searching

About Document Section Searching

Enabling Section Searching

Section Types

HTML Section Searching

Creating HTML Sections

Searching HTML Meta Tags

XML Section Searching

Automatic Sectioning

Attribute Searching

Creating Document Type Sensitive Sections

Path Section Searching

7 Working With a Thesaurus

Overview of Thesauri

Thesaurus Creation and Maintenance

Case-sensitive Thesauri

Case-insensitive Thesauri

Default Thesaurus

Supplied Thesaurus

Defining Thesaural Terms

Defining Synonyms

Defining Hierarchical Relations

Using a Thesaurus in a Query Application

Loading a Custom Thesaurus and Issuing Thesaural Queries

Augmenting Knowledge Base with Custom Thesaurus

About the Supplied Knowledge Base

Adding a Language-Specific Knowledge Base

8 Administration

Oracle Text Users and Roles

CTXSYS User

CTXAPP Role

Granting Roles and Privileges to Users

DML Queue

The CTX_OUTPUT Package

Servers

Administration Tool

A CONTEXT Query Application

Web Query Application Overview

The PSP Web Application

Web Application Prerequisites

Building the Web Application

PSP Sample Code

loader.ctl

loader.dat

search_htmlservices.sql

search_html.psp

The JSP Web Application

Web Application Prerequisites

JSP Sample Code: search_html.jsp

B CATSEARCH Query Application

CATSEARCH Web Query Application Overview

The JSP Web Application

Building the JSP Web Application

JSP Sample Code

loader.ctl

loader.dat

catalogSearch.jsp

Index