 |
|
A Model for SQL
Oracle Tips by Burleson Consulting |
The first model of SQL can be thought of as
having three categories of function: Define, Manipulate, and
Authorize.
- Define refers to the data definition
language (DDL) that performs object create, drop, and alter
functions.
- Manipulate refers to the data
manipulation language (DML) that performs select, insert, update,
and delete functions.
- Authorize refers to the control that
performs grant and revoke functions.
Within the Manipulate
functions, we see three dimensions to SQL, select,
project, and join. These three simple metrics define the
whole functionality of SQL.
Select Operation
A select operation reduces the length
of a table by filtering out unwanted rows. By specifying conditions
in the where clause, the user can filter unwanted rows out of
the result set, as shown in Figure 1-2. In sum, the select
operation reduces the results vertically.
Figure 2: A select filter reduces the number
of returned rows
Project Operation
Just as the select operation reduces
the number of rows, the project operation reduces the number
of columns. The column names specified in the SQL select determine
those columns that are displayed, as shown in Figure 1-3. In sum,
the project operation reduces the size of the result set
horizontally.
Figure 3: The project operation removes
unwanted columns
Join Operation
A join operation such as is shown in
Figure 1-4 is used to relate two or more independent tables that
share a common column. In a join, two or more independent tables are
merged according to a common column value.
Figure 4: A join operation
Within this simple framework we see that a query in
SQL is a “state-space” type of query. That is, the person who writes
the query does not need to be concerned with the navigation path to
the data. The SQL optimizer handles the navigation path to the data
tables internally.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
