Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Conventions

What's New in Data Cartridges?

• New Features in Oracle 11g Release 1 (11.1)

Part I Introduction

1 Introduction to Data Cartridges

• Overview of Data Cartridges
• Uses of Data Cartridges
  • Data Cartridge Domains
• Extending the Server: Services and Interfaces
  • Extensibility Services
    • Extensible Type System
    • Extensible Server Execution Environment
    • Extensible Indexing
    • Extensible Optimizer
  • Extensibility Interfaces
    • DBMS Interfaces
    • Cartridge Basic Service Interfaces
    • Data Cartridge Interfaces

2 Roadmap to Building a Data Cartridge

• Data Cartridge Development Process
  • Understanding the Purpose
  • Understand the Users
  • Plan the Project
  • Implement the Project
  • Test and Installation
• Cartridge Installation and Use
• Requirements and Guidelines for Data Cartridge Components
  • Cartridge Schemas
  • Cartridge Globals
  • Cartridge Error Message Names or Error Codes
  • Cartridge Installation Directory
  • Cartridge Files
  • Shared Library Names for External Procedures
• Data Cartridge Deployment Checklist
  • Data Cartridge Naming Conventions
    • Need for Naming Conventions
    • Unique Name Format
  • Cartridge Registration
  • Cartridge Directory Structure and Standards
  • Cartridge Upgrades
  • Import and Export of Cartridge Objects
  • Cartridge Versioning
    • Internal Versioning
    • External Versioning
  • Cartridge Internationalization
  • Cartridge Administration
    • Administering Cartridge Access
    • Invoker's Rights
    • Configuration
  • Suggested Development Approach

Part II Building Data Cartridges

3 Defining Object Types

• Objects and Object Types
• Assigning an Object Identifier to an Object Type
• Constructor Methods
• Object Comparison

4 Implementing Data Cartridges in 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

5 Implementing Data Cartridges in C, C++ and Java

• Using 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
• Doing Callbacks
  • Restrictions on Callbacks
• 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

6 Working with Multimedia Datatypes

• Overview of Cartridges and Multimedia Datatypes
• 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 Using Extensible Indexing

• Overview of Extensible Indexing
  • Purpose of Indexes
  • Purpose of Extensible Indexing
  • When to Use Extensible Indexing
  • Index Structures
    • B-tree
    • Hash
    • k-d tree
    • Point Quadtree
• Extensible Indexing
• Using the Text Indextype
  • Defining the Indextype
    • Non-Index-Based Functional Implementations
    • Index-Based Functional Implementations
  • Using the Indextype

8 Building Domain Indexes

• Overview of Indextypes and Domain Indexes
• ODCIIndex Interface
  • Index Definition Methods
    • ODCIIndexCreate()
    • ODCIIndexAlter()
    • ODCIIndexDrop()
  • Index Maintenance Methods
    • ODCIIndexInsert()
    • ODCIIndexDelete()
    • ODCIIndexUpdate()
  • Index Scan Methods
    • ODCIIndexStart()
    • ODCIIndexFetch()
    • ODCIIndexClose()
  • 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
• Creating, Dropping, and Commenting Indextypes
  • Creating Indextypes
  • Dropping Indextypes
  • Commenting Indextypes
• Domain Indexes
  • Domain Index Operations
    • Creating a Domain Index
    • Altering a Domain Index
    • Truncating a Domain Index
    • Dropping a Domain Index
  • Domain Indexes on Index-Organized Tables
    • Storing Rowids in a UROWID Column
    • DML on Index Storage Tables
    • Start, Fetch, and Close Operations on Index Storage Tables
    • Indexes on Non-Unique Columns
  • Domain Index Metadata
  • Moving Domain Indexes Using Export/Import
  • Moving Domain Indexes Using Transportable Tablespaces
  • Domain Index Views
• Object Dependencies, Drop Semantics, and Validation
  • Object Dependencies
  • Object Drop Semantics
  • Object Validation
