NonStop SQL: Scalability and Availability for Decision Support.

@inproceedings{DBLP:conf/sigmod/Englert94,
  author    = {Susanne Englert},
  editor    = {Richard T. Snodgrass and
               Marianne Winslett},
  title     = {NonStop SQL: Scalability and Availability for Decision Support},
  booktitle = {Proceedings of the 1994 ACM SIGMOD International Conference on
               Management of Data, Minneapolis, Minnesota, May 24-27, 1994},
  publisher = {ACM Press},
  year      = {1994},
  pages     = {491},
  ee        = {http://doi.acm.org/10.1145/191839.191945, db/conf/sigmod/sigmod94-491.html},
  crossref  = {DBLP:conf/sigmod/94},
  bibsource = {DBLP, http://dblp.uni-trier.de}
}

Abstract

• The databases are large (100 GB+).
• Usually, they are composed of one or two large tables and several smaller reference tables.
• The large tables are typically historical in nature. Older data is removed and new data inserted on a regular basis.
• Indexes on large tables are prohibitive because of disk use.
• Most queries are joins between one large table and smaller reference tables.
• Most queries require aggregation of the data by groups.

Scalability is an inherent objective of these environments, since query times should remain relatively constant regardless of the size of the large tables. To improve the scalability of typical decision support queries, Tandem has added parallel implementations of hash joins, cross product joins and hashed groupings to NonStop SQL. Hash joins are useful when a large table is joined with a smaller one, especially if there are no useful indexes on the join columns. We briefly describe the hash join algorithm and use results from a customer benchmark to illustrate why it is often superior to merge joins and nested-loop joins under the given circumstances. Cross products (or "star joins") allow small tables to be joined without predicates if there is a subsequent equijoin of the composite table to another table. They can reduce the need to scan large tables in joins. Results from the customer benchmark demonstrate their usefulness. We also describe hashed groupings, which eliminate sorts to form groups for subsequent aggregation. Hashed groupings allow execution of queries in the benchmark that were previously impossible.

Maintenance of the decision support database (updates, addition of indexes, changes in its physical layout) must be performed regularly, and it is increasingly desirable that the database be available during these operations. To this end, Tandem is introducing a host of new on-line data management operations, including data partition adds, drops, splits and moves, as well as on-line index creation. We describe the implementation of partition moves as an example. The basic idea is to move a "dirty" copy of the data, and then to bring the new COW up to date by applying log records describing the effects of transactions that took place during the move. Other on-line data management operations are similar.

Copyright © 1994 by the ACM, Inc., used by permission. Permission to make digital or hard copies is granted provided that copies are not made or distributed for profit or direct commercial advantage, and that copies show this notice on the first page or initial screen of a display along with the full citation.

ACM SIGMOD Anthology

CDROM Version: Load the CDROM "Volume 1 Issue 1, SIGMOD '93-'97" and ...

• Windows: Click the letter of your CD drive
  A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
• Mac: Click here
• UNIX/LINUX: mount the CD and click on the path of your mount point:
  /Anthology/smod9397 or /cdrom

• Windows: Click the letter of your CD drive
  A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
• Mac: Click here
• UNIX/LINUX: mount the DVD and click on the path of your mount point:
  /Anthology/aDVD1 or /dvd

Printed Edition

Online Edition: ACM Digital Library