How do the records retrieve from data base when using sql statements?

How do the records retrieve from data base when using sql statements?

Post by CRPenc » Tue, 24 Jun 2008 06:30:45



Order is /undefined/ in SQL access, except where explicitly requested
by an ORDER BY. This is true irrespective of involvement of join.

SQL is one particular language used to implement RDBMS access. SQL
generates set-based results. The RPG READ performs what is often called
a RLA [Row\Record Level Access] or ISAM [Indexed Sequential Access
Method] to retrieve a row to the program; even if underlying support can
effectively retrieve a set. By mathematical definition a /set/ has no
implied order, and SQL honors that definition. The manner in which to
effect a desired collation of data in a result set, is to request an
ORDER BY. A good implementation of SQL would not limit the access
method to use an existing index, opting to use /sort/ algorithms where
an exact index either does not exist or where an index which limits row
selection is a better choice for choosing which rows to include in the
set; in the given example, there is no WHERE clause to limit row selection.

That SQL SELECT with only an ORDER BY would not necessarily _use_ the
/file key/ [of any file, logical or physical] to retrieve the records.
In fact for such a request with many rows and no selection, the data is
most likely to be _sorted_ into an intermediate table [if the temporary
results are enabled]. This is because index access is /random I\O/
which may result in significant paging, whereas the /sequential I\O/ for
a request to retrieve all of the data in the file can be expected to be
extremely efficient due to minimal paging requirements. The collation
for character data is defined by the /Sort Sequence/ in effect for the
SQL SELECT request.

Regards, Chuck
 
 
 

How do the records retrieve from data base when using sql statements?

Post by CRPenc » Fri, 27 Jun 2008 20:44:32


Other databases typically offer /clustered indexes/ because they do
not have direct storage management access, since the RDBMS is not part
of the operating system. Thus for performance reasons, they incur the
cost at insert\update time to manage the physical ordering in contiguous
storage; i.e. /for performance/, with the hope that retrieval time will
be reduced, due to the physical ordering. The DB2 for i5/OS is tightly
integrated with the LIC storage management, plus its single level store
and automatic striping of data, limit the cases where the benefits of
physical ordering will assuredly outweigh the costs. As such there was
little justification to provide clustered indexes; too little to be
gained since few SQL statements would be able to take advantage. With
Encode Vector Indexes [EVI] there were even fewer reasons for clustered
index since those indexes can reflect the physical storage of the data
to the optimizer, to enable even quicker retrieval.

As Jonathon notes, there is the option to perform the request to
RGZPFM KEYFILE(Keyed_FileName) to effect essentially contiguous physical
storage of the rows, but that ordering is only in effect until the next
key-update or insert. The benefits from that type of reorganize action
are most visible for keyed row level access when large amounts of data
will be read, even if changes\additions have transpired; for the same
reason as clustered indexes. Similar benefit could be seen for the
simple but not often used, select all or first using physical keyed
order, but again, at a large cost to update and insert.

Regards, Chuck

 
 
 

How do the records retrieve from data base when using sql statements?

Post by CRPenc » Sat, 28 Jun 2008 22:57:54


Keyed logical files or SQL INDEX objects are more likely to be used
for selection than for ORDER BY. It is false to assume or imply that a
keyed logical file matching the ORDER BY will /automatically get used/
[to implement a query] to retrieve the rows. The optimizer [both CQE
and SQE] is not so limited in what it can do, in choosing how rows will
be retrieved. Because an index is random I/O, its cost for implementing
collation can become extremely high as the number of rows [that will be
returned] grows; thus for the query given by the OP where _all rows_ are
selected, such an index is not likely, in general, to be very beneficial
except in a mostly memory constrained environment where the query will
perform poorly for that & other reasons [even if only as side effects of
the memory constraints]. As the OP infers, a /clustered index/ could be
beneficial for that specific type of query, but as I noted since their
benefit is so limited in scope to those types of queries, and costly for
insert & update activity, that type of index is not supported by the DB2
for i5/OS.

Regards, Chuck