This post was originally published on this site

Over the last weeks I’ve conducted a little Christmas survey with a few SQL related questions, raffling off one copy of Practical Oracle SQL among those who participated in the survey.

The survey is over and I did a single call to DBMS_RANDOM.VALUE to find the winner. Drumroll…
The raffle was won by:
Mark Davidson, Ohio
Congrats and enjoy the book!
I hope it’ll arrive before Christmas 😁

And for those that are interested in the survey results, read on…

I asked three questions in my little informal survey:

What Oracle SQL topics would you like to improve your skills in?

About a third of the responses mention Tuning in some form or other – guess that’s to be expected. I think that’s in general one of the things that often make developers state “SQL is hard”, because the premise of SQL kind of is that you just declare what you want and let the database figure out the best way – and in reality that’s true of most of your SQL but not all. And for those cases where the database doesn’t figure out the best way itself, it can be relatively hard to know what to do to make it better.
Then another third of the response have analytic functions – which makes me happy that it’s a topic that people want to improve their skills in. At the same time it reminds me that unfortunately too many (in my opinion) developers don’t use analytic functions, even though they’ve been available for decades and can very often eliminate a lot of unnecessary procedural code.
And then there’s a smattering of other topics like pattern matching, SQL macros, polymorphic functions and more. All in all great topics – for details see below.

What Oracle SQL topics would you like in a book but can’t seem to find in current books on the market?

More variation here, but quite a few responses are about using and understanding the explain plan (and related topics on tuning and query optimization.) I think there are good books on tuning out there, but perhaps there’s a point that not many do a simple guide to the explain plan, similar perhaps to what Maria Colgan does in presentations? I am not sure about this.
The rest of the response range widely, including adaptive parallel execution, SQL macros, model clause, efficient DWH design, running multiple PDBs, JSON parsing, and a lot more. You can see all of it below. A few of these I might possibly have in mind if I ever decide on a second book, but a lot of them are out of my field, so I leave them here as inspiration for other authors 😉.

What enhancement to Oracle SQL would you really like SQL Santa to give you for Christmas?

Again quite a lot of variation, but SQL assertions pop up more than once, as well as boolean datatype and parameterized views. I can definitely relate to the SQL assertion wish as well as the other two. Though (as one response also state) SQL macro sort of gives us parameterized views in a way.
But there are also several responses that state topics that already exist, so I’m guessing here that people might have misunderstood me and written what knowledge they’d like to get from SQL Santa. That’s okay, I might have written the question a bit more clearly.
Anyway, thanks to everybody that participated – it’s been interesting to read the responses.
For those that would like to read the detailed responses in addition to my summary above, here are the anonymous individual responses in mangled order (so it is not the case that for example the third response in each list are by the same respondee.)

What Oracle SQL topics would you like to improve your skills in?

  • Performance tuning 

  • Performance Tuning

  • SQL Performance 

  • Tuning

  • Tuning

  • Tuning, predicates

  • Security and SQL Tuning 

  • make updates faster

  • Tuning a *question*, not just a *query* ; designing a database solution from requirements (my mind goes to an example in Tom Kyte’s first book, I don’t have it in front of me, where he talks about the fast phone/employee name lookup at Oracle however many years ago, and the design decisions behind that).  I guess in general, technical schema decisions that should weigh on implementation choices

  • I would like to improve my skills on analytics functions, performance tuning and advanced level SQL.

  • Triggers; performance; analytic functions

  • Use of Analytic Functions. Use of Hint.

  • Analytical functions

  • Analytical functions

  • Analytic Window functions

  • Analytic functions

  • Hierarchical, analytics

  • analytics, model clauses 

  • Pattern Matching, Analytical Functions, Model Clause

  • Window functions and sql macros

  • SQL macros

  • Row pattern matching, SQL Macros

  • Pattern matching

  • Table Polymorphic Functions

  • Tips and useful SQL / Plsql backend code for Web screens

  • pl/sql..
    Stored procs and REST interface.

  • Baselines, profiles and patches

  • New features in 18c, 19c, etc

