Method and article for processing queries that define outer...

Details Method and article for processing queries that define outer... Method and article for processing queries that define outer...

1999-02-09

2002-08-20

Homere, Jean R. (Department: 2177)

Data processing: database and file management or data structures

Database design

Data structure types

C707S793000, C707S793000, C707S793000

Reexamination Certificate

active

06438541

ABSTRACT:

FIELD OF THE INVENTION
The present invention relates to computer database systems and more particularly to processing queries that define an outer join operation on a view, where the view is produced by joining two database objects.
BACKGROUND OF THE INVENTION
Relational databases store information in collections of tables, in which each table is organized into rows and columns. FIG.
4
(
a
) illustrates an exemplary database containing two tables, a department table “DEPT”
400
and a employee table “EMP”
410
, useful for recording and organizing information about a company. The columns of the department table
400
hold attributes for the different departments of the company, including a department number “DEPTNO”
402
, a department name “DNAME”
404
, and a location “LOCATION”
406
. Each department is stored in a row. For example, row
408
is an entry for department
11
, which is an accounting department and located in Washington, D.C. The employee table
410
holds information in columns for each employee in the company. Such information may include, for example, an employee's name “ENAME”
412
, job title “JOBNAME”
414
, and department “DEPTNO”
416
.
A database user retrieves information from the tables of a relational database by entering input that is converted to queries by a database application, which submits the queries to a database server. In response to receiving a query, the database server accesses the tables specified in the query to determine which information within the tables satisfies the query. The information that satisfies the queries is then retrieved by the database server and transmitted to the database application and ultimately presented to the user. Database servers are also capable of combining or “aggregating” information contained in the tables in response to a query. For example, one query for the exemplary database is to list all the departments in Washington, D.C., and the departments' employees, if any, with their job titles.
For any given database application, the queries must conform to the rules of a particular query language. Most query languages provide users with a variety of ways to specify information to be retrieved. For example, in the Structured Query Language (SQL), the following query in the ANSI SQL syntax requests the retrieval all the departments in Washington, D.C., and the departments' employees, if any, with their job titles:
[QUERY
1
]
select DNAME, ENAME, JOBNAME
from DEPT left outer join EMP
on DEPT.DEPTNO=EMP.DEPTNO and DEPT.LOCATION=‘DC’;
This query performs an “outer join” operation on the department table
400
and the employee table
402
. An outer join is a species of a “join” operation that combines rows from two or more relational database objects, such as tables, views, or snapshots. A join is performed whenever multiple tables appear in the FROM clause of query. The SELECT list of the query can reference any of the columns from any of the base objects listed in the FROM clause.
Most join queries contain a WHERE or ON clause that contains a predicate that compares two columns, each from a different joined object. Such predicates are referred to join conditions. In addition to the join conditions, the WHERE or ON clause can also contain other conditions that refer to columns of only one of the joined objects for further restricting the rows returned by the join operation. To process a join, a database server combines pairs of rows that satisfy the join conditions and the other predicates. Rows that combine in a manner that satisfy the join conditions and other predicates are referred to herein as combining rows.
For an “inner join,” only rows formed by combining rows are reflected in the result. In contrast, an outer join returns all combining rows plus all rows from one of the joined objects for which no rows from the other joined object satisfy the join conditions (i.e. the non-combining rows). A “left outer join” is a outer join in which the noncombining rows from the left listed object in the FROM clause are brought into the result; a “right outer join” is an outer join in which the non-combining rows from the right listed object in the FROM clause are brought into the result; and a “full outer join” is an outer join in which the non-combining rows from both the right and left listed objects in the FROM clause are brought into the result.
As illustrated in FIG.
4
(
b
), the results
420
of processing QUERY
1
includes one or more rows for each of the departments in Washington, D.C. As in any join operation, the outer join results include the rows that satisfy the join condition of correlated department numbers (rows
424
and
426
). For the left outer join operation, another row
422
of the results
420
is derived from the left object, department table
400
, because there is no corresponding row in the employee table
410
that matches the join condition for row
408
of the department table
400
.
Techniques exist for efficiently processing an outer join on two tables. For example, a database server may recognize that the employee table
410
has an index built on the “DEPTNO” column
416
. A database index is conceptually similar to a normal index found at the end of a book, in that both kinds of indexes comprise an ordered list of information accompanied with the location of the information. Specifically, a database index contains ordered values from one or more columns of a table and a list of which rows in the table contain those values. Indexes can provide performance benefits over full table scans if the predicates are selective, because the index renders it no longer necessary to scan the entire table to find the rows that contain particular column values.
The performance benefits of an index are particularly apparent when a predicate in the WHERE clause of QUERY
1
causes only a few rows to be selected from the employee table
410
. In the example, a WHERE predicate specifying that the location is ‘DC’ is selective, because it causes only the department numbers of
11
and
31
to be looked up in the employee table
410
. On the other hand, if the predicates are nonselective, full table scans are preferred over index scans because full table scan can exploit the efficiencies of block input/output operations to read data from the table.
Over time, it is not uncommon for database administrator to reorganize their tables as conditions warrant. For example, after the employee table
410
was created, which explicitly included the job title in column “JOB”
414
, a database administrator may desire to reorganize the employee table
410
. Referring to FIG.
6
(
c
) , the database administrator may wish to create a new job table
440
to list the job title “JOBNAME”
444
and pay grade “PAYGRADE”
446
of each job in the company. Jobs in the job table
440
are identified by a job number “JOBNO”
442
. The employee table
410
is modified to produce a new employee table
430
that includes employee name “ENAME”
432
and department number “DEPTNO”
436
columns as before, but with job number “JOBNO” column
434
as foreign key value to reference the appropriate entry in job table
440
. Creation of the job table
440
is useful for pooling all the appropriate information about the company's job into a database object, without denornalization of the other tables.
An upward compatibility issue with this proposed database reorganization is that there may be many queries for many database applications that still reference the “JOBNAME” column
414
, that was moved into the new job table
440
. For some database environments, updating all the queries may require many staff-years to complete. Accordingly, database administrators prefer to create a “view” that looks to the database applications like the old employee table
420
but is really formed as a join of the new employee table
430
and the new job table
440
.
A view is a logical table, and as logical tables, views can be queried just as if they were tables. The data that views actually present

Affiliated with

Also associated with

Rating

Method and article for processing queries that define outer... does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Method and article for processing queries that define outer..., we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Method and article for processing queries that define outer... will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-2955923