A List: SQL Features You’ve Probably Never Used In Production

By popular demand (one anonymous person — hey, it's all relative), another list.

OK, you may have played with them, found a hypothetical circumstance for them, but you've probably never found a real situation in which to use them, or if you have you've probably backed out at the last minute. Or the DBA won't let you use them. Or you found there was a bug that spoiled it.

More likely you will not even recognize them — unless you're one of those losers who enjoys browsing documentation of course. * ahem *

  • The Subquery Factoring Clause: OK, a bit of a softball to start with. I bet that someone has used this. Not first time, but as part of a tuning exercise. And to show off.
  • The Partitioning Clause of a Query Table Expression: Actually hardcoding a partition name in a query? Wellll, OK maybe.
  • The Model Clause: This is great! What's it for?
  • Multiset Operators: Or nested tables in general
  • Submultiset: OK, that's cheating. Let's just say "nested tables"
  • The CORR_K() Function: Oh wait, there was that time when … nah not really. Never used it.
  • PowerMultiset_By_Cardinality: "…takes as input a nested table and a cardinality and returns a nested table of nested tables containing all nonempty subsets (called submultisets) of the nested table of the specified cardinality". * wipes-eyes * OK seriously, that's the last time I'm going to mention nested tables. Or nested tables of nested tables.
  • Alter Sequence: Admit it … you thought you were going to be able to change the current value with it, but then found you just had to drop the sequence and recreate it, so that's what you did.
  • Associate/Dissociate Statistics: What?

Well, I pride myself on the high education level of my readership, and also it's high regard for risk-taking. If you have actually used one of these features in a production system, and it has actually made it past the DBA/watchdog, then please reply under the heading "I have an unusual affinity for Shiny New Things, and have successfully used the following features in a production system:"


24 thoughts on “A List: SQL Features You’ve Probably Never Used In Production

  1. I use some of those on your list in production:

    – Subquery Factoring Clause –

    The “with” clause has been a life-saver for me several times.
    I have one select statement (very effective) using 4 “factored” subqueries joining each other and other tables. Without subquery factoring it was not efficient either going through the same date several times having to use some procedural code.

    – Model Clause –

    I use the model clause a few places. One example I use it as part of a select statement to identify items with a “seasonal” profile in sales statistics (bikinis sell in the summertime, mittens in the wintertime :-) The model clause allows me to simultaneously make up data for “missing” months in the sales statistics and do analytic sums and “wrap” the 12 months of the year – all in one statement.
    I could have simulated the same with some subqueries and analytic functions but the model clause definitely made this statement easier.

    – Multiset Operators –

    I have PL/SQL functions returning a nested table – but my ERP system has to “select” those data, it cannot use the returned table variable directly. In this case I’m simply forced to use multiset.

    The rest of your list? Well, I don’t use them :-)

  2. Thank you for the list (I would have left my name but I didn’t see the ‘other’ radio button on the comment page).

    I’m studying for my 10g DBA tests and the concise lists helps me take a holistic view of the database. Enjoy your blog very much.

  3. Subquery Factoring Clause

    In production: Yes

    great tool for “leaning” the optimizer toward taking that subquery and fully resolving it into a temp structure. Better than hints like no_merge etc because you still give the optimizer of NOT fully resolving it

    Partitioning Clause

    In production: Yes

    Great for “this years data” views. Each new years eve with recreate the views with the new years partition. Ensure that users get pruning without having to add date range clauses

    Model Clause

    In production: No

    Great for presentations because no-one will have a clue what you are talking about

    Multiset Operators

    In production: Yes

    cast/multiset into nested table types (and nested tables of nested tables) etc is a easy way of creating hierarchical XML via dbms_xmlgen

    Alter Sequence

    In production: Yes

    Want to reset a sequence to 1 each year? ‘alter seq increment by -12345’, then select nextval, then ‘alter seq increment by 1’


    In production: No

  4. Missing data with model clause…

    I’ll start with pointing at a question at AskTom. I have the last two comments on that question:

    Link to AskTom

    In my comments I give example of model clause for making up “missing months”.

    I have a more complex example in production – I’ll dig it up tomorrow…

  5. Matt,

    “Holistic” is my middle name.

    Actually, what I usualy aim for is to convert the documentation’s approach of supplying a list of features and the problems they might solve, into a list of problems and potential ways of solving them. Hopefully that’s working out, but I’m not sure that it’s compatible with OCP methodology, having never looked at it.

  6. Alter Sequence

    In my younger days I received a vendor “fix” which was just an “alter sequence increment by 10”. It did fix the problem, but both I & the vendor were surprised by the resulting sequence values!

  7. Actually, what I usualy aim for is to convert the documentation’s approach of supplying a list of features and the problems they might solve, into a list of problems and potential ways of solving them.

    Like most great ideas, so simple! You could sell that.

  8. DaPi,

    both I & the vendor were surprised by the resulting sequence values” … tease! Are you going to make us go and test this to see what’s surprising?

  9. How about a list of “features” that should have been in Oracle ages ago?

    I’ll throw one out there – a built-in function to easily identify the name of the function/procedure currently executing? It would have to support packages (pkg_name.proc_name).

  10. I’ve used subquery factoring a couple of times when I’ve hit a performance issue.
    In a data migration process I’ve used both ALTER SEQUENCE and the partition name against a table (but it would be GREAT if allowed a list of partition names).
    MODEL clause ain’t fair. Having got any live experience with 10G.
    I’ve used MULTISET in 8i. In 9i I seem to get by with the TABLE() syntax.

  11. Yeah, used “subquery factoring clause” as well. Useful for tuning: it strongly hints the optimiser, like Connor mentions. Mind you, down here we just call it the “WITH thing” for shrot.

    (subquery factoring clause indeed! mumble, grumble…)

  12. I have now dug into the application and found my complex statement.

    Turns out I use both with and model in the same statement.

    I use with for being able to easily and efficiently join a subquery with two different tables in a union all.

    I use model to get “recursive analytics” that I otherwise would have had to use 6 nested queries to get.

    If you like I will post it, but beware that it’s 124 lines of SQL, filled with danish words, and unfortunately lacks any descriptive comments.
    (I really will have to comment that code – otherwise I won’t recognize what it does in half a year :-)

  13. I’ll throw one out there – a built-in function to easily identify the name of the function/procedure currently executing?

    AskTom has “who called me” and “who am I” among his useful procs.

  14. a built-in function to easily identify the name of the function/procedure currently executing?

    hmmm, I think I’ve seen something similar to this … or maybe it was to do with error stacks. Anyone?

    I’m glad I softballed the subquery factoring (“WITH” … :) clause. It’s obviously a useful feature, but I don’t know when we’d expect query tools to start using it automatically .. they barely seem capable of recognising analytic functions right now. As for the model clause …

    With all of these features I guess that the key to understanding is to get a whole bunch of examples … Kim’s is a good one that I particularly like because (in line with other coments here) it shows how to solve a particular problem using a particular feature, and there’s a few other ways of solving that problem in the AskTom thread.

    I like the look of Mark’s example on the Model clause (thanks HJR) … that’s just the kind of crap — I mean “user requirement” — that I keep having to deal with. Unfortunately users generally want to be able to drill within a report on these things, and that implies the use of very standard SQL. Well, we’ll see.

    What’s with all the nested table use, people? Is this something that we are occasionally forced to use by application designers, or is there a genuine liking for them?

  15. It’s the only article I know on the subject at the end of which I mentially went, ‘Ah, so that’s it!’.

    Mark’s a real so and so for doing that :(. Some dizwell bloke seems to have the same knack.

    wot jealous me? nah. couldn’t be – I’m naturally green.

  16. Tom’s routine for identifying the current proc/func does not work when the proc/func is in a package (that’s why I gave the pkg_name.proc_name example).

    And on this thread, I will be putting our first query using WITH into production shortly (a complex join that is referenced on both sides of a union).

  17. The multiset thing used to be useful in the days when we wrote cursor FOR loops. Say you need to go through all orders and for each one, do something with each order item. In Oracle 7 you could either use a second cursor loop, or join the tables in one cursor and roll your own control break logic. With a MULTISET clause you get the best of both. Loops are just so last century though.

  18. ALTER SEQUENCE in Production? YES, performance tuning (great, but be aware of the consequences).
    ALTER SEQUENCE [sequence] CACHE [cache size]..

  19. Pingback: Pinky Programmer » Blog Archive » Probably……..

  20. “How I spent my Christmas vacation” (in part…)

    Here’s the revised link to Kim Berg Hansen’s askTom entry:

    My MODEL usage in production is NOWHERE as sophisticated as Kim’s; it’s simple Excel-like usage where I got tired of copying code, or nesting inline views. This shows how I use it (based on another askTom entry):

    “WITH clause”, aka “subquery factoring”, aka “common table expressions” (in other DBMSs)? I use ’em incessently. It’s modular programming in SQL.

    The rest of ’em? Not a chance.

    BTW, my favorite factoid about Kim involves what he did from 1988 – 1992. See his resume:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s