SQLDeveloper, Subquery Refactoring & Toad
June 29, 2007 2 Comments
A couple of things to mention today.
For ages, I’ve been meaning to get to know SQL Developer a bit better.
I’ve not really played with it since it was known as Raptor back in beta or release 1.0.
I’m impressed. I like it a lot. And I think the plug-in third party modules have got real potential if it takes off (how about a Qute equivalent?). It’s come a long way.
I’ve had enough of Toad though. For me, it seems to crash frequently. And although more and more features get added, it does not seem to get any more robust.
The final straw that has pushed me over to SQL Developer is actually an injustice. I had thought that the ‘With’ clause, aka subquery refactoring, was unsupported. It was raising an error, not an ora- error, along the lines of “SQL statement doesn’t return any rows”. I had put this down to a Toad inadequacy, for which I must apologise.
If you get this error message, make sure that you are connecting using an Oracle client >8 (an option on the connection screen depending on what Oracle clients that you have installed).
The final point to make is that the ‘WITH’ clause rocks. I’m using subquery refactoring more and more, possibly with a tendency to overuse it at times. But it rules. And SQL Developer is getting there.
OraStory 
can you give some examples of how WITH rocks? I’ve never found a practical use for it.
I could give a couple of noddy example of HOW to use but I expect you know that already.
I’m not convinced I can give/think of a specific and effective example that can show to you why I think it is so good.
What I can say is that sometimes where I would have used an inline view, I am using WITH. Where I see often big and complex statements that are poorly performing with lots of row-by-row lookups via plsql functions, I’m findng ways to improve this using WITH.
I find it a really effective way to encourage better Set thinking.
And where I want to materialise a set of data, maybe a reference table or similar, that’s going to be used in more than one place – for example the two parts of a UNION – where the optimiser might have to refer to it twice, I’m using WITH.