SQL Bible [Electronic resources] نسخه متنی

Other SQL99 and Implementation-Specific Objects

By now you
probably realize that SQL standards exist more in theory than in real life.
SQL99 defines many objects that are implemented in none of our three major
databases. In its turn, every vendor has its own unique set of database object
types not used in other RDBMS implementations and/or not described in SQL99
standards.

Domains (SQL99)

A domain is a
database object that can be used as an alternative to a data type when defining
table columns. In addition to a data type, it can optionally specify a default
value, a collation, and a set of constraints. The syntax is

CREATE DOMAIN
<domain_name> [AS] <datatype> [DEFAULT <default_value>]
[<constraint_definition>,...] [COLLATE
<collation_name>]

As we mentioned before, domains are not
implemented by Oracle, DB2, or MS SQL Server, though they all have some kind of
functionality to achieve similar goals. (For example,
CREATE
DISTINCT
TYPE in DB2,
CREATE
RULE in MS SQL Server, and so on. See
examples in
Chapter
3.)

Tablespaces and filegroups

We already mentioned tablespaces while
discussing the process of creating tables and indexes. The concept of
tablespace is not particularly intuitive — it's a logical
structure for physical storage. In other words, tablespace
is something you refer in your SQL code when you want to specify a physical
location of a database object. Tablespace can consist of one or more
datafiles — special system files where table, index, or
any other physical data is stored in binary form. One datafile can only belong
to one tablespace.
Figure
4-8 shows the relationship between tablespace and datafile.

Figure 4-8: Relationship between
tablespace and datafile (one-to-many).

When users create tables in, say, the
DATA01 tablespace, they have no control
over which datafile this table's rows will physically reside:
data01,
data02,
data03, or spread across all three of
them. RDBMS will manage that by itself. (Actually, in most cases, users do not
need to specify even the tablespace; objects are created in their default
tablespaces, i.e., tablespaces assigned to them by the database
administrator.)

Oracle
9i

Tablespaces in Oracle are created
using the
CREATE
TABLESPACE command. The simplified
syntax is

CREATE TABLESPACE
<tablespace_name> DATAFILE <file_path_and_name> SIZE
<size>[K|M] [REUSE] [<default_storage_clause>]

SIZE is
an integer concatenated with letter
K (for kilobytes) or
M (for megabytes). The default storage
specifies the default physical characteristics for objects created in this
particular tablespace.

The following example creates
tablespace
DATA01 with one datafile of size one
megabyte (assuming standard Oracle9i installation on
Windows):

CREATE TABLESPACE DATA01
DATAFILE 'C:\oracle\ora92\oradata\acme\data01.dbf' SIZE 1M;

Oracle creates file
data01.dbf in directory
C:\oracle\ora92\oradata\acme formatted
in Oracle blocks. You will get an error if the file already exists unless the
REUSE clause is specified. Now you can
try the example from the beginning of this chapter that creates table
PHONE in tablespace
DATA01.

DB2 UDB 8.1

The
CREATE
TABLESPACE statement in DB2 is also
fairly complex and is normally used by DBA only. The basic syntax is

CREATE
[REGULAR | LONG] TABLESPACE <tablespace_name> MANAGED BY [SYSTEM |
DATABASE] USING
([FILE | DEVICE] <file_or_device_name> [<size> K|M|G], ...)
[<storage_clause>]

The
REGULAR clause is the default; you
would only create
LONG tablespace to store
LOB objects.

The following code creates regular
system-managed tablespace
USERDATA01 in directory
C:\DB2\DATA01 (assuming the default
installation):

CREATE TABLESPACE USERDATA01
MANAGED BY SYSTEM USING ('C:\DB2\DATA01')

MS SQL Server 2000

There is no such thing as tablespace
in MS SQL Server, but you can create filegroups and add files to them, which is
exactly the same concept as using tablespaces in Oracle and DB2. This is simply
different terminology and slightly different syntax: you actually use the
ALTER
DATABASE statement to add filegroups
and/or files to a database.

