… is it just me, or have any other OTN Forum regulars switched to “!=” directly as a result of the Jive’s inability to display “<>“?
Home
› Tag Archives › otn forums
Oracle Tips and Tricks – Part 1 in a series (I hope!)
Over the past couple of years or so, I’ve been hanging round the OTN SQL and PL/SQL forum, trying to help out where I can. Originally, I started doing this as a way of gaining experience of different “real world” examples, so that I could consolidate my knowledge (such as how to work with analytic functions!) and also expose myself to a lot of different issues. And also, where possible, to help people!
Since then, I’ve learnt much more than I could ever have hoped to, from various tips and tricks that I use often, to things that I haven’t yet needed to use in my day-to-day job. Of this latter group, there are things that I haven’t got the set up or access to try out, but even though I haven’t used them, I *know* that they exist and can refer back to them if I ever do come across a situation that requires them.
I cannot recommend enough to people that if you want to consolidate your SQL and PL/SQL knowledge and improve your skills, start helping out on the OTN SQL and PL/SQL forum!
Anyway, I thought that I would start writing about some of the things that I have picked up from my fellow forum helpers (they know who they are! *waves*) in the hopes that more people will come across them.
Subquery Factoring
Subquery factoring has been around since Oracle 9i, and is an incredibly useful tool. This is one thing that I picked up from the forums that I use at least once every day whilst at work!
In essence, it’s a way of giving each subquery you use within a SELECT statement a name and giving the scope of that subquery to the whole query. Whilst you can name inline views, you have also restricted the scope of that subquery to the bit of the query that it’s in.
The format looks like:
with subq1 as (select ....),
subq2 as (select ....),
...
subqn as (select ....)
select ...
from subq1,
subq2,
...
subqn
where ....;
You can have one or multiple subqueries defined, and you can also refer to previous subqueries within later subqueries (eg. subq2 could select from subq1, etc). All subqueries must be selected from at least one somewhere in the overall query.
The beauty of subquery factoring is that if you have a subquery repeated throughout a query, like:
select 'first_query' name_col, col1, col3
from (select col1, col2, sum(col3) col3
from a_table)
where col2 != 'hello'
union all
select 'second_query' name_col, col1, col3
from (select col1, col2, sum(col3) col3
from a_table)
where col2 = 'hello';
where the subquery gets run twice, when you factor it out, it only gets run once and then accessed twice:
with my_sub_query as (select col1, col2, sum(col3) col3
from a_table)
select 'first_query' name_col, col1, col3
from my_sub_query
union all
select 'second_query' name_col, col1, col3
from my_sub_query;
Oracle will quite happily create a Global Temporary Table behind the scenes to hold the data from the subquery if it thinks it is beneficial to do so.
As well as performance benefits, it helps to make the query easier to read by modularising the SQL statement. And as we all know, modularising your code and making it resuable is Good Practice (TM)!
As well as all those benefits, subquery factoring has become very popular as a means of simulating a table with data, like so:
with my_tab as (select 1 col1, 'A' col2, 'hi' col3 from dual union all
select 2 col1, 'B' col2, 'bye' col3 from dual)
select *
from my_tab;
This comes in very, very handy when needing to set up test cases that other people can run, as there’s no longer a need for them to create a test table – the data’s already there!
New Icons and more thoughts on the OTN Forum upgrade
I’m not going to OOW (maybe one day!) but I thought that the badge icons that Justin Kestley put in his post might be usable as some icons for lj! I’ve updated the posts I’ve already written with the applicable icons (I’ve nabbed the OTN and SQL ones), but I couldn’t have done it without the help of my dearly beloved, who basically did the magic to extract the icons from the sample shown on Justin’s post!
As for the forums, I can’t help but feel that the lack of a Beta site for testing forum upgrades has kinda bitten Oracle on the bum. From the complaints I’ve seen, a lot of people rely on the Oracle forums and I can’t help but feel that the Forums have a low priority in the overall structure of Oracle-the-company. However, it’s just like how I feel about spelling mistakes and little low-priority niggles in front ends: sometimes it’s the minor details that influence people’s perceptions. A site that is usable but contains loads of spelling mistakes and extra clicks and key presses will cause the site and the team/company behind the site to go down quickly in people’s estimation. This is what I think is happening with the forums – as people lose faith in the forums due to down time, slowness, incomprehensible formatting, etc, they’ll leave and their faith in Oracle to get things right will go down.
I know that part of the trouble comes from the Jive software (I had a scoot round their site the other day; scalable and performant… ?!) and the host of extra “features” that comes for free. However, in a technical forum, I really, really don’t think we need all those bells and whistles – I mean, you need to be able to quote previous text, write your own text possibly with bolding or italics or containing links, but much more than this is not needed – I’m never going to write and need different sized headings, for example!
The other bug-bear that people have is the new reward system. They’re upset that they no longer have the “golden turnip/radish/medal/whatever” next to their name and are now reduced to being called “Newbie” and are no longer in the top user section in their particular forum. The people that have complained are the ones that look on answering things in the forum as a competition, so it’s more about ego than it is about helping people. I’ve no problem being labelled a Newbie (and I suspect that many of the people I would consider to be experts aren’t bothered either), because I post a) to help people and b) to learn. Sure, it’s nice to be credited, but it’s by no means a right that I expect! I’d rather people looked at my answers and took them to show my level of expertise, rather than how many points I have. Heck, I’m far from being an expert! I’m good at searching, and I like helping, but I still make stupid mistakes! So looking on the number of posts I’ve made does not show any indication of how much of an expert I am!
I do have one problem with the current reward system, and that is when a question is marked as Answered, I find that I am less willing to bother looking at that post, especially if the site or my network is being slow at that point. If I’m doing it, I’m pretty sure others are also doing similar, including experts. Now if no-one is going into these answered posts to check that the answer is right, who is going to correct the wrongly marked answers?
I’m sure this won’t be much of an issue now, whilst things are new, but over time, I think less people will bother checking answered questions, meaning that when people do searches, they’re going to see answers marked correct when in actual fact they aren’t correct – or, whilst being technically correct, aren’t the best solution.
Oh well, maybe it won’t be much of an issue, but I think it’s human nature to expend energy on something that’s apparently been marked as completed.
I think that if a Beta version had been in place, a lot of these arguments could have been worked out in advance and my suggestion of having a post rating similar to that of the Amazon.com review rating system (1 out of 10 people found this useful) might have been implemented – and that would sort some of the complaints out!
Ah well, maybe next time there’s an upgrade a Beta can be set up first – if it’s truely Oracle’s intention to involve the community, then that would be a good way of doing so in the forums!
Oracle Forums Upgrade
I see that the Oracle Forums have undergone an upgrade this weekend!
Looks like the new points rating system is getting a few people riled up; they’ve lost their golden turnip and have been reduced to “Newbies”! (I’m one of them, but I’m not particularly bothered, really!)
I do have a gripe with the text size, though – it really is quite small, and my eyesight is bad enough at the best of times! Still, seeing as I’m really busy at work at the moment, the amount of time I spend on the forums has dropped dramatically; now it’s lucky if I’m able to go on whilst I’m waiting for my code tests to finish!
I’ve learnt a lot (still am learning) from the SQL and PL/SQL forum, so much so that my mentor referred to me as a SQL expert the other week at work! That’s purely because I’ve picked up a number of tips and tricks for doing things from the forum (or, more accurately, I know how to search the forum, hehe!). It’s definitely worthwhile spending some time there trying to help answer questions if you’re wanting to brush up your SQL skills.
Recent Comments