Contents

Title and Copyright Information

Send Us Your Comments

Preface

What's New in Data Cartridges?

Part I Introduction

1 What Is a Data Cartridge?

What Are Data Cartridges?

Why Build Data Cartridges?

Data Cartridge Domains

Extending the Server--Services and Interfaces

Extensibility Services

Extensible Type System

Object Types

Collection Types

Relationship Types (REF)

Large Objects

Extensible Server Execution Environment

Extensible Indexing

Extensible Optimizer

Extensibility Interfaces

DBMS Interfaces

Cartridge Basic Service Interfaces

Data Cartridge Interfaces

Cartridges as Software Components

The Structure of a Data Cartridge

Object Type Specification

Object Type Body Code

External Library Linkage Specification

External Library Code

Installing a Data Cartridge

2 Roadmap to Building a Data Cartridge

Development Process

Installation and Use

Requirements and Guidelines for Data Cartridge Constituents

Schema

Globals

Error Message Names or Error Codes

Cartridge Installation Directory

Files

Shared Library Names for External Procedures

Deployment Checklist

Naming Conventions

Need for Naming Conventions

Unique Name Format

Cartridge Registration

Directory Structure and Standards

Cartridge Upgrades

Import and Export

Cartridge Versioning

Internal Versioning

External Versioning

Internationalization

External Access

Internal Access

Invoker's Rights

Test and Debug Services

Administration

Configuration

Suggested Development Approach

Part II Building Data Cartridges

3 Defining Object Types

Objects and Object Types

Assigning an OID to an Object Type

Constructor Methods

Object Comparison

4 Methods: Using C/C++ and Java

External Procedures

Using Shared Libraries

Registering an External Procedure

How PL/SQL Calls an External Procedure

Configuration Files for External Procedures

Passing Parameters to an External Procedure

Specifying Datatypes

Using the Parameters Clause

Using the WITH CONTEXT Clause

OCIExtProcGetEnv

Doing Callbacks

Restrictions on Callbacks

OCI Access Functions for External Procedures

OCIExtProcAllocCallMemory

OCIExtProcRaiseExcp

OCIExtProcRaiseExcpWithMsg

Common Potential Errors

Calls to External Functions

RPC Time Out

Debugging External Procedures

Using Package DEBUG_EXTPROC

Debugging C Code in DLLs on Windows NT Systems

Guidelines for Using External Procedures with Data Cartridges

Java Methods

5 Methods: Using PL/SQL

Methods

Implementing Methods

Invoking Methods

Referencing Attributes in a Method

PL/SQL Packages

Pragma RESTRICT_REFERENCES

Privileges Required to Create Procedures and Functions

Debugging PL/SQL Code

Notes for C and C++ Programmers

Common Potential Errors

Signature Mismatches

RPC Time Out

Package Corruption

6 Working with Multimedia Datatypes

Overview

DDL for LOBs

LOB Locators

EMPTY_BLOB and EMPTY_CLOB Functions

Using the OCI to Manipulate LOBs

Using DBMS_LOB to Manipulate LOBs

LOBs in External Procedures

LOBs and Triggers

Using Open/Close as Bracketing Operations for Efficient Performance

Errors and Restrictions Regarding Open/Close Operations

7 Building Domain Indexes

Introduction to Extensible Indexing

What is Indexing?

Index Structures

The Relationship between Logical and Physical Structures

The Need for Index Structures that Encompass Unstructured Data

Kinds of Indexes

B-tree

Hash

k-d tree

Point Quadtree

Why is Extensible Indexing Necessary?

The Extensible Indexing API

Concepts: Extensible Indexing

Overview

Example: A Text Indextype

Indextypes

Creating Indextypes

Dropping Indextypes

Commenting on Indextypes

ODCI Index Interface

Index Definition Methods

Index Maintenance Methods

Index Scan Methods

Index Metadata Method

Transaction Semantics during Index Method Execution

Transaction Semantics for Index Definition Routines

Consistency Semantics during Index Method Execution

Privileges During Index Method Execution

Domain Indexes

Domain Index Operations

Domain Indexes on Index-Organized Tables

Domain Index Metadata

Export/Import of Domain Indexes

Moving Domain Indexes Using Transportable Tablespaces

Operators

Operator Bindings

Creating operators

Invoking Operators

Operator Privileges

Operators and Indextypes

Operators in the WHERE Clause

Operators Outside the WHERE Clause

Ancillary Data

Object Dependencies, Drop Semantics, and Validation

Dependencies

Drop Semantics

Object Validation

Privileges

Partitioned Domain Indexes

Dropping a Local Domain Index

Altering a Local Domain Index

Summary of Index States

DML Operations with Local Domain Indexes

Table Operations That Affect Indexes

ODCIIndex Interfaces for Partitioning Domain Indexes

Domain Indexes and SQL*Loader

8 Query Optimization

Overview

Statistics

User-Defined Statistics

User-Defined Statistics for Partitioned Objects

Selectivity

User-Defined Selectivity

Cost

User-Defined Cost

Defining Statistics, Selectivity, and Cost Functions

