SQL statements are processed by the System R precompiler, which replaces the SQL statements ... System R is an experimental database management system designed and built ..... information in formatting the query result for display at a terminal. .... Fifteen percent free space is preserved on each data ...... 8 1 D. McLeod.
Research Report SUPPORT
Ff)fZ
REPETITIVE T W U C T I O N S AND AD-HOC QUERY IN SYSTEM R
D. D. Chamberlin M. M. Astrahan R. A . Lorie J . W. Mehl T, .G. Price 8. Schkolnick P. Griffiths Selinger D. R. Slutz I3. W. Wade R. A. Yost
IBM Research Laboratory San Jose, California 95193
Research D~vision 'forktown Heights, New York
San Jose, California
Zurich, Switzerland
LlMITED DISTRIBUTION NOTICE This report has been submitted for publication eleewhere and hae been issued as a Research Report for early dimmination of its contents. As a courtesy to the intended publisher, it should not be widely distributed until after the date of outside publication.
Copies may be requested from: IBM Thomas J . %'atson Research Center Post Office Box 218 Yorktown Heights, New Y'ork 10598
FLJ2SSl(33lSl) 5/22/79 Computer Science
SUPPORT FOR REPETITIVE TRANSACTIONS AND AD-HOC QUERY IN SYSTEM R
D. D. Chamberlin N. M. Astrahan R. A. Lorie J. W. Mehl T. G. Price M. Schkolnick P. Griffiths Selinger D. R. Slutz B. W. Wade R. A. Yost
IBM Research Laboratory San Jose, California 95193
ABSTRACT: System R supports a high-level relational user language called SQL, which may be used by ad-hoc users at terminals or as an imbedded data sublanguage in PL/I or COBOL. Host language programs with imbedded SQL statements are processed by the System R precompiler, which replaces the SQL statements by calls to a machine-language access module. The precompilation approach removes much of the work of parsing, name binding and access path selection from the path of a running program, enabling highly efficient support for repetitive transactions. Ad-hoc queries are processed by a similar approach of name binding and access path selection, which takes place on-line when the query is specified. By providing a flexible spectrum of binding times, System R permits transaction-oriented programs and ad-hoc query users to share a database without loss of efficiency. System R is an experimental database management system designed and built by members of the IBM San Jose Research Laboratory as part of a research program on the relational model of data. This paper describes the architecture of System R, and gives some preliminary measurements of system performance in both the ad-hoc query and the "canned program" environments.
IBM
Research Division
Yorktown Heights, New York
San Jose, California
.
Zurich, Switzerland
PAGE
1
INTRODUCTIOH
R
System
is
an experimental built at I B M
designed and part of data. [
a program
and S Q L ,
Since
now essentially
paper
to bring
description
up
of system
preliminary
described in
has undergone
and implementation of
system is is
System R
to
complete.
The
date the
of
the
3 1.
certain
purpose of
and
[
the prototype
previously
architecture,
measurements
model o f
first described in
interface, was
these publications,
system
Laboratory as
the relational
o f System R was
its user
architectural changes,
management
San Jose Research
o f research in
The architecture
1 I
database
published
to present
performance
this
of
some the
prototype.
One
of the
different
basic types
goals of
of System
processing against
ad-hoc queries and updates, which once,
and ( 2 1
program library makes all
canned programs,
These
is a
to support
two
database:
( 1 )
are usually executed only which are
and executed hundreds
the features of SQL
environments.
R
installed in
of times.
1 3 1 available in
features include
a
System R both these
statements to
query
and update a database, to define and delete database objects such as tables, views, and indexes, and to control access to the database by various users.
An ad-hoc
user at
a terminal may
t y p e SQL
s t a t t n e n t s and
view the result directly at the terminal as in the following examples:
S E L E C T NAME,
S A L A R Y F R O M E M P WHERE J O B =
'PROGRAMMER';
U P D A T E E M P S E T S A L A E Y = 9 5 0 0 WHERE E M P N O = 5 0 1 ;
The same S Q L
statements may be imbedded in a
program by prefixing from host-language C O B O L programs
them with %-slgns to statements.
may contain
SQL
PL/I
or C O B O L
distinguish them
statements in
PL/I
or
i f the
host-language variables
as in the follouing
variable-names are prefixed by S-signs, example:
$UPDATE
E M P S E T S A L A R Y = $X
Host-language variables place of
data values
WHERE EMPfiO = S Y ;
in a S Q L but n o t
statement may be
in place
used in
of table-names
or
field-names.
If a P L / I or C O B O L program wishes to execute a S Q L query and fetch the
result, it
cursor is defined
does so
by means
by a L E T statement,
cursor-name with a particular query. for retrieval by of
any
of a
search-conditions in the
The cursor is readied
variables
query.
A
which associates the
an O P E N statement, which
host-language
"cursor".
binds the ,~a!ues appear i n g
Then a FETCH
in ,
statement is
PAGE 3
used repeatedly to
fetch rows from the answer
set into the
designated program variables, as in the following example:
%LET C1 BE SELECT NAME, SALARY INTO $X, 5 Y FROM EMP WHERE J O B = 9Z;
%OPEN C1;
/*
B I N D S VALUE OF Z
SFETCH C 1 ;
/*
FETCHES ONE EMPLOYEE INTO X AND Y
$CLOSE C1;
/*
AFTER ALL VALUES HAVE BEEN FETCHED
After the execution of each SQL
*/ */
x/
statement, a status code is
returned t o the host program in a variable called S Y R - C O D E .
System
R is
called the
based on
a special
Research Storage
multi-user access
System (RSS),
for locking, logging, recovery,
method
with facilities
and lndex maintenance.
The
description of the RSS is essentially unchanged srnce 1 1 1 .
However, the Relational Data System of
the
access
functions: to
(1)
method a
is
now
(RDS) which runs on top
spllt
into
txo
precompiler, called XPREP, which
precompile host-language
programs and
"canned programs" under System R, system, called XRDI,
distinct is used
install them
as
and ( 2 ) an execution-tlne
which controls the e x e c u t i ~ n of these
PAGE 4
"canned
programs"
and
also executes
SQL
statements
for
ad-hoc terminal users.
When an application
programmer has written a
program with imbedded present
the program
SQL statements, his first to the
or COBOL
PL/I
step is to
R precompiler,
System
XPREP.
XPREP finds the SQL statements in the program and translates them into a machine-language "access module". program, the
SQL statements
are replaced
calls to the access module.
The
the
protect
System
modification.
R
database to
In the user's
b y host-language
access module is stored in
The precompilation
it
from
step
unauthorized
is illustrated
in
Fig. 1 .
The
advantages
gained
for
canned
programs
by
the
precompilation step are twofold:
(1)
Much of the work o f
parsing, name-binding, access path
selection, and authorization checking
can be done once
b y the precompiler and thus removed from the process of
running the canned program.
(2)
The
access
module,
specific program,
because it
is much smaller
is
tailored and runs
to
one
much nore
efficiently than a generalized SQL interpreter.
P L/ I Source Program
I I I
SELECT NAME INTO SX FROM EMP WHERE EMPNO= $Y
SYSTEM R PRECOMPI LER (XPREP)
e$fq$q GEN
Modified P L i l Program
1
FIG. 1 PRECOMPILATION STEP
Machine code
1
PAGE 6
After precompilation, the user's or COBOL, and can be
program contains pure PL/I
compiled using a conventional language
compiler .
When a "canned
program" is run o n System R ,
to XRDI, which
in turn loads and invokes
it makes calls
the access module
for the program.
The access module operates on the database
by
to RSS,
making calls
user's program.
The
delivers the
result to
the
This process is illustrated in Fig. 2 .
ad-hoc user
program
and
called
System R
of
is
supported
by a
the User-Friendly
Interface
(UFI),
s?ecial which
controls dialog management and the formatting of the display
The UFI has an access
terminal.
access module
is not complete
module of its own, but its because UFI's purpose
is to
execute SQL statements which are not known in advance. a
user
enters an
statement
to
ad-hoc
XRDI
by
"EXECUTE" calls which of these calls module to The
means
statement, UFI of
special
is to cause a new "section"
generated
machine-language
for the
section of
the
code,
is
and
indistinguishable from the sections the precompiler.
The interactions of
illustrated in Fig. 3.
passes
the
"PRETARE"
and
will be described later.
be dynamically generated
dynamically
contains
SQL
When
The effect
of UFI's access new statement. access in
module
e71erjr w a y
which were generated b y UFI with System
F.
are
PAGE 7
I
User's Object Program
call
v Execution-time System (XRDI)
Loads, then calls
b
Access Module
i
call
v
D A T A BASE
FIG. 2
EXECUTION STEP
PAGE S
"PREPARE" and "EXECUTE" calls
+
4
Execution-time System (XRDI) b
PARSE
Bu~lds, then calls
I OPT
Access Module
b
*
.
call
CODE
I
It
r3 DATA BASE
FIG. 3
PROCESSING O F AD-HOC QUERIES
PAGE 9
System R
permits many
performing
users to
a variety
of
be active
activities.
precompiling new programs while
simultaneously,
Some users
he
others are running existing
"canned programs".
At the
using UFI, querying
and updating the database
new tables and views.
may
same time,
other users
may be
and creating
A l l these simultaneous activities are
supported by the automatic locking
subsystem built into the
RSS, described in 1 4 1 .
We will System
now examine
R:
special
precompilation, Next,
program".
in detail the
w e will examine
PREPARE
support ad-hoc
and
and
two major execution
of
a
"canned
how System R implements the
EXECUTE calls
users.
functions o f
Finally,
which
we will
database and some measurements of
are
needed
present a
to
sample
the performance of System
R in both a canned-program and an ad-hoc query environment.
PDECOMP ILATION
When a PL/I or COBOL program with imbedded S Q L statements is presented to the System R to find the and replaces CALL.
SQL statements (they are each SQL
In addition,
three-step
precompiler, it scans the program
process
statement by
each SQL in
machine-language routine.
order
indicated by $-signs) a valid
statement is to
host-language put through
translate
it
to
The three steps are as follows:
a a
PAGE
1.
Parsing:
The
syntactic
parser
checks t h e
validity,
conventional
and
parse-tree
translates
representation.
h o s t program v a r i a b l e s
found
list of
(values
input variables
c a l l i n g program
The
d a t a t o b e f e t c h e d by
two
for a
parser
lists of
statement:
i n t h e SQL
a
t o be f u r n i s h e d by t h e
and used i n p r o c e s s i n g
list of output
into
it
t h e System R precompiler
also returns t o
and a
statenent
SQL
10
variables
the statement)
(target
the statement).
locations for For' example,
if
t h e S Q L s t a t e m e n t b e i n g p a r s e d were as f o l l o w s :
S E L E C T NAME,
SALARY
INTO SX, S Y
FROM EMP WHERE D E P T = BA A N D J O B = SB
the input
v a r i a b l e s would b e A
and B ,
and
the output
v a r i a b l e s would be X and Y .
2.
Optimization: with the parse
The
System R o p t i m i z e r
tree a s i n p u t .
The
is
then
invoked
optimizer performs
several tasks:
a.
First,
using the internal
resolves a l l
c a t a l o g s of Systen R ,
s y m b o l i c names
in the
it
SQL s t a t e m e n t
t o internal database objects.
b.
A
check
is
made
that
the
current
user
is
PAGE
authorized to
perform the indicated
11
operation on
the indicated table(s1.
c.
If
the SQL
statement
user-defined views, (stored in
operates
the definitions of
parse tree form)
SQL statement
on one
to form a
or
the views
are merged
new composite
more
with the SQL parse
tree which operates on real stored tables.
d.
The optimizer uses the system catalogs to find the set
of
available
statistical
indexes
information
processed. This access path
certain
the
tables
information is used to
and an
SQL statement.
on
and
algorithm for
The details
selection
process
are
optimizer
represents its
ASL (Access
in
chosen
structural modifications to the
to
be
choose an
processing the
of this
given
other
access path [
The
101.
access path
by
parse tree called
Specification Language)
constructing the RSS control blocks
[51,
and
by
to be used in
processing the statement.
3.
Code generation: structures
The code generator translates the RSL
produced
machine-language
by
the
routine which
access
path
called
a "section".
[61.
This
optimizer
into
implements the
machine-language
When running,
a
chosen
routine
t h e section
370
is
will
PAGE 1 2 access the
database by
using the
RSS control
blocks
which were produced by the optimizer.
After
all
the
translated
into
together to
statements
SQL
sections,
form an
in
the
a
program
sections
access module.
In
have
are
been
collected
the header
of the
access module is placed a Section Location Table which lists the relative byte Nodule. the
offset of each section
Each section has a Relocation Directory which lists
offsets within
the section
of
which must be relocated before the addition to SQL This
within the Access
all internal
section can he used.
machine-language code,
statement from enables the
access path should
which section
it
pointers
each section
was originally
to be
rebuilt
holds the
constructed.
if its
become unavailable at some
original
future time.
The structure of an access module is shown in Fig. 4. of the entries
in the access module,
be explained later
in this paper.)
In
(Some
e.g. INTERPSECT, will When
the access module
is complete, it is stored in the System R database for later use.
If
the user
who precompiled
the program
passes the
authorization test for all S O L statements in the program, he receives the "RUN" privilege for the access module.
When
the precompiler
section,
it must
user's PL/I XRDI,
also
translates
replace the
or COBOL program b y
t h e System
a
SQL statement
SQL
a CALL.
R e n t r y p o i n t u s e d for
into
stateaent in The
call
a the
invo!xes
e x e c u t i n g a stored
PAGE 13
Descriptor in System Catalog:
t
Program-name
Date
Creator
I
Valid?
Location
I
Section Location Table Section
TYpe
#
Offset
1
COMPl LESECT
0
2
INTERPSECT
3
PARSEDSECT
a a
Section 1 Machine code
3
+
relocation directory
+
original SQL statement
I Section 2 Parse tree
+
relocation directory
+
original SQL statement
.
Section 3 Parse tree
+ relocation directory +
original SQL statement
FIG. 4 STRUCTURE OF AN ACCESS MODULE
-
PAGE 14 access module. the
The
parameters of the call are
access module,
module, an and/or
the section
number
operation-code, and the
output
variables
to
be
the name of
within the
addresses of used
in
access
the input
processing
the
statement.
I f the SQL statement under consideration is not an operation on
a
cursor,
the
straightforward.
The
simply, "execute in
the
construction
operation-code is
the section."
original
SQL
of
the
call
AUXCALL,
is
meaning
host-program variables
All
statement are
passed
in
with
the
a cursor,
the
AUXCALL, as shown in Fig. 5 .
If
the SQL
statement
situation is slightly
is an
operation
more complex.
on
The cursor
is defined
by an SQL statement of the form:
LET (cursor-name> BE .
The FETCH
basic operations (cursor-name>,
statement
does
not
definitional in nature. the
System
R
on
cursors
and
CLOSE
result
cursor,
fetching,
and closing
in
O P E N , FETCH, and CLOSE
a
CALL,
produces
containing
(cursor-name>,
(cursor-name>.
In response to the
precompiler
indicated
are O P E N
a
machine
the cursor.
since
Then,
it
LET is
LET statement,
section code
The
for
for
the
opening,
in response
to
statements the precompiler generates
PAGE 15
UPDATE EMP SET SAL = SAL + SP WHERE EMPNO = $0:
LET C1 BE SELECT NAME. SAL INTO SX, $Y FROM EMP WHERE DEPTNO =$A AND JOB = SB;
CALL XRDl (
1 1;
A.M. Name = PROG 1 Section No. = 1 Opcode = AUXCALL
(No call produced, since this statement serves only to define the cursor. "Section 2" is created in the access module to implement the cursor.)
OPEN C1;
Section No. = 2 Opcode = OPENCALL
FETCH C1; A.M. Name = PROGI Section No. = 2 Opcode = FETCHCALL Variables
CLOSE C1:
CALL XRDl (
f
1;
1
A.M. Name = PROG1 Section No. = 2 Opcode = CLOSECALL Variables: None I
1
I
FIG. 5 REPLACEMENT OF SQL STATEMENTS B Y CALLS
P A G E 16
CALLS
on
the
appropriate
section 5.
operation-codes as shown in Fig. variables are passed input
values are
Addresses of
After
always
bound when
output variables are
in the
one
The addresses o f input
a
OPEN call, since cursor is
passed as
opened.
parameters o f
locations for the data to
R precompiler user's
has replaced
program by
program contains pure P L / I or COBOL, using
appropriate
No variables are involved in the CLOSE call.
the System
statements
the
as parameters o f the
the FETCH call, giving the target be fetched.
with
of
the conventional
calls
all the
SQL
to XRDI,
the
and it may be compiled
language
compilers.
The
resulting object program is now ready to be run on System R.
EXECUTING
PRECOMPILED P R O G R A M
When a user invokes a program
which has been precompiled on
System R , the normal facilities used to load
of the operating system are System !F first
and start the object program.
becomes aware of the program when it makes its first call to XRDI.
On the first such call,
the current user to invoke checks
that the
XRDI checks the authority o f
the indicated access module, and
access module
checks are successful, the access database
into virtual
memory,
is still
valid.
If
these
module is loaded from the its
internal
pointers
are
adjusted using t h e relocation directory of each section, and
then
control
subsequent
is calls
authorization bypassed, section.
passed to
check,
and
to
the
the
same
loading
control
indicated access
and
section.
On
m~dule,
the
relocation
passes directly
The machine language code
to
steps
the
are
indicated
~n the section examines
the operation-code of the call (e.g., OPENCALL or FETCHCALL) and
proceeds to
which
it was
process the
original
compiled, using
variables which were passed in
as
SQL statement
from
needed the
host-program
with the call.
Yhen running
under M V S , the access module is assigned a different storage protection key from
the user's program in
order to provide
them with mutual protection.
Since all
name binding, authorization checking,
path selection are done during resulting
access
module
is
the precompilation s t e p , the dependent
existence of the tables it operates as
access
paths,
Therefore,
and
whenever
privilege
the
a
System
or index
R
on
the
continued
on, the indexes it uses
privileges
table
is revoked,
and access
of is
its
creator.
drDpped
or
a
automatically performs
a
search in its internal catalogs to find access modules which are affected by the change.
If the change involl?es dropping
a table or revoking a necessary privilege, the access module is
erased
from
the
database.
However,
if
the
change
involves merely dropping an index used b y the acsess mgdule9 it
will be
choosing
possible
to regenerate
an alternative
access path.
the
access module In
this c a s e ,
by
the
P A G E 18
access module is is next
marked "invalid".
invoked, the
When the
invalid marking
access module
is regenerated
SQL statement
contained within each
passed
through the
available access paths. is
stored in
memory
the
in
any
the
section is
original once again
optimizer, and
section based on
database and
also
The user's
way,
regeneration process
The
and the
the
code
the currently
The newly regenerated access module
for execution.
affected
is detected
automatically.
parser,
produce a new
generator to
access module
and the
source
user
except for a
loaded into
is
virtual
program is unaware
slight delay
of
not the
during the
initial loading of his access module.
It
is
possible that
a
user
may
attempt to
change
the
database in some way which would invalidate an access module while the access
module is actually loaded
and running. It
would be undesirable if such a change were allowed to become effective while the running acess module is in the middle of some
operation.
To
prevent
"transaction" mechanism of can declare
TRANSACTION and
Users
are
advised
database is
unit of work the
to
end a
unit of
occurring, .\
statements of
transaction
only
work has
access
completed and
While a module
the
programmer
program by
and consistent state;
has not yet begun. loaded
in his
END TRANSACTION
in a "clean"
one user-defined
from
System R is used.
transaction boundaries
BEGIN
progress,
this
when
the SQL.
the
i . e . , when the next
transaction is in
protects
itself
by
PAGE 19
holding a lock on its own tables.
Therefore,
description in the system catalog
any
database
invalidate the access module
releases
end of each the lock
its
would
until the lock is released.
transaction, the running on
which
(changing its description from
"valid" to "invalid") must wait At the
change
own description,
access module allowing
any
database changes which were waiting for the lock to proceed. At the beginning of the
next transaction, the access module
attempts
the lock
to re-acquire
on
its own
description.
There are four possible outcomes:
1.
The
description
timestamp
is
in the
still
marked
description is
"valid", unchanged.
and
the
In
this
case, execution of the access module proceeds normally.
2.
The description
is gone.
destroyed b y loss
The
access module
of an essential table
An appropriate code is returned
has been
or privilege.
to the user's program,
which is unable to continue.
3.
The description is present indicates that an been dropped.
but marked "invalid".
index used by the
The access module
This
access module has
is regenerated on the
spot, choosing a new access path to replace the missing index.
The
interruption.
user
program
then
continues
uithout
4.
The description has
changed
regeneration.) loaded
into
is marked
(indicating
"valid", but another user
The new (regenerated) virtual
memory,
and
its timestamp has
caused
a
access mcdule is
the
user
program
continues.
TREATMENT ql: "NON-OPTIMIZABLEw STATEMENTS
For certain types
o f SQL statements, no
of access path is required. which
create and
of creating
a new
These statements include those
drop tables
transactions, and grant and
significant choice
and indexes,
begin and
revoke privileges.
table, for
The process
example, involves
description of the table in the system catalogs. process takes table, it routine
place essentially the
is possible for creating
to build tables.
is
placing a Since this
same way for
each neu
R a
standard
into System It
end
then unnecessary
to
generate new machine code in an access module whenever a new table is
to be created.
Instead, the standard
invoked and given the name o f the list of its
fields and their data
program is
table to be created and a types.
This information
is conveyed in the form of the SQL parse tree for the CREETE TABLE
statement.
We will refer to SQL statements uhich can
be handled in this way as "non-optimizable" statements.
When the
System R precompiler encounters
a non-optimizable
PAGE 2 1
statement in a user program, it places the parse tree of the statement directly
into the
section of
rather than invoking the optimizer resulting
section
is
labelled
the access
moclule
and code generator. as
an
The
"INTERPSECT",
to
distinguish it from a section containing machine code, which is labelled a "COMPILESECT".
At run
time, when XRDI receives
section, it examines
a call to execute
the label o n the section.
If
a given it is a
COMPILESECT, XRDI gives control directly to the section. it is an
INTERPSECT, XRDI determines the
examining
the root
of
the parse
appropriate standard routine.
The
its necessary inputs ( e . g . , table
tree,
If
statement-type by then invokes
the
standard routine obtains and field-names) from the
parse tree in the INTERPSECT.
OPERATIONS
TEMPORARY TABLES
Occasionally
a user
may write
a program
which creates
a
temporary table in the database, processes the table in some way, then such a
destroys the table a t
program is
the end of the
precompiled, the
System R
run.
When
optimizer is
unable to choose a n access path for processing the temporary table because it does not yet exist. discovers during
Whenever the optimrzer
precompilation that some
tabls referenced
in an SQL statement does not exist, it places th? parse tree
PAGE 2 2 for the SQL
statement in a special section and
"PARSEDSECT".
This indicates
parsing, optimization,
that the
and code
labels it a
normal process
generation was
of
terminated
after the parsing step.
At
run time,
when
PARSEDSECT, it cannot
XRDI receives
a
call
to execute
give control directly to
because it does not yet contain machine code. makes another attempt tree
in the
the section Instead, XBDI
to invoke the optimizer
PARSEDSECT.
This
time,
the
on the parse temporary
since the
table is about to be operated on, it should be in existence. If
optimization
invoked, a
is
successful,
machine language routine
PARSEDSECT changes into a executed.
the
However,
if
code
gonerator
is gonerat?d,
is
and the
COMPILESECT, which is immediately optimization
indicated table still does not exist,
fai 1s
because
the
a code is returned to
the calling program indicating "nonexistent tahle".
The
transformation
of
a
PARSEDSECT
affects only the version of the in virtual database.
into
a
COMPILESECT
access module w3ich is held
memory, not the version
which is stored
in the
PAGE 2 3
DYNAMICALLY DEFINED STATEMENTS
Some p r o g r a m s may n e e d t o not
known a t
of
System
time t h e
the
example of such R,
which
allows
a terminal,
the
Another
l o a d e r program,
b u t which
t h e name o f
the
The
SQL
was p r e c o m p i l e d .
"User-Friendly
users
then
example
to
is
ad-hoc
SQL
and d i s p l a y s
a general-purpose
bulk
i n t o t a b l e s v i a SQL INSERT
d o e s n o t know a t
t a b l e t o be
An
Interface"
type
e x e c u t e s them
which l o a d s d a t a
statements,
t y p e s of
program
is t h e
a program
statements at results.
e x e c u t e SQL s t a t e m e n t s w h i c h w e r e
loaded,
precompilation t i m e
or the
number and d a t a
its columns.
language
application
is
feature the
which
PREPARE
supports
statement,
this which
type has
of the
following syntax:
PREPARE ( s t a t e m e n t - n a m e >
For example,
AS < v a r i a b l e >
a programmer m i g h t write:
PREPARE S 1 AS QSTRING;
This
indicates
character-type which s h o u l d be
to
System
R
that,
a t
run-time,
the
v a r i a b l e QSTRING w i l l c o n t a i n a SQL s t a t e m e n t o p t i m i z e d and a s s o c i a t e d w i t h
QSTRING may c o n t a i n
any k i n d of
SQL s t a t e m e n t ,
t h e name S 1 . a n d the S Q L
PAGE 2 4
statement may have "parameters" indicated by question-marks, such as:
UPDATE EMP SET SALARY = ? WHERE EMPNO = ?
When the
precompiler encounters
program, it
creates a
access module called
a PREPARE
special zero-length an INDEFSECT.
In the
the PREPARE statement is replaced by
statement in section in
a the
user's program,
a special call to XRDI
with operation code = SETUPCALL, containing a pointer to the variable QSTRING.
At run-time, XRDI interprets the SETUPCALL as an instruction to accept a it through result is
dynamically-defined SQL statement, and the parser, optimizer, and a new COMPILESECT
is replaced
only in the
code-generator.
or INTERPSECT,
the INDEPSECT in the access module.
to pass
which replaces
(However, the INDEFSECT
virtual-memory copy of
the access
module, not in the copy which remains in the database.) dynamically-defined statement
The
is now
ready to
The
be executed
like any other SQL statement.
After writing want
PREPARE S1
to execute
the
prepared statement was the following syntax:
AS QSTRING,
statement he
has
not a query, the
the programner
will
prepared.
If
the
programmer
may
use
EXECUTE (statement-name>
[
USING 1
For example:
EXECUTE S1 USING SX, SY
The precompiler will translate this EXECUTE statement into a normal
AUXCALL
addresses
on
of $X
the
and $Y
parameters passed at
indicated as
parameters of
parameters
table-names executed
or
many
may
times, with optimizer.
QSTRING statement
data
the call.
The
in positional
values
The statement
different
but S1
parameters,
again, the
not
may
be
without
the PREPARE
However, i f
is executed
the
the SQL statement S1 (note:
represent
column-names.)
re-invoking the
passing
EXECUTE time are bound,
order, to the question-marks in these
section,
contents of
S1 AS the
section are discarded and a new COMPILESECT or INTERPSECT is constructed based on the new contents of QSTRING.
If
the
produced
prepared for
it
statement is will
produced for a cursor.
look
and S1
AS
query,
exactly
like
the
COMPILESECT
a
COIIPILESECT
In other words, the two stategents:
LET C 1 BE
PREPARE
a
QSTRING
PAGE 26
will produce
exactly the
QSTRING are the same as on a
same section .
"prepared" query are the
cursor:
OPEN,
FETCH, and
contents of
Therefore, the operations
same as the operations
CLOSE.
included in an OPEN statement,
if the
Input
variables may
on a be
and the target variables are
listed in the FETCH statement, as in the following examples:
OPEN Sf USING BA, S B ;
(Precompiler produces OPENCALL with addresses of 9 R and SB as parameters. 1
FETCH S1 INTO 9 x 9 SY;
(Precompiler produces FETCHCALL with addresses of SK and $Y as parameters.)
CLOSE S 1 ;
(Precompiler produces CLOSECALL.)
In addition
to OPEN,
FETCH, and
another operation called DESCRIBE a query.
CLOSE, System
on sections which contain
The syntax of a DESCRIBE statement is as follows:
DESCRIBE (statement-name>
The System
indicated
receives
INTO carray?
R precompiler translates the
into a special DESCRIBECALL o n the
R supports
statement-name.
the DESCRIBECALL,
DESCRISE statement
the section corresponding to At
it returns
run-time, into the
when
XRDI
indicated
PAGE 27 array a description of the field-names and data-types in the query
result.
The
calling
program
information in formatting the query terminal.
A
DESCRIBECRLL
on a
can
then
use
this
result for display at a section
which
does
not
contain a query returns a code indicating "no result."
AND CALL-TYPES SECTION-TYPES -
SUMMARY
The above description has shown that the four basic steps in the processing of a SQL statement are parsing, optimization, code
generation, and
System R
is to perform as
during precompilation, at run-time. break
execution.
The
basic philoscphy
many of these steps
then to perform the
Depending on the
between precompile-time
of
as possible
remaining steps
nature of the statement, the and
run-time
may occur
at
several different places in the processing of the statement, as
shown in
early-binding
Fig. 6.
The mechanism
philosophy
of
System
section-types and six call-types.
The
which implements
R
consists
of
the four
behavior of X R D I for
each combination of section-type and call-type is summarized in F i g . 7.
PAGE 29 COMPILESECT
AUXCALL
INTERPSECT
I Execute the1 Execute a I machine I standard
PARSEDSECT
I
INDEFSECT
I
I I
code in the1 routine I optimizer I section. I controlled I and code I I by the I generator I I I I content of I to convert I I I the section1 the section1 ----------I------------I-----------+ into a +-----------I Execute the1 I COMPILESECTI OPENCALL I machine I I or I I code in the1 I 1NTERPSECT;I I section, I (Not u s e d ) I then 1 (Not used) I with opcodel I execute it.1 I =OPEN. I 1 I ----------I------------I------------b----------l-----------1 Execute the1 I I FETCHCALL I machine I (Not used) I (Not used) I (Not used) I code in the1 I I I section, I I I I with opcodel I I I =FETCH. I I I
----------I------------I------------I------------l-----------CLOSECALL
I
I Invoke the I (Not used) I
I I I I I I I
I I I I I + I I I 1
I 1
+
I Execute the1 I I 1 I machine I (Not used) I (Not used) I (Not used) I I code in the1 I I 1 I section, I I I 1 I with opcodel I I I I =CLOSE. I I I I
----------+------------+------------!------------/-----------+ I Throw away the current I 1 Invoke the I I content of the section, I I parser, I SETUPCALL I and invoke the parser, I (Not used) I optimizer, 1 I optimizer, and code I I and code I I generator to build a I I generator I I new COMPILESECT o r I I to c o n v e r t I I INTERPSECT from a new I I a new S3L I I SQL statement. I I statement 1 I I I into a I I I I COMPILESECT1 I I I or I I 1 I INTEP.PSECT.1
----------I------------+------------I------------l------------
FIG. 7 SECTION-TYPES AND CALL-TYPES
+
P A G E 30
PERFORMANCE MEASUREMENT
In order
to illustrate
environment where transactions, constructed.
how System
R might
ad-hoc queries are mixed
the The
example
database
PARTS table
be used
with repetitive
in
contains
in an
Fig.
the
8
was
description,
quantity on hand, and quantity on
order for a collection of
parts identified by part numbers.
The ORDERS table contains
a set
parts.
of outstanding
orders for
contains a set of price quotes is
identified by
a
quote applies. and
part
may
QUOTES table
Each price quote
supplier number
and
part
maximum quantities for which the
Typically a
numbers
for parts.
particular
number and the minimum and
The
have
given combination several
quotes:
of supplier one
for
quantities from 1 to 1 0 0 , another for quantities from 101 to 10,000,
etc.
PARTS
PARTNO L I ,
'CHARW
DESCRIP
-
QO H
000
CHAR(50)VAR
INTEGER
INTEGER
1 v'
ORDERS
ORDERNO
PARTNO
SUPPNO
DATE
QTY
CHAR (6)
CHAR(6)
CHAR(3)
CHAR(6)
INTEGER-,,
MlNQ
MAXQ
PRICE
INTEGER
INTEGER
INTEGER
7
QUOTES CHAR (3)
CHAR(6)
FIG.
8
SAMPLE DATA BASE
I
:;
*
PAGE 32
The
following
structural
and
statistical
information
completes our description of the example database:
Total
size of
database
(including
data records
but
not
including indexes) = 7.44 Megabytes.
The
data
values
in
the
sample
database
were
randomly
generated according to the following rules:
No. of different part descriptions = 1024
No. of different supplier numbers = 1000
Each part no. has exactly 3 outstanding orders, and has
3 price quotes from each of 3 different suppliers.
Clustering method:
The
three tables are stored
on disk in
an interleaved fashion? ordered
by PARTNO.
Each PARTS
record is
ORDERS and
Q U O T E S for
followed by
that part number, Fifteen percent
all the
then by the next free space is
PARTS record, etc.
preserved on
each data
page to allow for future insertions? which will also be clustered by PARTNO.
A two-part experiment was performed on the example database. The f i r s t p a r t i n v o l v e d m e a s u r e m e n t o f
s u b m i t t e d v i a the U s e r - F r i e n d l y
three
example queries
Interface ( U F I ) o f S y s t e m I?.
PAGE 3 3
For
each
query, the
measured for
each step
optimization, code
time
CPU
and
number of
in processing
generation, and
110's
the query:
fetching of
was
parsing. the answer
set.
The second
part of the
experiment involved writing
program
to process
three
types
against
the sample
database.
of "canned This
a PL/I
transactions"
program accesses
and
updates the database by means of imbedded SQL statements, as described in
[31.
Measurements of
C P U time and
number of
110's were made during precompilation and compilation of the transaction
program, and for execution of each of the three
transaction types. how
the execution
In addition, measurements were times
for
the three
affected by database size, using
made
transactions
of are
a series of fi+?e databases
structurally identical to the one
described above, but with
different numbers of records.
A l l experiments were performed on an I B M 370 M o d ~ l 158 under the VM/CflS
operating system.
the timing
facilities of
Measurements were
V M , which
report the
made using cumulative
virtual C P U time and number of 110's performed by the user's virtual
machine.
These
measurements
"overhead1*costs of VM in providing
do
about
2500
etc.1
instructions
Under
per
110.
the
the user with a virtual
machine (e.g., dispatching, virtual momory program translation,
not include
paging, channel
V M , "overhead"
These
costs
includes are
not
PAGE 3 4
included in our experiment because they are highly dependent on
the operating
( e - g . , VMICMS,
system
or
MVSITSO,
data communication
CICS,
etc.)
subsystem
under
which
the
application is executing.
All
measurements
were
made
with
R
System
multi-user mode (locking subsystem enabled).
running
in
Experience has
shown that System R uses the same number of 110's to perform equivalent functions but uses slightly
in single-user
and multi-user
less C P U time in single-user
modes,
mode due to
the disabling of the locking subsystem.
Q U E R Y MEASUREMENTS
The
three
experimental
queries,
and
performance, are summarized in Tables 1 ,
their
measured
2 , and 3.
In order
to make these measurements reproducible, it was necessary to ensure that no query could in the system has
a
buffers from previous activities.
buffer
space
recently fetched
4096-byte
in
virtual
from the
adjustable in size; 20
benefit from data which remained
memory
database.
containing
The
Therefore,
data
buffer space
in our experiment it
pages. 1
(System R
is
was configured at
before
measuring
each
query, another query was run which fetched at least 50 pages of
data,
measured.
none
of
which pertained
to
the
query
to
be
+
+------------------------------------------------------------
I I English Form: I Find the supplier number and price of all quotes I for part number 0 1 0 0 0 2 in quantities of 1000.
I
I I I +
+------------------------------------------------------------
I I SQL Form: SELECT SUPPNO, PRICE FROM QUOTES I I W H E R E PARTNO = ' 0 1 0 0 0 2 ' AND MINQ < = 1 0 0 0 AND MAXQ > = 1 0 0 0 ; I
1
I 1 I I
+------------------------------------------------------------
+
I I Access path chosen by optimizer: I PARTNO index on QUOTES table.
1 I I
+ I I
+------------------------------------------------------------
i I Cardinality of answer set:
3
I
I
+
+-----------------------------+---------------+-------------
CPU time I (msec on 1 5 8 )
I 1
Number of I / O 1 s
I
I
I Parsing
I
I I
0
I
I
I
Operation
+-----------------------------+---------------+--------------
TABLE
1:
39.5
QUERY
1
I I
+ I I
PAGE 36
+-------------------------------------------------------------+ I I English Form: I Find the order number, part number, description, I date and quantity for all parts ordered from I supplier no. 797 during 1975.
1
I I
I I + I I
+-------------------------------------------------------------
I I SQL Form: 1 I I 1 I
SELECT ORDERNO, ORDERS.PARTN0, DESCRIP, DATE, QTY FROM ORDERS, PARTS WHERE ORDERS.PARTN0 = PARTS.PARTN0 AND DATE BETWEEN '750000' AND '751231' AND SUPPNO = '797';
+-------------------------------------------------------------+
I
I I 1 I
1 1
I Access path chosen by optimizer:
I
I I
I
I
Access ORDERS by SUPPNO index. For each qualifying ORDERS record, access corresponding PARTS record by index on PARTNO.
1 I
+-------------------------------------------------------------+
I
I
I Cardinality of answer set:
I I
7
I
+
+------------------------------+---------------+--------------
+
+------------------------------+---------------
I
I
I Fetch answer set
I
1
148.2
(21.2 msec per answer record11
I I
I
I
I I I I
I
I
55
+------------------------------+---------------+
I
I
I Close cursor
I
15.0
I
I
+ I
+------------------------------+---------------+--------------
I Total (including all of
I
I above plus formatting
I 1
I answer set for display.)
I 630.9
+------------------------------+---------------+--------------+
TABLE 2:
QUERY 2
I
I
53
I I
P A G E 37
T A B L E 3:
QUERY 3
PAGE 38 A
striking fact
is
the
about the measurements shown
small
amount
generation" step.
This
choice of
quite
small
(zero
required to parse
involved
in
the
is the processing step
access
translating the chosen
time
"code
which could
which interprets a query immediately
be avoided in a system after
of
in the tables
path.
We
see
that
the cost
access path into executable
I/O's,
and 14%-37%
of
the
and optimize the query).
of
code is CPU
tine
The payoff for
this small investment is a small, efficient machine-language routine to process the query. shorter
path
length
and
This generated routine has a smaller
working
set
than
a
general-purpose SQL interpreter, because it is tailored to a specific query. code
Therefore, it quickly
generation
database.
as
it fetches
Thus we see that
System R has
pays off the cost of
answer
records
from
the
the SQL compilation approach of
significant benefits for ad-hoc
query as well
as for a "canned transaction" environment.
TRANSACTION MEASUREMENTS
The second part of our
experiment involved preparing a PL/I
program
SQL
with imbedded
statements
types of "canned transactions" The program, named
to implement
three
against the sample database.
O R D E R S , is included in
A p ~ e n d i xA .
The
ORDERS program differs from traditional database transaction programs in
that its terminal
interactions are
handled b y
39
PAGE
PL/I 110 rather than by a data communication subsystem. program reads from a terminal
The
a transaction-type code, then
performs one of the following types of transactions:
Transaction Type N: (New order)
A new order has been placed. the new order and
update
Enter
in the ORDEXS table, the
Q00 field
of the
appropriate PARTS record.
Transaction Type A : (Arrival 1
An
existing
order
of
parts
has
arrived. Access the ORDERS table to find
the part number
in the order,
and
and quantity
update
the ap-
propriate PARTS record accordingly. Then delete the appropriate
ORDTRS
record from the database.
Transaction Type Q: (Query)
Given
a part number,
description,
look up
the
quantity on hand, and
quantity o n order of the given part and display them on a terminal.
These three might
be
therefore efficiency.
transactions represent expected
to
included in An
occur a
simple processes
repeatedly,
precompiled
which
program for
actual inventory control
probably include a
and
which are
maximum
application would
much larger collection of
these "canned
PAGE 40
transactions."
involved the CPU time
The first measurement by System
R during
precompilation of
During this process,
containing statement. selection,
a
parsing,
and
precompilation.
program.
the program are
an access module is constructed
machine-language All
the ORDERS
all SQL statements in
replaced by PL/I calls, and
and I/Ofs used
"section"
for
name-binding,
authorization
checking
each
access occurs
S3L
path during
The cost o f the precompilation step for the
ORDERS program, performed in multi-user mode on System R, is summarized in Table 4.
By
way
of comparison,
Table
gives
5
compilation of the ORDERS program, the System PL/I
R precompiler.
Optimizing
the cost
PL/I
after it was modified by
The compiler
Compiler,
of
Release
used was
3.0,
with
the I B M listing
suppressed and warning messages suppressed.
After
precompiling
the
ORDERS program,
we
examined
the
resulting access module to determine its size and the access paths which had been selected. nine SQL statements.
Therefore,
nine sections and a Section
The ORDERS program contains its access module contains
Location Table ( S L T ) .
The size
of the access module is summarized in Table 6 .
Next, measurements were
made of the CPU time
and number of
P A G E 41
I CPU t i m e I I(sec o n 158) I
Number of I / O 1 s
+----------------------------+-------------+------------
I ILoad System R precompiler land begin p r o c e s s i n g .
I I I
2.98
f
I I
4 53
I
I
I s t a t e m e n t s into "sections1' I I of an a c c e s s module. I
1.65
I
I I I
39
I
I I S t o r e t h e resulting a c c e s s I I module in t h e d a t a b a s e . I
.32
I I I
88
I
I
I
I
I
I
1
Miscellaneous.
I
1.47
I
+----------------------------+-------------+----.--------
I
I I
I
I I
TOTAL
8.21
+----------------------------+-------------+------------
I
154
I I 1 I
'7 8 9
CPU t i m e ( s e c . on 158)
TABLE
COST
I
1
Number of I/O's
I I
+
+-----------------+------------+
I
I 1 I
+ I
T A B L E 4: C O S T OF P R E C O M P I L A T I O N
I
I
+
+----------------------------+-------------+------------
I
I
+
+----------------------------+-------------+----.--------
I
I I +
+----------------------------+-------------+------------
I T r a n s l a t e all S Q L
I I
I
I
COMPILATION
PAGE 42
+
+---------+---------------------+---------+-------------------
I I I Section I I No. I
I Size of 1 I Section I I (bytes) I
SQL Statement
I I
Access Path Selected
I +
+---------+---------------------+---------+-------------------
I
I
(SLT)
I
I I 1 I I
1 2
3
I I
4
I I i I
5
6
I I I I I I
I
7 8
9
I
I I I
1 I I I I
I
I
I
I BEGIN TRANSACTION I I INSERT INTO ORDERS I I UPDATE PARTS
I I
70
I
751
I I 1
1321
I Index on PARTNO I
I I
1319
I Index on ORDEBNO I I Established I cursor position I I Index on PARTNO I I Index on PARTNO
I
I
1
I
I
I I
I I I 1 1 I
1 1 1 DELETE ORDERS WHERE CURRENT OF C1 I
SELECT FROM ORDERS
254
I
648
I 1 I
1449
I I SELECT FROM PARTS
1
1423
I
I 1 I
UPDATE PARTS
1 END TRANSACTION I I RESTORE TRANSACTION I I I
Total size of access module:
I I
1
I 68
1 I
72
I
on System R in multi-user
of a user session is executed additional cost
I 7375 bytes
into virtual memory, and this For this part sensitivity of
the
database.
When the first transaction
(independent of its type), an
is incurred for
loading the
transaction execution times
Therefore,
access module
cost was measured separately.
of the experiment, we desired
we
loaded
five
I I I I I I I
I I
of the three transaction types
mode.
I
I
TABLE 6: CONTENTS OF ACCESS MODULE
I/O1s used in executing each
I
to measure the to the
size of
databases
of
PAGE 4 3
and ran 100
different sizes, each
database.
(Of
transactions of each
course,
the
ORDERS
separately precompiled in each database.) structurally identical to the one difference being the
type on
program
was
Each database was
described above, the only
total number of records
of each type.
The sizes of the five databases are summarized in Table 7.
For each database, a "script" part numbers over
all
was created consisting of 300
randomly selected with a
the part
numbers
in
uniform distribution
the database.
script, 100 transactions
o f each type were
300 random
(Type N
part numbers
part; Type A = query on
part.)
were mixed together
random
three types
in random order.
Since
are physically clustered
sequence
uncorrelated with precaution
The
of
part
numbers
eliminates
transaction accessing
any
of transactions the records in
the
the physical placement of spurious
number, the script
records.
effects
pages which were
a given
part; Type 4 =
by part in
the
executed on the
order for
arrival of order for a given
a given
the database
= new
Using
left in
due
is This to
a
the system
buffers by previous transactions.
For each transaction type, in each database, the average CPU time and average n u m b e r of I / O 1 s
were measured over the 100
executions
The
of the
includes time spent
transaction.
CPU time
in the PL/I portion
as well as time spent in
System R .
measurement
of the transaction
However, the I / O counts
I Number of I Number of 1 Number of I Total size I I QUOTES I of database I I ORDERS I PARTS I records I records I records I (not incl. I I indexes) I I I I IDatabasel I No. 1 I I 1
5,000
I I I
15,000
I I
45,000
I
I 1 I
I 1.86 M 9
I
+
+--------+-----------+-----------+-----------+-------------
IDatabasel INo.2 1
I
10,000
I
I I
30,000
I
I
I 1
90,000
1
I 3.72 M B
I
1
include only terminal.)
I
F I V E E X P E R I M E N T A L DATABASES
database accesses The
summarized in
I
+
+--------+-----------+-----------+-----------+-------------
T A B L E 7:
I
costs of Table 8,
(not interactions
the three and in
since all the transactions access
with the
transaction types
F i g . 9.
We observe
are that,
the database directly via
an index, they are relatively insensitive to the size of the database ( a tenfold increase in 21% increase
in CPU time
and a
database size causes only a Y2%
increase in
number of
I / O 1 s .1
In addition to the transaction costs other costs were
measured.
listed in T a b l e 8, txo
The cost of
loading the access
PAGE 45
1
A v e r a g e n u m b e r o f 110's
I +--------+--------+--------+--------+--------
1
1 . 8 6 MBI
+
3.72
MBI 7 . 4 4 M B 1 1 4 . 8 8 M B 1 1 8 . 6 0 R B I ldatabaseldatabaseldatabaseldatabase~database~
+
+-------------+--------+--------+--------+--------+--------
I I I
I TypeN
1
I
I 10.0
I
1
10.3
I
1
I 10.8
I
1
I 11.5
1
13.7
I
I
+
+-------------+--------+--------+--------+------.--+--------
I I
I Type A
I
I
9.3
I
I 1
9.6
I
I 1
9.8
I
I 1
11.4
1
1 1
13.7
I
I
Type
Q
I I I
I 3.4
1 I
3.8
I 1
3.6
I
1 1 I
I 5.0
+-------------+--------+--------+--------+--------+--------
TABLE 8 :
E X E C U T I O N C O S T OF T R A N S A C T I O K S
1 I
I 1 I
+
+-------------+--------+--------+--------+--------+--------
I I
I 1 1
4.8
I 1 I
+
PAGE 46
A - Type
ZH
*/ 4
N - Type
0-0
/.
Q - Type
4
0
Database Size (megabytes)
N - Type 0-
0-•
0.-•
0
2
4
6 8 10 12 14 16 Database Size (megabytes)
Figure 9. Execution Cost of Transactions.
18
20
PAGE 47
module, which occurs at the
first transaction of a session, and 1 1 I/O's, independent of the
is 1 6 2 . 7 msec. of CPU time size of the
takes
a
database. "checkpoint"
after
transactions
(frequency
parameter.)
Checkpoints
bookkeeping, and
activity
since
database,
of
approximately checkpoint
on the size of
the
last
the cost
of a
is
involve certain
are not visible to
checkpoint depends
R automatically
In addition, System
every an
7000
adjustable
internal
users.
system
The cost
the database and
checkpoint. checkpoint
Fcr
our
of a on the
18.6
YlB
is approximately
430
msec. of CPU time and 49 I / O 1 s , representing an average cost per transaction of about - 0 6 msec. and - 0 0 7 110's.
We
will now
observed 110
As
discuss
a simple
to
the
account for
counts for the various transaction types.
described in
[ I ] ,
an index consists
like the one in Fig. 10. records,
model
an index
of a tree-structure
For small tables of a few thousand
consists only
of
a "root
page" and
a
collection of "leaf pages" which point directly to the "data pages"
containing
indexes may page and
actual data.
contain "intermediate
the leaf pages.
index page is than three
the
Since
l a r ~ e r tables,
pages" between
the root
the typical fan-out
200, it is unusual
levels (root level
For
to find an index
of an of more
+ intermediate k v e l
level can index 2 0 0 x 200 x 200 = 8,000,000 records.)
+
leaf
P A G E 48
When a c c e s s i n g
data via an
the root,
and d a t a p a g e s
pages,
leaf,
if
accessed system
any)
must be
So
access v i a an index
When a n been
(and
we expect
and
But t h e
is
r o o t page
t o be
a b o u t two
index
found i n
the
fetches
for
page
i n most c a s e s .
from
intermediate,
example d a t a b a s e ,
intermediate-level
is l i k e l y
index must be updated
deleted
i n our
accessed.
that it
so often buffer.
index
the
(e.g.,
table),
leaf)
because a
only
the
record has
index
p a g e s n e e d be a c c e s s e d .
(root,
Again,
it
is l i k e l y t h a t t h e r o o t page w i l l be found i n t h e b u f f e r .
When a p a g e c o s t of be
is f e t c h e d i n t o
t h e f e t c h may b e o n e
if
incurred
"dirty"
t h e system buffer,
the
o r two
page c u r r e n t l y
secondary fetched
Such a
Two 1 1 0 ' s
in the
buffer-frame
d i r t y page must
it
storage before page.
modified,
it
c a n be
the
newly-fetched
w i l l
not
be
but w i l l
be w r i t t e n
replaced
If
secondary storage,
would
is
in
out to
by a
is
page
immediately
written
newly
in
turn
out
to
general remain i n the buffer
its b u f f e r frame is needed
buffer
I/O1s.
( i . e . , i t h a s been m o d i f i e d s i n c e b e i n g f e t c h e d from
the database.)
until
t h e measured
f r a m e s a r e m a n a g e d b y RSS
f o r some l a t e r on a " l e a s t
fetch.
The
r e c e n t l y used"
basis.
An a d d i t i o n a l s o u r c e o f RSS
organizes
pages
I/O7s
into
must be c o n s i d e r e d also.
groups
of
128
pages,
The
called
Intermediate Pages
Leaf Pages
Data Pages
FIG.
10
STRUCTURE OF AN INDEX
"blocks".
Each
block is described
512 bytes
which serves
pages in the block. in virtual memory, to hold
32 block
block which record
page location
System R
which was configured in records.
When
fetched into
o r two 110's
record" of
table for
the
has a separate "block buffer"
has not previously
must be
require one
as a
by a "block
since
a page
our experiments is accessed
been referenced, the block
currently hold a "dirty" block record.)
the block
buffer (which
the block buffer
in a
may
frame m a y
However, in a small
PAGE 50
database,
most block
records will
be found
in the
block
buffer .
In summary, a newly-referenced page (either an index page or cause at least one 110, and
a data page) will to four I/O1s if
its buffer page frame is dirty,
in a newly-referenced also dirty.
block, and the block
These phenomena cause a
in the observed number of I/O's
We will
may cause up
now suggest a
and it is
buffer frame is
considerable variance
for individual transactions.
simplified model to
roughly account
for the observed I/O counts of the various transactions. assume that that
all index root pages
indexes
are
only
two
are found in
levels
deep,
referenced blocks are already in the buffer. that
each
data page
transaction
is
conditions,
not the
or
index
found
in
I/O1s
leaf the
incurred
the buffer,
and
that
by
all
We also assume
page accossed buffer.
We
by
a
Under
these
the
three
transaction-types would be as follows:
Transaction Type N: 1.
Fetch data page to insert new ORDERS record.
2.
Fetch
index
page
to
insert
new
key
in
to
insert
new
key
in
to
insert
new
key
in
ORDERS(ORDERN0) index. 3.
Fetch
index
page
ORDERS(PARTN0) index.
4.
Fetch
index
page
PAGE 51 ORDERS(SUPPN0)
index.
5.
Fetch index page to access PARTS via PARTNO index.
6.
Fetch data page to update Q00 in PARTS record.
7.
Write a log record to end the transaction.
Transaction Type A :
1.
Fetch
index
page
to access
ORDERS
by
ORDERNO
index.
2.
Fetch data page from ORDERS to read QT-f and delete record.
ORDERNO index page can be updated without
additional I/O since it has already been fetched.
3.
Fetch index page to delete key from ORDERS(PABTH0) index.
4.
Fetch index page to delete key from ORDERS(SUPPN0) index.
5.
Fetch index page to access PARTS via PARTNO index.
6.
Fetch
data
page
to update
400,
QOH
in
PARTS
record.
7.
Write a log record to end the transaction.
Transaction Type Q: 1.
Fetch index page to access PARTS via P1P.Tl[O index.
2.
Fetch data page from PARTS. (Note: no
log record is written
at the end
of a
Q-type transaction, since the transaction does n o t update the database.
PAGE 52
The
discussion above
A-type transaction
suggests
and 2
7 I/O's
110's for
for
an N-type
a Q-type
or
transaction.
However, we have not yet accounted for the I/O1s involved in writing out We
observe
three
to disk the that, of
the 14
transactions,
transaction
contents of modified
10
and must
of
the modified
pages are
The
by
transaction
the
re-used.
expected average
number
written out
by
to
the disk.
page frames
Therefore,
each
the current content of the buffer
non-log page fetch is therefore factor
be
modified
the
10/14=0.714 probability of causing
an extra 1/0 to write out frame.
are
fetched b y
occur when the buffer
non-log page fetch has a
page
them
therefore
These "write" operations holding
non-log pages
page frames.
1.714.
of non-log
as described
transaction types N and A ,
number
above, and
of
I/O's
per
I f we multiply this
page
fetches
add the
in
log I / O
each for
we arrive at the predicted nunher
of 110's summarized in Table 9.
PAGE
TABLE 9 :
A
1 1 0 COUNTS PREDICTED B Y S I M P L I F I E D M O D E L
Table 9
comparison between
measured
I/O
counts
for the
s l i g h t l y smaller t h a n databases, the
53
and three
predicted
Fig.
9
shows t h a t
the
transaction types
are
i n the case
of
and s l i g h t l y l a r g e r t h a n p r e d i c t e d
larger databases.
the smaller
i n t h e c a s e of
T h e s e e f f e c t s c a n b e e x p l a i n e d by t h e
following observations:
1.
Occasionally a referenced w i l l be
found i n
d a t a page
the system
or
buffer,
index
leaf
page
particularly
in
small databases.
2 '.
On t h e o t h e r h a n d ,
occasionally a block-record
r o o t page w i l l n o t be
found
in the buffer,
or
index
particularly
in large databases.
3.
In the three
largest database,
the
l e v e l s r a t h e r t h a n two.
indexes
on O R D E R S
har/e
PAGE 54 AND C O N C L U S I O N S -
We
have
described
the architecture
supports a flexible spectrum of
of
R,
System
which
binding times, ranging from
precompilation of "canned transactions" to on-line execution of ad-hoc queries.
The advantages of this
approach may be
summarized as follows:
1.
For repetitive
transactions, all the work
of parsing,
name binding, and access path selection is done once at precompilation time and need not be repeated.
2.
Ad-hoc
queries
are
machine-language
compiled on-line
access
module
into
which
a
small,
executes
more
efficiently than an interpreter.
3.
Users
are given
ad-hoc queries
a single as well
language, S Q L ,
as in
writing P L / I
for use
in
and C O B O L
transaction programs.
4.
The
SQL parser,
machine between
access path
language code query
selection routines,
generator
processing
are
and
used in
and
common
precompilation
of
transaction programs.
5.
When an index used by a transaction program is dropped, a new
access path
is automatically
selected for
the
PAGE 55
transaction without user intervention.
6.
The multi-user be
running
locking subsystem allows some transaction
precompiling
programs,
new programs,
users to
others
and others
to
to be
be
running
ad-hoc queries and updates, all on the same database at the same time.
We have also described an example
database and shown how it
might be used both by ad-hoc
query users and by transaction
programs.
performance measurements
made on
Some preliminary the database using an
VM/370 operating
system.
The
I B M 370 Model 158 results of
were
under the
our measurements
support the following conclusions:
1.
Ad-hoc queries, including joins of more than one table, can be parsed, optimized, and executed in substantially less than one
virtual second i f their
ansuer sets are
small and the appropriate indexes are available.
2.
The
process
of generating
execute a query adds a than one-third)
machine-language
code
to
small increment (typically less
to the cost
of access
path selection
for the query.
3.
The access modules resulting from compilation of simple transactions contain about 1 0 0 0 - 1 5 0 0
bytes of code and
PAGE 5 6
control blocks per SQL statement.
4.
For simple
transactions which are compiled
and which are supported
in advance
by appropriate indexes, System
R can process several transactions
per second on a 370
Model 158.
5.
When a query
or transaction is supported
its performance
5y an index,
is relatively insensitive to
the size
of the database ( e - g . , in our transaction experiment, a tenfold
increase in
database size
caused an
average
increase of only 21% in CPU time and 42% in 110 count.)
ACKNOWLEDGEMENT
The authors of
this paper owe a
employees, visiting
students and
great debt to all
the IBN
faculty members,
and I B M
postdoctoral fellows who made important contributions to the architecture and implementation of System R .
PAGE 5 7
CITED
GENERAL REFERENCES
[ I ]
M. M. Astrahan, M. W . Blasgen, D. D. Chamberlin, K . P . Eswaran, J. N. Gray, P. P. Griffiths, W. F . King, R . A . Lorie, P. R. McJones, J. W . Mehl, G . R. Putzolu, I . L. "System I?: A Traiger, B. W. Wade, and V. Watson. Relational Approach to Database Management." ACM T o . 2 , June Transactions on Database Systems, Vol. 1 1976 (pp. 97-137.)
[21
"Relational Database Management D. D. Chamberlin. Systems." Computins Surveys, Vol. 8 , No. 1 , March 1976 (pp. 43-66.)
[31
D. D. Chamberlin, M. M. Astrahan,
K . P. Eswaran, P. P. Griffiths, Raymond A. Lorie, J. W. Mehl, P . Reisner, and B. W. Wade. "SEQUEL 2: A Unified Approach to Data Definition, Manipulation, and Control." I B M Journal of Research and Development, Vol. 20, No. 6 , Nov. 1976.
[41
J. N. Gray, R . A. Lorie, G. I?. Putzolu, and I. L. Traiger. "Granularity of Locks and Degrees of Consistency in a Shared Database." Research Report RJ1654, IBM Research Laboratory, San Jose, C A . , 1975.
[sl
R. A. Lorie and J . F. Nilsson. "An Access Specification Language for a Relational Database IBX Research System." Research Report RJ22 18, Laboratory, San Jose, C A . , April 1978.
161
R. A . Lorie and B. W. Wade. "The C o m p i l a t i ~ nof a Very High Level Language." Research Report ilJ.2008, IBM Research Laboratory, San Jose, CA., May 1977.
[71
W.
C. McGee. "The Information Management System IMS/VS." IBM Systems Journal, '101. 16, 'lo. 2 , 1P77 ( p p . 84-168.)
81
D . McLeod and M. Meldman. 1 ~ ~A1 " " Generalized Minicomputer Relational Database Management System." Proc. AFIPS 1975 National Comwuter Conf+rencs, p p . 397-402.
[91
J . Mylopoulos, S. Schuster, and D . Tsichritzis. fr A Multi-level Relational System." Proc. .tFIPS 1 9 7 5 National Computer Conference, pp. 403-408.
[
D. [lo1 P. Griffiths Selinger, M. M. Astrahan, D. Chamberlin, R . A . Lorie, and T . G. Price. Proceedin7s of 1979 - ACM
SIGMOD
Conference.
PAGE 58
[
1 1 1 M . S t o n e b r a k e r , E.
Wong, P. K r e p s , and G . Held. "The ACM Transactions D e s i g n and I m p l e m e n t a t i o n of INGRES." on Database S y s t e m s , Vol. 1 , No. 3, S e p t . 1976 ( p p . 189-222.)
[I21 S.
J. P . T o d d . "The P e t e r l e e R e l a t i o n a l T e s t Vehicle--A System Overview." I B M S y s t e m s J o u r n a l , V o l . 15, N o . 4 , 1976.
[ I 3 1 M . M . Zloof. ISM Systems 324-343.)
" Q u e r y By Example: A Database Lansuaue." J o u r n a l , Vol. 16, No. 4, 1977 ( p p . d
PAGE 59 APPENDIX A The following is a pseudo-code form of the PL/I program which was used in the performance measurements described in this paper. ORDERS: PROCEDURE; /***XXXXXX**XX**XXX***XX**XX******X**XX**X****Xx**X*X**x***X
* x x Y:
x
X
INTERACTIVE PROCESSING OF 3 TRANSACTION TYPES: 'N' = NEW ORDER 'A' = ARRIVAL OF ORDER '(4' = QUERY SUPPLY OF A GIVEN PART
X
:X
Y
x x 'Y
XXXXXXXXXfXXXXXXXXXXXXXYXXXXXXXXYXXYXXXXXXXXXXX:KXXXX*X~X;YXX/
( A declaration of the System R return code structure, containing SYR-CODE and SYR-MESSAGE, must be copied into the program from a macro library.) DECLARE DECLARE DECLARE DECLAP,E DECLARE DECLAP.E DECLARE DECLARE DECLARE
PARTNO DESCRIP QOH QOO ORDERNO SUPPNO QTY DATE TRANTYPE
CHARACTER(6); CHARACTER(50) VARYING; BIN FIXED(31); BIN FIXED(31); CHARACTER(6); CHARACTER(3); BIN FIXED( 31 1 ; CHARACTER(6); CHARACTER(1);
GETNEXTTRANS: Read TRANTYPE from terminal: N 1 A I Q or Z to quit; IF TRANTYPE = 'N' THEN Read ORDERNO, PARTNO, SUPPNO, DATE, QTY from terminal; ELSE IF TRANTYPE = ' A 1 THEN Read ORDERNO from terminal; ELSE IF TRANTYPE = 'Q' THEN Read PARTNO from terminal; ELSE IF TRANTYPE='Z1 THEN STOP; ELSE DO; Write 'INVALID TRANSACTION TYPE' on termi~lal; GO TO GETNEXTTRANS; END;
PAGE 6 0
SBEGIN TRANSACTION; IF SYR-CODE-=O THEN CALL TROUBLEC'BEGIN TBANS'); IF TRANTYPE='N' THEN DO; / * NEW ORDER * / $INSERT INTO O R D E R S : < $ O R D E R N O , $ P A R T N O f S ~ ~ ~ ~ N 0 , B D A T E . ~ Q T Y > ; IF SYR-CODE-=O THEN CALL TROUBLE('INSERT1); BUPDATE PARTS SET QOO=QOO+BQTY WHERE PARTNO=BPARTNO; IF SYR-CODE-=O THEN CALL TROUBLE('UPDATE'); END ; ELSE IF TRANTYPE='A' THEN DO; / * ARRIVAL */ BLET C1 BE SELECT PARTN0,QTY INTO SPARTN0,SQTY FROM ORDERS WHERE ORDERNO=$ORDERNO; BOPEN C 1 ; IF SYR-CODE-=O THEN CALL TROUBLE('0PEN CURSOR'); BFETCH C1; IF SYR-CODE-=O THEN CALL TP.OUBLE('FETCH'); $DELETE ORDERS WHERE CURRENT OF C 1 ; IF SYR-CODE-=O THEN CALL TROUBLE('DELETE'); $CLOSE C1; IF SYR-CODE-=O THEN CALL TROUBLE('CLOSEf 1 ; SUPDATE PARTS SET QOH=QOH+BQTY, Q00=Q00-BQTY WHERE PARTNO=$PARTNO; IF SYR-CODE-=O THEN CALL TROUBLE('UPDATE1); END; ELSE IF TRANTYPE='Q' THEN DO; I * QUERY */ $SELECT DESCRIP,QOH,QOO INTO BDESCRIP,SqOH,BQOO FROM PARTS WHERE PARTNO=BPARTNO; IF SYR-CODE = 0 THEN Write DESCRIP, QOH, QOO o n terminal; ELSE IF SYR-CODE = 100 THEN Write 'THERE IS NO SUCH PART' o n terminal; ELSE CALL TROUBLE ('SELECT'); END; SEND TRANSACTION; IF SYR-CODE-=O THEN CALL TROUBLEC'END TRANS'); GO TO GETNEXTTRANS;
P A G E 61
TROUBLE: PROCEDURE(STMT); DECLARE STMT CHARACTER(I2) VARYING Write
'TROUBLE ENCOUNTERED'
;
on terminal;
W r i t e T R A N T Y P E , S T M T , O R D E R N O , P A R T N O , SYR-CODE, SYR-MESSAGE o n t e r m i n a l ; %RESTORE TRANSACTION; GO TO GETNEXTTRANS; END TROUBLE; END ORDERS;
