Skip to main content

Nuances of NOCACHE, a.k.a., Steven eating (partially) his words

I posted this tweet yesterday:
#314b #plsql Avoid gaps in sequence NEXTVALs: use NOCACHE option when creating or modifying sequence. #FrugalProgramming
It 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 stated
To 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 @kibeha tweeted:
Reason why tweets aren't well suited to teach - unless always tweeet links to blog / video / etc
So 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.

Comments

  1. Hi Steven,

    We 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

    ReplyDelete
  2. I am glad to hear that apologizing and admitting mistakes makes one great.

    Because 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.

    ReplyDelete
  3. Hello All,

    I 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

    ReplyDelete
  4. "The only thing is it doing is setting "slow=true"."
    https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6393918681776

    ReplyDelete
  5. 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

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel