Browse Prior Art Database

Dynamic Ordering of Joined Rows using Fields from Multiple Tables

IP.com Disclosure Number: IPCOM000106505D
Original Publication Date: 1993-Nov-01
Included in the Prior Art Database: 2005-Mar-21
Document File: 4 page(s) / 152K

Publishing Venue

IBM

Related People

Youngren, LW: AUTHOR

Abstract

Disclosed is a method for ordering joined live data. No frozen temporary result table is required. An Index is provided which assures both ordering and joining without sacrificing a live view of the data.

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 34% of the total text.

Dynamic Ordering of Joined Rows using Fields from Multiple Tables

      Disclosed is a method for ordering joined live data.  No frozen
temporary result table is required.  An Index is provided which
assures both ordering and joining without sacrificing a live view of
the data.

      Within relational Database systems, the need exists to both
join together rows from separate tables as well as to order the
resulting joined images.  In addition, some environments are such
that customers prefer to work with "live" data rather than frozen
data  (i.e., they want and expect the joined view to adapt
dynamically as the underlying rows are changed).

      In the past, most Database systems have failed to provide all
three attributes simultaneously and have considered them
incompatible.  Often if you wanted both a dynamic view and a joined
view you were not permitted to order the resulting joined row images
by fields extracted from MORE THAN ONE underlying table.  Similarly,
if you wanted to order by fields from separate tables while joining,
you were forced to settle for a FROZEN rather than dynamic view.

With this invention, all three attributes can coexist.

      For on-line interactive applications, the ability to navigate
directly to the underlying rows (rather than to a frozen table)
brings up the first screen of matching rows faster.  Should the
customer then decide (as he often does) that his query is ill-formed
and too broad, he then can back out and modify his query to be more
selective without having incurred the high cost associated with
construction of a frozen table.

      The solution consists of maintaining both composite
ordering_Indexes as well as Joining_Indexes.

      Historically, ordering has been accomplished by either
constructing an Index to be maintained in real time (a live dynamic
view of the data) or by extracting the rows, placing them in a
temporary (frozen) table and then sorting the rows.  Similarly,
joining has often consisted of extracting a frozen copy of the rows
to be joined.  When the ordering involves fields extracted from more
than one table, live data was generally sacrificed.

      Instead, this invention provides a mechanism by which a single
ordering/joining index can be maintained (have its key values
updated) in real time without mandating use of frozen data.  Hence,
any SQL request which includes BOTH ordering and join (and is ordered
on fields arising from more than one table) need not make a frozen
copy of the resulting joined view in a separate work table.  When the
size of the answer set is large (lots of joined row present) both the
time and the space required to construct this frozen table can be
onerous, not to mention the fact that some customers object to seeing
stale rather than live data.  The proposed invention requires no such
frozen table.  Instead, it maintains a composite ordering/Join index
in real time despite the fact that the fields which...