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?
posted by David Aldridge @ 11:59 PM
21 Comments:
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 :-)
That's a pretty impressive list there ... can you give an example of how the model clause lets you make up the "missing" data?
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.
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'
Associate/Dissociate
In production: No
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...
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.
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!
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.
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?
"Like most great ideas, so simple! You could sell that."
I take PayPal!
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).
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.
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...)
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 :-)
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.
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?
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.
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).
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.
ALTER SEQUENCE in Production? YES, performance tuning (great, but be aware of the consequences).
ALTER SEQUENCE [sequence] CACHE [cache size]..
Had to use "WITH" to get this to work. Could not get the materialize hint to work without that.
Warning: Might look familiar. You helped resolve this one :).
http://dizwell.com/main/component/option,com_smf/Itemid,114/topic,168.msg1208#msg1208
Post a Comment
<< Home