• Indextype, Domain Index, and Operator 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
• Using System Partitioning
  • Advantages of System Partitioned Tables
  • Implementing System Partitioning
    • Creating a System-Partitioned Table
    • Inserting Data into a System-Partitioned Table
    • Deleting and Updating Data in a System-Partitioned Table
  • Supporting Operations with System-Partitioned Tables
  • Running Partition Maintenance Operations
  • Altering Table Exchange Partitions with Indexes
• Using System-Managed Domain Indexes
• Designing System-Managed Domain Indexes
• Creating Local Domain Indexes
• Maintaining Local Domain Indexes with INSERT, DELETE, and UPDATE
• Querying Local Domain Indexes
• Restrictions of System-Managed Domain Indexing
• Migrating Non-Partitioned Indexes
• Migrating Local Partitioned Indexes

9 Defining Operators

• User-Defined Operators
  • Operator Bindings
  • Operator Privileges
  • Creating Operators
  • Dropping Operators
  • Altering Operators
  • Commenting Operators
  • Invoking Operators
• Operators and Indextypes
  • Operators in the WHERE Clause
    • Operator Predicates
    • Operator Resolution
    • Index Scan Setup
    • Execution Model for Index Scan Methods
  • Using Operators Outside the WHERE Clause
    • Creating Index-based Functional Implementations
    • Operator Resolution
    • Operator Execution
  • Operators that Return Ancillary Data
    • Operator Bindings That Compute Ancillary Data
    • Operator Bindings That Model Ancillary Data
    • Operator Resolution
    • Operator Execution

10 Using Extensible Optimizer

• Overview of Query Optimization
  • 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
  • Generating Statistics for System-Managed 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
  • Distributed Execution
  • System-Managed Storage Tables and ASSOCIATE STATISTICS
  • Aggregate Object-Level Statistics
  • System-Managed Domain Indexing
  • Performance

11 Using User-Defined Aggregate Functions

• Overview of User-Defined Aggregate Functions
• Creating a User-Defined Aggregate
• Using a User-Defined Aggregate
• Evaluating User-Defined Aggregates in Parallel
• Handling Large Aggregation Contexts
  • External Context and Parallel Aggregation
  • User-Defined Aggregates and Analytic Functions
  • Reusing the Aggregation Context for Analytic Functions
  • External Context and User-Defined Analytic Functions
• Using Materialized Views with User-Defined Aggregates
• Creating and Using a User-Defined Aggregate Function

12 Using Cartridge Services

• Introduction to Cartridge Services
• 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

13 Using Pipelined and Parallel Table Functions

• Overview of Table Functions
• Table Function 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
  • Combining PIPE ROW with AUTONOMOUS_TRANSACTION
  • Implementing the Interface Approach
    • Scan Context
    • Start Routine
    • Fetch Routine
    • Close Routine
    • Describe Method
    • Prepare Method
  • 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
• Creating Domain Indexes in Parallel
• Transient and Generic Types

14 Designing Data Cartridges

• Choosing the Programming Language
• Invoker's Rights
• Callouts and LOBs
• Saving and Passing State
• Designing Indexes
  • Domain Index Performance
  • Domain Index Component Names
  • When to Use Index-Organized Tables
  • Storing Index Structures in LOBs
  • External Index Structures
  • Multi-Row Fetch
• Designing Operators
• Designing for the Extensible Optimizer
  • Weighing Cost and Selectivity
  • Cost for functions
    • Selectivity for Functions
    • Statistics for Tables
    • Statistics for Indexes
• Designing for Maintenance
• Enabling Cartridge Installation
• Designing for Portability

Part III Scenarios and Examples

15 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
    • 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

16 PSBTREE: Extensible Indexing Example

• Introducing the PSBTREE Example
• Designing of the Indextype
• Implementing Operators
  • Create Functional Implementations
  • Create Operators
• Implementing the ODCIIndex Interfaces
  • Defining an Implementation Type for PSBTREE
  • Creating the Implementation Type Body
  • Defining PL/SQL Routines in the Implementation Body
  • Registering the C Implementation of the ODCIIndexXXX() Methods
  • Defining Additional Structures in C Implementation
  • Defining C Methods in the Implementation Body
  • Implementing the Indextype
