Lesson 16

The purpose of this module has been to provide a high-level overview of Oracle networking and to place Oracle connectivity within an historical and conceptual framework. The main points of this module include:

1. Oracle Networking has evolved to meet the changing demands of the 1990s.
2. The purpose of Net8 was to hide the complexity of the database connection. Oracle Net8 has since been replaced with Oracle Network Services since Oracle 9i.
3. The tnsnames.ora file supplies the host name, protocol, and database name to outgoing database requests.
4. Net8 facilitates configuration as a client (TNS names), a server (listener), or both.
5. Database links add USER ID and password information to the TNS service names.
6. Database links can be hidden from programmers by creating public synonyms.
7. The listener process waits for incoming requests and creates a process for the request to establish connectivity with Oracle.

In a distributed database, decisions must be made regarding which site should be used to store which portions of the database. For now, we will assume that there is no replication. That is, each 1) relation or 2) portion of a relation is stored at one site only. We use the terminology of relational databases, but similar concepts apply to other data models. We assume that we are starting with a relational database schema and must decide on how to distribute the relations over the various sites. Before we decide on how to distribute the data, we must determine the logical units of the database that are to be distributed. The simplest logical units are the relations themselves, where each whole relation is to be stored at a particular site.
In our example, we must decide on a site to store each of the relations EMPLOYEE, DEPARTMENT, PROJECT, WORKS_ON, and DEPENDENT. In many cases, however, a relation can be divided into smaller logical units for distribution. We may want to store the database information relating to each department at the computer site for that department. A technique called horizontal fragmentation can be used to partition each relation by department.

A horizontal fragment of a relation is a subset of the tuples in that relation. The tuples that belong to the horizontal fragment are specified by a condition on one or more attributes of the relation. Often, only a single attribute is involved. For example, we may define three horizontal fragments on the EMPLOYEE relation with the following conditions:

(Dno = 5),
(Dno = 4), 
(Dno = 1)

each fragment contains the EMPLOYEE tuples working for a particular department. Similarly, we may define three horizontal fragments for the PROJECT relation, with the conditions

(Dnum = 5), (Dnum = 4), and (Dnum = 1) 

where each fragment contains the PROJECT tuples controlled by a particular department. Horizontal fragmentation divides a relation horizontally by grouping rows to create subsets of tuples, where each subset has a certain logical meaning. These fragments can then be assigned to different sites in the distributed system. Derived horizontal fragmentation applies the partitioning of a primary relation (DEPARTMENT in our example) to other secondary relations (EMPLOYEE and PROJECT in our example), which are related to the primary relation by means of a foreign key. This way, related data between the primary and secondary relations gets fragmented in the same way.

Each site may not need all the attributes of a relation, which would indicate the need for a different type of fragmentation. Vertical fragmentation divides a relation "vertically" by columns. A vertical fragment of a relation keeps only certain attributes of the relation. For example, we may want to fragment the EMPLOYEE relation into two vertical fragments.
The first fragment includes personal information: 1) Name, 2) Bdate, 3) Address, and Gender and the second includes work-related information such as a) SSN, b) Salary, c) Super_ssn, and d) department_number. This vertical fragmentation is not quite proper, because if the two fragments are stored separately, we cannot put the original employee tuples back together, since there is no common attribute between the two fragments. It is necessary to include the primary key or some candidate key attribute in every vertical fragment so that the full relation can be reconstructed from the fragments. Hence, we must add the Ssn attribute to the personal information fragment.

This module introduced you to the following terms:

1. Distributed transaction management : A feature of an Oracle database that enables it to manage an update, insert, or delete to multiple databases from a single query. Distributed transaction management
2. Fragmentation independence: fragmentation independence is the ability to partition data within a table (or, more accurately, within a relation) transparently.
3. Horizontal partitioning:Horizontal partitioning divides a table into multiple tables. Each table then contains the same number of columns, but fewer rows. For example, a table that contains 1 billion rows could be partitioned horizontally into 12 tables, with each smaller table representing one month of data for a specific year. Any queries requiring data for a specific month only reference the appropriate table.
4. packet : A packet is the unit of data that is routed between an origin and a destination on the Internet or any other packet-switched network.
5. Replication: The Oracle database provides key data replication and integration features critical to the success and well being of all enterprises in today's fast moving economy.
6. Snapshot:Storage snapshots have offered development and QA capabilities for database and non-database environments, providing the ability to quickly create point-in-time storage-efficient virtual copies of the data. Snapshots do not require an initial copy, as they are not stored as physical copies of blocks, but rather as pointers to the blocks that existed when the snapshot was created. Because of this tight physical relationship, the snapshot is maintained on the same storage array as the original data. Snapshots are generally implemented either as copy-on-write or redirect-on-write-based methods.
7. Topology
8. Vertical partitioning: Vertical partitioning involves creating tables with fewer columns and using additional tables to store the remaining columns. Vertical partitioning goes beyond normalization and partitions columns even when already normalized.
   Different physical storage might be used to realize vertical partitioning as well.
   Done explicitly or implicitly, this type of partitioning is called "row splitting" (the row is split by its columns).
   A common form of vertical partitioning is to split (slow to find) dynamic data from (fast to find) static data in a table where the dynamic data is not used as often as the static.

In the coming modules, we will explore the internal details of the Oracle networking engine, taking a close look at the Oracle listener, the multi-threaded server, the Oracle connectivity GUI tools, and a host of other topics.

Before moving on to the next module, click the Quiz link below to check your knowledge of the material covered in this module with a short multiple-choice quiz.
TNS Names - Quiz