The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.
27 July 2010
Players question validity of a choice in the 26 July quiz(1249)
A half dozen players submitted the same question regarding the 26 July quiz, which asked:
"Which of these blocks, when executed, display the names of tables owned by the schema to which I am currently connected?"
One of the choices, which we scored as correct, offered a query against the USER_TAB_COLUMNS data dictionary view.
Players objected, because the query of DISTINCT TABLE_NAME from this view would return the names not only of tables, but of views, as well.
Here's one player's comment: "I did not select distinct table from from user_tab_columns option because it would also show you column names from views in the connected schema. The wording of the question could be taken either way --- it did not specify "only" tables, but it could be readily interpreted to mean that. In this case, I thought the user_tab_columns answer was a poor selection as it did not yield the precise answer the question was asking."
While I can understand that this scenario would raise a question in the minds of some players, I must stand by the scoring of this choice as correct. The result set does display the names of tables owned by the schema. If the question had asked you to choose those blocks of code that display only the names of tables, it would clearly be incorrect.
Subscribe to:
Post Comments (Atom)
Steven, I agree with whomever first notified you ... User_Tab_Columns is imprecise ... since coding is all about precision, you cannot fault those who did not select option one. Option 1 of the 4 options, tho' correct, was not precise. You should allow NOT checking that box as an acceptable answer.
ReplyDeleteI will continue to enjoy and appreciate the quiz and the considerable efforts that go into offering and maintaining it, but I really disagree with the reasoning on this particular question. The essence of a SELECT statement is to return a set of rows that meet a criteria.
ReplyDeleteAny SELECT statement that returns more than the desired rows, especially without providing any information that allows the user to discriminate which return rows apply to the intended query, has by definition not answered the query. By the standard applied here, 'SELECT OBJECT_NAME FROM DBA_OBJECTS' is equally valid, as the user is still left to guess which rows represent table_names in the current schema.
Would 'SELECT COLUMN_NAME FROM USER_TAB_COLUMNS' be considered a correct match for a query that was supposed to return the column names of a table? It meets the standards of language being applied here -- it would certainly return all of the columns of the table. It's just that there would be no way to identify them among the results.
Anyway, I just thought I would rant here -- I do very much appreciate the games and look forward to them every day now. The lost points definitely smart (the other points I lost were my own doing), but I will keep playing because I really do enjoy the game.
You can extend debate to tables that have been dropped and are in the recyclebin. They are, with a BIN$ name, in USER_OBJECTS as tables and in USER_TAB_COLUMNS (and USER_UPDATABLE_COLUMNS), but not in USER_TABLES.
ReplyDeleteIf recyclebin objects count, and you want all tables and only tables, then USER_OBJECTS is correct but USER_TABLES is wrong.
Similarly, if recyclebin objects don't count, and you want all tables and only tables, then USER_TABLES is correct and the others are wrong.
Everyone agrees that USER_TABLE_NAMES has been correctly marked as wrong. The other three choices are debatable EXCEPT in the widest interpretation that they generally work (ie if you successfully do a CREATE TABLE and immediately run the script, you'll see the table name).
While we're nitpicking, USER_TABLES also wouldn't include object tables, for which you would need to query USER_ALL_TABLES. However it didn't say it returned ALL table names in the schema so I think the question's still OK.
ReplyDeleteI completely agree with GS - the 'SELECT COLUMN_NAME FROM USER_TAB_COLUMNS' shall be qualified as the wrong answer - otherwise it would be hard to explain why you add WHERE clause to the select from USER_OBJECTS - if follow your logic 'SELECT object_name FROM USER_OBJECTS' should be satisfactory. Or even 'SELECT object_name FROM DBA_OBJECTS' (assuming that the user has access to this view) - all names of user tables would be present in its result set. Or even (hypothetical) select that returns all alpha-numeric sequences up to 31 character long.
ReplyDeleteIn worst case at least answers that include only
SELECT object_name FROM user_objects
WHERE object_type = 'TABLE'
and
SELECT table_name FROM user_tables
shall be re-scored as 100% correct
I very much agree with GS's comment on this matter.
ReplyDeleteOn the other hand, it is a matter of correctly interpreting the exact wording of the question asked and not having any assumptions about practicality of a query and usual SQL reasoning (if I am asked to return something, I usually assume the result should not include anything else except what I was asked for)...
I just wanted to post a comment almost identical to the previous two...
ReplyDeleteProgramming is indeed about precision and when we write a SELECT we want to return EXACTLY what we are looking for and NOT a superset of that set.
I think that those who did not mark this answer as being correct ARE RIGHT and also do have the additional merit of having known that USER_TAB_COLUMNS also returns columns of VIEWS,
not only those of TABLES, which many people do not know and therefore create generic utilities that do not work correctly ...
In the context of this question, I think that all those who DID NOT mark this answer as being correct DO DESERVE A SCORE ADJUSTMENT.
I do enjoy very much the challenge, though, unfortunately, I sometimes miss some of the contest days.
It is very instructive and, if some questions send us (again !) to the documentation and to test out things by ourselves, then this is its most valuable outcome.
Best Regards,
Iudith Mentzel
ZIM Intergated Shipping Services Ltd.
Haifa, Israel
I completely agree with GS. That select statement returns more than desired rows, and by that standard it is incorrect.
ReplyDeleteSteven, I think your argument with "only" is weak. If in real life you got a specification "select names of tables owned by the current schema" would you write such a select statement?
I vote for rescoring.
Dalibor Kovac
GS has shown nicely how you can stretch the given interpretation of the answer in dispute to an absurdity. If I had to choose an argumentation on which to base my decision of crediting users' answers, I'd go with his.
ReplyDeleteI must agree with what GS said in the comment before. There is no logic in returning more data than necessary, especially if the data explicitly does NOT meet the criteria. Imagine you ask the computer what the result of 1+1 is and it returns 2,3,4,5... The answer is somewhere in the result set ;)
ReplyDeleteAnyway, I do not see the need for any re-scoring, not in this question nor in any other question. I love to play the game every day and I love it when it comes to such detailed discussions afterwards. But I do not think that we should overrate things. It's just fun, it's a game, nothing else :)
Steven, thanks for all the efforts you put into this, please go on!
Hi all,
ReplyDeleteFirst let me say I fully understand that people have concerns with some questions.
However, it seems to me that there will not any question where all players agree on the meaning and whether it applies to best practices or the like. I had the same feelings once and a while also in the beginning (and I have played all questions since the start), where I got frustrated because I misinterpreted a question as a search for a best practice or best solution. I stopped doing that (getting frustrated) and now just enjoy the daily quiz, trying to learn from it. And as said, getting frustrated about some questions and the errors you make because you misinterpreted, did not make me stop playing every day.
I hope people get somewhat more relaxed about the questions and accept that once and a while you make a mistake because you read differently or think there is more to a question than there is. On average it will not hinder you to reach a good ranking since there are many opportunities to do well (I also speak from experience I think). Yesterday's question had no problems: all that was asked was which PL/SQL block would return tables in your schema, it did not ask for the best solutions, it did not ask for 'only tables'. SuMiche, if not clicking the box would also be counted as Correct, you cannot distinguish between people who really did not know and people who interpreted differently. As far as I know, the answer is correct, it may be not be your favorite way to search for tables in your schema (I agree), but it is not an incorrect answer to the question as it is.
Have fun playing the quiz, accept some bad days and you will be fine. I know I will. And if the day comes that I cannot handle it anymore, I will just stop playing. It can be that easy... ;-). I think Steven by now knows what to focus on when designing questions, but he is only human.
There seems to be a problem with the question when it isn't possible to answer a true/false question unambiguously.
ReplyDeleteUsually I would expect that when required to provide some output, this should exactly match the request. I was asked to provide a list of table names but this answer instead provides a list of table and view names. This is not what was requested but instead a superset.
Usually, I would hope that I could learn something from getting a question wrong. A chance to round out my knowledge of PL/SQL and SQL but I don't believe that has been the case with this question.
Dear all,
ReplyDeleteThanks so much for all your feedback! And while I appreciate Toine's very sensible approach ("Have fun playing the quiz, accept some bad days and you will be fine."), I am thoroughly convinced by many of the responses. While I could insist that by leaving out the word "only", I am "right" and you all are "wrong", that is both not in the spirit of the Challenge and not a reasonable interpretation of the question.
I am going to do the following:
1. Change the question so that it addresses feedback re: the recycle bin; change the user_tab_columns choice so that it excludes views. [as I have noted before, I must make sure these quizzes have no issues, for future use.]
2. Mark as correct anyone who selected wrong for the user_tab_columns choice.
3. Rescore and rerank. I should have this done on Wed. I am training all day today.
Just so you know: I get very annoyed with myself when I publish a quiz that is not 100% accurate and unambiguous, but I always feel nothing but appreciation and admiration for those of you who take the time to analyze the quizzes so closely and voice your opinions. That is precisely what I had hoped for the Challenge!
Warm regards, SF
Late breaking news: and it gets even worse! Player Alen reported to me the following: the choice that queries from USER_TABLES does not show all tables in my schema. Specifically, it does not show the names of tables of objects. You can verify this as follows:
ReplyDeleteCREATE OR REPLACE TYPE o_test AS OBJECT
(id NUMBER);
/
CREATE TABLE t_test OF o_test
/
SELECT table_name
FROM user_tables
WHERE table_name = 'T_TEST'
/
So I will need to address that in my "fix" for this question.
Here's the lesson I have learned: with all the nuances that Oracle has added to the set of database objects over the years (recycle bin, tables of objects, etc.), those data dictionary views have become a "mine field" when it comes to consistent behaviors and good material for straightforward quiz questions.
Yikes!
Also, user_tables does not include XML tables. Consider an example:
ReplyDeletecreate table xml_table of xmltype;
select *
from user_tables
where table_name = 'XML_TABLE';
0 rows returned
select *
from user_objects
where object_type = 'TABLE'
and object_name = 'XML_TABLE';
1 row returned
select *
from user_xml_tables
where table_name = 'XML_TABLE';
1 row returned
It may be reasonable to resort to Oracle dictionary:
ReplyDeleteselect comments from dict where table_name='USER_TABLES';
Description of the user's own relational tables
See the word "relational" there.
And there are separate USER_OBJECT_TABLES, USER_XML_TABLES, USER_QUEUE_TABLES and so on views.
As well as USER_ALL_TABLES.
So it seems to me that the quiz in question should be not re-scored but completely discarded.
al0 makes a very good point and I will follow your advice. I think that what I will do is change the text of the quiz to reflect the "modern world" of Oracle tables, and give everyone the same score for that day. That's the closest I can come right now to "discarding" the quiz.
ReplyDelete@steven:
ReplyDelete> and it gets even worse!
As I said, you'd need to query USER_ALL_TABLES (or just for fun, ALL_ALL_TABLES where owner = [your username]) to include non-relational tables. However I still don't think that invalidates the question because it didn't claim to return ALL tables in the schema.
If anything, this kind of debate makes the quiz more fun :)
Ciao everyone.
ReplyDeleteIn the Spirit of the Challenge, to share knowledge and experience, and not to invoke a rerank...
I did not choose the answer "SELECT object_name FROM user_objects WHERE object_type = 'TABLE'" because it does not only show a record for 'tables' but also for Materialized Views:
Here is my point:
SQL> SELECT object_name FROM user_objects WHERE object_type = 'TABLE';
no rows selected
SQL> create materialized view m_v refresh on demand with rowid as select sysdate from dual;
Materialized view created.
SQL> SELECT object_name FROM user_objects WHERE object_type = 'TABLE';
OBJECT_NAME
------------------------------
M_V
SQL>
M_V is indeed not a table, but it is shown as a "table" (maybe in the internal implementation of the mviews it is).
This is the reason why I considered wrong the choice.
What do you think about this?
Thanks for the continued effort in giving us the Challenge,
Sterol Andro
Hi Steven, perhaps it is the question, not the answers, that needs revision. The way I understood the question (and thus answered "correctly") was:
ReplyDelete"Which of the following queries will yield results that include the name of at least one table in the user's schema? (Assume that the user currently logged in owns at least one table.)"
Touche Toine,
ReplyDeleteI learnt from something from this quiz - a distinction between user_tab_cols and user_tab_columns.
And I agree with Jeff - correct/loosen/adjust the question.
I think the answers need a revision too. Because
ReplyDelete(SELECT DISTINCT TABLE_NAME FROM USER_TAB_COLUMNS ) is wrong as it's wrong to say (SELECT OBJECT_NAME FROM USER_OBJECTS) or (SELECT OBJECT_NAME FROM DBA_OBJECTS), that you would consider right.
But if my boss would ask me: "give me a list of the tables in your schema?" and I would give him a list of tables and views, The time he will check that one of the view in the list is not a table, he would think that I'm joking with him and this doesn't look fair. If it would be important I would risk my job for it too.
Steven, with all the respect to you (I just want to explain my position, don't take it personally), I can't follow your idea and I only consider that, with such a position, you're just climbing mirrors I don't know how many examples I could make to show you that there is a convention in our language that makes it implicit that when you ask for something, you only want what you asked for, if not specified otherwise. The rest is unfair.
Last of all, if I would follow your idea, so also a list of all possible strings with 30 or less than 30 characters would be right answer. It looks quite strange!
For the fact of the other kind of tables "by definition"(http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4473.htm#i1635629):" USER_TABLES describes the relational tables owned by the current user" and if there is something different, it's an Oracle bug. Object tables are not relational tables so that's why they're not there.
In addition there is a column named DROPPED in the USER_TABLES view to say if it's a recycle bin table. To identify other kind of tables there are also other columns: IOT-TYPE,TEMPORARY,SECONDARY and NESTED.
It's all documented.
Bye Alessandro
@Sterol Andro
ReplyDeleteThe interesting fact is that select from USER_TABLES returns M_V as well.
SQL> create materialized view m_v refresh on demand with rowid as select sysdate from dual;
Materialized view created.
SQL> select table_name from user_tables ;
TABLE_NAME
------------------------------
M_V
@Sterol, a materialized view is definitely a table, that is the "materialized" part of it. Once you create a materialized view, Oracle creates a table with the same name. You cannot make updates to it yourself, but is is a table. You can also create a table and then create a materialized view on top of it (on prebuilt table). I do not think that invalidates the answer you marked as incorrect.
ReplyDeleteHa ha, yesterday I have put myself in a difficult position by saying to just accept some bad days and enjoy the quiz: this morning I had trouble with on of the the answers of yesterday's quiz, because I had a good reason (as I see it) not to check one of the answers that was marked as correct as it seems. Shall I or shall I not question the output or wait until others do it ;-)...hmmm, life can be tough sometimes...
Feel free to continue discussing this topic, but be aware that I have set the scores for ALL players that day to 100% correct and 500 points (the maximum). The text of the question has been changed to avoid the issues raised by many players. Adriano T. wins an O''Reilly Media ebook for being the first to identify this problem.
ReplyDeleteThanks for a great discussion and learning experience!