Down for the Count – Multiple Choice Quiz

August 8, 2010 (Modified August 8, 2010, August 10, 2010)

I am not much of a supporter of True or False type questions, nor do I much care for multiple choice type questions.  It seems that essay questions are usually the only appropriate type of questions on exams.  Take, for example the following question that might appear on a SQL exam:

Three, and only three, user sessions are connected to the database.  Session 1 creates 2 tables using the following commands:

DROP TABLE T1 PURGE;
DROP TABLE T2 PURGE;
CREATE TABLE T1(COL1 NUMBER);
CREATE TABLE T2(COL1 NUMBER);

These are the only tables in the database named T1 and T2, as shown by the following:

SELECT
  TABLE_NAME
FROM
  DBA_TABLES
WHERE
  TABLE_NAME IN ('T1','T2');

TABLE_NAME
------------------------------
T1
T2

The following operations are performed in order:

In Session 1:

INSERT INTO T1
SELECT
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

COMMIT;

INSERT INTO T1
SELECT
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

In Session 2:

INSERT INTO T1
SELECT
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

INSERT INTO T2
SELECT
  *
FROM
  T1;

COMMIT;

INSERT INTO T2
SELECT
  *
FROM
  T2;

In Session 3:

SELECT
  COUNT(*)
FROM
  T2;

What value is displayed when session 3 executes its query against table T2?
a.  600
b.  200
c.  100
d.  0
e.  All of the above
f.  None of the above

After you answer the multiple choice question, explain why your answer is correct.

————–
Edit roughly 4.5 hours after the initial publishing of this blog article: 7 people, including the first person to comment (Sean) saw a value of 1600 for answer a. – that value was modified within minutes of the first comment appearing in this article to the value 600.  The number 1600 was either a typo or an answer that I thought no one would ever select.  For fun, let’s add the following to the list of possible answers for the question… could it be the correct answer?:
a2.  1600

—————-

Edit August 10, 2010: The Test Answers:

As I stated a couple of days ago, I intend to reveal the correct answer to the question posed by this blog article.  As mentioned by Gary, just because a person (or certification board) designs a test question, that by default does not mean that the person knows the correct answer to the question.

Within roughly 15 minutes of this blog post appearing on Sunday Sean provided a very good answer with strong justification.  If I only had 60 seconds to answer the question, I would hope to be able to work out the same solution.  That said, the WordPress blog article category for this article is “Quiz – Whose Answer is it Anyway?” and answers E and F seem to be a bit of a clue that something is a bit strange about this question.  I had more than 60 seconds to think about the answer, so I will pick one of the other answers.

I suspect that several readers were a bit suspicious about the question in this blog article that just might appear on some sort of standardized test (I designed the question, so I hope that it does not).  Gary, Joel Garry, and Martin Berger stepped up to the challenge and offered suggestions regarding how there *might* be more than one answer to the provided question.  As you might guess, the numbers selected for the answers are not random.  I thought that I would share with you the thoughts that I had when putting together the potential test answers.

——

b. 200 – assumes that we are using SQL*Plus or some other tool that does not auto-commit after every DML call, have not modified the environment of the tool, and do not closed the tool after the script for each session was executed – SQL*Plus commits by default when exiting.  This is probably the right answer.

——

a. 600 – assumes that we are using a tool that auto-commits after every DML statement by default, or possibly using a database (other than Oracle) that auto-commits after every DML statement.  This could be a Visual Basic (or VBScript) program using ODBC or OLEDB (I might have stated ADO before) that does not start a transaction with a controllable end-point unless the BeginTrans method of the connection object is called first – or it could just as easily be a Java program using JDBC that does not call the setAutoCommit method of the connection object with a parameter of false.  Gary mentioned another possibility.

——

d. 0 – the question states that there are three and exactly three sessions connected to the database, so it probably is not reasonable to connect or disconnect from the database.  This is an instance where a pre-existing condition in one of the sessions might cause problems.  For example, assume that these three sessions had been connected for a while and one of the previous questions in the test asked about the serializable isolation level.  So, at some point in session 3 the command ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE; was executed and a transaction started – maybe something as simple as: SELECT * FROM TABLE2 WHERE 0=1 FOR UPDATE;  With this isolation level, once a transaction begins, the answers to all SQL statements are as of the start time for the transaction.  Session 3 will therefore see a count of 0 for table T2.

Another possibility is that either session 2 or (mutually exclusive) session 3 is connected as a different user.  The problem is that the test showed that there is only one table T1 and one table T2 in the database.  Suppose that one of the earlier test questions asked about synonyms and a table named TABLE2 was created in session 1 with a public synonym name of T2.  If session 2 is connected as a different user, it will actually insert into TABLE2 when attempting to insert into table T2, and session 3 will report the number of rows in the real table T2.  Note that the output does not show whether or not the inserts were successful, so it is possible that session 2 could not resolve the name T2 and returned an error.  If session 3 is connected as a different user, it will report the number of rows in table TABLE2, rather than T2.

Another possibility is that either session 2 or session 3 is connected as a different user and a public synonym points to a view created on table T2 that includes a WHERE clause of COL1=0.  Note that the output does not show whether or not the inserts were successful, so the view could have been created WITH CHECK OPTION.

——

c. 100 – the easiest way to obtain this value is if session 2 had at some point issued the command ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE; and already had an in-process transaction (maybe SELECT * FROM TABLE2 WHERE 0=1 FOR UPDATE;).

A second possibility of obtaining a value of 100 is if at some point in the past session 1 executed the following commands:

CREATE OR REPLACE VIEW VIEW_T2 AS
SELECT
  *
FROM
  T2
WHERE
  COL1<=50;
 
GRANT ALL ON VIEW_T2 TO PUBLIC;
 
CREATE PUBLIC SYNONYM T1 FOR T1;
CREATE PUBLIC SYNONYM T2 FOR VIEW_T2;

In this case, sessions 2 and 3 are logged in as the same user, which is different from session 1.

——

a2. 1600 – you might be able to guess how I obtained this number.  I performed the test several times, obtaining different results each time, not realizing that session 1 could not drop tables T1 and T2 because session 2 had an active transaction referencing one or both of those tables (maybe you could not guess how I obtained that number).

How else might you obtain a value of 1600?  Assume that at some point in the past session 1 executed the following commands:

CREATE TABLE TABLE2(COL1 NUMBER);
GRANT ALL ON TABLE2 TO PUBLIC;
CREATE PUBLIC SYNONYM T1 FOR T1;
CREATE PUBLIC SYNONYM T2 FOR TABLE2;
 
CREATE OR REPLACE TRIGGER T2_POPULATE AFTER INSERT ON TABLE2
BEGIN
  DELETE FROM T2;
 
  INSERT INTO
    T2
  SELECT
    ROWNUM
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1600;
END;
/

Now every time a transaction inserts into table TABLE2 all rows will be deleted from table T2 and 1600 new rows will be inserted into table T2.  Session 1 and session 3 are connected to the database as the same user.  Session 2 is connected to the database as a different user.

——
 
e. All of the Above – all answers were obtained without violating what the question asked, so I guess that e is the correct answer.

——

f. None of the Above – the count of table T2 will produce a single resulting value, not 5 values, and the value returned could very well be something other than the 5 values listed, depending on the prior state of the database.

————————

How else might you obtain the different potential answers listed for the test question?