Given the following table definition:
INVOICE_NO CHAR(20) NOT NULL
If the following SELECT statement is executed, which of the following describes the order of the rows in the result data set produced?
SELECT * FROM sales:
A. The rows are sorted by INVOICE_NO in ascending order.
B. The rows are sorted by INVOICE_NO in descending order.
C. The rows are ordered based on when they were inserted into the table.
D. The rows are not sorted in any particular order.
I think the option should be C. But the answer provided in the book is D.
Joined: 20 Oct 2006 Posts: 6968 Location: porcelain throne
Craig Mullins DB2 Dev Guide wrote:
You cannot guarantee the order of the rows returned from a SELECT statement without an ORDER BY clause. At times SQL developers get confused when DB2 uses an index to satisfy a query and the results are returned in the desired order even without the ORDER BY clause. But, due to the nature of the DB2 optimizer, the access path by which the data is retrieved might change from execution to execution of an application program. If the access path changes, and ORDER BY is not specified, the results can be returned in a different (non-desired) order. For this reason, always code the ORDER BY clause when the sequence of rows being returned is important.
If you are basing your thinking on knowledge that VSAM inserts do not reorder the base but only the index, you are forgetting about REORG.