Contents

Title and Copyright Information

Send Us Your Comments

Preface

Part I Introduction

1 What Is a Data Cartridge?

What Are Data Cartridges?

Why Build Data Cartridges?

Extending the Server -- Services and Interfaces

Extensibility Services

Object Types

Collection Types

Relationship Types (REF)

Large Objects

Extensibility Interfaces

Cartridges as Software Components

2 Roadmap to Building a Data Cartridge

Development Process

Installation and Use

Requirements and Guidelines for Data Cartridge Constituents.

Deployment Checklist

Need for Naming Conventions

Unique Name Format

Internal Versioning

External Versioning

External Access

Internal Access

Invoker's Rights

Test and Debug Services

Configuration

3 Defining Object Types

Objects and Object Types

Assigning an OID to an Object Type

Constructor Methods

Object Comparison

Part II Building Data Cartridges

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

OCIExtProcGetEnv

Doing Callbacks

OCI Access Functions for External Procedures

Common Potential Errors

Debugging External Procedures

Guidelines for Using External Procedures with Data Cartridges

Java Methods

5 Methods: Using PL/SQL

Methods

PL/SQL Packages

Pragma RESTRICT_REFERENCES

Privileges Required to Create Procedures and Functions

Debugging PL/SQL Code

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

7 Building Domain Indexes

Introduction to Extensible Indexing

The Relationship between Logical and Physical Structures

The Need for Index Structures that Encompass Unstructured Data

B-tree

Hash

k-d tree

Point Quadtree

The Extensible Indexing API

Overview

Example: A Text Indextype

Creating Indextypes

Dropping Indextypes

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 Index Operations

Domain Index Meta Data

Export/Import of Domain Indexes

Operator Bindings

Creating operators

Operator Invocation

Operator Privileges

Operators in WHERE clause

Operators elseWHERE

Ancillary Data

Dependencies

Drop Semantics

Object Validation

8 Query Optimization

Overview

User-Defined Statistics

User-Defined Statistics for Partitioned Objects

User-defined Selectivity

User-Defined Cost

Defining Statistics, Selectivity, and Cost Functions

Using User-defined Statistics, Selectivity, and Cost

Column Statistics

Domain Index Statistics

User-defined Operators

Stand-Alone Functions

Package Functions

Type Methods

Default Selectivity

User-defined Operators

Stand-Alone Functions

Package Functions

Type Methods

Default Cost

Predicate Ordering

Dependency Model

Restrictions and Suggestions

9 Using Cartridge Services

Cartridge Services -- Introduction

Cartridge Handle

Memory Services

Maintaining Context

National Language Service (NLS)

Parameter Manager Interface

File I/O

String Formatting

Part III Advanced Topics

10 Design Considerations

Designing the types

Nested Tables

VARRAYs

Inheritance Implementation Consequences

Simulating Inheritance

Dual Subtype / Super-type Reference

Callouts

Designing Indexes

Influencing Index Performance

Influencing Index Performance

Designing Operators

Talking to the Optimizer

Design for maintenance

Miscellaneous

Part IV Scenarios and Examples

11 Power Demand Cartridge Example

Modeling the Application

Queries and Extensible Indexing

Creating the Domain Index

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

Testing the Domain Index

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

Testing the Domain Index

Using Time Series with the Power Demand Cartridge

Using Spatial with the Power Demand Cartridge

12 SBTREE: An Example of Extensible Indexing

Introduction

Design of the indextype

Implementing Operators

Functional Implementation of EQ (EQUALS)

Functional Implementation of LT (LESS THAN)

Functional Implementation of GT (GREATER THAN)

Operator EQ

Operator LT

Operator GT

Implementing the Index routines

The C Code

Implementing the Indextype

Usage examples

Part V Reference

13 Reference: Cartridge Services Using C

Cartridge Services -- OCI External Procedures

OCIExtProcAllocCallMemory()

OCIExtProcRaiseExcp()

OCIExtProcRaiseExcpWithMsg()

OCIExtProcGetEnv()

Cartridge Services -- Memory Services

OCIDurationBegin()

OCIDurationEnd()

OCIMemoryAlloc()

OCIMemoryResize()

OCIMemoryFree()

Cartridge Services -- Maintaining Context

OCIContextSetValue()

OCIContextGetValue()

OCIContextClearValue()

OCIContextGenerateKey()

Cartridge Services -- Parameter Manager Interface

OCIExtractInit()

OCIExtractTerm()

OCIExtractReset()

OCIExtractSetNumKeys()

OCIExtractSetKey()

OCIExtractFromFile()

OCIExtractFromStr()

OCIExtractToInt()

OCIExtractToBool()

OCIExtractToStr()

OCIExtractToOCINum()

OCIExtractToList()

OCIExtractFromList()

Cartridge Services -- File I/O Interface

OCIFileInit()

OCIFileTerm()

OCIFileOpen()

OCIFileClose()

OCIFileRead()

OCIFileWrite()

OCIFileSeek()

OCIFileExists()

OCIFileGetLength()

OCIFileFlush()

Cartridge Services -- File I/O Interface

OCIFileObject

Cartridge Services -- String Formatting Interface

OCIFormatInit

OCIFormatTerm

OCIFormatString

Format Modifiers

Format Codes

Example

14 Reference: Cartridge Service Using Java

File Installation

Cartridge Service -- Maintaining Context

CountException()

CountException(String)

InvalidKeyException()

InvalidKeyException(String)

15 Reference: Extensibility Constants, Types, & Mappings

Extensibility Constants, Types, & Mappings

Alter Options Values

ODCIArgDesc.ArgType 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

ODCICost

ODCIFuncInfo

ODCIIndexInfo

ODCIPredInfo

ODCIIndexCtx

ODCIObject

ODCIObjectList

ODCIQueryInfo

ODCIStatsOptions

C Constants

C Types

Mappings in Java

16 Reference: Extensible Indexing Interface

Extensible Indexing -- System Defined Interface Routines

ODCIGetInterfaces

ODCIIndexAlter

OCDIIndexCreate

ODCIIndexClose

ODCIIndexDelete

ODCIIndexDrop

ODCIIndexFetch

ODCIIndexGetMetadata

ODCIIndexInsert

ODCIIndexStart

ODCIIndexTruncate

OCIIndexUpdate

17 Reference: Extensible Optimizer Interface

Extensible Optimizer -- Interface

EXPLAIN PLAN

INDEX Hint

ORDERED_PREDICATES Hint

Example

User-Defined ODCIStats Functions

ODCIStatsCollect

ODCIStatsDelete

ODCIStatsSelectivity

ODCIStatsFunctionCost

ODCIStatsIndexCost

A Java Demo Script

extdemo3.sql Demonstration Script

extdemo3.java Demonstration Script

extdemo3a.java Demonstration Script

Index