Here is the syntax:

ALTER DATABASE ADD FILEGROUP
<filegroup_name> 

ALTER DATABASE ADD FILE (
NAME = <logical_file_name> [, FILENAME = <os_file_name>] [, SIZE =
<size> KB|MB|GB|TG] [, <other_physical_parameters>]), ... [ TO
FILEGROUP <filegroup_name> ]

As you can see, the idea is not much
different from what you already learned: MS SQL Server
filegroup is a logical structure, just like tablespace in
Oracle and DB2, and a file is an element of physical storage.

Few minor variations to mention here:
you can create logical names that are different from their physical OS names;
size can be indicated in terabytes in addition to kilobytes, megabytes, and
gigabytes, and so on. The following code creates filegroup
DATA01 and then adds a file with
logical name
DATA0101 to it of size
1M:

ALTER
DATABASE acme ADD FILEGROUP DATA01 GO ALTER DATABASE acme ADD FILE ( NAME =
DATA0101, FILENAME ='C:\Program Files\Microsoft SQL
Server\MSSQL\Data\data0101.ndf', SIZE = 1MB ) TO FILEGROUP
DATA01

Sequences

A sequence is a
database object with functionality similar to that of identity (discussed
previously in this chapter). The main difference is that identity is tied to a
table column, and sequence is totally independent from other database objects;
thus, multiple users can generate unique numeric values from a sequence and use
them for different purposes. The most typical use is to generate primary key
values for a table (or for multiple tables).

Figure 4-9: Using
sequence-generated numbers to populate primary keys in multiple
tables

In general, a sequence is more flexible
than an identity column because it is an independent database object, whereas
an identity column is a part of a table definition. For example, sequences can
be much more convenient than identity columns when used in a procedural program
(C, Java, COBOL, etc.) that populates records for parent/child
tables.

Sequences in Oracle
9i

The syntax to create a sequence in
Oracle is

CREATE SEQUENCE
[<schema>.]<sequence_name> [START WITH <start_value>]
[INCREMENT BY <increment_value>] [MAXVALUE <max_value> |
NOMAXVALUE] [MINVALUE <min_value> | NOMINVALUE] [CYCLE | NOCYCLE ] [CACHE
<value> | NOCACHE] [ORDER | NOORDER]

As you probably noticed, most clauses
in the
CREATE
SEQUENCE statement are optional. If you
just want to create a sequence that starts with 1 and generates sequential
values (2, 3, 4,...) all the way until it reaches the largest possible integer
in Oracle (10²⁷),
this statement will do:

CREATE SEQUENCE
my_sequence1;

If you need something more
complicated, here is what you can do:

Creating ascending and
descending sequences

You can create a sequence that
starts with a certain value (START
WITH clause) and then each next
sequence value gets populated according to the value specified in the
INCREMENT
BY clause. To create a descending
sequence, use a negative increment. You would also have to specify the maximum
and the minimum sequence values:

CREATE SEQUENCE
my_sequence2 START WITH 500 INCREMENT BY -10 MAXVALUE 500 MINVALUE
0;

Creating cycling
sequences

In the previous example,
my_sequence2 will generate values
500, 490, 480, 470,...0. After that, you will get an error saying that your
sequence goes below the
MINVALUE and cannot be instantiated.
What you can do is to create a sequence that cycles; that is, after it reaches
its maximum (or minimum in the case with a descending sequence), it will simply
start over. In the next example sequence,
my_sequence3 will restart with 500
again after reaching its minimum value:

CREATE SEQUENCE
my_sequence3 START WITH 1000 INCREMENT BY -10 MAXVALUE 1000 MINVALUE 0
CYCLE;

It's often difficult to understand
the difference between
START
WITH and
MINVALUE (or
MAXVALUE for descending sequences)
clauses. Actually, the difference is only important for cycling sequences. For
example, you may want your sequence to start with 100 and then when it reaches
its maximum value (for example, 10,000) start over again, but this time not
with 100, but rather with 10. In this case, you specify 100 for the
START
WITH clause and 10 for the
MINVALUE clause:

CREATE SEQUENCE
my_sequence4 START WITH 100 INCREMENT BY 1 MINVALUE 10 MAXVALUE 10000
CYCLE;

Caching sequence values

By default, Oracle caches 20
consecutive sequence values in memory for faster access. You can override the
default behavior either by specifying a different number of values to cache
(10, 100, 1000, or whatever you prefer) or by using the
NOCACHE clause that guarantees you
sequential values every time you generate values using the sequence. Otherwise
the values in memory would be wiped out if, for example, the davabase has been
restarted.

Guaranteeing the order of
sequence values

You may want to guarantee that
sequence numbers are generated in order (for example, if you are using them as
timestamps) by specifying an
ORDER clause. The default is
NOORDER.

Accessing sequences in
Oracle

You can generate new sequence values
by using
SEQUENCE_NAME.NEXTVAL in your SQL
code. To access the current sequence value (i.e., the last generated sequence
number) use
SEQUENCE_NAME.CURRVAL:

SQL> SELECT
my_sequence4.NEXTVAL 2 FROM dual; NEXTVAL ---------- 102 SQL> SELECT
my_sequence4.CURRVAL 2 FROM dual; CURRVAL ---------- 102 SQL> SELECT
my_sequence4.CURRVAL 2 FROM dual; CURRVAL ---------- 102

DB2 UDB 8.1

The syntax to create a sequence in DB2
is

CREATE SEQUENCE
<sequence_name> [AS SMALLINT | INTEGER | BIGINT | DECIMAL ] [START WITH
<start_value>] [INCREMENT BY <increment_value>] [MAXVALUE
<max_value> | NOMAXVALUE] [MINVALUE <min_value> | NOMINVALUE]
[CYCLE | NOCYCLE] [CACHE <value> | NOCACHE] [ORDER |
NOORDER]

As you can see, it's almost identical
to the Oracle syntax; all examples from the
previous
section would work in DB2. There are a couple of minor
differences:

You can specify the data type you want the sequence values to
be populated of — it's mainly the precision metter. The default is
INTEGER; if you specify
DECIMAL
data
type, the scale must be zero.
Oracle always assumes the
NUMBER data type.

In DB2, you can create static sequences that would always
populate the same value (it's very difficult to imagine why you would need
something like that) either by specifying
INCREMENT
BY
0 or by using same values for
MINVALUE and
MAXVALUE. Oracle requires that
INCREMENT
TO be a positive or a negative
integer, zero is not permitted;
MAXVALUE must be greater than
MINVALUE.

Accessing sequences in
DB2

You can retrieve either current or
previous sequence value using the
NEXTVAL and
PREVVAL keywords.
NEXTVAL is not different from
NEXTVAL in Oracle;
PREVVAL is an equivalent to Oracle's
CURRVAL. The access to the sequence
values is slightly different:

db2 => SELECT NEXTVAL
FOR my_sequence4 AS NETVAL \ db2 (cont.) FROM SYSIBM.SYSDUMMY1 NEXTVAL
----------- 102 1 record(s) selected. db2 => SELECT PREVVAL FOR
my_sequence4 AS PREVVAL \ db2 (cont.) FROM SYSIBM.SYSDUMMY1 PREVVAL -----------
102 1 record(s) selected. db2 => SELECT PREVVAL FOR my_sequence4 AS PREVVAL
\ db2 (cont.) FROM SYSIBM.SYSDUMMY1 PREVVAL ----------- 102 1 record(s)
selected.

Materialized views (Oracle
9i)

A
MATERIALIZED
VIEW is yet another Oracle object that
contains data and occupies physical space. The name is a bit confusing — you
already know that a view is just a compiled query, but the materialized views
are more like tables — they have actual rows with data that could be updated
dynamically. The closest analogy is summary tables in DB2; although in addition
to data aggregation materialized views can be used in many different ways (data
warehousing, data replication, and more). In addition to that, materialized
views have fewer limitations than DB2 summary tables — most select statements
that work with the
CREATE
VIEW statement can be used to create a
materialized view.

The
CREATE
MATERIALIZED
VIEW syntax is quite complex; most
clauses are of the database administrator's concern only and require special
database security privileges most users don't have. For example, the
CREATE
MATERIALIZED
VIEW privilege allows users to create
materialized views;
QUERY
REWRITE permits users to create
materialized views used in query rewrites by optimizer, and so on.

The following examples illustrate a few
possible uses for materialized views.

Materialized view refreshed on
demand

Materialized view
VRM_ORDERLINE_SUMMARY is analogous to
the DB2
ORDERLINE_SUMMARY summary table we
talked about earlier in the chapter. It summarizes ordered and shipped product
quantities by order on demand:

CREATE MATERIALIZED VIEW
vrm_orderline_summary BUILD IMMEDIATE REFRESH FAST ON DEMAND AS ( SELECT
ordline_ordhdrid_fn, SUM(ordline_ordqty_n) AS ord_qty_summary,
SUM(ordline_shipqty_n) AS ship_qty_summary, COUNT (*) AS rowcount FROM
order_line GROUP BY ordline_ordhdrid_fn );

Materialized view refreshed
periodically with no user interaction

Materialized view
VRM_CONTACT_LIST uses the same
select_statement as
V_CONTACT_LIST view. The snapshot of
records is taken at the moment of the materialized view creation and is
refreshed daily at 6 AM:

CREATE MATERIALIZED VIEW
TEST2 REFRESH START WITH SYSDATE NEXT (TRUNC(SYSDATE + 1)) + 6/24 AS SELECT
cust_name_s, phone_phonenum_s, 'CUSTOMER' CONTACT FROM customer, phone WHERE
cust_id_n = phone_custid_fn AND phone_type_s = 'PHONE' UNION SELECT
salesman_name_s, phone_phonenum_s, 'SALESPERSON' FROM salesman, phone WHERE
salesman_id_n = phone_salesmanid_fn AND phone_type_s = 'PHONE';

This materialized view can be used in
situations when the use of a regular view is inappropriate, for example, when
underlying tables are too large and data retrieval becomes too slow; or when
tables used in the
select_statement physically reside on a
remote database and must be transferred over a network; a materialized view can
refresh data during off-load time.

GRANT CREATE [ANY]
MATERIALIZED VIEW TO user_name;

Database links (Oracle
9i)

A database link is
an object in the local database that enables you to access remote database
objects. The remote database does not have to be an Oracle database — you can
directly connect to DB2, MS SQL Server, Sybase, or any other RDBMS that
supports ODBC or OLE DB protocols. You can refer to a remote table or view in a
SQL statement by appending
@<dblink> to the table or view name
(where <dblink> is the name of a previously
created database link).

CREATE DATABASE LINK
statement

The syntax for
CREATE
DATABASE
LINK is

CREATE [SHARED] [PUBLIC]
DATABASE LINK <dblink> CONNECT TO {[CURRENT_USER | <user_name>
IDENTIFIED BY <password>] } USING
'<connect_string>';

The
SHARED keyword specifies that a single
network connection can be used by multiple users;
PUBLIC means the database link is
available to all users, not only to its creator.
CURRENT_USER can be specified if the
remote database user and password are identical to those of whoever is using
the database link; otherwise, remote user name and password have to be
specified.

The
connect_string parameter must be a
valid service name. (See Oracle's Net Services Administrator's Guide
for setting service names.) It has to be enclosed by single
quotes.

The following command creates database
link named
DBL_SALES assuming you have a valid
service name
SALES and pointing to a database where
user
SALES_MANAGER exists:

CREATE DATABASE LINK
dbl_sales CONNECT TO sales_manager IDENTIFIED BY sales123 USING
'SALES';