I posted this tweet yesterday:
But my twitter stats will improve, so hurray! :-)
Seriously, though, it was a problematic tweet, partly because it was a piece of advice that only makes sense in a fairly narrow context, or as @brynlite pointed out:
As I was stretching this morning I realized: I should have written a post on my blog explaining the tweet, and then I could refer to that. Or as PL/SQL Challenge SQL Quizmaster@kibeha tweeted:
At the PL/SQL Challenge, we write quizzes. This happens on a regular, but not frequent basis. In other words, insertions into the QDB_QUESTIONS table (I could explain the difference in our data model between a "question" and a "quiz", but why bother, right?) do not resemble placing orders on Amazon.com.
A reviewer noted that with the QDB_QUESTIONS_SEQ defined with the usual cache of 20, question IDs (which we reference regularly in the reviewing and editing process, since there is no "name" per se for a question) would leap forward 20 at a time.
This gave a distorted view (not presented to our players) of quiz submissions. So he recommended switching to NOCACHE to minimize the gaps in question IDs.
I thought that was a fine and sensible idea, and so applied that change. Then, as is often the case, I used what I learned (or was reminded of) that day for my daily PL/SQL tweet.
Unfortunately, this particular "tip" is not appropriate for many (most, really) scenarios faced by developers when needing to generate unique ID numbers.
My apologies, and thanks to all for the corrective advice.
#314b #plsql Avoid gaps in sequence NEXTVALs: use NOCACHE option when creating or modifying sequence. #FrugalProgrammingIt was a great success social media-wise. Several Oracle technologists I admire greatly responded to it, and none favorably.
But my twitter stats will improve, so hurray! :-)
Seriously, though, it was a problematic tweet, partly because it was a piece of advice that only makes sense in a fairly narrow context, or as @brynlite pointed out:
I expect to repeat this often, Steven: don't advocate specific solutions until the requirements have been statedTo switch I rather snarkily replied:
Keep on saying it, I guess, Bryn, but I am not sure how the advice can apply in the world of Twitter.OK, fine, it wasn't my finest moment.
As I was stretching this morning I realized: I should have written a post on my blog explaining the tweet, and then I could refer to that. Or as PL/SQL Challenge SQL Quizmaster
Reason why tweets aren't well suited to teach - unless always tweeet links to blog / video / etcSo here we go:
At the PL/SQL Challenge, we write quizzes. This happens on a regular, but not frequent basis. In other words, insertions into the QDB_QUESTIONS table (I could explain the difference in our data model between a "question" and a "quiz", but why bother, right?) do not resemble placing orders on Amazon.com.
A reviewer noted that with the QDB_QUESTIONS_SEQ defined with the usual cache of 20, question IDs (which we reference regularly in the reviewing and editing process, since there is no "name" per se for a question) would leap forward 20 at a time.
This gave a distorted view (not presented to our players) of quiz submissions. So he recommended switching to NOCACHE to minimize the gaps in question IDs.
I thought that was a fine and sensible idea, and so applied that change. Then, as is often the case, I used what I learned (or was reminded of) that day for my daily PL/SQL tweet.
Unfortunately, this particular "tip" is not appropriate for many (most, really) scenarios faced by developers when needing to generate unique ID numbers.
My apologies, and thanks to all for the corrective advice.
Hi Steven,
ReplyDeleteWe all make mistakes. To apologize and admit one is what makes one great.
Still that leaves-me wondering:
Why would anyone care about the gaps on sequencial ids?
Steven would you please care on giving further explanation or is he a "Pseudokey Neat-Freak" (naming by Bill Karwin)? There could be a logical reason that I am not aware.
Best regards
I am glad to hear that apologizing and admitting mistakes makes one great.
ReplyDeleteBecause that means I am truly, amazingly great. I make so many mistakes - and have learned it is way better to admit than to defend the indefensible.
Why care about gaps? I suppose it comes down to being a "neat freak" or at least accepting that perceptions play a role in how applications are used.
We want the rate of increase of question IDs to more accurately reflect the pace at which new questions are being added to the repository. That's it. Nothing terribly deep.
Hello All,
ReplyDeleteI would just like to add that this is indeed very, very application dependent.
There exist applications (for example, Financial and Accounting applications),
in which having gap-less sequences for identifiers of specific transaction
types is very important for the users.
In some cases like these, we have used a table for generating sequence
numbers, which requires, of course, some form of serialization (though, if
correctly designed, this is for such a short time, that it goes practically
unnoticed), and this is a 100% precise solution.
In other cases, we do indeed use sequences defined with NOCACHE,
and, without being completely 100% gap-less, are, however, completely
satisfactory for our scenario.
Both solutions work very well for us, considering that the application has a
small number of users, and the inserts in those specific tables are relatively
unfrequent.
I suppose that this is probably the case with the QDB application,
creating new quizzes is probably not a too frequent occurrence,
though, I can imagine how happy Steven would be if the opposite was true :):)
Of course, having a large number of simultanous inserts changes
the scenario completely.
Thanks & Best Regards,
Iudith
"The only thing is it doing is setting "slow=true"."
ReplyDeletehttps://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6393918681776
Yes, for small number of inserts for your quizzes vs. huge number of queries, NOCACHE makes sense. And for cases where you can't have gaps, NOCACHE is far better than having a "next available number table" as far as scalability and performance is concerned. There are more than a few threads on AskTom about gaps in sequences where Tom Kyte repeatedly says "WHO CARES" about gaps. :)
ReplyDelete