• Using PSBTREE

17 Pipelined Table Functions: Interface Approach Example

• Pipelined Table Functions Example: C Implementation
  • Making SQL Declarations for C Implementation
  • Implementation ODCITable Methods in C
• Pipelined Table Functions Example: Java Implementation
  • Making SQL Declarations for Java Implementation
  • Implementing the ODCITable Methods in Java

Part IV Reference

18 Cartridge Services Using C, C++ and Java

• OCI Access Functions for External Procedures
  • OCIExtProcAllocCallMemory
  • OCIExtProcRaiseExcp
  • OCIExtProcRaiseExcpWithMsg
  • OCIExtProcGetEnv
• Installing Java Cartridge Services Files
• Cartridge Services-Maintaining Context
  • ContextManager
  • CountException()
  • CountException(String)
  • InvalidKeyException()
  • InvalidKeyException(String)

19 Extensibility Constants, Types, and Mappings

• System Defined Constants
• System-Defined Types
  • ODCIArgDesc
  • ODCIArgDescList
  • ODCIRidList
  • ODCIColInfo
  • ODCIColInfoList
  • ODCICost
  • ODCIEnv
  • ODCIFuncInfo
  • ODCIIndexInfo
  • ODCIIndexCtx
  • ODCIObject
  • ODCIObjectList
  • ODCIPartInfo
  • ODCIPartInfoList
  • ODCIPredInfo
  • ODCIQueryInfo
  • ODCIStatsOptions
  • ODCITabFuncStats
  • ODCITabStats
  • ODCIBFileList
  • ODCITabFuncInfo
  • ODCIDateList
  • ODCINumberList
  • ODCIRawList
  • ODCIVarchar2List
  • ODCIFuncCallInfo
• Mappings of Constants and Types
  • Mappings in PL/SQL
  • Mappings in C

20 Extensible Indexing Interface

• Extensible Indexing - System-Defined Interface Routines
  • ODCIGetInterfaces()
  • ODCIIndexAlter()
  • ODCIIndexClose()
  • ODCIIndexCreate()
  • ODCIIndexDelete()
  • ODCIIndexDrop()
  • ODCIIndexExchangePartition()
  • ODCIIndexFetch()
  • ODCIIndexGetMetadata()
  • ODCIIndexInsert()
  • ODCIIndexStart()
  • ODCIIndexUpdate()
  • ODCIIndexUpdPartMetadata()
  • ODCIIndexUtilCleanup()
  • ODCIIndexUtilGetTableNames()

21 Extensible Optimizer Interface

• The Extensible Optimizer Interface
  • EXPLAIN PLAN
  • INDEX Hint
  • ORDERED_PREDICATES Hint
• User-Defined ODCIStats Functions
  • ODCIGetInterfaces()
  • ODCIStatsCollect()
  • ODCIStatsDelete()
  • ODCIStatsFunctionCost()
  • ODCIStatsExchangePartition()
  • ODCIStatsIndexCost()
  • ODCIStatsSelectivity()
  • ODCIStatsTableFunction()
  • ODCIStatsUpdPartStatistics()

22 User-Defined Aggregate Functions Interface

• User-Defined Aggregate Functions
  • ODCIAggregateDelete()
  • ODCIAggregateInitialize()
  • ODCIAggregateIterate()
  • ODCIAggregateMerge()
  • ODCIAggregateTerminate()
  • ODCIAggregateWrapContext()

23 Pipelined and Parallel Table Functions

• Routines for Pipelined and Parallel Table Functions in C
  • ODCITableClose()
  • ODCITableDescribe()
  • ODCITableFetch()
  • ODCITablePrepare()
  • ODCITableStart()

A User-Managed Local Domain Indexes

• Comparing User-Managed and System-Managed Domain Indexes
• Truncating Domain Indexes
• Creating Indextypes
• Using Domain Indexes for the Indextype
• Partitioning Domain Indexes
• APIs for User-Managed Domain Indexes
  • ODCIIndexTruncate()
  • ODCIIndexMergePartition()
  • ODCIIndexSplitPartition()

Index