User-Defined Statistics Functions

User-Defined Selectivity Functions

User-Defined Cost Functions for Functions

User-Defined Cost Functions for Domain Indexes

Using User-Defined Statistics, Selectivity, and Cost

User-Defined Statistics

Column Statistics

Domain Index Statistics

User-Defined Selectivity

User-defined Operators

Standalone Functions

Package Functions

Type Methods

Default Selectivity

User-Defined Cost

User-defined Operators

Standalone Functions

Package Functions

Type Methods

Default Cost

Declaring a NULL Association for an Index or Column

How Statistics Are Affected by DDL Operations

Predicate Ordering

Dependency Model

Restrictions and Suggestions

Parallel Query

Distributed Execution

Performance

9 Using Cartridge Services

Cartridge Services -- Introduction

Cartridge Handle

Client Side Usage

Cartridge Side Usage

Service Calls

Error Handling

Memory Services

Maintaining Context

Durations

Globalization Support

Globalization Support Language Information Retrieval

String Manipulation

Parameter Manager Interface

Input Processing

Parameter Manager Behavior Flag

Key Registration

Parameter Storage and Retrieval

Parameter Manager Context

File I/O

String Formatting

Part III Advanced Topics

10 Design Considerations

Designing the Types

Structured and Unstructured Data

Using Nested Tables or VARRAYs

Nested Tables

VARRAYs

Choosing a Language in Which to Write Methods

Invokers Rights -- Why, When, How

Callouts

When to Callout

When to Callback

Callouts and LOB

Saving and Passing State

Designing Indexes

Influencing Index Performance

Influencing Index Performance

When to Use IOTs

Can Index Structures Be Stored in LOBs

External Index Structures

Multi-Row Fetch

Designing Operators

Functional and Index Implementations

Talking to the Optimizer

Weighing Cost and Selectivity

Design for maintenance

How to Make Your Cartridge Extensible

How to Make Your Cartridge Installable

Miscellaneous

How to Write Portable Cartridge Code

11 User-Defined Aggregate Functions

The ODCIAggregate Interface: Overview

Creating a User-Defined Aggregate

Using a User-Defined Aggregate

Parallel Evaluation of User-Defined Aggregates

Handling Large Aggregation Contexts

External Context and Parallel Aggregation

External Context and User-Defined Analytic Functions

Summary of Steps to Support External Context

User-Defined Aggregates and Materialized Views

User-Defined Aggregates and Analytic Functions

Reusing the Aggregation Context for Analytic Functions

Example: Creating and Using a User-Defined Aggregate

12 Pipelined and Parallel Table Functions

Overview

Concepts

Table Functions

Pipelined Table Functions

Pipelined Table Functions with REF CURSOR Arguments

Errors and Restrictions

Parallel Execution of Table Functions

Pipelined Table Functions

Implementation Choices for Pipelined Table Functions

Declarations of Pipelined Table Functions

Implementing the Native PL/SQL Approach

Pipelining Between PL/SQL Table Functions

Implementing the Interface Approach

Scan Context

Start Routine

Fetch Routine

Close Routine

Example: Pipelined Table Functions: Interface Approach

Describe Routine

Querying Table Functions

Multiple Calls to Table Functions

PL/SQL

Performing DML Operations Inside Table Functions

Performing DML Operations on Table Functions

Handling Exceptions in Table Functions

Parallel Table Functions

Inputting Data with Cursor Variables

Using Multiple REF CURSOR Input Variables

Explicitly Opening a REF CURSOR for a Query

PL/SQL REF CURSOR Arguments to Java and C/C++ Functions

Input Data Partitioning

Parallel Execution of Leaf-level Table Functions

Input Data Streaming for Table Functions

Parallel Execution: Partitioning and Clustering

Parallelizing Creation of a Domain Index

Transient and Generic Types

Part IV Scenarios and Examples

13 Power Demand Cartridge Example

Feature Requirements

Modeling the Application

Sample Queries

Queries and Extensible Indexing

Queries Not Benefiting from Extensible Indexing

Queries Benefiting from Extensible Indexing

Creating the Domain Index

Creating the Schema to Own the Index

Creating the Object Type (PowerDemand_Typ)

Defining the Object Type Methods

Creating the Functions and Operators

Creating the Indextype Implementation Methods

Type Definition

ODCIGetInterfaces Method

ODCIIndexCreate Method

ODCIIndexDrop Method

ODCIIndexStart Method (for Specific Queries)

ODCIIndexStart Method (for Any Queries)

ODCIIndexFetch Method

ODCIIndexClose Method

ODCIIndexInsert Method

ODCIIndexDelete Method

ODCIIndexUpdate Method

ODCIIndexGetMetadata Method

Creating the Indextype

Defining a Type and Methods for Extensible Optimizing

Creating the Statistics Table (PowerCartUserStats)

Creating the Extensible Optimizer Methods

Type Definition

ODCIGetInterfaces Method

ODCIStatsCollect Method (for PowerDemand_Typ columns)

ODCIStatsDelete Method (for PowerDemand_Typ columns)

