Some basic questions on RDBMSes.

Question

I've skimmed thru Date and Silberschatz but can't seem to find answers to these specific questions of mine.

1. If 2 database users issue a query -- say, 'select * from AVERYBIGTABLE;' -- where would the results of the query get stored in general... i.e., independent of the size of the result set?

   a. In the OS-managed physical/virtual memory of the DBMS server?

   b. In a DBMS-managed temporary file?

2. Is the query result set maintained per connection?

3. If the query result set is indeed maintained per connection, then what if there's connection pooling in effect (by a layer of code sitting above the DBMS)? Won't, then, the result set be maintained per query (instead of per connection)?

4. If the database is changing in realtime while its users concurrently issue select queries, what happens to the queries that have already been executed but not yet (fully) 'consumed' by the query issuers? For example, assume the result set has 50,000 rows; the user is currently iterating at 100th, when parallely another user executes an insert/delete such that it would lead to more/less than 50,000 rows if the earlier query were to be re-issued by any user of the DBMS?

5. On the other hand, in case of a database that does not change in realtime, if 2 users issue identical queries each with identical but VERY LARGE result sets, would the DBMS maintain 2 identical copies of the result set, or would it have a single shared copy?

Many thanks in advance.

Answer 1

The reason you don't find this in Date etc is because they could change between DBMS products, there is nothing in the relational model theory about pooling connections to the database or how to maintain the result sets from a query (like caching etc). The only point which is partially covered is 4 - where the read level would come into play (eg read uncommitted), but this only applies until the result set has been produced.

Answer 2

Some of this may be specific to Oracle.

1. The full results of the query do not need to copied each user gets a cursor (like a pointer) that maintains which rows have been retrieved, and what rows still need to be fetched. The database will cache as much of data as it can as it reads the data out of the tables. Same principal as two users have read only file handle on file.

2. The cursors are maintained per connection, the data for the next row may or may not already be in memory.

3. Connections for the most part are single threaded, only 1 client can use a connection at a time. If the same query is executed twice on the same connection then the cursor position is reset.

4. If a cursor is open on table that is being updated then the old rows are copied into a separate space (undo in Oracle) and is maintained for the life of the cursor, or at least until it runs out of space to maintain it. (Oracle will give a snapshot too old error)

5. The database will never duplicate the data stored in cache, in Oracle's case with cursor sharing there would a single cached cursor and each client cursor would only have to maintain its position in the cached cursor.

Oracle Database Concepts

See 8 Memory for questions 1, 2, 5

See 13 Data Concurrency and Consistency (Questions 3, 4)