Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, graeme.

Asked: October 22, 2012 - 10:09 am UTC

Last updated: October 25, 2012 - 10:48 am UTC

Version: 11Gr2

Viewed 1000+ times

You Asked

After 15 years development, implementation and administration of Oracle Databases I’m going to be totally honest.

1. I have used Oracle 6-11g
2. I do understand the concepts of Oracle RDBMS databases
3. I do find PL/SQL ‘relatively easy’
4. I have been following Tom Kyte in web and print since the mid 90s from his comp.databases.oracle days
5. I’ve always been a proponent of modular well written and well documented code
6. I’ve always been a big believer in as simple and logical as common sense approach as possible

BUT

1. I still find SQL very difficult
2. I still can’t easily think in sets
3. I still can’t really read an EXPLAIN PLAN with more than a few lines of indentation
4. I think I have used 'real' analytics about 3 times – after seeing it done in a specific example on this site
5. I would still bet that the majority of SQL writers out there would have no idea about or concept of query execution plans

Our last developer just left and wrote a lot of our database views – he did a good job with that – if I can say with my assistance and guidance on how we structured our views etc. Our new guy has been programming for many many years, unix kernel, device drivers, cryptography, Pro*C, etc. I can tell he finds SQL challenging. Like me he can get the job done but it’s a struggle.

Do you think the majority of people who work in Oracle can think and write both procedurally and ‘in sets’ – are some people hard wired for one or another?

and Tom said...

I think anyone can do pretty much anything. Anyone can write 'good' SQL, given the time and energy.

it took me a while to get my head around analytics, I still learn new tricks with them from other people from time to time.

A lot of it is pattern recognition. Over the years, I've seen certain "patterns" and have found or thought up or been taught about a general method to solving a problem having a certain pattern. I've seen so many over the years, they just pop out now - but they didn't always.

Here is an excellent write up on this:

http://explainextended.com/2009/07/12/double-thinking-in-sql/

I've referenced that page before in presentations. It spells it all out.


(and if you can read an explain plan with a few lines of indentation - you can read one that has many - you already know how to read the plan - you just have a bigger plan to read! you might find a tool like sqldeveloper or OEM - with a graphical representation - to be more user friendly. If you can collapse parts of the plan, it might be easier to get your head around it).


5. I would still bet that the majority of SQL writers out there would have no idea about or concept of query execution plans


i will agree with that - but that is only because they haven't or don't want to try. They are just an outline, psuedo code, of what is to be done.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

"Set" for Success

Duke Ganote, October 25, 2012 - 3:34 pm UTC

Jeff Moden (highly regarded in the SQL Server community) has some advice...

First step towards the paradigm shift of writing Set Based code:
"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

excellent question

ajeet, October 26, 2012 - 1:35 am UTC

There should be an option where pepole can just say that they liked the question, just like facebook provides , as on this one, I do not have a question but can not leave without saying the question is very relevant for developers and more or less all of them might have similar queries..answer from Tom as usual excellent.

'real' analytics

Duke Ganote, October 26, 2012 - 3:46 pm UTC

RE: "I think I have used 'real' analytics about 3 times"

It depends on your focus. If it's transaction-processing, windowing/analytic functions are (perhaps) rarely essential.

If it's analytic (hmm, that word!) processing -- data warehousing, etc. -- _my_ world -- I can't imagine a day without 'em!

I feel better now...

Dana, October 26, 2012 - 10:09 pm UTC

I have really had a difficult time with analytics and window processing. I can usually "see" queries/business problems where they would be useful; I just have not been able to write them. It's good to know others have difficulty with this too.

Thanks.

umar, December 03, 2012 - 8:49 am UTC

frankly speaking, its all about improvising. we as humans have a great ability to improve ourseleves.. day in and day out.. its about being curious on making new changes to the code to make it more efficient.. we do write bad queries, and i too do it at times, but we should always try to visit and improvise it, consistently...
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.