What Oracle SQL topics would you like in a book but can’t seem to find in current books on the market?

  • Explain plan

  • How to read a SQL Plan. 

  • Decent guide on using the explain plan.

  • details about explain/execution plan operations: easy way to remember what they mean/do?

  • I would like to know about performance tuning for collection types query and how we can check explain plan of nested table query and what steps should we follow to improve it step by step.

  • Very basic introduction into the optimizer and sql performance

  • When I was a “beginner” that was the WORST time to find anything that was written for those folks at such a beginner level. All the books and websites showed such complex examples. When I really just needed to k is HOW to do my simple queries and joins most efficiently and accurately. I still feel there isn’t anything written with that audience in mind. 

  • optimizer hints

  • How locks work and how to diagnose with real world examples.

  • Tuning of adaptive parallel execution statements

  • Query optimisation when we don’t have licences for the Tuning and Diagnostics pack

  • Analytical Functions, Pivot

  • Food explanation of a analytic queries – current offerings are basic concepts. Best guide I’ve found is by an online blogger.

  • SQL macros

  • SQL Macros (OK, it’s hard to google topics in books)

  • Tips on SQL / Plsql embedded in C/C++ and backend code for web screens

  • The MODEL clause, practical examples, beyond the toy examples on the Internet today ; real-life tuning case studies, similar to Jonathan Lewis’ blog

  • Dynamic sql topics

  • Get insights on all autonomous db decisions, example checking sql execution plan, and others.

  • advanced sql

  • 19c sql enhancements

  • Capacity Planning, both Storage and CPU

  • Index Creation Technique 

  • I would be happy to read in your book what other cool things I can do with pattern matching that I didn’t think of yet 😉

  • I am missing a comprehensive test case “How to efficiently design your DWH and load the data into it?”. More than 99% of Oracle developers do not know how make the best of their DWH environment. I would like also to be part of that topic as a contributor, because I would like to push myself to the limits and also to learn something new.

  • the separation of duties and tasks for cdb/pdb. running multiple PDBs efficiently, or running without access to cdb or parameters..
    hmm.. Proper Table Design… and proper system design, the two biggest hurdles to efficient systems… imho.

  • Best practices for very high DML tables (very high inserts, updates,selects)

  • Using Oracle to generate an send emails.

  • Haven’t looked at books

  • Security and Json Parsing 

What enhancement to Oracle SQL would you really like SQL Santa to give you for Christmas?

  • CREATE ASSERTION 

  • Asserts

  • SQL assertions, boolean data type

  • View parameters and a Boolean data type

  • Parametrized Views.

  • Certification vouchers on various Oracle SQL 

  • Model clause

  • Features just like as SQL Macro in lower Versions of SQL.

  • That with statements can be referenced like tables in sql

  • More support for graph related queries and some machine learning functions may be.

  • It had always been true parameterized views, but apparently SQL macros do this.  Unfortunately, I don’t have access to a 20c database yet.  I’m sure there are other little things that amount to syntactic sugar, but I can’t think of specific examples right now.

  • To be expert of oracle sql

  • no more context switching between PL/SQL and SQL 

  • Performance tuning steps

  • same sql enhancements apply to SQL Server and Oracle and NoSQL

  • Restricting duplicate records in a table 

  • history….like an up arrow in linux to show and possible execute a previous SQL

  • Analytical Function

  • group by using a column number

  • no triggers.
    select sqr(4) ; (e.g. select without a from-clause like in PG)

  • Power to read execution plans in a go 🙂

  • Less redundancy

  • Oracle should implement the TOP clause as we know it from MS SQL server. It is much more convenient than using rownum.

  • I’d love to win the hard copy of the book. I have the electronic version but maybe I’ll be the winner! Hoping 

  • Not sure

  • One statement to clone userid + ALL its priviledges

  • Materiallized View with parameters

  • Better error descriptions! Ones that are actually meaningful.