Plan-Per-Tuple Optimization Solution - Parallel Execution of Expensive User-Defined Functions.

Felipe Cariño, William O'Connell: Plan-Per-Tuple Optimization Solution - Parallel Execution of Expensive User-Defined Functions. VLDB 1998: 690-695
  author    = {Felipe Cari{\~n}o and
               William O'Connell},
  editor    = {Ashish Gupta and
               Oded Shmueli and
               Jennifer Widom},
  title     = {Plan-Per-Tuple Optimization Solution - Parallel Execution of
               Expensive User-Defined Functions},
  booktitle = {VLDB'98, Proceedings of 24rd International Conference on Very
               Large Data Bases, August 24-27, 1998, New York City, New York,
  publisher = {Morgan Kaufmann},
  year      = {1998},
  isbn      = {1-55860-566-5},
  pages     = {690-695},
  ee        = {db/conf/vldb/CarinoO98.html},
  crossref  = {DBLP:conf/vldb/98},
  bibsource = {DBLP,}


Object-Relational database systems allow users to define new user-defined types and functions. This presents new optimizer and run-time challenges to the database systemon shared-nothing architectures. In this paper, we describe a new strategy we are exploring for the NCR Teradata Multimedia Database System; our focus is directing research for realapplications we are seeing. In doing so, we will briefly describe optimizer challenges particularly related to predicate use of large multimedia objects, such as video/audio clips, images, and text documents. The motivation for this work is based on database tuning [SD96] for diverse queries related to multimedia objects. Most notably, expensive and/or high variant user defined functions [Hel98].

Our approach is referred to as plan-per-tuple. The primary focus being on large objects used as predicate-based terms when a non co-located join is involved in the query. But can also be applicable in non co-located join scenarios also. The execution engine can choose from among N! resource optimization strategies; where N represents system manageable resources. In our case, the N resources are: (i) interconnect saturation levels, (ii)available physical memory, (iii) CPU utilization, and (iv) available disk spool space percentages. However, this technique can be applied to any system resources being managed. The optimizer search space does not include these N! resource optimizationstrategies per'se, these are execution engine run-time optimization strategies. When the optimizer identifies expensive, or more importantly a high variant, user- defined function in the predicate (via collected statistics), then the optimizer can generate plans that incorporate plan-per-tuple optimization for that particular compiled query. When executing the plan, a different execution strategy can be used per tuple; the available execution choices do not necessarily equal N! Wedescribe when such an overhead for run-time selection is acceptable.

Copyright © 1998 by the VLDB Endowment. Permission to copy without fee all or part of this material is granted provided that the copies are not made or distributed for direct commercial advantage, the VLDB copyright notice and the title of the publication and its date appear, and notice is given that copying is by the permission of the Very Large Data Base Endowment. To copy otherwise, or to republish, requires a fee and/or special permission from the Endowment.

Online Paper


CDROM Version: Load the CDROM "DiSC, Volume 1 Number 1" and ...

ACM SIGMOD Anthology

DVD Version: Load ACM SIGMOD Anthology DVD 1" and ... BibTeX

Printed Edition

Ashish Gupta, Oded Shmueli, Jennifer Widom (Eds.): VLDB'98, Proceedings of 24rd International Conference on Very Large Data Bases, August 24-27, 1998, New York City, New York, USA. Morgan Kaufmann 1998, ISBN 1-55860-566-5
Contents BibTeX


Richard L. Cole, Goetz Graefe: Optimization of Dynamic Query Evaluation Plans. SIGMOD Conference 1994: 150-160 BibTeX
Surajit Chaudhuri, Kyuseok Shim: Optimization of Queries with User-defined Predicates. VLDB 1996: 87-98 BibTeX
Johann Christoph Freytag: A Rule-Based View of Query Optimization. SIGMOD Conference 1987: 173-180 BibTeX
Joseph M. Hellerstein, Michael Stonebraker: Predicate Migration: Optimizing Queries with Expensive Predicates. SIGMOD Conference 1993: 267-276 BibTeX
William J. McKenna, Louis Burger, Chi Hoang, Melissa Truong: EROC: A Toolkit for Building NEATO Query Optimizers. VLDB 1996: 111-121 BibTeX
William O'Connell, Ion Tim Ieong, David Schrader, C. Watson, Grace Au, Alexandros Biliris, S. Choo, P. Colin, G. Linderman, Euthimios Panagos, J. Wang, T. Walters: A Content-Based Multimedia Server for Massively Parallel Architectures. SIGMOD Conference 1996: 68-78 BibTeX
Hamid Pirahesh, Joseph M. Hellerstein, Waqar Hasan: Extensible/Rule Based Query Rewrite Optimization in Starburst. SIGMOD Conference 1992: 39-48 BibTeX
Patricia G. Selinger, Morton M. Astrahan, Donald D. Chamberlin, Raymond A. Lorie, Thomas G. Price: Access Path Selection in a Relational Database Management System. SIGMOD Conference 1979: 23-34 BibTeX
Surajit Chaudhuri, Luis Gravano: Optimizing Queries over Multimedia Repositories. SIGMOD Conference 1996: 91-102 BibTeX
Dennis Shasha: Database Tuning - A Principled Approach. Prentice-Hall 1992, ISBN 0-13-205246-6
Contents BibTeX
Michael Stonebraker, Dorothy Moore: Object-Relational DBMSs: The Next Great Wave. Morgan Kaufmann 1996, ISBN 1-55860-397-2
Andrew Witkowski, Felipe Cariño, Pekka Kostamaa: NCR 3700 - The Next-Generation Industrial Database Computer. VLDB 1993: 230-243 BibTeX

Referenced by

  1. William O'Connell, Felipe Cariño, G. Linderman: Optimizer and Parallel Engine Extensions for Handling Expensive Methods Based on Large Objects. ICDE 1999: 304-313
ACM SIGMOD Anthology - DBLP: [Home | Search: Author, Title | Conferences | Journals]
VLDB Proceedings: Copyright © by VLDB Endowment,
ACM SIGMOD Anthology: Copyright © by ACM (, Corrections:
DBLP: Copyright © by Michael Ley (, last change: Sat May 16 23:46:24 2009