ODCIStatsCollect Method (for power_idxtype Domain Indexes)

ODCIStatsDelete Method (for power_idxtype Domain Indexes)

ODCIStatsSelectivity Method (for Specific Queries)

ODCIStatsSelectivity Method (for Any Queries)

ODCIStatsIndexCost Method (for Specific Queries)

ODCIStatsIndexCost Method (for Any Queries)

ODCIStatsFunctionCost Method

Associating the Extensible Optimizer Methods with Database Objects

Analyzing the Database Objects

Testing the Domain Index

Creating and Populating the Power Demand Table

Querying Without the Index

Creating the Index

Querying with the Index

14 PSBTREE: An Example of Extensible Indexing

Introduction

Design of the indextype

Implementing Operators

Create Functional Implementations

Functional Implementation of EQ (EQUALS)

Functional Implementation of LT (LESS THAN)

Functional Implementation of GT (GREATER THAN)

Create Operators

Operator EQ

Operator LT

Operator GT

Implementing the Index Routines

The C Code

General Notes

Common Error Processing Routine

Implementation Of The ODCIIndexInsert Routine

Implementation of the ODCIIndexDelete Routine

Implementation of the ODCIIndexUpdate Routine

Implementation of the ODCIIndexStart Routine

Implementation of the ODCIIndexFetch Routine

Implementation of the ODCIIndexClose Routine

Implementing the Indextype

Usage examples

Explain Plan Output

Part V Reference

15 Reference: Cartridge Services Using Java

File Installation

Cartridge Services--Maintaining Context

ContextManager

Class Interface

Variable

Constructors

Methods

CountException()

CountException(String)

InvalidKeyException()

InvalidKeyException(String)

16 Reference: Extensibility Constants, Types, and Mappings

System Defined Constants

ODCIIndexAlter Options

ODCIArgDesc.ArgType Bits

ODCIEnv.CallProperty Values

ODCIIndexInfo.Flags Bits

ODCIPredInfo.Flag Bits

ODCIFuncInfo.Flags Bits

ODCIQueryInfo.Flags Bits

ODCIStatsOptions.Flags Bits

ODCIStatsOptions.Options Bits

ScnFlg (Function with Index Context) Values

Status Values

System Defined Types

ODCIArgDesc

ODCIArgDescList

ODCIRidList

ODCIColInfo

ODCIColInfoList

ODCIColStats

ODCIColStatsList

ODCICost

ODCIEnv

ODCIFuncInfo

ODCIIndexInfo

ODCIPredInfo

ODCIIndexCtx

ODCIObject

ODCIObjectList

ODCIPartInfo

ODCIQueryInfo

ODCIStatsOptions

ODCITabStats

ODCITableFunctionStats

Mappings of Constants and Types

Mappings in PL/SQL

Mappings in C

Mappings in Java

Constants Definitions

Constants for Return Status

Constants for ODCIPredInfo.Flags

Constants for ODCIQueryInfo.Flags

Constants for ScnFlg (Func with Index Context)

Constants for ODCIFuncInfo.Flags

Constants for ODCIArgDesc.ArgType

Constants for ODCIStatsOptions.Options

Constants for ODCIStatsOptions.Flags

Constants for ODCIIndexAlter parameter alter_option

Constants for ODCIIndexInfo.IndexInfoFlags

Constants for ODCIEnv.CallProperty

17 Reference: Extensible Indexing Interface

Extensible Indexing -- System Defined Interface Routines

ODCIGetInterfaces

ODCIIndexAlter

ODCIIndexClose

ODCIIndexCreate

ODCIIndexDelete

ODCIIndexDrop

ODCIIndexExchangePartition

ODCIIndexFetch

ODCIIndexGetMetadata

ODCIIndexInsert

ODCIIndexMergePartition

ODCIIndexSplitPartition

ODCIIndexStart

ODCIIndexTruncate

ODCIIndexUpdate

18 Reference: Extensible Optimizer Interface

Note on the New Interfaces

The Extensible Optimizer Interface

EXPLAIN PLAN

INDEX Hint

ORDERED_PREDICATES Hint

Example

User-Defined ODCIStats Functions

ODCIGetInterfaces

ODCIStatsCollect (Column)

ODCIStatsCollect (Index)

ODCIStatsDelete (Column)

ODCIStatsDelete (Index)

ODCIStatsFunctionCost

ODCIStatsIndexCost

ODCIStatsSelectivity

19 Reference: User-Defined Aggregates Interface

ODCIAggregateInitialize

ODCIAggregateIterate

ODCIAggregateMerge

ODCIAggregateTerminate

ODCIAggregateDelete

ODCIAggregateWrapContext

20 Reference: Pipelined and Parallel Table Functions

ODCITableStart

ODCITableFetch

ODCITableClose

ODCITableDescribe

A Example: Pipelined Table Functions: Interface Approach

C Implementation

SQL Declarations for C Implementation

C Implementation of the ODCITable Methods

Java Implementation

SQL Declarations for Java Implementation

Java Implementation of the ODCITable Methods

Index